July 21, 2008 at 10:20 am
Hi everyone,
I've got a question about natural and non natural hierarchies in SSAS.
I've created a non natural hierarchy based on a Time dimension.
Since SP2, SSAS explain that non natural hierarchies has worse performances than naturals...
... But, when I try to transform my hierarchy in a natural one, the values are wrong.
My time Table has the following structure :
pk_time int (eg : 20060101)
id_year int (eg : 2006)
id_month int (eg : 1)
id_week int (eg : 1)
id_quarter int (eg : 1)
and the following label columns (lb_year, lb_month, lb_week, ...).
The key dimension is the primary key of the Time Table
and my non natural hierarchy is the following :
First Level : Year
Second Level : Quarter
Third Level : Month
Fourth Level : Week
Fifht Level : Day
When I browser this hierarchy I've got the right values, witch mean :
2006
Q 1
Q 2
April
May
June
Week X,
Week ....
But, when I define the relationship between the Levels attributes, in order to build natural hierachy I've got something like this :
2006
Q1
2007
Q2
Q3
2008
Q4
...
Witch doesn't have any sense....
Does anybody have encounter the same trouble, and found something to solve it?
Thanks a lot by Advance
Best regards
Sébastien
July 21, 2008 at 1:28 pm
You have attribute keys that are not unique. Every year has the same 4 quarters. Since you named your Quarters Q1, Q2, Q3, and Q4, the same 4 quarters are repeated.
You need to make your attribute keys unique. You can do this either by using a field that is unique (many people will include the year in the quarter description "2008 -Q1") or you can add the year to the attribute key of the quarter - this is why attribute keys can include multiple fields.
Also, search on your favorite search engine for "BIDS Helper" and download it. It is free. It will give you a utility called the "Dimension Health Check" that will verify your attribute relationships for you.
July 22, 2008 at 2:06 am
Yes, you're absolutely right !!!
Thanks again for your answer, I'll try it right now !
Sébastien
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply