How to have design a aggergate and daily level fact table
3 posters
Page 1 of 1
How to have design a aggergate and daily level fact table
Hi I have a requirement where I need to have fact table where it will have aggergate as well as daily level datas. For example
Sales Report should contain the following Date.
Report Start Date 01/08/2011
Report End Date 30/08/2011
Total Sales Amount as on 31/07/2011 --- XXX Amount --- Aggeregate for Transaction
Total Sales between 01/8/2011 to 31/08/2011 --- YYY Amount --- Between the period aggregate
Total Amount --- XXX+YYY Amount -- Total
This is sample. The same approach is applciable for Product wise, Customer Wise
Please suggest me How I can create a single fact daily level but need to have cumulative total also.
Sales Report should contain the following Date.
Report Start Date 01/08/2011
Report End Date 30/08/2011
Total Sales Amount as on 31/07/2011 --- XXX Amount --- Aggeregate for Transaction
Total Sales between 01/8/2011 to 31/08/2011 --- YYY Amount --- Between the period aggregate
Total Amount --- XXX+YYY Amount -- Total
This is sample. The same approach is applciable for Product wise, Customer Wise
Please suggest me How I can create a single fact daily level but need to have cumulative total also.
roger- Posts : 1
Join date : 2011-09-30
Re: How to have design a aggergate and daily level fact table
That would be a mixed grain fact table which is a bad design. If you want aggregate values, simply sum the daily values.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Re: How to have design a aggergate and daily level fact table
As B&L said, it is a very bad practice to have a mixed grain fact table. You could build and populate a separate aggregate fact table.
Then if you really need both the detail and aggregate together in a single result set you could do a union query to pull them together.
Then if you really need both the detail and aggregate together in a single result set you could do a union query to pull them together.
VHF- Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US
Similar topics
» Allocating Header Level FACT table Dimensions FKs into Line Level Fact
» FACT Design Question - How to design FACT Table when computation is required on the degenerate dimension?
» Daily net sales - single aggregate fact table or calculate from two
» Daily snapshot fact table-any chance to reduce data volume?
» Design a snapshot Fact table in data mart from compliance-enable fact table in enterprise data warehouse
» FACT Design Question - How to design FACT Table when computation is required on the degenerate dimension?
» Daily net sales - single aggregate fact table or calculate from two
» Daily snapshot fact table-any chance to reduce data volume?
» Design a snapshot Fact table in data mart from compliance-enable fact table in enterprise data warehouse
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum