dimensional data modelling design - Data warehouse

[SandeepKomminen]

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.

[TomMartens · Accepted solution]

Hey,

you don't have to add the average to you fact table. Using Power BI there are 2 two ways to show the average

• just use the column unit_sold_cost in any visual and change the aggregation function that you want, it's also possible to use the same column more than in once in the same visual with different aggregation functions
• just create a simple DAX statement to create a measure on top of your fact table like so avg cost = CALCULATE(AVGERAGE('nameofyourfacttable'[unit_sold_cost]))

Regarding your question about transactions using the membership card, this is a difficult question, because even if you that a customer owns a membership card this does not necessarily mean he uses this membership card. For this reason you may implement another information in your facttable, "used membership card".

Hope this answers some parts of your question

Regards