Hierarchy and grain in a Dimension

I currently have a hierarchical structure that I am trying to model in a Category dimension (category, sub-category, sub-sub-category, sub-sub-sub-category). The Category hierarchy has four levels so I put all four levels as attributes in my dimension. My first question is whether it is ok to have records in the category dimension that don't have sub-categories because then a record would have a different level of grain than another?

I also have an Item dimension and I need to create another dimension/fact table where item can be associated to one or more records in the Category dimension. In this situation an Item could be related to a category (no sub-catgegories) in one instance and also be related to a sub-sub-category in another instance. If the answer to my first question is no, how would I go about modelling this situation?

I have put a sample below if it helps:

Code:

DimCategory
ID  Category  Sub-Category  Sub-sub-category  Sub-sub-sub-category
1        1
2        1            1.1
3        1            1.1                  1.1.1
4        1            1.1                  1.1.1                    1.1.1.1
5        1            1.2
6        2

DimItem
ID  ItemCode
10    ABC

Related Table???
ItemID  CategoryID
  10            3
  10            5
  10            6

Thank you in advance!

Hi adventr

It sounds to me like you need to combine two techniques:

• Parent-Child - for your DimCategory
  
• Many-to-Many relationship - for your Related Table

Parent-Child:
Each row represents a node in the tree with a single Key column and a Parent Key, e.g.

Key	Parent Key
1	NULL
1.1	1
1.1.1	1.1

etc ...

Usually these dimensions are not too complex to build as your raw data is probably already in this format. The challenge then is presenting it to your users, as the output columns needed are variable. I'd use the SSAS Parent-Child dimension design, presented via Excel Pivot Tables.

Many-to-Many relationship:
This is also known as bridge tables - each row represents a link between one dimension and another. Again this is usually easier to construct than present - the challenge is avoiding double-aggregating your fact data. I'd use the SSAS Many-to-Many relationship which avoids that risk.

Good luck!
Mike

I would make the category values descend to the lowest level i.e. the child record may stop at category, but would be repeated until sub-sub-sub-category.
You can then hide these levels within SSAS if the parent is the same as the child.

Thank you for your responses.