Create cube calculation in SSAS using MDX statement to allocate monthly target to daily level

Question

I have a cube fact table for targets, the targets are at the monthly level (Jan = 300, Feb = 450, Mar = 350 etc.)

This is joined to the date dimension in my cube at using month as the join, however the dimensions grain is at the day level.

What I want to do is create a calculation so that the monthly target is allocated evenly to the days in the month so each day in Jan has a target of 300/31 Feb 450/28 etc.

I then want to be able to use this calculated measure as my target and have it available as the daily/monthly/quarterly/Annual Target.

I think this should be possible with an MDX statement (or perhaps a few using a few calculated measures) but I'm not that familiar with it.

So would this approach work and what would the mdx look like?

Thanks

Brent M. Spell Brent M. Spell · Accepted Answer · 2012-12-12T16:21:30

If you have the ability to make changes to your source OLTP/staging database, then it would be best to change the grain of your Targets fact table to daily if you need to report at that level. You could do this with a view if you have a date dimension table in your OLTP database by joining your monthly fact table to your date dimension on the month attribute, and then evenly distributing the target value over the days of the month.

If you are using SQL Server and your OLTP schema looks like the following:

CREATE TABLE dimDate (
    Date Date,
    YearMonth Integer,
    ...
)

CREATE TABLE factMonthlyTarget (
   YearMonth Integer,
   Value Integer,
   ...
)

Your new fact view would be something like the following:

CREATE VIEW factDailyTarget AS
   SELECT 
      dimDate.Date,
      factMonthlyTarget.Value / Months.DaysPerMonth AS Value
   FROM
      factMonthlyTarget, 
      dimDate, 
      (
         SELECT YearMonth, COUNT(*) AS DaysPerMonth
         FROM dimDate
         GROUP BY YearMonth
      ) Months
   WHERE factMonthlyTarget.YearMonth = Months.YearMonth
         AND Months.YearMonth = dimDate.YearMonth

Create cube calculation in SSAS using MDX statement to allocate monthly target to daily level

1 Answers