I have two attributes that I'm really not sure where is the best place to place:'Account Open Date' and 'Account Close Date'. In my model, I have [Dim Accounts] as a dimension and [F transact] as a fact table containing accounts transactions. An account can have many transactions, so the dates have different cardinality than the transactions.
- I thought to put the dates in the Accounts dimension, but this led to problems: difficulties in calculations related to those dates--like if I want to get the transactions of the accounts that opened in the 4th quarter of 2012, or to get the difference between the date of last transaction and the account opening date, and so on. In other words I can't benefit from the Date dimension and the hierarchies it contains.
- So I though about placing those dates in the fact table, but what made me hesitate is that the granularity of those dates is higher than the fact table, so there will be a lot of redundancy.
- AhmadBethlehem, Palestine
Ahmad is thinking about this problem in the right way: how will my choice affect my ability to study the facts?
It turns out that (1) this is not an either/or question, and (2) granularity is not an issue.
Dates that Describe Important Dimensions
 |
Image licensed via Creative Commons 2.0
from Patrick Hoesley
|
First, though, lets look at what happens if the dates are only represented as foreign keys in the fact table:
If the dates are not stored in the dimension, the open and close date are only associated with the Account dimension through the fact table. The fact table only has records when transactions occur. So it becomes harder to find a list of open accounts, or to find the set of accounts that were active as of a particular date.
An additional factless fact table may help here, but it is far more simple to store the dates in the dimension.
Date as Attribute vs Outrigger
If plan to represent the dates in your dimension table, you have two choices. You can model the dates themselves as attributes, or you can model a pair of day keys in your account dimension. Either approach is acceptable.
The first option does not expose the richness of your full day dimension for analytic usage, but it may be simpler to use for many business questions. Other questions (like your quarterly example) will require a bit more technical knowledge, but most BI tools help with this.
The second option transforms your star into a (partial) snowflake. The day dimension becomes known as an "outrigger" when it connects to your account dimension. This allows you to explicitly leverage all the attributes of your Day dimension. The cost is some extra joins, which may be confusing and may also disrupt star-join optimization.
Making the correct choice here involves balancing several perspectives:
- The business view and usability
- The capabilities of your BI software front end
- The capabilities of your DBMS back end software
Day Keys in the Fact Table
Having said all that, it is also useful to represent at least one of these dates in the fact table. The account open date may be a good dimensional perspective for the analysis of facts.
As you observed, this date has different cardinality than the transactions. The account open date for an account remains constant, even if it has dozens of transactions in your fact table. But the fact that it has low cardinality should not stop you from choosing it as a major dimension as your star!
Your account transaction fact table may have a pair of day keys -- one for the date the account was opened, and one for the date of the transaction.
If you choose to do this, the account dimension itself should include the open date. The outrigger solution is not necessary since your fact table has full access to the Day dimension.
Note that I do not recommend this for your account closed date, because that date changes. Storing it as a key for every transaction against an account would require a lot of updates to fact table rows once the account becomes closed.
More Information
I've touched on this topic in the past. In particular, see this post:
- Dates in Dimension Tables (5/12/2011)
Although I edited it out of Ahmad's question, he also cited an issue surrounding the use of NULL for accounts that do not have a closed date. On that topic, see this recent post:
- Avoid NULL in Dimensions (1/7/2013)
Support this Blog
I maintain this blog in my spare time. If you find it helpful, you can help support it by picking up a copy of my book: Star Schema: The Complete Reference.
Use the links on this blog to get a copy of this or any of the other recommended books, and you will be helping to keep this effort going.
