Hierarchy and grain in a Dimension

View previous topic View next topic Go down

Hierarchy and grain in a Dimension

Post  adventr on Fri Mar 30, 2012 11:52 am

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!


Last edited by adventr on Fri Mar 30, 2012 11:57 am; edited 1 time in total (Reason for editing : formatting)

adventr

Posts : 3
Join date : 2012-02-06

View user profile

Back to top Go down

Re: Hierarchy and grain in a Dimension

Post  Mike Honey on Mon Apr 02, 2012 1:03 am

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.

KeyParent Key
1NULL
1.11
1.1.11.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
avatar
Mike Honey

Posts : 185
Join date : 2010-08-04
Location : Melbourne, Australia

View user profile http://www.mangasolutions.com

Back to top Go down

Re: Hierarchy and grain in a Dimension

Post  John Simon on Mon Apr 02, 2012 1:58 am

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.

John Simon

Posts : 186
Join date : 2009-05-29
Location : Melbourne, Australia

View user profile http://jsimonbi.wordpress.com

Back to top Go down

Re: Hierarchy and grain in a Dimension

Post  adventr on Mon Apr 02, 2012 10:29 am

Thank you for your responses.

adventr

Posts : 3
Join date : 2012-02-06

View user profile

Back to top Go down

Re: Hierarchy and grain in a Dimension

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum