Simulating Parent-Child dimension with data on leaf members only


Parent-child dimensions

When SQL Server introduced Parent-Child dimensions with Analysis Services 2000, it came with different options regarding members associated data, also called Data Members.

This option could be set via Members With Data property.

Excerpt from SSAS 2000 BOL: "The Members With Data property indicates whether nonleaf members of a parent-child dimension are allowed to have associated fact table data. By default, nonleaf members are not allowed to have associated fact table data, so the property is initially set to Leaf members only. The related Data Member Caption Template property controls the names of data members when the Members With Data property is set to Nonleaf data visible. For more information about these properties, see Properties Pane (Cube Editor Data View) and Properties Pane (Dimension Editor Data View)".

There were three possible values for Members With Data property: Leaf members only, Nonleaf data visible and Nonleaf data hidden.

Analysis Services 2005 changes

With the coming of SSAS 2005, Leaf members only value for Parent-Child dimensions isn’t implemented anymore.

This shouldn’t be a real issue, as Nonleaf data hidden can be used as a workaround to give the illusion only leaf members have data.

This workaround is satisfying for most of the situations, except for the following case.

When Leaf members only is required

Let’s consider Organization Parent-Child dimension in SSAS 2005 Adventure Works DW sample database.

Let’s assume we have data only for leaf members (i.e. non leaf members are aggregating leaf ones; even if this is not the exact business meaning in the sample).

If we execute the following UPDATE CUBE command at [European Operations] member level:

UPDATE CUBE [Organization] SET ([Organization].[AdventureWorks Cycle].[European Operations])=60

We expect to have an equal distribution of 60 on [France] and [Germany], 30 each. Instead, we get 20 on [France], and 20 on [Germany] (but still an aggregated value of 60).

Where are the missing 20?

Remember our dimension is set with Non leaf data hidden option set (as a replacement to the disappeared Leaf member only option). This means member [European Operations] has its own data, so aggregated [European Operations] has in reality three children (and not two, as shown in query result). That’s where the missing 20 is gone: on [European Operations] Data Member (20+20+20=60).

This is a serious issue in a Budget and Planning context.

One could say the problem shouldn’t occur if the cube is pre-filled with fact data for all Leaf members. An UPDATE CUBE command combined with the USE_WEIGHTED_ALLOCATION option would then distribute data as expected.

The point here is we don’t always have fact data. If a company creates a new division (a new member in the dimension), it will start a budget from scratch, i.e. with no fact data.

So what can we do?


Obviously, we must avoid Parent-Child dimensions, and come back to standard ones.

The hierarchy level HideMemberIf property can be used to give the illusion of an unbalanced structure (as with Parent-Child).

Data source

The difficult thing is to get the dimension data source fit the new dimension type.

Our Organization Parent-Child dimension data source had a structure based on a key field (OrganizationKey) and a parent field (ParentOrganizationKey).

With a standard dimension, we need a table structure with as many fields as levels.

This structure change can be handled quite easily if we have control on the data source.

This is not always the case:

  • Data source can already exist when this change is required.
  • Parent-Child table structure must be kept for compatibility and usability reasons.

On the fly transformation

There are different techniques to transform a Parent-Child structure to a hierarchical one.

Many stored procedures have been published on the internet by T-SQL gurus. One of these stored procedures could be executed by a SQL Server Integration Services (SSIS) package before dimension processing.

For some reasons, we may need/want to keep all the logic at SSAS level, without relying on external packages. In this case, our standard dimension data source can be linked to a named query in the Data Source View. Our named query will be defined using a recursive query, which is possible since SSQL 2005 with the use of Common Table Expressions.

Using such recursive query on Parent-Child table structure will result in the following structure and dataset for our Organization dimension data source:

If we analyze in detail the transformed table structure, we notice it’s organized in groups of three fields, one group corresponding to a level:

  • k_Level<N>: OrganizationKey for level <N>, the member key.
  • n_Level<N>: OrganizationName for level <N>, the member name.
  • PercentageOfOwnership_Level<N>: for level <N>, a member property.

For leaf members with a smaller depth, values are duplicated on descendant levels.

Dimension design

Dimension design is straight forward. Each group of fields will be used to create an attribute. Then, attributes will be used to build a user hierarchy.

Finally, the HideMemberIf property will be set for each hierarchy level with value OnlyChildWithParentName. This will make those descendant (see before) levels invisible to the client.

Using it

If we browse the new dimension, it will look the same as the original Parent-Child dimension.

If we execute the same UPDATE CUBE command at [European Operations] member level:

UPDATE CUBE [Organization] SET ([Organization].[AdventureWorks Cycle].[European Operations])=60

We get the expected result, with value 60 equally distributed on [France] and [Germany]:


