Product Dimension

The Product dimension has the following hierarchy.

Merchandise hierarchy, important hierarchy of the product dimension
Department > Categories > Brands > SKU (Stock keeping Units)-identifies individual product.

To simplify the above hierarchy the following example could be used. Hierarchy in Flipkart

Men (Department) > Clothing (Categories) >T-Shirts>UCB(Brand)>Blue colored shirt.

Some of the attributes of the Product dimension are as follows:

1. SKU Number (Natural Key\Business Key)
2. Product Key (PK)
3. Category Description
4. Department Description etc.

Date Dimension

Why do we need a separate date dimension, if we can insert the date from the transnational data directly,as is?

A date dimension has 365 rows ,each row representing a particular date and a Date ID. This Date ID is maintained in the fact table rather than the date from the transnational table.The date dimension would have the following attributes:

1. Day of the week

2. Week of the month

3. Month

4. Year

5. Holiday indicator

6. Season {Thanksgiving,Christmas etc.}

7. Fiscal year (Financial year of US)

8. Fiscal month

9. Fiscal Week etc.

All these attributes are very relevant at report level, and could give a whole lot of information combined with the fact, as compared to a single date value coming from the transnational system.

Hierarchy for Date Dimension

Year > Month >Week>Day>Hour>Minute>Second.