A Parent Child Dimension for an Unbalanced Hierarchy

August 4, 2008

I found myself revisiting the Parent Child dimension quite unexpectedly.


I’m working on a project which involves the division of the city to different statistical areas. The division of the city into these different statistical areas happens once every 10 years or so, according to the changes in population tracked by the municipality itself and the government in the Census of Population and Housing.


Up till now, my cube had in it just the statistical areas distribution from one Census onwards and so had just one sort of distribution I needed to connect to in my cube. However, now I was asked to add historical data which included in it the distribution that existed before the current Census. The current distribution of the city into areas is either similar to the previous version, or at times a split of a certain areas into two new areas (for instance). If it stopped there, we could say that the lowest granularity level I need to connect to in the fact tables in my SSAS project are always those of the last Census. Unfortunately enough, that isn’t the case. I was also handed a draft of the statistical areas forecasted for the Census in one year’s time. This time around, there were areas which were some times identical, sometimes a merger of other areas and at other times a division of other areas. So, in effect, I didn’t have anymore a certain Census for which the areas in it consisted for the lowest granularity. I needed to find a new way to define a granularity level for this dimension in my cube. I guess from the title you can infer the solution…


My statistical area is now no longer the simple hierarchy it used to be. Instead, I found it will be a different hierarchy for each Census conducted. Also, no hierarchy is necessarily of a lower granularity than another. In the new dimension – which is a Parent Child dimension – the areas would now be defined not just by their ID number, but also by the Census year to which they belong. The Parent Child dimension will now also have a fictitious level for each division or unification that happened. Considering that each area consists of 3 digits, and that I have areas before the Census of 1995, after it and after that of 2009, my Parent Child dimension could look somewhat like this:



 


While looking at the above, we have to remember that a new member can be created at each level of the Parent Child Dimension. I no longer have a balanced hierarchy, as each Census may lead to a new member created based on, or stemming from, one of the existing children. This is due to the fact that each area may become a parent to two or more areas (the lower branch of the tree) and a new area may become the parent of two or more merged areas (the upper branch of the tree).


Before, my Parent Child Dimension came from the fact I needed to connect to data at non – leaf levels, but I knew I had 4 levels in my organizational hierarchy, now I’m not sure how many levels I have in my Parent Child Dimension. This may mean that I may not be able to manipulate my Dimension to show in Report Builder.


Now all that’s left is to redefine the keys in the fact tables in my cube, do checks to see it was done properly, redefine the Panorama reports…

Add comment
facebook linkedin twitter email

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

4 comments

  1. SteveAugust 23, 2010 ב 22:10

    Hi,
    We are having an issue with what we think is an unbalanced hiearchy. We have segmentation levels 1 2 and 3. 1 has say A and B, 2 has A and B and 3 does not have B. In RB as soon as we add level 3 to the report we drop B out of the report. This does not happen when building in the cube. The cube maintains B.

    Thanks for any direction youy can offer.

    Steve

    Reply
  2. Ella MaschiachAugust 26, 2010 ב 9:35

    Hi Steve,

    If you’re talking about Report Builder 1.0, then please look at the following posts which detail what to do:
    http://blogs.microsoft.co.il/blogs/barbaro/archive/2007/11/21/creating-a-report-model-based-on-an-analysis-cube.aspx
    http://blogs.microsoft.co.il/blogs/barbaro/archive/2008/05/15/creating-a-report-model-based-on-an-analysis-cube-pros-and-cons.aspx
    I would say that a Parent Child Hierarchy through Report Builder 1.0 isn’t very easy or simple to implement.

    All the best,
    Ella

    Reply
  3. SteveAugust 26, 2010 ב 19:58

    Thanks for response Ella, I should have clarified that we are using RB 2.0. Will the article still apply.
    Steve

    Reply
  4. Ella MaschiachAugust 31, 2010 ב 14:08

    Hi Steve,

    I’m sorry to say I haven’t got any personal experience of working with a Parent Child dimension in Report Builder 2.0. I found this article though, which I hope might help:
    http://www.sqlserverbiblog.com/Trackback.aspx?guid=2a5fac2d-1c77-4583-908c-ccec71ae2e2d

    All the best,
    Ella

    Reply