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.

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.

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

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?

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.

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