Modelling + documentation tool

View previous topic View next topic Go down

Modelling + documentation tool

Post  AtoDW on Mon Feb 01, 2010 8:14 am

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

AtoDW

Posts : 2
Join date : 2009-08-01
Location : Salerno, Italy

View user profile

Back to top Go down

Re: Modelling + documentation tool

Post  BoxesAndLines on Mon Feb 01, 2010 1:12 pm

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.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

PowerDesigner will do this:

Post  Skipjacker on Wed Nov 17, 2010 6:26 pm

"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.
avatar
Skipjacker

Posts : 16
Join date : 2010-11-10
Age : 49
Location : Baltimore, MD

View user profile

Back to top Go down

Re: Modelling + documentation tool

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum