How to have design a aggergate and daily level fact table

[roger]

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.

[BoxesAndLines]

That would be a mixed grain fact table which is a bad design. If you want aggregate values, simply sum the daily values.

[VHF]

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.

[Sponsored content]