dimensional data modelling design - Data warehouse

Moderator: NorbertKrupa

Post Reply
SandeepKommineni
Newbie
Newbie
Posts: 1
Joined: Wed Aug 30, 2017 7:29 am

dimensional data modelling design - Data warehouse

Post by SandeepKommineni » Wed Aug 30, 2017 7:35 am

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.

sKwa
Newbie
Newbie
Posts: 15
Joined: Wed Aug 02, 2017 3:12 pm

Re: dimensional data modelling design - Data warehouse

Post by sKwa » Wed Aug 30, 2017 11:41 pm

Hi!
SandeepKommineni wrote:
Wed Aug 30, 2017 7:35 am
Should I add average_sales column in fact table...
No, you should not.
SandeepKommineni wrote:
Wed Aug 30, 2017 7:35 am
using the membership card should I add corresponding field in fact table?
No, you should not.
SandeepKommineni wrote:
Wed Aug 30, 2017 7:35 am
My understanding so far is only countable measures should be in fact table so I guess membership_card should not come 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.

PS
1. You don't need Date dimention in Vertica.
2. Take a look on this VMart example, this example very similar to your.

Image
Image
Image

Post Reply

Return to “General”