SSAS Date Utility Dimensions: Pre-Aggregating Over Time

A pretty common question in business intelligence (and within SSAS cubes specifically) is numbers at points in time. How does my performance from this month compare to last month? How about this month last year? What is the actual difference? Luckily, there is a great way that we can easily build this out and give this functionality to business users, without having to create huge, complex MDX scripts. It involves creating a date utility dimensions, bringing it into the SSAS database, and then writing a simple MDX script. To start, create a date calculation dimension table in your data warehouse (or wherever your date dimension is):

 

CREATE TABLE [dbo].[DimDateCalculations]

([ID] [tinyint] IDENTITY(1,1) NOT NULL,

[Calculation] [varchar](50) NOT NULL)

GO

 

After that, we’ll want to insert some things we’ll want to calculate:

INSERT INTO [dbo].[DimDateCalculations]

VALUES (‘Actuals’)

GO

INSERT INTO [dbo].[DimDateCalculations]

VALUES (‘Calendar Year To Date’)

GO

INSERT INTO [dbo].[DimDateCalculations]

VALUES (‘Calendar Previous Period’)

GO

INSERT INTO [dbo].[DimDateCalculations]

VALUES (‘Calendar Previous Period Diff’)

GO

 

That’s all we have to do on the database side. Now switch over to Analysis Services in BIDS. This is where the real fun begins. Open up your DSV, right click and select “Add/Remove Tables”…

 

image

 

Select your new dimension table. In this case, called “DimDateCalculations”. Once it’s added in, don’t worry about joining it to anything. It exists within the context of the database, and doesn’t need to be explicitly joined to any of your measure groups. Right click on Dimensions, select “New Dimension”. Use An Existing table, select your DateCalculations table, and the ID column is the key.

 

image

On the next screen, check the calculation box to make it an available attribute, click Next again, give it a name (Personally, I usually drop the Dim, so you get something like “Date Calculations”) in this case. NOTE: Your name here affects the script a little bit later, so if you decide to name it something else make sure you change the script a little bit farther down.

Once your dimension is built, click on the “ID” column (your key) and set the attribute visible property to false. It doesn’t need to be viewable. Your dimension piece is now done.

Go into the cube, in the bottom left hand corner where the dimensions are listed click and add your cube dimension:

image

Select your Date Calculation dimension from the list, and click OK. Go to the dimension usage tab:

 

image

 

You don’t need to do anything here, I just wanted to show that it is OK to have no relationships with your measure groups. Go to the calculations tab. On the calculations tab, go to script view. Code is:

 

//Calendar Year To Date

SCOPE ([Date Calculations].[Calculation].[Calendar Year To Date]);

THIS =

AGGREGATE(

YTD ([Date].[Calendar].CurrentMember),

[Date Calculations].[Calculation].[Actuals]);

END SCOPE

 

//Calendar Previous Period

SCOPE ([Date Calculations].[Calculation].[Calendar Previous Period]);

THIS =

([Date].[Calendar].CurrentMember.PrevMember,

[Date Calculations].[Calculation].[Actuals]);

END SCOPE

 

//Calendar Previous Period Diff

SCOPE([Date Calculations].[Calculation].[Calendar Previous Period Diff]);

THIS =

([Date].[Calendar].CurrentMember,

[Date Calculations].[Calculation].[Actuals])-

([Date].[Calendar].CurrentMember.PrevMember,

[Date Calculations].[Calculation].[Actuals]);

END SCOPE

 

After that, process the cube. If you get any errors, go back and check the name of your date hierarchy (if it’s not [Calendar] you’ll have to modify the above script) or the Date Calculations piece if you named it something different. Once it process successfully, open up Excel. Create an Analysis Services connection to your newly processed database, create a pivot table. Drag over your date dimension on the rows, your new date calculation dimension on columns, and some measure into values ( in this example, I’m using Adventure Works Fact Internet Sales). Now you can easily see how your value performed over time and changed.

image

 

Of course, this is just a start. I only used 3 different values for this example, but you could add as many calculations as you want to add percentages, fiscal information, etc. Couple this in with some KPI’s you can build in and you can get some pretty dynamic calculations coming out of your cube pretty easily.

Advertisements

