topic dimensional data modelling design - Data warehouse in QlikView

dimensional data modelling design - Data warehouse

Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: dimensional data modelling design - Data warehouse

effinty2112 — Wed, 30 Aug 2017 16:18:24 GMT

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

Re: dimensional data modelling design - Data warehouse

Thu, 31 Aug 2017 06:34:01 GMT

Thanks Andrew Walker.