Tall Narrow Dimension - A Good Idea?

Hi,

I have a Category Dimension which can have multiple levels in it. Also, in the Contract Fact the Contract can be mapped to category at any level and in most of the cases to more than one dimension.

That is -

Category Code Category Name Category Level Category Parent Code
========= ========== ========= ==============
C1 Category 1 1 C1
C2 Category 2 2 C1
C3 Category 3 3 C2
C4 Category 4 3 C2
C5 Category 5 4 C3
C6 Category 6 4 C4
C7 Category 7 4 C4

I am trying to model the Category Dimension and im not sure how should i be creating a Category Dimension in the Tall Narrow fashion or should i try to flatten it.

That is -

Tall narrow Fashion -

Category Key Category Code Category Name Category Level Category Parent Code
========= ========== ========= ========= ==============
1 C1 Category 1 1 C1
2 C2 Category 2 2 C1
3 C3 Category 3 3 C2
4 C4 Category 4 3 C2
5 C5 Category 5 4 C3
6 C6 Category 6 4 C4
7 C7 Category 7 4 C4

The Flattened Fashion -

Category Key Category Level 1 Category Level 2 Category Level 3 Category Level 4
======== =========== ========== =========== ===========
1 Category 1 Category 2 Category 3 Category 5
1 Category 1 Category 2 Category 4 Category 6
1 Category 1 Category 2 Category 4 Category 7

Considering the fact that the Category can be mapped at any level to the Contract Fact and to add to it, this is a single model that will be rolled out to several clients, where some client will have 4 levels, some will have 5 levels and some will have 7 levels, wanted to understand what is the correct process to go about it.

Regards.
V.S.Purushothaman.

An unstructured recursive hierarchy is often modeled using an exploded hierarchy bridge table and a dimension with one row per category.
This gives you the greatest flexibility to support any possible hierarchy.

However, end-users often prefer a flat hierarchy. So you could model it flat with some fixed maximum number of levels.

Appreciate the quick response.

I was also leaning on towards modelling it as a flat hierarchy with a maximum number of levels. But the problem i have is that in our process here, a Contract can get mapped to member at any level and not the leaf level always.

To give an example it is possible -
 1. Lets assume the category hierarchy is Computers with Laptops and Desktops as children, then Keyboard, Mouse, CPU and Monitors as child to Desktop
 2. Now i can have Contract 1 for Laptops - So Contract gets mapped to Laptop which is a 2nd level member
 3. I have Contract 2 for just Monitors - So Contract 2 gets mapped to Monitors which is a 3rd level member
 4. Usually to handle this we create a snowflaked dimension and multiple facts
 5. That is Category dimension snowflaked to 3 tables and two contract facts, With Category Level 2 mapped to Fact 1 (Contract 1 and Laptop as a record) and Category Level 3 mapped to Fact 2.(Contract 2 and Monitors as a record)


Now creating a snowflaked dimension table when we are not sure how many levels it can have is the real problem there.

I am not even sure if its a good idea to come up with a Single Dimensional Model that can be applied to all the customers, basically the idea of Dimensional Model as a product kinda concept is such a good idea. The one size fits all kinda approach usually creates more problems than it solves.

Regards
V.S.Purushothaman

Have a row for every possible category. Higher level categories would have null values for lower (child) levels in the hierarchy.

So you mean bringing each member in the hierarchy to the lowest level.  Understood. That could be a good option.

I was also going through this link and was wondering if your suggestion here could be an option too?

Ragged Hierarchy with Bridge Table