Source to Target Mapping Report

[Former Member]

Is anyone aware of a way to generate a typical "Source to Target Mapping" report from a data movement model?

I am looking for something like this:

Source Table     Source Column     Source Datatype     Transformation     Target Table     Target Column     Target Datatype

------------------     --------------------     -----------------------     --------------------     -----------------     --------------------     ----------------------

Employee          EMP_NO               VARCHAR(10)        pass-thru              D_EMP             E_NO                    VARCHAR(10)

I don't see anything out of the box that comes close to this.  It seems to support listing the contents of each object, but no way to represent the logical flow of columns represented inside a DMM.

[Former Member]

Hi all

we are not using a dmm model for that because you could do it with a mapping editor and it is easy to customize.

For the report you can then use the VB scripts that would give you all necessery information. We were able to go trough several layers of the DWH for example so to have source to system of records and then from there to dimensional model. Only thing is that you would need to be able to open those models but i dont see real reason not to

VB script would look something like this

you would have to call all objects on the Entity like Entity.Mappings and then you can call all EntityMappings.StructuralFeatureMaps. That would give you all info about two model mappings. This is done on logical models. If you need a physical thats a bit different but with help function on the table you should be able to find the links. After that you can link several models together and retrive the lineague

br

[Former Member]

do you have the VBscript(s) that produce this STM spreadsheet?

[GeorgeMcGeachie]

Did you come up with a solution in the end?

[former_member1194361]

I will let Jonathan add what he did, but from my end, no solution yet. I just gave up on this as a limitation of PD not being able to effectively store the source to target mapping. I just rely on the ETL Developers maintaining the same information in a spreadsheet, but it would have been nice to record this and report on it from PD.

[GeorgeMcGeachie]

PowerDesigner is very good at storing this information. The Data Movement Model (DMM) allows you to record the mappings for a complex architecture in a single model, the mapping editor essentially records the same metadata in a more localised fashion. Using the DMM allows database designers to focus on the PDM, and the DW architects to focus on the architecture.

What PowerDesigner lacks is a decent out-of-the-box method for extracting the information in a report or spreadsheet; however, it does provide decent mechanisms for discovering the source of individual columns via Impact and Lineage analysis. It is possible to generate ETL requirements or scripts directly from the Data Movement model or mappings inside a model, but not out of the box. I have heard whispers in the past that a generic ETL generation capability will be provided by SAP, but I haven't seen it yet. If that appeared it would be a great template for extracting the same metadata to Excel or some other format.

Some of the time I expect your ETL and BI developers could make use of the impact analysis available via the repository, using the PD client, PD viewer, or the web portal. There are improvements to portal search and impact analysis coming along in the near future, which should help.

[navinladda]

Hi georgemcgeachie

Here I am 5 years later tasked with researching how to capture, search, publish Data Lineage information for few layers of Data warehouse that our data passes through. Also need to be able to do Impact Analysis of changes to a column or table in first point zone down to the last zone consumed by end users. But PDs ability to link models is a double edged sword when it comes to capturing Data Lineage information.

I had to refresh my memory on mapping editor by looking at my old posts and even with PD 16.6.10 it is still the same issue with Mapping Editor. If the source data model is not open in workspace, all mapping information is lost. So frustrating !!

DMM is what I am reading up on now, but that seems to just re-use this same mapping editor information as I did not see a way to add mapping info using Mapping Editor in a DMM, it likes to read that from a PDM that you point to.

I was hoping to get a textual reporting of source to target for Point A to Point B like what Mapping Editor shows for all of our various zones in our environment, but now even the mapping editor export to excel of column level mapping gives an error : "Exception from HRESULT : 0x800A03EC". I can always select all and copy and paste into excel.

[GeorgeMcGeachie]

navinladda If the source model isn't open, of course the Mappings are not lost, just invisible. You can open the Source model via the Data Source 'Models' tab, but it's quicker to do it via the list of Target Models.

I'm currently looking at someone else's model extension that can export Mappings using GTL, with a view to customising it to generate content in various templating languages. It's possible that this solution will form part of a larger solution for supporting the full Data Vault methodology as described by John Giles in "The Elephant in the Fridge", and that at least some it (possibly the mappings generator and more) will be open source.

What would you like to see in the Mapping export? Would a delimited file be okay? We could allow the delimiter to be changed in the extension.

[navinladda]

At the minimum in the Mapping report, it needs to show Source schema name, Source Table Name, Source Column Name, Data Type, Length, Target table name, Column name, length, Transformation Comment.

I right now have Local Extensions at Column level with Source schema name, Source Table Name, Source Column Name, Transformation and at Table level, source table list, transformation comment.

I will look to see if I can import this data from our existing Excel Source to Target mappings as I do not want to be manually entering them.

All this because I did not see a way to import the information that Mapping Editor needs as input. I only saw the option to manually connect source to target table columns and then it can give the data out in excel, which at the moment my PD gives an error.

[GeorgeMcGeachie]

navinladda I have an existing script to import mappings from Excel that I could configure for you to match your spreadsheet

I'm going to take a look at creating a GTL-based extension to generate Mappings in CSV format

[former_member186838]

Not sure if this can be done from a reprot painter.

But it should be possible to use a VBScript to collect all these

informations and write them down into a CSV file.

There is a SourceTables collection in the DMM you can start from with all columns.

[GeorgeMcGeachie]

I was looking for something else and came across this discussion - it's actually quite simple to generate CSV files using GTL teampltes instead of VBScript

[former_member1194361]

I would like to see a solution for this as well. I used to be able to record this information and get a report of that, very easily in Erwin.

It is very hard to do both of these in PD.

PD likes to have the source model open in Workspace for it to even show previously created source to target mappings. In Erwin, it just imports the table structures from the model and then there is no connection to that model anymore. You can then see an extra Data Source tab attached to each columns definition where you see this source model table and column name to pick from. A report in CSV format is then easily created from this information.

This has been valuable information for me when supporting the Data warehouse models I created. I first lost of of this info in model conversion. And second, no easy way to create and report on this in PD 16.1 that we use.

[GeorgeMcGeachie]

In 16.5, PD remembers more details of shortcuts to objects in other models, even when those other models are closed. If and when you have both source and target models open at the same time, the shortcuts are brought up to date. Does ERwin bring the imported table structures up to date?

[former_member1194361]

No, Erwin does not keep updating the source table structures as like PD it does not create a link to the source model. It imports the table definitions at the time of linking and thats the end of it. I like that model as with PD, if the source model is not open, all the source to target mapping data is not loaded either.