The Parent – Child Dimension

October 9, 2007


A Parent – Child Dimension is a special sort of dimension, to be used in case: 




  1. You have data even for non – leaf members. For instance, you may have set – up costs allocated for the Category level (the higher level), but profit data for your Product level (the lower level).

  2. You have an unbalanced hierarchy. The classic example for this might be the Geographical Hierarchy which at times might look like: Country > State > City, and at other times like Country > City. For an international company trying to track its worldwide business, the hierarchy in the US would be: US > New York > New York.  Its business in the UK would look like: UK > London. (See illustration below).    
     

The Adventure Works example shows us the use of this for the Employee Dimension which incorporates both aspects: you have a ragged hierarchy for employees as each manager might not necessarily have the same number of level of workers under him \ her. You also have managers which have sales attributed to them, not only to the leaf – level employees.


In my own SSAS project, I found I needed the parent child dimension to describe my organizational hierarchy. This stemmed mostly from the fact I had budget allocations for different levels of the organization (not necessarily the team level unit which is the lowest level). Using the parent child dimension also enabled me to delete “unnecessary” levels which were created in the MF tables but were unnecessary in my SQL tables (these were mostly business units with no name which only existed in the MF tables as the MF tables only allow for the usual rigid hierarchies).


Last but not least, parent child dimensions are the only kind of dimension that you can write-enable, so that may also be a consideration for implementing them.


The Parent Child Dimension will have at least 3 columns:


1. The parent key


2. The child key (the key for the member of the current level)


3. The child name (the name for the member of the current level)


Both the parent key column and the child key column must be of the same data type. For the highest level (in my case, the entire organization), we may give a parent key which either equals itself, a non existent value key or null.


You can add a Parent Child Dimension to your cube, using the dimension Wizard. You define the key column to the dimension as being the child key column with it’s name being the child name column. In a later screen of the wizard, you check the square for “this dimension contains a parent – child relationship between attributes” and the wizard should recognize that the relevant column is the parent key column, giving you a preview for the result.


When you use a hierarchy with a parent child dimension, you can use the HideMemberIf property that specifies when members will be hidden in a ragged hierarchy. For instance, you can choose if to hide a member if its name is identical to that of its parent.


For more details about how to incorporate a parent child dimension in your Analysis Services project please refer to the excellent MSDN article on the subject.  

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>

*

16 comments

  1. Lester GarciaFebruary 8, 2008 ב 2:07

    I have been trying without a lot of success of using a parent-child hierarchy to group other dependent dimensions.
    Suppose that we have a Category-Product-Sales(facts) relationships where Category is an unbalanced tree.
    When the category hierarchy is dragged to the viewer and any of the product attributes, the “incomplete” paths unable the correspondent product items to show.
    This might be another additional issue that we can face using parent child dimensions

    Reply
  2. AlexApril 8, 2008 ב 2:09

    I am not sure if you can help me out here, but do you know how I can represent the following:

    PARENT — DESCRIPTION

     CHILD — CHILD DESCRIPTION — LEVEL

    I am new to SSAS, i can display the Parent and children but I can’t see description….and level

    both fields are attributes.

    Thanks

    Aleks

    Reply
  3. Ella MaschiachApril 8, 2008 ב 11:58

    Hi Alex,

    Well first of all, I would suggest your table looks a bit more like:

    Parent_key, Child_key, Child_description, Level.

    You don’t need a parent description. Lets say we are talking about a table for your employees – managers, like for instance table “DimEmployee” in the Adventure Works samples. The manger still has a name for himself, only not as a parent, but when he is a child of someone else (which is why I don’t bother with a Parent_description). Even with your top level employee, for instance your CEO, you would find a line in your table which would look like:

    Null, 1, CEO, 1

    As your CEO has no one working above him. For all the rest, Parent_key would have the Employee ID of their manager.
    After adding the table to your DSV you can define a Parent – Child Dimension out of it with the Child_key as the key to the dimension and the Parent_key as the parent of the dimension (under the “Usage” property or even during the building of the dimension itself). You may then drag the Level column into the dimension as an attribute. Description can be added with the “NameColumn” property of an attribute (just pick from the list the relevant column in the table for the description).
    If you need anymore help, please feel free to contact me through the “Contact” button at the top of the page.

    All the best,
    Ella

    Reply
  4. SarahOctober 14, 2008 ב 5:17

    Hi

    we have the parent-child employee situation. However, an employee may move in the hierarchy. so we have a parent child table like this:

    Parent_key, Child_key, Child_description, Level, effectivestartdate, effectiveenddate.

    Is there any way of generating the cube so that it can select the valid parent-child records for the hierarchy for the fact table?

    Reply
  5. Ella MaschiachOctober 24, 2008 ב 1:12

    Hi Sarah,

    Sorry for the late reply but I have only now come back from vacation…
    In any case, what you describe sounds very much like the “Employee” dimension in Adventure Works – which is Parent Child and Slowly Changing Dimension Type 2 as well. Look into it and you will see how your dimension should be realized. All in all, what you did sounds very much like what they did.

    All the best,
    Ella

    Reply
  6. DanieleNovember 12, 2008 ב 12:39

    Hi Ella,

    i came up to this post in your blog googling about ragged hierarchy in Ms SQL Server 2005…
    to be more precise i’m trying to understand if in AdventureWorks there is an example of ragged hierarchy…
    the Employee Dimension in AdventureWorks (to which you refer in your post) is not an unbalanced hierarchy more than a ragged hierarchy?
    Probably i’m wrong… i’m an absolute beginner in BI..just followed a university course in BI
    I’m racking my brain but i couldn’t find any other example of ragged hierarchy than the geographical one…

    Thank you in advance

    Daniele

    Reply
  7. Ella MaschiachNovember 13, 2008 ב 15:16

    Hi Daniele,

    Glad you dropped in for a visit 🙂

    Yes, I guess you are correct and the example of the Employee Dimension in Adventure Works is more of an unbalanced dimension than that of a ragged one. It took me a while to find an example of a ragged hierarchy but thankfully enough Ajit Singh had just the example from the world of finance! I admit I found it hard to find another example except for that one though…

    In any case, there’s no need to doubt yourself so much, it sounds like you have quite a good understanding of the material at hand.

    Hope that helped.

    All the best,

    Ella

    Reply
  8. ChicagoBobJanuary 22, 2009 ב 18:45

    Did SQL 2008 fix the issue with the HideMemberIf property is set to
    OnlyChildWithParentName ?
    I went through a very iterative process and found that it DOES in fact work,
    but ONLY if you set the property at the LOWEST level of the Hierarchy. If you
    have a 3-level hierarchy and you set it for the middle level, it does not
    work.
    Any body hear what the resolution was (or is going to be) from Microsoft?
    Thanks

    Reply
  9. Dipesh KarkiMay 19, 2009 ב 10:17

    Hello Ella,

    Thankyou for you post really incisive.

    I have a problem with parent-child dimension labeling perhaps you can suggest me some solution. I have analysis project that do analysis of sales of cosmetics in AS2005. And the product are in parent-child hierarchy. For example:
    Color Cosmetics -> Lip->Lipstick, Lip Gloss
    -> Eye -> Mascasre, Eyeshadow

    I have my dimensnion of format,

    product_id, product_name, parent_product_id

    Everything is fine but in deployment . The dimesnion hierarchy has labels like: Level 1 for Color Cosmetics, Level 2 for Lip, Eye and Level 3 for Lipsticks, Mascara etc.

    Is there way to relabel these dimesnion hierarchy when they appear in cube. For example instead Level 1, it appear “Major Segment”, and level 2 as “Minor Segement” and so on.

    I look forward to your response.

    Reply
  10. Ella MaschiachMay 20, 2009 ב 9:34

    Hi Dipesh,

    If you stand on your parent attribute and go to its Naming Template property, you should be able to manipulate the name of each level.

    All the best,
    Ella

    Reply
  11. jhoiJuly 11, 2010 ב 4:33

    may i know more about the theories related to broad dimensions of a child and adolescent development concerning physical, linguistic, cognitive and socio-emotional aspects…

    thanks..

    from: Opulencia, Tezajonican E.
    2nd year BEED..
    Colegio de San Juan de Letran-calamba..

    Reply
  12. Ella MaschiachJuly 19, 2010 ב 10:21

    Sorry Jhoi, I think you’re in the wrong blog…

    Reply
  13. ArmandoSeptember 17, 2011 ב 15:44

    Hi,

    i am working with a parent-child dimension in which the childs have several parents and i have no clue of how to face the problem.

    any help would be appreciated.

    thanks.

    Reply
  14. Ella MaschiachSeptember 17, 2011 ב 15:47

    Hi Armando,

    I’m terribly sorry, but a Parent Child Dimension can only have one parent for each child as far as I know. If you’d like, you can make further inquiries at the forum:
    http://social.msdn.microsoft.com/forums/en-US/sqlanalysisservices/threads/

    All the best,
    Ella

    Reply
  15. JyoerbcOctober 31, 2011 ב 16:08

    In the example given, how can the leaf level New York can rollup to Us skipping its immediate parent: New York. In this situation when processing the dimension, getting the error : Duplicate parent keys ( one as New York and the other as US). please advice.

    Reply
  16. Ella MaschiachNovember 2, 2011 ב 12:31

    Hi Jyoerbc,

    Duplicate key means you have a duplicate key in your dimension, it is not at all connected to skipping a parent. You need a key for the State and a different key for the city. After creating those, you can choose for the parent attribute which is visibe the property of “RootMemberIf” and define it equal to “ParentIsBlankSelfOrMissing”. That means that if the name of the parent and the child are the same, you’ll only see one of them.

    Hope that helped,

    Ella

    Reply