Mapping Editor vs. Data Movement Model

[former_member217396]

Hi,

we are in process of mapping source to target tables (and attributes of course).

I'm asking myselft, if we really should use the DMM or maybe the simeple mapping editor will do the job ...

My biggest concern is, we have sometimes "logic" inbetween, like for example:

1. TargetTable1.Column1 is a concatenation of SourceTable1.Column1, SourceTable2.Column2, Sequence.NEXTVAL

2. TargetTable1.Column2 is populated from a lookup table LookupTable1.Column2 based on SourceTable1.Column2 (it's a conversion INT -> CHAR)

3. There are some calculations for some INT attributes, like -,+,/ and * operations

Can I utilize the mapping editor to model all of this, or shall we really stict to DMM.

DMM is quite complitated and close to the logic of Replication Server. For example, I was trying to calculate a new attribute, and was forced to pick a data type for this attribute... but there are noly typical Replication Server data types available....

So back to my question: is there a rule, defining how far can I go using the simple Mapping Editor?

I found this post:

But again ... this would indicate, we have to switch to DMM.

Regards,

Rafal

[Former Member]

The Mapping Editor was designed to define source-to-target mappings for an entire model from one or more source models.  In the mapping editor, each target table has one or more mappings associated with it.  If you select the mapping, and select the "Properties" button, you see you can create a comment, or use Notes (Description/Annotation) for each one as needed.

This is a great way to capture mappings without needing to understand how the data gets moved and transformed into the target, and you can record things at a relatively granular enough level to create good data movement documentation.

The Data Movement Model was designed to define a logical flow of data throughout all data movement technologies used in your enterprise, and provide the steps and stages (essentially a roadmap) of all the data movement.  It is more complex to use, and provides a richer result (you do not only know where the data came from, you know how it got there and exactly how it was transformed and handled along the way).  The DMM adds advantages of being able to create mappings at a stage level, and provide even richer documentation on each stage as the data is moved and transformed.

As a recommendation, I usually state the following:

If you need to document source-to-target for a given warehouse, and do not need to understand all the details of the data movement, use the mapping editor.  You can always convert it to a DMM later on if more detail is needed.  The Mapping Editor is easier to use, simpler to understand, and provides sufficient documentation to be able to use impact analysis successfully when changing any data system.

If you need to understand the data movement at a more intimate level, or need richer documentation on the source to target mappings than the Mapping Editor can provide, then move to the Data Movement Model.  The higher investment in capturing the details will be worth it in the better understanding you will have in data movement.  This is not easy to do, so I usually recommend reviewing the DMM capabilities only when the Mapping Editor cannot provide the needed level of documentation.

[former_member185199]

Hello David,

You stated: "You can always convert it to a DMM later on if more detail is needed."

Could you give some more hints on how to do that ? Sounds interesting for me

regards

dj

[former_member217396]

Hi Dirk,

when you're in the Data Movement Model, go to Tools -> Convert Mapping to ETL Wizard ...

We have tested it some time ago... it needs some major adjustments after the generation. The main issue for us was the fact, the join relations have not been created as Joins but as Data Calculators with the joins conditions as "conditions"...

But you can give it a try, maybe it's OK for you.

Cheers,

Rafal

[former_member217396]

Any thoughts ... ?