Datamart Design for multiple Dimensions containing History
3 posters
Page 1 of 1
Datamart Design for multiple Dimensions containing History
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.
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
Re: Datamart Design for multiple Dimensions containing History
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.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Re: Datamart Design for multiple Dimensions containing History
A Type 2 dimension does not create a many-to-many relationship. The fact should have type 2 FKs.
Re: Datamart Design for multiple Dimensions containing History
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?ngalemmo wrote:A Type 2 dimension does not create a many-to-many relationship. The fact should have type 2 FKs.
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
Re: Datamart Design for multiple Dimensions containing History
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.
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.
Re: Datamart Design for multiple Dimensions containing History
Thanks to each of you for sharing your perspective on how to address this.
JeannetteSpence- Posts : 3
Join date : 2013-08-26
Similar topics
» Modeling multiple multivalued dimensions and other design questions
» Design all dimensions as conformed dimensions
» Multiple Datamart Architecture
» Tracking history of multiple SCD type 2 attributes
» Fact table to track history on 4 dimensions?
» Design all dimensions as conformed dimensions
» Multiple Datamart Architecture
» Tracking history of multiple SCD type 2 attributes
» Fact table to track history on 4 dimensions?
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|