Introduction

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?

Workaround

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]:

Conclusion

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

Advantages

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

Drawbacks

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