Daily net sales - single aggregate fact table or calculate from two
4 posters
Page 1 of 1
Daily net sales - single aggregate fact table or calculate from two
We have two facts tables: invoice_detail_fact and customer_returns_fact.
These are both at the lowest invoice line item level.
There is a need to report on daily net sales.
We can create a daily_sales_fact aggregate table from invoice_detail_fact and similarly a daily_returns_fact from customer_returns_fact, both with the same dimensionality. Then leave it to the Microstrategy reporting tool to calculate daily net sales (sales - returns = net sales).
An alternative suggested to me was to create a single daily aggregate table which would have both sales and returns in it with a "transaction_type", allowing the reporting tool to get net sales from a single table.
Any thoughts on the best approach?
These are both at the lowest invoice line item level.
There is a need to report on daily net sales.
We can create a daily_sales_fact aggregate table from invoice_detail_fact and similarly a daily_returns_fact from customer_returns_fact, both with the same dimensionality. Then leave it to the Microstrategy reporting tool to calculate daily net sales (sales - returns = net sales).
An alternative suggested to me was to create a single daily aggregate table which would have both sales and returns in it with a "transaction_type", allowing the reporting tool to get net sales from a single table.
Any thoughts on the best approach?
kjfischer- Posts : 28
Join date : 2011-05-04
Re: Daily net sales - single aggregate fact table or calculate from two
Your first option is creating an aggregation table for each measure. It's not significant.
Create a daily sales aggregation fact and put all the transaction type level measures in it.
Create a daily sales aggregation fact and put all the transaction type level measures in it.
umutiscan- Posts : 51
Join date : 2010-11-26
Age : 43
Location : Istanbul, Turkey
Re: Daily net sales - single aggregate fact table or calculate from two
If the two facts share the same dimensionality with the same grain, consolidating them into single fact table makes sense. I would store sales and returns in the same measure column as positive and negative sales values so that the measure is fully additive.
hang- Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia
Re: Daily net sales - single aggregate fact table or calculate from two
Either way is reasonable, a third alternative would be a view that unions the two if performance is ok. Hang's suggestion is the simplest, however, you would probably have some issues as the returns fact usually has additional dimensions (such as reference to the original invoice and return reasons). It doesn't mess up the grain, but invoices would need to have default values for the dimensions that don't apply to them.
Similar topics
» Sales Rep <--> Customer relationship with Sales Fact Table
» Difference between base fact table and aggregate fact table ?
» Single fact table vs multiple fact tables - what is the right way in a dimensional model?
» How to have design a aggergate and daily level fact table
» Daily snapshot fact table-any chance to reduce data volume?
» Difference between base fact table and aggregate fact table ?
» Single fact table vs multiple fact tables - what is the right way in a dimensional model?
» How to have design a aggergate and daily level fact table
» Daily snapshot fact table-any chance to reduce data volume?
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum