Budget at month level

View previous topic View next topic Go down

Budget at month level

Post  sssqllearner on Tue Sep 16, 2014 9:01 am

Hi,

This is my first post so apologies if I am approaching in incorrect way. I need some help in below development:-

Currently we receive targets at cycle level (a cycle is of 4 months i.e.. 01/01/2014 to 30/04/2014). However there is requirement from client to show these targets at month level. Month level data will be based on Actual Working Days in a month.
e.g. if a month has 26% of the working days in a business cycle (4 months period) the target (Budget) for this month should be 26% of the total business cycle target (budget).

In the etl.FactSalesTargetsTbl we have cycleid and targetvalue with few other columns whereas in view we have cycelid plus some calculated columns based on targetvalue column to show targets in local currency and in USD.

I would like to know what should I do to show the targets at month level.

Thanks,
Regards
sssqllearner.

sssqllearner

Posts : 8
Join date : 2014-09-16

View user profile

Back to top Go down

Re: Budget at month level

Post  nick_white on Tue Sep 16, 2014 12:05 pm

Hi,
you need to create a new fact table that has an FK to a Month Dimension instead of the FK to the Cycle Dim that you currently have.
Ideally you should include the Cycle attributes in your Month Dimension and then you can easily report on both monthly and cycle measures from the same table.
Your existing fact table is basically then an aggregate of your new fact table - keep it for performance reasons if you want to.

If there is likely to be a future requirement to report at shorter time frames such as Day or Week it would make sense to build your new fact table at the lowest time grain possible and then it will satisfy any future requirements without you needing to rebuild anything.

Regards,

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: Budget at month level

Post  ngalemmo on Tue Sep 16, 2014 2:21 pm

You would build a bridge table (cycle -> month) that contains year and allocation factor (PK would be year, month) attributes. The year could be a FK to a year dimension table if you have one. I assume there are cycle and month dimensions.

You pre-calculate the rows based on calendar information. If future non-working are not well defined you can always add new rows periodically as that information is available. It would be a matter of making any new rows available in time for users to create reports. It has no effect on loading the original fact itself.

If needed you can always define a view that incorporates the fact and bridge to make it appear as if you have a month level fact table.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Budget at month level

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