Question

Create a snowflake schema diagram (you do not have to mark every possible level) and include your implicit assumptions for each level of the dimension.

Starting with the base cuboid [customer, date, product, store], what are the specific OLAP operations (e.g., roll-up student to the department (level)) that one should perform in order to list the average sales of each cosmetic product since January 2005?

Answer 1

) Lets go step by step first understand what is dimension and fact table

Dimension table:-

• Dimension tables provides descriptive information for all the measurements recorded in fact table.
• Dimensions are relatively very small as comparison of fact table. in our example dimensions are customer, date, product and store.

Fact Table:

• It contains all the primary keys of the dimension and associated facts or measures(is a property on which calculations can be made) like in our question sales is fact table.

I am writing schema for four dimensions like below-

customer(CustID,Name,Address,City,Country,PhoneNo,)

date (Day, DayofWeek, MonthID, QuarterID, Year)

product(ProdID,ProdName,SupplierID,Unit,Price)

store(StoreID,StoreName.ContactName,Type,Address)

Fact table-

sales(CustID,Day,ProdID,StoreID,QtySold,Amt, Sales)

Measures can be described as, total count = count(*) and avg sales= avg(sales).

2) retrieve data from joining of product, date and sales tables and find the avg sales where product is grouped by on the basis of type equal to "cosmetic",product name and date is between Jan 2005 to Current Date.