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…