Novice Question on Dimension Hierarchy

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?

Only if your BI Query tool requires it

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.

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

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_ID	PRODUCT_NAME	BRAND	CATEGORY_ID	CATEGORY_NAME	SUBCATEGORY_ID	SUBCATEGORY	Level
1			1	Ski			3
2			2	Bike			3
3			1	Ski	3	Base	2
4			1	Ski	4	Sticks	2
5			2	Bike	5	Mountain	2
6			2	Bike	6	Helmet	2
7	Pro Ski Cambers	Dynastar	1	Ski	3	Base	1
8	Pro Ski Pole	Hexel	1	Ski	4	Sticks	1
9	Hyper Havoc Full	Audi	2	Bike	5	Mountain	1
10	Bern Watts	Hexcel	2	Bike	6	Helmet	1

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.

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?

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.

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.