Budget at month level

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.

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,

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.