question

vicatcu avatar image
vicatcu asked

Ragged Hierarchy Dimension Definition

I'm just getting started on a Data Warehousing and OLAP project. I'm at the stage of trying to define my dimension tables. For the sake of an example, I'll ask a question about an Products dimension. The dimension might have a hierarchical structure like the following (painstakingly detailed ascii art): AllProducts | +------------------+------------------------+ | | Furniture Electronics | | +---------+----------+ +------------------------+----------+ | | | | | Desks Chairs Beds Home Office | | +-----------+-----------+ +-----------+-----------+ | | | | Radios TVs Fax Copier | | +------+------+ +------+------+ | | | | Battery Electric Color B&W Meta-data associated with the dimension might be name and description. What is the best way to define the fields in the dimension table to encode the hierarchical information for each product defined on the Products dimension?
schema
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Scot Hauder avatar image
Scot Hauder answered
Here is one way ![alt text][1] The trick is to propagate the leaf level up the hierarchy (here it is the (ProductName). This basically creates a regular user hierarchy out of the ragged. Then change the hierarchy level property HideMemberIf to OnlyChildWithParentName. Do this for ProductName and any higher subcategory levels but leave the highest level Category (here it is ProductCategory) set to Never. ![alt text][2] [1]: /upfiles/grid.PNG [2]: /upfiles/xls.PNG You can also achieve this with Parent/Child hierarchy but there are several downfalls: poor performance, only one parent/child hierarchy can exist in a dimension, more difficult to work with etc so I generally shy away from them
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

You can't seem them, but the columns above are ProductCategory, ProductSubcategory1, ProductSubcategory2, ProductSubcategory3, ProductSubcategory4. I set the attribute relationships like this: Product key->ProductName->ProductSubcategory4->ProductSubcategory3->ProductSubcategory2->ProductSubcategory1->ProductCategory
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.