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:
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
An previous week it was :
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.