Datamart Design for multiple Dimensions containing History

View previous topic View next topic Go down

Datamart Design for multiple Dimensions containing History

Post  JeannetteSpence on Mon Aug 26, 2013 11:42 am

We are working on a datamart design.    There are multiple dimensions for which there is a desire to keep the history, with start and end dates.     Some users will want to join the facts to the current row of a dimension and others will want to join to the row that was in effect the month the fact was created.

In one sense, we have a many to many relationship from Fact to Dimension.   But we also have a Many to One relationship if we use Current Row Indicator or Accounting Month to distinguish which dimension row we want to use.  

How would you depict the relationship in the ERD?      

Thank you for your guidance.

JeannetteSpence

Posts : 3
Join date : 2013-08-26

View user profile

Back to top Go down

Re: Datamart Design for multiple Dimensions containing History

Post  BoxesAndLines on Mon Aug 26, 2013 2:03 pm

You present it as a one-to-many relationship from the dimension to the fact. That relationship shows you the point in time values. Getting the current row is normally accomplished via a self join which is not reflected in the data model.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Datamart Design for multiple Dimensions containing History

Post  ngalemmo on Mon Aug 26, 2013 4:01 pm

A Type 2 dimension does not create a many-to-many relationship. The fact should have type 2 FKs.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Datamart Design for multiple Dimensions containing History

Post  JeannetteSpence on Mon Aug 26, 2013 6:14 pm

ngalemmo wrote:A Type 2 dimension does not create a many-to-many relationship.  The fact should have type 2 FKs.
Can you expand on this please? The dimension has multiple rows for the business key that track when changes occur, with start and end dates and surrogate keys. Are you saying that is not adequate criteria to be described as a type 2 dimension?

We have considered FKs to both the current and point in time dimension rows, either with multiple fact tables (one for current and one for point in time) or multiple FKs on a single fact table. In either case, using the dimension's surrogate key. Is that what you are suggesting? On the other hand, say we have the computing power to support joining on the business key (itself an integer column) and date or the business key and current row indicator. What would be the downside to this approach?





JeannetteSpence

Posts : 3
Join date : 2013-08-26

View user profile

Back to top Go down

Re: Datamart Design for multiple Dimensions containing History

Post  ngalemmo on Mon Aug 26, 2013 11:32 pm

A type 2 dimension has a single surrogate primary key representing the version of a business key's row.  A fact using such a dimension carries that key.  It is a one to many relationship.

Using a compound key is not a matter of computing power.  It is a matter of consistency and simplicity.  The idea of implementing a type 2 dimension is to capture the state of a dimension at a point in time and to relate fact rows to that state.  Using a compound key does not do that.  It allows you to relate a fact row to any version of a dimension row.  This can lead to inconsistencies and errors as the results are dependent on a properly formed query.  That is not the case with a proper type 2 implementation.

That is also the point of using surrogate keys in the first place.  To provide a stable, permanent relationship between rows in the data warehouse.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Datamart Design for multiple Dimensions containing History

Post  JeannetteSpence on Tue Aug 27, 2013 8:18 pm

Thanks to each of you for sharing your perspective on how to address this.

JeannetteSpence

Posts : 3
Join date : 2013-08-26

View user profile

Back to top Go down

Re: Datamart Design for multiple Dimensions containing History

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