9 thoughts on “SSAS Date Utility Dimensions: Pre-Aggregating Over Time”

  1. This is a brilliant solution thank you. The only issue I have is that I am using Excel 2013 and I am unable to filter on each of the date Calculations it is an all or nothing ie if I would like to see only Calendar Year To Date and not the others is that possible?

  2. Thanks again I solved the filter issue I am trying to add a YTD Last Year but am getting nulls below is the code I am using please could you have a look.
    // YTD Last Year

    SCOPE ([Date Calculations].[Calculation].[YTD Last Year]);

    THIS =

    AGGREGATE (

    YTD ( ParallelPeriod ([Date].[YearMonthDate], 1,

    [Date].[YearMonthDate].CurrentMember)
    ),

    [Date Calculations].[Calculation].[Current Period]);

    END SCOPE;

    1. Thanks for the feedback Trish! Make sure that [YTD Last Year] is a record in the date calculation table, and try this calculation:
      //YTD Last Year
      SCOPE([Date Calculations].[Calculation].[YTD Last Year]);
      THIS=
      SUM(YTD(ParallelPeriod([Date].[Calendar].[Calendar Year]
      , 1
      , [Date].[Calendar].CurrentMember))
      , [Date Calculations].[Calculation].[Actuals]);
      END SCOPE

      1. Thank you so much worked like a dream. Just one further question if I may. How do you apply formatting to the values generated from these scope statements?

  3. Thanks for the great article, it works perfect.

    I have two date hierarchies “Calendar” and “Fiscal Calendar”. How can I use the same calulation value (e.g. YTD) in both hierarchies?

    Kind regards, Lars

    1. Hi Lars,
      Thanks for the question. If you want to use the same calculation value against both, you’d have to create an additional calculation to reference one hierarchy at a time. This will wind up yielding 2 metrics to show to the user. This is due to the limitations that exist within the SSAS Multidimensional engine. For example, if I wanted to create a previous period calculation, my calculation statement would look something like this:

      //Calendar Previous Period

      SCOPE ([Date Calculations].[Calculation].[Calendar Previous Period]);

      THIS =

      ([Date].[Calendar].CurrentMember.PrevMember,

      [Date Calculations].[Calculation].[Actuals]);

      END SCOPE
      ———————————————————
      //Fiscal Calendar Previous Period

      SCOPE ([Date Calculations].[Calculation].[Calendar Previous Period]);

      THIS =

      ([Date].[Fiscal Calendar].CurrentMember.PrevMember,

      [Date Calculations].[Calculation].[Actuals]);

      END SCOPE

      Hope this helps!
      Chris

  4. Hi Chris,

    thanks for the super fast reply.

    I tried exactly the same, but it’s not working. I don’t see the [Previous Period] member in the browser nor excel. There was no error message during the building of the cube.

    It is working, if I just use one of the statements.

    — Previous Period
    SCOPE ([Date Calculation].[Calculation].[Previous Period]);
    THIS =
    (
    [Date].[Calendar].CurrentMember.PrevMember,
    [Date Calculation].[Calculation].[Actual]
    );
    END SCOPE;
    SCOPE ([Date Calculation].[Calculation].[Previous Period]);
    THIS =
    (
    [Date].[Fiscal Calendar].CurrentMember.PrevMember,
    [Date Calculation].[Calculation].[Actual]
    );

    Lars

    1. Hi Lars,
      I remember needing to implement something similar now. The trick is to input a new record into the DateCalculations table, calling it something slightly different. For example,
      INSERT INTO [dbo].[DimDateCalculations]

      VALUES (‘Calendar Fiscal Previous Period’)

      GO

      Then in your scope statement, create the additional one that references your new calculation:
      //Fiscal Calendar Previous Period

      SCOPE ([Date Calculations].[Calculation].[Calendar Fiscal Previous Period]);

      THIS =

      ([Date].[Fiscal Calendar].CurrentMember.PrevMember,

      [Date Calculations].[Calculation].[Actuals]);

      END SCOPE

      The reason why this happens is because of your first line (SCOPE(……[Calendar Previous Period]). As you’re scoping to the level of a calculation you can only have one SCOPE per “line item”, otherwise SSAS is trying to perform multiple calculations on the same cube sub space.

      Chris

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