I'm trying to design a cube in SSAS 2008 for data whose base unit is Member-Month, meaning that for each member there is demographic data, certain other indicators that may change, and dollar amounts paid per month. I feel like I need to include MemberID and MonthKey in the same dimension, but this seems like the wrong approach in the case when I just want to see dollars by month. If so, would I put both a Month Key and the Member-Month Key in the fact table? Or use a surrogate key in the Member-Month dimension, but include the MemberID and MonthKey in it? It seems wrong to have Month in two different places (Member-Month and Date). Any help is appreciated!
A:
If I understand your question correctly, you should create a member table, month (or dates) table and a fact table that has FactKey,MemberKey,MonthKey,Amount columns in it. Then you may create Member and Month dimensions.
You should not add month data to the member dimension. The relation between month and member dimensions is already built by the fact table which has all data required for cross dimension data existance.
This is a very simple design problem and easily get implemented with SSAS.
Hope this help.
orka
2010-08-26 11:49:39
Thanks for your response. The complicating issue is that there are also fields that need to be recorded at the member month level. They can change from month to month. Since they are at the lowest level of granularity, do I need a separate dimension for each one?
Eric
2010-08-27 21:36:28
Do you mean Slowly Changing Dimension? If you are not aware the SCD concept I strongly suggest you to read [Slowly Changing Dimensions](http://en.wikipedia.org/wiki/Slowly_changing_dimension) wikipedia article. You can also find many resources related to the subject easily.
orka
2010-08-28 01:19:06