Creating Base Column Mapping by Script (ColumnMaps)

[former_member636440]

Hello PowerDesigner Community,

i am currently developing a PowerShell script (later into PD Script) that should be able to duplicate a mapping for a table, but the new mapping is based on a new datasource.

This is what I have so far:

## ToDo before running the script
### !All relevant Models must be opened
### !New datasource must be already created in the target model
### !Change and validate the initial variable names
## !Initiate Variable Names
$targetModelName = 'Test_Stage'
$duplicatedTableName = 'StageTable47'
$oldSourceModelName = 'Test_Source'
$oldDatasourceName = 'DataSource_Source2Stage'
$newSourceModelName = 'Duplicated_Test_Source'
$newDatasourceName = 'DataSource_Duplicated_Src2Stage'

## Get PowerDesigner window as new object
$powerDesigner = New-Object -com powerdesigner.application

## Select the target model as an object, also select the duplicated table where the mapping should be changed
$targetModel = $powerDesigner.Models | Where Code -eq $targetModelName
$duplicatedTable = $targetModel.Tables | Where Code -eq $duplicatedTableName

## Select the new and old souce models to set or get attributes for the mappings
$newSourceModel = $powerDesigner.Models | Where Code -eq $newSourceModelName
$oldSourceModel = $powerDesigner.Models | Where Code -eq $oldSourceModelName

## Select the new and old datasources
$datasourceFrom = $targetModel.DataSources | Where Code -eq 
$oldDatasourceName$datasourceTo = $targetModel.DataSources | Where Code -eq $newDatasourceName

# Select the old mapping (which got duplicated too)
$oldMapping = $datasourceFrom.GetMapping($duplicatedTable)

# Create a new mapping on the duplicated table
$newMapping = $datasourceTo.CreateMapping($duplicatedTable)

# Set some attributes to the new mapping
$newMapping.SetAttribute('Name', -join($oldMapping.Name,'_duplicated'))
$newMapping.SetAttribute('Code', -join($oldMapping.Code,'_duplicated'))

# Add the new source table (from the new datasource) to the new mapping
$newSourceTable = $newSourceModel.Tables | Where Code -eq $oldMapping.MappedTo
$newMapping.AddSource($newSourceTable)

My question is: How can I create a new Base Column Mapping now so it is exactly like the old mapping but with the new datasource as the parent.

I tried out to copy the old base column mapping but this didnt changed the parent

I also tried out to use CreateNew() Method onto the ColumnMaps Collection of $newMapping but this resulted in an empty Column in the BaseColumnMapping and this is a read only property which I cant change after the creation.

I need the method (constructor) to create a base column mapping with a specified column.

Thanks,

Stephan

Update:

Some Additional Information:

This is what the current Mapping Editor looks like after using the script:

[GeorgeMcGeachie]

I wrote a script to import mappings recently. Like you, it took me a while to figure out how to create object and sub-object mappings (the documentation could be more helpful, but I've seen far worse documentation for data modelling tools).

My approach was to use the CreateMapping method to add a table mapping to a Data Source, then add a column mapping to the table mapping, again using the CreateMapping method.

[former_member670100]

Hi Stephan, George,

Did you try ExcelImport for this and is this likely to work?

I'm not a programmer and don't have easy access to one yet (unfortunately).

I was trying to do the same via ExcelImport. I was planning to:
- first, import Data Sources
- then, mappings (Entity to Table)
- then, SubObjectMappings (Attribute to Column)

I already got stuck at data sources though.

The data in my excel (1 line):

When I import I get: 1 warning has been logged into the output window.

Opening document C:\Users\<user>\Desktop\export DS.xls Importing data from table
DS 16:59:53
Error: Could not find object with Name = LDM_2 1 object(s) updated
Closing document export DS.xls 16:59:54

LDM_2 was open in my workspace and is a Logical Data Model.

The Data source object is created, with Name, Model Type and Access Type set properly, but not Model (as log indicates).

Problem seems to be related to the model tab. If I try to add a model manually, it is pointing to "physical model" type (instead of model type Logical Data Model). This seems to be standard behavior (I can reproduce).

Hence my thought: maybe I'm going about this the wrong way.

Is excel import a dead end for what I'm trying to do?

Regards, Raf

[GeorgeMcGeachie]

I suspect you'll need to tell the Excel import to serach for referenced objects in other models - see the import options

[former_member636440]

I think I found the answer myself:

#Add a column mapping in the new table Mapping for each column mapping in the old table mapping

foreach ($oldColMapping in $oldTableMapping.ColumnMaps) 
{
$targetColumn = $duplicatedTable.Columns | Where Code -eq $oldColMapping.Column.Code 
Write-Host 'Create new column mapping for' $targetColumn.Code
$newColumnMapping = $DatasourceTo.CreateMapping($targetColumn)
$newColumnMapping.MappedTo = $oldColMapping.MappedTo  
}

If someone has a better solution or this way will produce errors in some cases, please let me know. My first tests worked for my little example.

The underlying understanding problem was the following: You can use the CreateMapping Method on datasources for tables and on tables for columns. This was hard for me to see in the documentation.