The lack of a Leaf members only value for Members With Data property in SSAS 2005 can be solved with unbalanced standard dimensions.


  • Query performance: it is recommended to avoid Parent-Child dimension, as values are not aggregated.


  • Process performance: depending on the dimension data source size and depth, recursive query execution can be time consuming when dimension needs to be processed.
  • Display: SSAS doesn’t properly estimate member children count for unbalanced standard dimensions. This can be seen when browsing dimension. On screen shot below, highlighted leaf members not being at the deepest level have an associated [+] symbol, indicating they can be drilled-down (which is not the case).

This entry was posted in Analysis Services. Bookmark the permalink.

7 Responses to Simulating Parent-Child dimension with data on leaf members only

  1. 旺立 says:


  2. Unknown says: Compaq dv2000 Battery Compaq dv2200 Battery Compaq pavilion dv2000 Battery Compaq pavilion dv6000 Battery Dell d5318 Battery Dell e1405 Battery Dell gw240 Battery Dell inspiron 630m Battery Dell inspiron 640m Battery Dell inspiron 8000 Battery Dell inspiron 8100 Battery Dell inspiron 8200 Battery Dell m1210 Battery Dell pu563 Battery Dell rn873 Battery Dell RU030 Battery Dell td175 Battery Dell TK330 Battery Dell w953g Battery Dell WG317 Battery Gateway m360 Battery Gateway m460 Battery Gateway m680 Battery Hp dv9500 Battery Hp dv9600 Battery Hp dv9700 Battery Gateway 8msb Battery Gateway 8msbg Battery Hp zt1000 Battery Hp zt3000 Battery Ibm 02k6928 Battery Ibm 02k7055 Battery Ibm 02k7072 Battery Ibm thinkpad r40 Battery Ibm thinkpad t30 Battery Dell inspiron 6400 Adapter Dell latitude d620 Adapter Dell latitude d630 Adapter Dell latitude d820 Adapter Dell d820 Adapter Hp dv4000 Adapter HP pavilion dv9700 Battery HP pavilion dv9500 Battery Liteon presario 1200 Adapter Liteon presario 1600 Adapter

  3. Unknown says: Compaq presario 1200 Battery Compaq presario 1600 Battery Compaq presario 1800 Battery Dell 312-009 Battery Dell 4e369 Battery Dell d5318 Battery Dell e1405 Battery Dell gw240 Battery Dell HG307 Battery Dell inspiron 1000 Battery Dell inspiron 1100 series Battery Dell inspiron 1150 Battery Dell inspiron 1200 Battery Dell inspiron 1300 Battery Dell inspiron 1521 Battery Dell inspiron 4000 Battery Dell inspiron 8000 Battery Dell inspiron 8100 Battery Dell inspiron 8200 Battery Dell inspiron 9300 Battery Dell inspiron 9400 Battery Dell inspiron b120 Battery Hp nc6000 Battery Hp nc8000 Battery Hp nx9500 Battery Hp pavilion dv8100 Battery Hp pavilion dv8200 Battery Dell rn873 Battery Dell RU030 Battery Dell TK330 Battery Dell xps m140 Battery Gateway 6500632 Battery Hp 500 Battery Hp 520 Battery Mitac bp-8050(p) Battery Mitac bp-8089p Battery Ibm thinkpad t42 Battery Ibm thinkpad t60 Battery Ibm thinkpad x41 Battery Ibm thinkpad z60m Battery Ibm thinkpad z60t Battery Ibm thinkpad z61e Battery Ibm thinkpad z61m Battery Ibm thinkpad z61t Battery Mitac 8050 Battery

  4. Unknown says: Acer extensa 5210 battery Acer extensa 5220 battery Acer extensa 5620g battery Acer travelmate 5710 battery Acer travelmate 5720g battery Acer travelmate 7520g battery Asus a22-700 battery Asus a22-p701 battery Asus eee pc 1000 battery Asus eee pc 701 battery Dell latitude d410 battery Dell latitude d420 battery Dell latitude d430 battery Dell latitude d610 battery Dell latitude d810 battery Dell vostro 1310 battery Dell vostro 1320 battery Dell vostro 1510 battery Dell vostro 1520 battery Dell vostro 2510 battery Hp pavilion dv2133 battery Hp pavilion dv3000 battery Hp pavilion dv3500 battery Hp pavilion n5130 battery Hp pavilion n5150 battery Hp pavilion n5170 battery Ibm thinkpad X40 battery Ibm thinkpad x41 battery Ibm thinkpad x60 battery Ibm thinkpad x60s battery Ibm thinkpad z60t battery Ibm thinkpad z61t battery Msi bty-s11 battery Msi bty-s12 battery Sony vgp-bps5 battery Sony vgp-bps5a battery Toshiba pa3178u-1brs battery hp 510 adapter hp 530 adapter sony vgp-bpl9 battery sony vgp-bps10 battery sony vgp-bps8 battery sony vgp-bps8a battery sony vgp-bps9 battery

  5. hfgh says:


  6. Sam Kane says:

    Here are this and some other articles on Parent-Child Dimension:

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s