dimensional data modelling design - Data warehouse

[#1]

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?

dimensional data modelling design - Data warehouse

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.

[effinty2112]

Hi Sandeep,

I would suggest that for average sales calculations these are done in a chart in the UI. I wouldn't precalculate in the load unless your data volume enormous and calculation times are slow. The only thing i can see missing from your data design is that date_id is not included in Sales but I'm sure you'd realise that quickly enough when you sit down and do it.

Keep membership_card where it belongs, in the customer table.

good luck

Andrew

[#3]

Thanks Andrew Walker.