Fact with eff / exp dates referencing dimension with eff / exp dates

I have found myself really taking advantage of the fact that I can take nightly snapshots of data to track change and trending in values from a system that otherwise doesn't provide that type of historical information. But I foresee a slight stumbling block.

To give a real scenario: A system I'm pulling data from tells how many "Positions" are open for a given "Staffing Order" at that very moment, but offers no historical change data, only real time. To compensate for this and in order to provide trending and historical information, my ETL routine takes snapshots every night and records the number of "Positions" exist at that point in time.

This "Position" count doesn't change terribly often and there may be many "Staffing Orders" over a long period of time. Given this, I decided to model my fact table like this:

fact_staffing_order_positions

• id
  
• staffing_order_id
  
• position_count
  
• row_effective_date
  
• row_expiration_date
  

This works well, however my "Staffing Order" dimension ALSO has row_effective_date and row_expiration_date fields. The fact row is now also dependent upon changes in the dimension. That being said, if I had a fact row with an expiration date of 9999-12-31, yet a change comes in to my dimension "Staffing Order" which causes my dimension entry to expire and thus should also cause my fact's row_expiration_date to expire on the same date. Additionally, a new entry should be added to my dimension and to my fact.

This example assumes I have just 1 dimension, what if I had multiple? The issue only compounds. Any expiration of a dimension would (should) cause an expiration of the existing row and the subsequent addition of a new row.

Any suggestions or corrections to this methodology would be appreciated.

I assume you are talking about a Type 2 dimension. The purpose of a Type 2 dimension is to relate a fact to the version of the dimension at the time the fact occurred (or added to the fact table).

However, this does not mean you cannot associate the fact with any version of the dimension. The most common case is reporting the fact using the current state of the dimension. There are a lot of ways you can implement this. The original method was to do a self-join on the dimension to locate the desired version of the dimension. This method would let you access any version.

Since 'current' is the most common another technique is to store both Type 1 and 2 keys in the fact and either implement 2 dimensions or store the Type 1 key as an attribute in the Type 2 table and create a view that filters for the current version and returns the Type 1 key.

Thank you for your reply. This makes sense (for the most part) with the exception of the "self-join" you reference.

You are correct, that I'm referring to a Type 2 dimension. I have my durable key stored in the table along with my primary key, so I can access the dimension from either angle. I've been under the impression that accessing the dimension through its natural/durable key is looked down upon and thus have avoided that technique. Given that I am still new to modeling, I'm trying to not be so liberal with my own ideas until I have a deep understanding and comfort with this type of modeling and can understand all implications of my moves. If you're saying that I could store the dimensions primary key along with the natural key of my dimensional entry, then I could do exactly as you suggest and create a view which returns the correct dimension entry for a given date.

Please let me know if I appear to not understand something you had suggested.

Thank you again!

Yes, you use the natural key in the self-join.