Dimensional modelling in UML with EA

[Kiwilee]

I have an associate who is creating an MIS solution including the creation of a data warehouse.  In order to do this, he is planning to carry out some dimensional modelling.  Does any one have any idea if EA could be used to support this.

[thomaskilian]

AFAIK stars in datawarehouses are created with simple ERD tools (I may be wrong since it's a long time I had contact with that). Filling the data is done by some machine permanently watching the data stream and generating tons of redundant data in order to improve performance when searching for certain aspects. IOW I could imagine to use EA for the data modelling and mapping the stars to classes should also be feasible.

[jaimeglz]

Hi all,

The atomic level datawarehouse is a regular, vanilla-flavored third normal form database (albeit a consolidated, historical, and correcly grained one).

Mutidimensional data marts are modeled as stars, which are "quasi" third normal form data models (they would be third normal form if the dimensions did not contain so much redundant data).

Both atomic level DW and data marts can be modeled as you would any regular RBMS (relational) table diagram, and EA works great for generating the create tables and the indexes.

For futher reference on this topic see Ralph Kimball's book The Datawarehouse Toolkit.

Cheers,

Jaime

[moses]

Hi, I guess I am coming a little late but :
we too have decided to use EA for dimensional modelling, we decided to model each object (such as a dimension, a level, an atribute) as a class and draw links between them.
This helps the process of dimensional modelling a lot, but requires some work to create the db scheme later - we ended up developping an uml-profile with required tags for each of these objects and some rules for the DBA to create the structure. (this would be described by importing the ddl scheme).
We are in the begining only, but I think it works fine . I wonder what you did at the end ?
regards, moses

[ceatley]

  I have little or no ideas about data wahehouses et al, but I found a web site that has some very good material on databases and UML
Take a look at www.agiledata.org.  Perhaps you will be able to use their ideas as a base for what you need to do.

[Janez]

Modeling dimensions and stars with EA is straightforward. But does anybody has an idea or experience in how to use EA to define the essential part of the model- the data warehouse MAPPINGs?

Some examples are:

- source systems (tables, files, …)  to relational data warehouse
- relational DW to dimensional model

Complex algorithms (many to many, cleansing, filtering, …) should be supported. Top down approach is needed- both high level mapping (object to object) and atomic level mapping (attribute to attribute). Full traceability of mappings should be supported both interactively and in the generated documentation.

Has anybody suggestion or experience on how to do that in the best way?

Janez

PS:
I have tried a wide variety of possibilities:

- modeling mappings using associations:
good: nice diagrams
bad: time consuming, could not generate satisfactory documentation (a lot of paper, little information);
- using “transformational” classes(for object level mapping) and methods(for attribute-to-attribute mapping)
good: ability for top-down approach; promising documentation
bad: did not find the convenient way to define particular input and output attributes for atomic mappings; mapping trace from source-to-target is lost?
- Using activities (object level) and actions (attribute level)
good: nice pictures
bad: low integrity and redundancy of the model (at least one action and two relationships).
- Tool extension: extended repository (the new Mappings table) and visual tool.
Good: works exactly the desired way, but
Bad: redundant to what already available in the EA, risky to manipulate repository at the table level; basic tool should be self-containing.

You can imagine the investigation took some time…