How to have design a aggergate and daily level fact table

View previous topic View next topic Go down

How to have design a aggergate and daily level fact table

Post  roger on Fri Sep 30, 2011 7:16 am

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.

roger

Posts : 1
Join date : 2011-09-30

View user profile

Back to top Go down

Re: How to have design a aggergate and daily level fact table

Post  BoxesAndLines on Fri Sep 30, 2011 8:42 am

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

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: How to have design a aggergate and daily level fact table

Post  VHF on Fri Sep 30, 2011 11:47 am

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.

VHF

Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US

View user profile

Back to top Go down

Re: How to have design a aggergate and daily level fact table

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