Track and control of dimension and fact tables

Hi !

We need an overview of all dimension and fact tables developed/under development.
An overview should:
- help us when designing new ones/alter existing in response to user needs
- help us spotting missing tables
- help us spotting dimensional modelling errors
- other use ?

Such an overview may need to be made in "layers" with different detail levels.
We can import metadata from our production DB and pivot them in excel, but there are many (metadata) information elements not residing in the DB.

I hope someone can gice some good advice and point to examples of practises / examples (typical Bus Matrix etc)

Regards Ole

Sounds like all you need is a data model. ERwin or ER-Studio will help accomplish all of your requirements.

And some gap analysis of the model against requirements.

Thanks for your comments !

I was focusing (or really "defocusing"..) on a more aggregated overview, ie along the lines of a more expanded bus-matrix.
It then would be easier to "shop" dimensions when preparing for new fact tables.
Anyone know of any examples ?

Regards Ole

The bus matrix is a very high level view of the dimensional model - effectively it is just an inventory of the facts (rows) & dimensions (columns), showing the relationships (i.e. - what dimensions apply to each fact). It rarely has enough detail to answer some of the questions you mentioned in your original post.

It can be helpful for communication/analysis - for example, when looking at new facts, it allows you to quickly assess which ones require new dimensions compared with exisiting dimensions - this can be valuable when prioritizing development work.

In all my solutions I always include a logical data model document, which presents the dimensional model in greater detail, including hierarchies, SCD rules etc.