Looking for a right way to document data transformations

[MarcinG]

Hello,

is there any way to document data transformation or traceability between data coming different database tables ?  One of our teams has been asked to provide traceability between columns in data warehouse and and source system.
Yes, we can show customer the ETL documentation, but it is too detailed and the customer wanted (apart of technical ETL documentation) also a high level view stating that dimension1.column1 traces back to source1.column1 and dimension1.column2 traces back to other column(s) of another source table.

Best regards
Marcin

[Uffe]

Hi Marcin,

You could import the DB schemas and add a bunch of connectors between the columns using Link to Element Feature.
I wouldn't trust a DB model modified in this way to generate the schema back out again, but you could use it to show the client or to generate documents.

HTH,


/Uffe

[Geert Bellekens]

We use an add-in I developed, the EA Mapping tool.

https://bellekens.com/ea-mapping/

We use it to map technical models (database model and message models (XSD)) to logical data model models, but you could also do database to database model mappings.

It uses tagged value to store the mappings, so it shouldn't interfere with any forward engineering you would be doing.

Geert

[MarcinG]

Geert, Uffe - thank you very much for your responses.  I like Link to Element mentioned by Uffe, but it will mean upgrading to 15 which might take us a while.  But it is certainly appreciated, and I hope that it is not just visual, but also reflected in traceability.  Geert - I will check you add-in as well.

Many thanks to you both.

Marcin

[Geert Bellekens]

Geert, Uffe - thank you very much for your responses.  I like Link to Element mentioned by Uffe, but it will mean upgrading to 15 which might take us a while.  But it is certainly appreciated, and I hope that it is not just visual, but also reflected in traceability.  Geert - I will check you add-in as well.

Many thanks to you both.

Marcin

The problem with the "link to element feature" thing is that this is represented as a link between elements. It's only in some obscure database fields that the actual features are referenced. This makes it difficult to see and use those links.
You can do them on a diagram since v12 I think. It's only in v15 that you can also do them in a matrix format, which is a major improvement.

But it will still be hard to use in document generation or searches.

Geert

[Uffe]

Yes, the Link to Element Feature thing is a fringe function that isn't fully supported in things like document generation. So if generating documents is a prime concern, you do need to take that into account.

You will need to use template fragments to pull the data out, and the storage format isn't documented. Basically the connector holds references to the features it's connected to (1 or 2) in the StyleEx property / column, where the feature(s) are referenced using their GUIDs.

I'm sure the precise details are in some post somewhere. It's been discussed in this forum several times over the years.

/Uffe