Create hierarchy classifed according the date RRS feed

  • Question

  • hello,

       For one of my client, i need to create a hierarchy in dimension that display the attribut dépends on the dates, for example :

    Hierarchy Society :

    Society  Date of creation     Date of   modification   Date of end
    A 01/01/2012 01/03/2013    null
    B 01/01/2012         null    null
    C 01/01/2013        null    null

    D                    01/01/2012             null                             01/01/2013

    i need to have in my hierarchy :

     2012 -> Society A,B and D

    2013 -> Society A,B and C

    Someone can help pls?.


    Tuesday, September 27, 2016 9:53 AM


  • Hi Adghar,

    In this scenario, firstly you need to pivot your table and add a Year column. It should look like:

    Society     Year

    A             2012
    A             2013
    B             2012
    B             2013
    C             2013
    D             2012

    If you are in multidimensional model, since one year contains multiple Societies, and one society can appear under multiple years, you should define many to many relationship. Then you can use create a fact dimension to achieve your goal. Please refer to links below:

    Defining a Fact Relationship
    Degenerate Dimensions

    If you are in Tabular model, you can directly define a hierarchy with Year and Society columns. Then you will see duplicated values on Society level. Please refer to AdventureWorks sample:


    Simon Hou
    TechNet Community Support

    Friday, September 30, 2016 9:33 AM