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.