dimensional data modelling design - Data warehouse

Question

I am having

dimension tables

item (item_id,name,category)
Store(store_id,location,region,city)
Date(date_id,day,month,quarter)
customer(customer_id,name,address,member_card)

fact tables

Sales(item_id,store_id,date_id,customer_id,unit_sold,cost)

My question is if I want to find average sales of a location for a month Should I add average_sales column in fact table and if i want to find sales done using the membership card should I add corresponding field in fact table?

My understanding so far is only countable measures should be in fact table so I guess membership_card should not come in fact table.

Please let me know if I am wrong.

How do you calculate sales? Is each sale represented simply by Sales.cost, or do you have to multiply Sales.unit_sold times Sales.cost, or is there something else? — Mike Sherrill 'Cat Recall', Mar 16 '14 at 00:59

score 0 · Answer 1 · answered Mar 19 '14 at 19:30

No, you should not add an average sales column to your fact table, it is a calculated value, and is not at the same "grain" as the fact table.

Your sales fact table should be as granular as possible, so it should really be sales_order_line_items, one row per sales order line item.

You want to calculate the average sales of a given store for a given month...?

First, by "sales" do you mean "revenue" (total dollars in) or "quantity sold"?

Average daily revenue?

Average monthly revenue, by month?

If you have the store id, date, quantity sold (per line item) and unit price, then it's pretty easy to figure out.

I agree. But, is it possible to count distinct customers over a 30 day period for each day in an axis? (or whatever time level is chosen) — beefyhalo, Mar 20 '14 at 17:49

score 0 · Answer 2 · answered Mar 25 '14 at 05:57

You Should not add aggregate columns In the same fact table. The measures in the fact table should be at the same grain. So if you want aggregate metrics, build a separate fact table at the required grain.

So, I might have a fact aggregate table named F_LOC_MON_AGG which has the measures aggregated at location and month level.

If you do not have aggregate tables, modern business intelligence tools such as OBIEE can do the aggregation at run time.

Vijay

dimensional data modelling design - Data warehouse

2 Answers2