Right Tool for Data Mapping in Relation to Business Intelligence

Question

When you are working as a BI developer you also need to create a data mapping between dimensional modeling and source system. What kinda application to visualize a data mapping?

Answer 1

As with your other questions, this one could be more specific. However, I will assume that you want to document mappings arising from analysis work.

An ETL tool will actually load the data, and the mappings tend to take the form of wiring loom diagrams that show the mappings graphically. If you have relatively simple transformations the internal self-documenting capabilities of the ETL tool may suffice for this.

However, this tends to break down on more complex transformations or in situations where not all of the logic is done within the tool. The more complex the transformation the more likely it is to happen. In many cases a decisions is made to do all the transformation work in stored procedure code and the ETL tooling is largely used for extraction, logging and load control tasks.

If you are mapping for documentation or specification purposes, people often use spreadsheets if no other tool is available. However, this really carries an assumption of an isomorphic 1:1 mapping from source to target and does a poor job of capturing and documenting complex logic.

Some CASE tools have meta models for mapping available. I've done this with Sparx Enterprise Architect, although we had to build a custom metamodel for this. Some tools have this feature out of the box.

Finally (and this is my preferred approach) a functional specification for the ETL can be written up as a technical document. This can describe mappings and detailed specifications for the logic. In the case of a system with complex logic in the ETL a spec document is by far the best way to do this.