SSAS How-To: Create Dynamic Time Calculations To Reuse Over Any Measure

Rather than making individual time calculations for each measure in an SSAS cube (i.e. YTD, Prior YTD, YTD Variances, etc), one can use a more dynamic approach to time calcs applicable to all measures and save a great amount of dev time and overhead in the process.

1. Create Named Query in DSV:

SELECT      1 AS DateCalculationKey, ‘Selected Date’ AS DateCalculation
UNION
SELECT      2, ‘YTD’
UNION
SELECT      3, ‘PY YTD’
UNION
SELECT      4, ‘YTD Chg’
UNION
SELECT      5, ‘YTD Chg %’
BStep1_NamedSet

2. Create the dimension and add it to the Dimension Usage tab of the cube (no relations to the fact will exist)

3. Add the MDX script calculations:

SCOPE (
[Date].[Calendar Hierarchy].MEMBERS);
[Date Calculations].[Date Calculations].[YTD] =
AGGREGATE
(
    {[Date Calculations].[Date Calculations].[Selected Date]} *
     PERIODSTODATE
     (
       [Date].[Calendar Hierarchy].[Calendar Year],
       [Date].[Calendar Hierarchy].CURRENTMEMBER
     )
);
[Date Calculations].[Date Calculations].[PY YTD] =
AGGREGATE
(
   {[Date Calculations].[Date Calculations].[Selected Date]} *
   PERIODSTODATE
   (
      [Date].[Calendar Hierarchy].[Calendar Year],
      PARALLELPERIOD
      (
         [Date].[Calendar Hierarchy].[Calendar Year],
         1,
         [Date].[Calendar Hierarchy].CURRENTMEMBER
   )
)
);
END SCOPE;
SCOPE ([Date].[Calendar Hierarchy].[All]);
[Date Calculations].[Date Calculations].[YTD] = NULL;
[Date Calculations].[Date Calculations].[PY YTD]=NULL;
END SCOPE;
[Date Calculations].[Date Calculations].[YTD Chg]=[Date Calculations].[Date Calculations].[YTD]-
[Date Calculations].[Date Calculations].[PY YTD];
[Date Calculations].[Date Calculations].[YTD Chg %] =
IIF
(
[Date Calculations].[Date Calculations].[PY YTD] = 0,
NULL,
([Date Calculations].[Date Calculations].[YTD] - [Date Calculations].[Date Calculations].[PY YTD]) /
[Date Calculations].[Date Calculations].[PY YTD]
);
FORMAT_STRING([Date Calculations].[Date Calculations].[YTD Chg %]) = “#,0.0%”;
//Create current year and month in scripts:
CREATESET CURRENTCUBE.[Current Year]
AS {(strtomember(“[Date].[Calendar Hierarchy].[Calendar Year].["+ cstr(year(now()))+"]“))}, DISPLAY_FOLDER = ‘Named Sets’ ;
CREATESET CURRENTCUBE.[Current Month] AS
TAIL
(
NONEMPTY
(
[Date].[Calendar Hierarchy].[Month Name Year],
[Measures].[Fact Sales Count]
)
);
BStep2-Scripts

The result!

BStep2b-CubeBrowser

About these ads

About Sal De Loera
I have been involved in Information Technology, specifically Data Warehousing and BI, for over 8 years. Over these years I have assumed the following roles: Software Engineer Intern, Programmer/Analyst, Sr. Data Warehouse Engineer, Data Architect, Director of Information Technology, and as most recently as a Data Warehouse / BI consultant. Additionally, my IT experience spans the following industries: Insurance, Restaurant, Mass Media, Education, and Local Government. I have created this blog primarily as a means to not only share information and case studies regarding Business Intelligence, Data Warehousing, and Data Modeling, but also receive input from the community on some of the topics that I'l cover. Looking forward to your feedback and to the exciting opportunities that come with new people and technology!

3 Responses to SSAS How-To: Create Dynamic Time Calculations To Reuse Over Any Measure

  1. Like the post (Dynamiic Time Calculations) especially since I am confronted with the following:

    8 Date dimensions, each with 3 Hierarchies (YQMD, YWD, Cycle YQCycle)
    100 Measures; okay, actually 92

    Until reading this I thought I was going to be creating 24 * 100 = 2,400 calculations which would mean 2,400 visible to the User Measures; UGH! I wouldn’t even wish that on my ex-wife. If they can choose which to show (via Filter) I am easily back to an elegant solution rather than a kludge.

    I am trying your solution now and will let you know how it works for this situation.

    Darn, darn, darn… sure hope it is the solution. If it is I will spread the word far and wide…

    Kindest Regards,

    Kurt J. Vesecky

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 56 other followers

%d bloggers like this: