February 17, 2010 at 1:09 pm
I have a cube with dimension for time, products, customers, etc.
To increase query performance, I have begun to create hierarchies and defining attribute relationships. I started with the time dimension setting a simple hierarchy of Year-Month-Day. I setup the attribute relationships accordingly. I immediately encounted duplicates keys when deploying so I used column binding within the KeyColumn to define Month as (Year/Month) with a NameColumn set to Month.
Deploys fine now, however now if I want to filter (within BIDS browser or Excel pivottable) by month I get duplicate months for every year which I am sure is caused by the keybinding. This problem is exceeding worse if I more to more complex dimensions such as product and customer.
Without attribute relationships, I can filter independantly by year and/or month. Anyone know how I can keep that same functionality with the implementation of hierarchies and attribute relationships?
February 19, 2010 at 6:27 am
Regarding the set-up of hierarchies and attribute relationships for your time dimension, make sure you use the concatenation of year and month as primary key for month. For example: 200911 instead of just 11. Otherwise SSAS won't know to which year the column 'November' maps, as each year has a month November.
The same goes for quarters and weeks.
This is what I do for my time dimension:
I have a surrogate key like '20091102', which stands for the second of november 2009. The property AttributeHierarchyVisible is set to False.
My lowest granularity is the Calendar_Date which has the form of '2009-11-02 00:00:00', which is of the datatype datetime. I also have a Calendar_Date_Descr, which is in fact a user friendly way of showing the date, for example 'Nov 11 2009'. In my time dimension I only use the Calendar_Date attribute, with the Name property set to Calendar_Date_Descr and the key property to Calendar_Date. The order is defined by the key.
The same is true for Calendar Year Month (Descr), Calendar Year Quarter (Descr) en Calendar Year (Descr).
Then I create the attribute relationships shown in the attachment.
Next I create my hierarchy with the Description attributes. (see the screenshot in attachment)
This should work for the time dimension.
For regular dimensions, just make sure your attribute relationships are correct. This means two things:
* Start from your SK and go up the highest level from your hierarchy. (eg: SK_Location -> City -> Region -> Country -> Territory)
* If you create attribute relationships, make sure you have natural hierarchies.
An unnatural hierarchy is for example:
SK_Material -> Material_Name -> Size
as the Size Small can be related to multiple materials. It isn't unique, while in a natural hierarchy the members are unique through the whole hierarchy.
I hope this helps...
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply