July 21, 2012 at 2:12 am
I am facing an issue with hierachy model in SSAS. We have a model where we keep geographical data.
In this model we have one of the dimension where we store geographical hierachy. Example:
USA>Texas>Dallas>Downtown
Our datasource views are not tables but Views. We are using views to make relation.
When we process our analytics database we get latest structure of hierarchies in Hierarchy dimension. It was recently which we noticed that our hierarchy dimension has to be dynamic. Every week has a key which is unique and on each week we notice some change in hierachy structure.
Now, we want our hierachy to be shown as per change of week . If we select last week it should show last weeks hierarchy. In hierarchy it takes only latest structure. Example: in hierarch view we get data as
current week id=002
last week id =001
id childid parentid levelno
001 10 8 4
001 11 8 4
002 10 7 4
002 11 8 4
here childid is primary key of the view because of which only one entry is going in dimension and that is latest one(002). In dimension for current week it looks like
* ALL
*USA
*Texas
*Dallas
*Downtown
*Oak Lawn
*uptown
*Houston
*San Antonio
*Florida
An previous week it was :
* ALL
*USA
*Texas
*Dallas
*Downtown
*Houston
*Oak Lawn
*uptown
*San Antonio
*Florida
As we can see uptonws and Oak Lawn were in Houston and now in Dallas. When I am selecting current week i can see current hierachy but not able to see previous week.
when i select previous week it shows current hierachy.
I think this is case of SLowly changing dimension So I changed my views and made composite key with city(dallas) and area(downtown).
but all my fact views are tagged with Area.I am getting error on other cubes because now my dimension have composite key and I am not able to set key refereing to Facts data in DImension usage under cubes.facts views have real data on area sales so it will have areaid which is same of childid of above table.
I have also heard that if I use composite key like above then I will not get hierachy structure.
How to get this done with all hierachy structure of past.
July 21, 2012 at 3:53 pm
Each row of your hierarchical information is going to need to have a StartDate and an EndDate to be able to do this easily. You'll also need an "Instead Of" trigger that will change UPDATEs to INSERTs which will "end-date" the current active row and create a new one. While you're there, make the trigger reject all DELETEs.
If you use an EndDate of 99991230 (trust me... don't use 99991231. It'll mess up certain queries down the line), then you can ultimately use all of your current hierarchical queries just by adding a WHERE clause in the classic format of [font="Courier New"]WHERE @DesiredDate >= StartDate AND @DesiredDate < EndDate[/font].
{Edit} If business requirements absolutely need you to do this by week number, then you'll need to do something similar but by using StartWeek and EndWeek (inclusive) instead. Yes, you could do this using a single column called WeekNumber like you have, but it will make you're code a lot slower because you'll need to check other rows to see what's active during the given week. Well, unless you create a row for each week even if the data didn't change. That seems terribly wasteful on many fronts.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply