Snowflaking a Date dimension

Hello

I have a SQL Server 2008 R2 Cube with the following objects (only listing the relevant stuff):
1. FactDailyBalances
2. DimAccount
3. DimDate

The objects are linked as a star schema and our users make extensive use of the Date dimension when performing ad-hoc analysis of daily balances.

The Account dimension has two attributes (OpenedDate and CloseDate) which are normal SQL Server Date fields. Because Account and Date are not linked (i.e. no Snowflake), the users aren't able to make use of the many useful features of the Date dimension. In fact, they have now formally requested that they can use the Date dimension in the same way that they do with the fact data.

What is the best design for this requirements? Should I snowflake and link Account to Date? If so, what is the best implementation for this in SQL Server 2008 R2 Analysis Services?

Thanks in advance.

SQL Server Analysis Services Referenced relationships gave me exactly what I was looking for.

http://msdn.microsoft.com/en-us/library/ms175669.aspx

http://technet.microsoft.com/en-us/library/ms166704(SQL.90).aspx

Depends how you are going to leverage th date dimension. Account is a big dimension and is costly to join in order to reach the date attributes. Sometimes you do need timestamp in the dimension to express the point in time without having to make an extra join.

If date dimension is really important to the fact, having accont open/close date key directly in the fact table is more effecient approach. In that way, you can easily and more obviously aggregate the facts on date attributes without joining to account dim, or you may not have the account dim in the fact table at all.

Our fact table is a daily balances table so it does not make sense to have the account opened and closed date on the table.

Using the technique above, SSAS has effectively made the following "schema":

factBalances --> dimAccount --> dimDate

Even though there is no snowflaking (i.e. foreign key) between dimAccount and dimDate.

And, it is working very well and the users already love it :-)

Use a snowflake.

You can't really put the account open and close dates on the fact table because they change, or at least the close date will change.

The date Dimension is small, so a snowflake shouldn't cause performance issues. Yeah, you could put the relevant columns from the date dimension onto the account dimension, but account dimensions can be big - maybe one of the largest dimensions in your database - so adding a bunch of columns might not be practical.

Try it. It's a simple solution, with the lowest cost. And you can always change it if it becomes a problem.