Unanswered Slowly changing dimension

  • Saturday, July 21, 2012 7:42 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.