Daily net sales - single aggregate fact table or calculate from two

View previous topic View next topic Go down

Daily net sales - single aggregate fact table or calculate from two

Post  kjfischer on Fri Dec 16, 2011 3:40 pm

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?

kjfischer

Posts : 28
Join date : 2011-05-04

View user profile

Back to top Go down

Re: Daily net sales - single aggregate fact table or calculate from two

Post  umutiscan on Fri Dec 16, 2011 4:10 pm

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.

umutiscan

Posts : 51
Join date : 2010-11-26
Age : 36
Location : Istanbul, Turkey

View user profile

Back to top Go down

Re: Daily net sales - single aggregate fact table or calculate from two

Post  hang on Fri Dec 16, 2011 10:07 pm

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

View user profile

Back to top Go down

Re: Daily net sales - single aggregate fact table or calculate from two

Post  ngalemmo on Sun Dec 18, 2011 1:07 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Daily net sales - single aggregate fact table or calculate from two

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum