Monday, March 26, 2012

Ragged Hierarchy / Unbalanced Hierarchy - whats the difference and how do i process it ?

Q1) what is the difference between a ragged hierarchy and an unbalanced hierarchy ?

Q2)

I have a structure for items in the store

Each item can be classified into

Category

SubCategory

Class

Most items go down all three levels

but some are only classified into the top two categories

and a few are only classified in the Category classification

i have tried to create a Hierarchy for this

but when i review the results in the browser, it will only show me the results of the items that are classified to the bottom level - it does NOT show me the ones that are classified in only the top one or two levels

according to BOL

"To make a parent-child hierarchy ragged, use the SkippedLevelsColumn property of the parent attribute in the hierarchy to support the ragged structure."

but i have found that this has been removed from AS2005

(http://msdn2.microsoft.com/en-us/library/ms143229.aspx)

Skipped levels in parent-child hierarchies

The SkippedLevelsColumnproperty, which indicates how many levels to skip when creating a member under a parent in a parent-child hierarchy, is no longer supported.

so - how can this be done ?

Hello! To me a ragged or unbalanced hierarchy is the same.

You control this on each level in the user hierarchy in the dimension editor. Click on a level in the hiearchy and check the property "HideMemberIf"

Parent-Child dimensions is a different case because they have unnamed, recursive levels. Ragged and Unbalanced Hierarchies have named levels but with missing members or values in each level in the user hierarchy.

HTH

Thomas Ivarsson

|||

Thomas

thx for this

but can you please explain a bit more

i have set the HideMemberIf property on both the SubCategory and Class description to NoName - and this hides the blank entries (as expected)

But

when i put attribute relationships between the SubCategory and Category and between the SubCategory and Class onto the hierarchy (in the Hierarchies and Levels panel), then the original problem arises in that the entries are not shown in the cube browser

m

|||

Hello again. I have checked the Geography dimension and the Geography-hierarchy in the Adventure Works sample cube that is part of the installation of SQL Server 2005.

In this hierarchy you have the levels Country-StateProvince-City-PostalCode all with attribute relations to the parent and a HideMemberIf-property set to NoName on the StateProvince level. United Kingdom have no members in the StateProvince-level, like you can see in the browser. All other countries have. This example works as you expect so you must have another problem in your dimension.

The HideMemberIf-property will not be supported in the next release of SQL Server, according to books on line.

HTH

Thomas Ivarsson

No comments:

Post a Comment