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.
dimensional data modelling design - Data warehouse
Moderator: NorbertKrupa
-
- Newbie
- Posts: 1
- Joined: Wed Aug 30, 2017 7:29 am
Re: dimensional data modelling design - Data warehouse
Hi!
PS
1. You don't need Date dimention in Vertica.
2. Take a look on this VMart example, this example very similar to your.
No, you should not.
No, you should not.SandeepKommineni wrote: ↑Wed Aug 30, 2017 7:35 amusing the membership card should I add corresponding field in fact table?
For Vertica it preferable to denormalize schema, i.e. one big fat table in most cases better than normalized schema, but sometimes you can't denormalize schema. If both fields(average_sales, membership_card) are mutable so don't keep it in fact table and <average sales of a location for a month> is a mutable field (for current month you will need to update your filed and Vertica do not likes UPDATEs and DELETEs). Adding membership_card to fact will simplify some queries(less joins). I will recommend: keep it normalized.SandeepKommineni wrote: ↑Wed Aug 30, 2017 7:35 amMy understanding so far is only countable measures should be in fact table so I guess membership_card should not come in fact table.
PS
1. You don't need Date dimention in Vertica.
2. Take a look on this VMart example, this example very similar to your.