Skip to Content

Creating Base Column Mapping by Script (ColumnMaps)

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:

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

2 Answers

  • Sep 13 at 10:13 AM

    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.

    Add comment
    10|10000 characters needed characters exceeded

  • Sep 11 at 05:19 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded