Forum Discussion
dimensional data modelling design - Data warehouse
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.
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
1 Reply
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