Snowflaking a Date dimension

View previous topic View next topic Go down

Snowflaking a Date dimension

Post  amir2 on Wed Nov 21, 2012 12:47 pm

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.

amir2

Posts : 29
Join date : 2010-07-29

View user profile

Back to top Go down

Re: Snowflaking a Date dimension

Post  amir2 on Mon Dec 03, 2012 6:59 am

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

amir2

Posts : 29
Join date : 2010-07-29

View user profile

Back to top Go down

Re: Snowflaking a Date dimension

Post  hang on Mon Dec 03, 2012 4:58 pm

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.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Snowflaking a Date dimension

Post  amir2 on Tue Dec 04, 2012 5:12 am

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 :-)

amir2

Posts : 29
Join date : 2010-07-29

View user profile

Back to top Go down

Re: Snowflaking a Date dimension

Post  Jeff Smith on Tue Dec 04, 2012 1:15 pm

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.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Snowflaking a Date dimension

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum