How to Model ETL (Extract Transform Load) mapping

[frenchtoast]

Hello all

I am asked to create a UML model that has the following:

1. Staging Tables & attributes
2. ODS Tables & attributes & relationships
3. Data Mart Tables & attributes & relationships
4. Data mapping and transformation rules between Staging tables/attributes to ODS tables/attributes.
5. Data mapping and transformation rules between ODS tables/attributes to Data Mart tables/attributes.

I have created 1 to 3 using the Data Modeling provided by EA. I am not sure how to model items 4 and 5.

Your input or ideas would be appreciated, Thanks

[salayande]

Hi ,

Create association classes between the source and target and name this class source_target_transform. Identify the input_attributes and output_attributes. The methods or operations are the transformation code.

Segun

[frenchtoast]

Hi ,

Create association classes between the source and target and name this class source_target_transform. Identify the input_attributes and output_attributes. The methods or operations are the transformation code.

Segun

Good idea. How do I identify the input and output attrubutes? I tried in the Details/Attributes form but I need to retype all the attributes. I cannot link them.

What I am trying to do is to is
1. Link Source Table colums --> transformation process --> Target Table columns.
2. document the transformation rules in the transformation process
3. With the table columns linked (not typed in) I get an impact analysis tool that helps me assess the impact when a source column changes.

[salayande]

Hi,

The lowest level element that you can associate is the class. EA will not allow you to associate individual attributes.

An alternative is to model each attribute using a class and stereotype as Source_Attribute and Target_Attribute. You may then associate these using the matrix facility.

Please, kindly raise a feature request to Sparx. There is an OMG standard  UML Profile, the Common Warehouse Metadata (CWM) which Sparx may implement as an extension to the current data modelling facility.

Segun

[«Midnight»]

You could always import the CWM metamodel at OMG. Since around EA 6.5 that's been possible.

In the meantime take a look at composite structure diagrams as defined in UML 2.x. You can represent features - in this case structural features, including attributes - as 'first class' entities in these models. It is then possible to link these to other classifiers.

It isn't necessarily pretty, and involves some work, but that's what automation is for. BTW, that's the general approach the CWM practitioners' guide takes as well.

David

[salayande]

Thank you for the contribution, David. I was getting out of my depth, here.

regards

Segun