0

I am studying data warehousing star schema and attribute hierarchies and I am getting confused because the examples of the book do not provide sample data on which to confirm my understanding of things.

In the book there is a sales data warehouse with a dimension for products that has the following attribute hierarchy: PRODUCT(AllProducts, ByProductType, OneProduct)

See the image below: enter image description here

What kind of sample data would go into the product dimension table?

PRODUCTS

AllProducts ByProductType OneProduct

?                 ?                       ?

?                 ?                       ?

?                 ?                       ?

If I'm not mistaken attributes are table columns or fields and I can't understand how would the data look like in this table

If you take the dimension Time from the same image, data can easily be sampled:

TIME

Year Quarter Month Week

2010 1           1        1

2010 1           1        2

2010 1           1        3

2010 1           1        4

One other thing I am confused about is how the data looks like in the fact table. In the book it is stated that the fact table would deliberately contain duplicated data. For example to come up with the sales figures of a whole year there is no need to aggregate weekly sales values on the fly because they would have already been calculated. Now if in the time dimension I have a primary key value 1 that represents a year a quarter a month and a week all at once how can I hold the aggregate value of 1 whole year in the fact table?

TIME

ID Year Quarter Month Week

1  2010 1           1        1



SALES_FACT_TABLE

Sales TimeID ProductID

1000  1          ?

prince
  • 671
  • 2
  • 11
  • 26

1 Answers1

0

The greyish fields (AllProducts, OneProduct etc) in the figure are examples of queries on the products dimension, not attributes.

Attributes would be Product.Name, Brand, SKU, InternalName, DevelopmentStartDate, OnMarketDate ... whatever

knb
  • 9,138
  • 4
  • 58
  • 85
  • Even the greyish fields of the time dimension? Why isn't there a field in the time dimension named All_Years to resemble the All_Products field in the Product dimension? – prince May 04 '11 at 10:12
  • I think so, yes. Attributes should be year, month, day, dayofyear, weekday, is_workday, is_national_holiday, etc – knb May 04 '11 at 11:50
  • I think the diagram is a little misleading especially when it is read by a beginner and when read in conjunction with the text presented in the book. However I got the grip now. Thanks. – prince May 05 '11 at 10:34