Export the Entity Mapping in VB Script from PowerDesigner

[Former Member]

Hi,

I am using PowerDesigner 16 to define the LDM, PDM and the Mapping between the Entity-Attributes. I am trying to use the Attribute.GetCollectionByKind method to first retrieve all the attributes of the specific mapping and then to export them to the Excel spreadsheet.

I am not sure if I am currently on the right track in terms of using the right Method, could someone shed some light on it?

Is there any existing code artifact that I can reuse to achieve this objective?

Thanks very much in advance.

Jeremy

[GeorgeMcGeachie]

What's the focus of your export? Do you want to start from a given model and show the sources, or start from a Data Source and show the sources and targets?

[Former Member]

Thanks for you response!

We have an external Oracle DB instance where the enterprise-level meta data is kept including the attribute mapping meta data. We are looking into PowerDesigner as the modelling design environment via which we will design the target data model (target) and import the source data model (source); Once the 'target' and 'source' are available within PowerDesigner the attribute-level mapping will be defined in PowerDesigner. I am using Tool/Mapping Editor to specify the mapping.

At current point I can enter the mapping into 'Mapping Editor' and saving it. My current challenge is 'how to export the mapping from 'Mapping Editor by the VB Script' to the external Excel sheet, and the Excel sheet will then be read by our existing ETL job in order to load the data into the Oracle DB instance that I mentioned at the beginning.

Any thoughts?

[GeorgeMcGeachie]

This sounds like a job for the Data Movement Model - there's a wizard that can create ETL Transformations in the Data Movement Model, from the source to target mappings in your PDM.  Out of the box, these can be generated to SAP Replication Server. Over a year ago, I saw a presentation from SAP, in which they mentioned that they were working on generic ETL script generation from the Data Movement Model. It's worth checking on the status of that with SAP.

[Former Member]

Thanks.

[former_member200945]

This is the code sample:

set mappings=ActiveModel.mappings

for each m in mappings

    output m.datasource.name & "\" & m.classifier.name & "<--------->" & m.model.name & "\" & m.MappedTo

next

In Mapping editor, there is existing Excel export utility.

You can take advantage of the tool.

[GeorgeMcGeachie]

Thanks for the code, Phillip. You're quite right to mention the Excel export, which will produce similar output to your code at the top level of mappings. It works the same way as the standard Excel Export option on any list, in that it only exports what you can see on the current tab. What I would like to see is an Excel export option that will export all the metadata on the current tab, from the sibling tabs, and also from any child mappings.

For example, if the selected mapping is for a table, it must export the following for the table mapping:

• Table Sources
• Mapped To
• Base Column Mapping
• Criteria
• Select
• Insert
• Update
• Delete

Then it should navigate to the column mappings and export all the metadata available, including some essential information about the source and target objects.

Many customers want this level of reporting, it's a real shame that we all have to build it ourselves.

[former_member200945]

The question is not very clear to me.

Could you provide screenshot showing the mapping?

[Former Member]

Thanks for the response, please refer to my other reply.