Modelling + documentation tool

[AtoDW]

Hi,
I am in the middle of building my second data mart (so I'm very newbie) and the relevant ETL process. Until now I have used a mix of several file to document my work, i.e.
- a doc file to gather customer needs and analyzing business process in a verbose flavour
- a worksheet to build a bus matrix, according to tips from The Data Warehouse Toolkit. In this file I am also trying to describe the source of data, though at an high abstraction level.
- a modelling tool to create the dw physical structure, made of facts and dims tables.

The deeper I go through the process, the more I feel the need for an unique tool to wrap together these different steps. Does such a tool exist? Or it's only a matter of "best practices" to follow?

Many thanks in advance for your reply.

BR
//andrea

[BoxesAndLines]

Not that I have found. If you are using ERwin, you can store most of the metadata there. That includes, data structure metadata, source to target mappings, as well as ETL logic. The bus matrix is really an abstraction of facts and dimensions. You could possibly track this using ERwin UDP's, although it's probably best tracked outside. Some of the ETL tools have metadata repositories (e.g. Informatica). They claim that all of this information can be stored within their repository. I've not seen anyone actually do this yet. The problem with storing lineage and transformation logic in Informatica is that it is always more difficult in reality to transform data from source to target than described in the requirements. For example, my requirement maybe take column A and move to column B. In reality, column A may go through multiple staging tables before it arrives at column B. Simply running a lineage report out of the ETL repository will end up causing more harm than good. Hopefully someone on the board has implemented a holistic solution.

[Skipjacker]

"a doc file to gather customer needs and analyzing business process in a verbose flavor"

PowerDesigner has a Requirement Model that works nicely for that purpose

"a worksheet to build a bus matrix, according to tips from The Data Warehouse Toolkit. In this file I am also trying to describe the source of data, though at an high abstraction level."

There's no single obvious choice for this, but there are several ways. The bus matrix should be derivable from datamodels. PD supports dependency matrices. You'd put fact tables down the side, dimensions across the top and the cells would be checked if there's a link between the two.

But often you want to do it as a planning step in advance of the model. in that case you could use a free model or conceptual data model where you don't add attributes, just big empty entities (some are dimensions and some are facts)

- a modelling tool to create the dw physical structure, made of facts and dims tables.

This is where the tool started... so you can build a PDM (Physical Data Model) for most RDBMS. Oracle, ASE, MSSQL, DB2, MYSQL... don't know if teradata is in there yet.

the best part is that you can relate them all to each other. Like:

I have this requirement -----> Which is why I need this Fact table ----> which is why I made this table in Oracle.

But it also stores the reverse links so if you click on the Oracle table in the PDM it shows the reverse path back to the requirement.

[Sponsored content]