Novice Question on Dimension Hierarchy

View previous topic View next topic Go down

Novice Question on Dimension Hierarchy

Post  indkal on Sat Oct 11, 2014 4:01 pm

I have a product dimension with attributes:
Dimension Key
ProductID
ProductDescription
CategoryID
CategoryDescription
SubCategoryID
SubCategoryDescription

SubCategory has 1:N relationship with Category.
Category has 1:N relationship with Product.

Do I need to add a Level attribute to show the hierarchy between product, category and subcategory?

indkal

Posts : 4
Join date : 2014-10-11

View user profile

Back to top Go down

Re: Novice Question on Dimension Hierarchy

Post  nick_white on Mon Oct 13, 2014 3:00 am

Only if your BI Query tool requires it

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: Novice Question on Dimension Hierarchy

Post  indkal on Sun Oct 19, 2014 9:05 pm

Ok, I am trying to understand how the Level attribute aligns with other attributes of the dimension. For the base level (Level value 1) I understand all attributes will have values. I am not clear on the next levels. So for the next level (Level value 2) there will be separate rows with only those attributes values that belong to this level. Now if SubCategory is the middle level (Level value 2), then the table rows will only have values for SubCategory and Category? Similarly for Level value 3, only rows with Category values. Am I correct in my understanding that as you move up the hierarchy, not all base attributes will have values in corresponding rows? Any other example on the Web too that shows a table with level attribute values would also be useful.

Thanks.



indkal

Posts : 4
Join date : 2014-10-11

View user profile

Back to top Go down

Re: Novice Question on Dimension Hierarchy

Post  nick_white on Mon Oct 20, 2014 2:32 am

Ok - to keep things simple, I'll assume that every Product has a parent sub-category and every sub-category has a parent category.

Each row in the table will have every column populated. Each row has a 'grain' of Product and includes details of its parent sub-category and its grandparent category. The sub-category and category information is obviously duplicated multiple times within the table but that's fine - a dimensional model is not meant to be in 3NF


nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: Novice Question on Dimension Hierarchy

Post  indkal on Tue Oct 21, 2014 8:57 am

I understand what you are saying. My question was on the inclusion of level attribute in the dimension table. I have a sample dimension table below with Level attribute.

PRODUCT_IDPRODUCT_NAMEBRANDCATEGORY_IDCATEGORY_NAMESUBCATEGORY_IDSUBCATEGORYLevel
11Ski3
22Bike3
31Ski3Base2
41Ski4Sticks2
52Bike5Mountain2
62Bike6Helmet2
7Pro Ski CambersDynastar1Ski3Base1
8Pro Ski PoleHexel1Ski4Sticks1
9Hyper Havoc FullAudi2Bike5Mountain1
10Bern WattsHexcel2Bike6Helmet1
In the above table, my question is whether rows 1 through 6 are populated correctly with regard to Level attribute values of 2 and 3.

Thanks.

indkal

Posts : 4
Join date : 2014-10-11

View user profile

Back to top Go down

Re: Novice Question on Dimension Hierarchy

Post  nick_white on Tue Oct 21, 2014 10:28 am

nick_white wrote:Each row in the table will have every column populated

Not sure you have understood what I'm saying as not every column in your table is populated for every row.
You should delete rows 1-6 from your table as you don't need them. You then also don't need the Level column
Your fact table should have an FK that links to Product (not Category or Sub-category) - as the Product implies the Category and Sub-category and you get them from the relevant product record in the Product Dim.

Does this help at all?

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: Novice Question on Dimension Hierarchy

Post  indkal on Tue Oct 21, 2014 12:16 pm

Ok, so one does not need a separate Level attribute. I understand that the fact table will link with the Product dimension table through Product_ID attribute. The reason for my confusion was that I had seen in a book a dimension table which had a separate level attribute and some of the higher level attribute values (level 2 and 3 as in my example) rows did not have all the columns populated.

Thanks.

indkal

Posts : 4
Join date : 2014-10-11

View user profile

Back to top Go down

Re: Novice Question on Dimension Hierarchy

Post  nick_white on Wed Oct 22, 2014 8:40 am

Possibly the examples you saw were related to ragged hierarchies, skip-level hierarchies and/or n-level hierarchies (which use a bridge table). These can start getting complicated and as long as you have standard hierarchy with a fixed number of levels and no gaps then you don't need to worry about them.
As long as every product has a single parent sub-category which in turn has a single parent category then you should be fine with the design we've discussed.

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: Novice Question on Dimension Hierarchy

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