Ella Maschiach's BI Blog

BI, Olap Technologies, Tools, System Analysis and Design

News

About Ella Maschiach

Business Inteligence

The Parent - Child Dimension

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.  

Posted: Oct 09 2007, 06:56 AM by Ella Maschiach | with 20 comment(s)
תגים:, , , , , , , , ,

Comments

israel » The Parent - Child Dimension said:

Pingback from  israel » The Parent - Child Dimension

# October 14, 2007 1:42 AM

Ella Maschiach's BI Blog said:

Well I guess congratulations are in order because I finally got around to using a report builder in my

# November 21, 2007 9:32 PM

Ella Maschiach's BI Blog said:

I have recently discovered a brand tool to help me better understand my work. It's a free tool from

# December 30, 2007 7:13 PM

Ella Maschiach's BI Blog said:

Well I thought today we'd go over visual totals in MDX and also see how they may have an impact on

# February 6, 2008 6:13 PM

Lester Garcia said:

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

# February 8, 2008 2:07 AM

Alex said:

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

# April 8, 2008 2:09 AM

Ella Maschiach said:

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

# April 8, 2008 11:58 AM

Ella Maschiach's BI Blog said:

I found myself revisiting the Parent Child dimension quite unexpectedly. I'm working on a project

# August 4, 2008 1:02 PM

Sarah said:

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?

# October 14, 2008 5:17 AM

Ella Maschiach said:

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

# October 24, 2008 1:12 AM

Daniele said:

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

# November 12, 2008 12:39 PM

Ella Maschiach said:

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

# November 13, 2008 3:16 PM

Ella Maschiach's BI Blog said:

Creating Sum for a Group with Recursion in SSRS Sum with scope A colleague of mine from work was creating

# December 6, 2008 4:09 AM

ChicagoBob said:

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

# January 22, 2009 6:45 PM

Dipesh Karki said:

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.

# May 19, 2009 10:17 AM

Ella Maschiach said:

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

# May 20, 2009 9:34 AM

Ella Maschiach's BI Blog said:

Here is the presentation I gave on the topic of the Parent Child Dimension at the BI User Group meeting

# June 29, 2009 12:19 PM

Handling Recursive Hierarchies in SQL Server using the LR Method or Kimball Helper Tables | BI Monkey said:

Pingback from  Handling Recursive Hierarchies in SQL Server using the LR Method or Kimball Helper Tables | BI Monkey

# September 18, 2009 9:59 AM

jhoi said:

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..

# July 11, 2010 4:33 AM

Ella Maschiach said:

Sorry Jhoi, I think you're in the wrong blog...

# July 19, 2010 10:21 AM
Leave a Comment

(required) 

(required) 

(optional)

(required) 


Enter the numbers above: