Multi select – friendly time calculations in Analysis Services.

Multi select – friendly time calculations in Analysis Services.

By Duane Dicks – ditchie@gmail.com

SSAS is blessed with a variety of built in time calculations, as well as many methods of creating your own calculations, for when your time dimension is designed in such a way that the built in calculations don’t serve the exact purpose that you require. I find that these methods are flawed in a couple of ways though, as they rely on the “.CurrentMember” function. The problems that I experience with these are listed below:

1.)       The “.CurrentMember” function does not behave as expected when multiple members are selected.

2.)       The “.CurrentMember” function is a function of a dimension hierarchy which when referenced in a complex time dimension design, with a set of complex time calculations, can cause a maintenance nightmare if for whatever reason the hierarchy design changes.

Well I was forced to come up with a better solution a couple of years ago as my users were wanting to report on Last Year figures and Year on year growth figures while multiple time members were selected and on top of this, they added an extra hierarchy to the time dimension and I already had hundreds of time calculations coded into the cube which was done in many thousands of lines of MDX code as the solution was for a large corporate comprising of 5 companies, each sharing the same cube, each with 5 time hierarchies (same time hierarchies, but with a different make up of members).

Let me illustrate the problem below:

Using the Adventureworks Cube db that comes with a 2008 R2 installation – run the following code from an MDX query session (It returns the Reseller sales amount for the Calendar year of 2007 as well as the previous year’s sales in the LY measure:

<code>WITH MEMBER [Measures].[LY Reseller Sales Amount]
AS
SUM([Date].[Calendar].CurrentMember.LAG(1), [Measures].[Reseller Sales Amount])
SELECT
{
          [Measures].[Reseller Sales Amount],
          [Measures].[LY Reseller Sales Amount]
} ON 0
FROM
          [Adventure Works]
WHERE
{
          [Date].[Calendar].[Calendar Year].&[2007]
}
</code>

The results should look something like this:

Reseller Sales Amount LY Reseller Sales Amount
$32,202,669.43 $24,144,429.65

That’s all fine, but when you change the filter to include a second, or third, or even more date member then the LY calculation has problems, but the normal measure doesn’t.

See code and results below:

<code>

WITH MEMBER [Measures].[LY Reseller Sales Amount]
AS
SUM([Date].[Calendar].CurrentMember.LAG(1), [Measures].[Reseller Sales Amount])
SELECT
{
          [Measures].[Reseller Sales Amount],
          [Measures].[LY Reseller Sales Amount]
} ON 0
FROM
          [Adventure Works]
WHERE
{
          [Date].[Calendar].[Calendar Year].&[2007],
          [Date].[Calendar].[Calendar Year].&[2008]
}

</code>

The results should look something like this:

Reseller Sales Amount LY Reseller Sales Amount
$48,240,732.02 #Error

If you mouse over the error in management studio you will read the message:

“The MDX function CURRENTMEMBER failed because the coordinate of the ‘Calendar Year’ attribute contains a set”.

I can’t accept this, why does the current year’s sales work fine on a multi select, while the LY sales don’t work on a multi select.

After some thought I attempted to achieve this by referring to the Date Keys of the dimension in an MDX statement as a test, this worked perfectly:

<code>
WITH MEMBER [Measures].[LY Reseller Sales Amount]
AS
SUM([Date].[Date].&[20060101]:[Date].[Date].&[20071231], [Measures].[Reseller Sales Amount])
SELECT
{
          [Measures].[Reseller Sales Amount],
          [Measures].[LY Reseller Sales Amount]
} ON 0
FROM
          [Adventure Works]
WHERE
{
          [Date].[Calendar].[Calendar Year].&[2007],
          [Date].[Calendar].[Calendar Year].&[2008]
}
</code>

 

Reseller Sales Amount LY Reseller Sales Amount
$48,240,732.02 $56,347,099.08

By going down to the lowest level of granularity on the time dimension, this multi select solution works perfectly. The date attribute being referred to in this query is the primary key on the time dimension in the adventure works cube, but it is a hidden attribute, which is why you may not see it in management studio.

The only problem that we are left with is that we need this LY measure to be dynamic enough to reference the exact LY equivalent of what is being selected in the time filter of the query, also we will need this to be coded as a measure in the cube itself and not necessarily reference it as a “WITH MEMBER” at the start of an MDX query.

That’s when I had a “light bulb” moment, essentially we are trying to run a query to aggregate the sales value against a set spanning from the MIN member of the previous year’s equivalent of the selected range to the MAX member previous years equivalent of the selected range, now the current year’s sales works perfectly because the min and max member are being returned from the hierarchy structures available on the time dimension design, so we need to replicate this for our LY calculation and all other time calculations for that matter. So I decided to create another measure group referencing only 1 dimension being the time dimension, it also has a 1 to 1 relationship with the time dimension. It holds a “DateKey” plus an equivalent min and max “DateKey” for each attribute involved in all hierarchies on the time dimension, so that calculations can be coded up using the STRTOSET function in MDX referencing these measures on this new measure group.

I know that STRTOSET is often referred to as a “dirty word” in MDX as it often means a performance lag in queries, but in this case it seems quite the opposite. I have found that this method outperforms the built in time calculations available to Analysis Services. AdventureWorks is hardly the place to do the performance tests because of the fact that it is a tiny data set compared to what one would face at large corporate sites. I will post results of the performance tests in a later post.

The fact table feeding the measure group should look something like this:

DateKey MinDayLY MaxDayLY MinMTD MaxMTD
20060101 20050101 20050101 20060101 20060101
20060102 20050102 20050102 20060101 20060102
20060103 20050103 20050103 20060101 20060103
20060104 20050104 20050104 20060101 20060104
20060105 20050105 20050105 20060101 20060105
20060106 20050106 20050106 20060101 20060106
20060107 20050107 20050107 20060101 20060107
20060108 20050108 20050108 20060101 20060108
20060109 20050109 20050109 20060101 20060109
20060110 20050110 20050110 20060101 20060110

 

Imagine the measure group having a MIN aggregation set for each column prefixed with Min and a MAX aggregation for each column prefixed with a MAX.

Now if a user selected an attribute that spanned across the date range ‘2006-01-01’ to ‘2006-01-10’ in a query and we had a measure reporting on the LY Reseller Sales Amount for that period we would using this method need to have a piece of code looking something like this:

<code>
SUM(STRTOSET("{[Date].[Date].&[" + STR([Measures].[MinDayLY]) + "]:[Date].[Date].&[" + STR([Measures].[MaxDayLY]) + "]}"), [Measures].[Reseller Sales Amount])
</code>

Now because we have an aggregation type of “Min” set on the MinDayLY column and “Max” on the MaxDayLY column our measure would have generated a piece of code that looks like this:

<code>
SUM([Date].[Date].&[20050101]:[Date].[Date].&[20050110], [Measures].[Reseller Sales Amount])
</code>

That’s exactly what we are looking for.

Now, let’s do all this in the AdventureworksDW2008R2 database SQL and Analysis Services.

First step – SQL.

Open a TSQL session on your box hosting the AdventureWorksDW2008R2 database.

Run this code to create the table (Our time calcs measure group):

<code>
USE [AdventureWorksDW2008R2]
GO
CREATE TABLE [dbo].[FactDateCalcs]
(
          DateKey            INT,
          MinDayLY           INT,
          MaxDayLY           INT,
          MinMTD             INT,
          MaxMTD             INT,
          MinMTDLY           INT,
          MaxMTDLY           INT,
          MinQTD             INT, --***Quarter to date
          MaxQTD             INT, --***Quarter to date
          MinQTDLY           INT, --***Quarter to date
          MaxQTDLY           INT, --***Quarter to date
          MinYTD             INT,
          MaxYTD             INT,
          MinYTDLY           INT,
          MaxYTDLY           INT,
          MinSTD             INT,  --***Semester To date (Get your mind out the gutter)
          MaxSTD             INT,  --***Semester To date (Get your mind out the gutter)
          MinSTDLY           INT,  --***Semester To date (Get your mind out the gutter)
          MaxSTDLY           INT,  --***Semester To date (Get your mind out the gutter)
          MinFQTD            INT, --***Fiscal quarter to date
          MaxFQTD            INT, --***Fiscal quarter to date
          MinFQTDLY          INT, --***Fiscal quarter to date
          MaxFQTDLY          INT, --***Fiscal quarter to date
          MinFYTD            INT, --***Fiscal YTD
          MaxFYTD            INT, --***Fiscal YTD
          MinFYTDLY          INT, --***Fiscal YTD
          MaxFYTDLY          INT, --***Fiscal YTD
          MinFSTD            INT, --Fiscal Semester to date
          MaxFSTD             INT, --Fiscal Semester to date
          MinFSTDLY          INT, --Fiscal Semester to date
          MaxFSTDLY          INT, --Fiscal Semester to date
          OverallMin                   INT  --Used for handling incomplete periods at the beginning of the date dimension
) ON [PRIMARY]
GO
</code>

All columns are pretty self explanatory except for the last column “OverallMin” this will hold the lowest DateKey in the date Dimension against each record. We need this because we will create some LY records that are up to a year earlier than the lowest date member on the date dimension (we can’t have any nulls in these columns as Analysis Services treats them as zeros and that will mess us around especially when they are set to have a min aggregation).Then in our MDX code in the cube we also can’t make reference to a Date member that is lower than the lowest date member on the Date dimension, so we have to start at the lowest date member – hence the reason for this column.

Execute the code to create the table.

Next run this piece of code, to identify if there are any “holes” in your date dimension as the version of the AdventureWorks database that I had did have some holes, we can’t have holes in our dimension or date calcs measure group for that matter as that too will have a big effect on our MIN and MAX aggregated data:

<code>
select * from dbo.DimDate order by 1
--Hole in datekey from 20080901 to 20101031
</code>

I identified a large hole in my Date dimension, by manually checking the rows, see my comments above.

Don’t worry, I wrote a stored proc to fix that.

Create this sproc on your DB:

<code>
CREATE PROCEDURE dbo.PRC_DimDate_FillHoles
(
          @StartDate                            DATETIME,
          @EndDate                     DATETIME
)
AS
DECLARE   @theDate           DATETIME 
SELECT DISTINCT
          DayNumberOfWeek,
          EnglishDayNameOfWeek,
          SpanishDayNameOfWeek,
          FrenchDayNameOfWeek
INTO #WeekDetails
FROM
          dbo.DimDate 

SELECT DISTINCT
          EnglishMonthName,
          SpanishMonthName,
          FrenchMonthName,
          MonthNumberOfYear,
          CalendarQuarter,
          CalendarSemester,
          FiscalQuarter,
          FiscalSemester
INTO #MonthDetails
FROM
          dbo.DimDate         

SET      @theDate = @StartDate
WHILE    @theDate <= @EndDate
 BEGIN
          INSERT INTO dbo.DimDate
          (
                   DateKey,
                   FullDateAlternateKey,
                   DayNumberOfWeek,
                   EnglishDayNameOfWeek,
                   SpanishDayNameOfWeek,
                   FrenchDayNameOfWeek,
                   DayNumberOfMonth,
                   DayNumberOfYear,
                   WeekNumberOfYear,
                   EnglishMonthName,
                   SpanishMonthName,
                   FrenchMonthName,
                   MonthNumberOfYear,
                   CalendarQuarter,
                   CalendarYear,
                   CalendarSemester,
                   FiscalQuarter,
                   FiscalYear,
                   FiscalSemester
          )
          SELECT
                   CAST(CONVERT(VARCHAR(8), @theDate, 112) AS INT) AS DateKey,
                   @theDate AS FullDateAlternateKey,
                   wd.DayNumberOfWeek,
                   DATENAME(WEEKDAY, @theDate) AS EnglishDayNameOfWeek,
                   wd.SpanishDayNameOfWeek,
                   wd.FrenchDayNameOfWeek,
                   DAY(@theDate) AS DayNumberOfMonth,
                   DATENAME(DAYOFYEAR, @theDate) AS DayNumberOfYear,
                   DATENAME(WK, @theDate) AS WeekNumberOfYear,
                   md.EnglishMonthName,
                   md.SpanishMonthName,
                   md.FrenchMonthName,
                   MONTH(@theDate) AS MonthNumberOfYear,
                   md.CalendarQuarter,
                   YEAR(@theDate) AS CalendarYear,
                   md.CalendarSemester,
                   md.FiscalQuarter,
                   CASE WHEN MONTH(@theDate) > 6 THEN YEAR(@theDate) + 1 ELSE YEAR(@theDate) END AS FiscalYear,
                   md.FiscalSemester
          FROM
                   (SELECT * FROM #WeekDetails WHERE EnglishDayNameOfWeek = DATENAME(WEEKDAY, @theDate)) AS wd
                   CROSS JOIN (SELECT * FROM #MonthDetails WHERE MonthNumberOfYear = MONTH(@theDate)) AS md 
          SET       @theDate = @theDate + 1
 END
GO
</code>

Next execute the stored procedure using a from and to date where you identified the holes in your date dimension.

If you have more than one hole you will need to run it for each hole.

<code>
EXEC dbo.PRC_DimDate_FillHoles '2008-09-01', '2010-10-31'
GO
</code>

Next create the stored procedure to populate our date calc measure group’s fact table, I have pasted the code below and used comments in the code to explain as much as possible:

<code>
CREATE PROCEDURE [dbo].[PRC_FactDateCalcs_Populate]
AS
DECLARE @OverallMin AS INT 
SELECT
          @OverallMin = MIN(DateKey)
FROM
          dbo.DimDate
SELECT
          CalendarYear,
          MonthNumberOfYear,
          MIN(DateKey) AS    MinMTD
INTO #MTD
FROM
          dbo.DimDate
GROUP BY
          CalendarYear,
          MonthNumberOfYear
 --****Add a year previous worth of months for LY calcs.
INSERT INTO #MTD
SELECT TOP 12
          CalendarYear - 1,
          MonthNumberOfYear,
          MinMTD - 10000
FROM
          #MTD
ORDER BY 1,2
 
SELECT
          CalendarYear,
          CalendarQuarter,
          MIN(DateKey) AS    MinQTD
INTO #QTD
FROM          dbo.DimDate
GROUP BY
          CalendarYear,
          CalendarQuarter
 --****Add a year previous worth of quarters for LY calcs.
INSERT INTO #QTD
SELECT TOP 4
          CalendarYear - 1,
          CalendarQuarter,
          MinQTD - 10000
FROM
          #QTD
ORDER BY 1,2 

SELECT
          CalendarYear,
          MIN(DateKey) AS    MinYTD
INTO #YTD
FROM
          dbo.DimDate
GROUP BY
          CalendarYear
 --****Add a previous year for LY calcs.
INSERT INTO #YTD
SELECT TOP 1
          CalendarYear - 1,
          MinYTD - 10000
FROM
          #YTD
ORDER BY 1

SELECT
          CalendarYear,
          CalendarSemester,
          MIN(DateKey) AS    MinSTD
INTO #STD
FROM
          dbo.DimDate
GROUP BY
          CalendarYear,
          CalendarSemester 
 --****Add a year previous worth of semesters for LY calcs.
INSERT INTO #STD
SELECT TOP 2
          CalendarYear - 1,
          CalendarSemester,
          MinSTD - 10000
FROM
          #STD
ORDER BY 1,2

SELECT
          FiscalYear,
          FiscalQuarter,
          MIN(DateKey) AS    MinFQT
INTO #FQTD
FROM
          dbo.DimDate
GROUP BY
          FiscalYear,
          FiscalQuarter 
--****Add a year previous worth of fiscal quarters for LY calcs.
INSERT INTO #FQTD
SELECT TOP 4
          FiscalYear - 1,
          FiscalQuarter,
          MinFQTD - 10000
FROM
          #FQTD
ORDER BY 1,2 
SELECT
          FiscalYear,
          MIN(DateKey) AS    MinFYTD
INTO #FYTD
FROM
          dbo.DimDate
GROUP BY
          FiscalYear
 --****Add a previous fiscal year for LY calcs.
INSERT INTO #FYTD
SELECT TOP 1
          FiscalYear - 1,
          MinFYTD - 10000
FROM
          #FYTD
ORDER BY 1

SELECT
          FiscalYear,
          FiscalSemester,
          MIN(DateKey) AS    MinFSTD
INTO #FSTD
FROM
          dbo.DimDate
GROUP BY
          FiscalYear,
          FiscalSemester 
 --****Add a year previous worth of Fiscal semesters for LY calcs.
INSERT INTO #FSTD
SELECT TOP 2
          FiscalYear - 1,
          FiscalSemester,
          MinFSTD - 10000
FROM
          #FSTD
ORDER BY 1,2

SELECT * INTO #DimDate FROM dbo.DimDate UNION ALL –****have to get a years back worth of keys for LY calcs
SELECT TOP 365                      –****will need to hack a bit more on a leap year though
DateKey – 10000,
FullDateAlternateKey,
DayNumberOfWeek,
EnglishDayNameOfWeek,
SpanishDayNameOfWeek,
FrenchDayNameOfWeek,
DayNumberOfMonth,
DayNumberOfYear,
WeekNumberOfYear,
EnglishMonthName,
SpanishMonthName,
FrenchMonthName,
MonthNumberOfYear,
CalendarQuarter,
CalendarYear – 1,
CalendarSemester,
FiscalQuarter,
FiscalYear – 1,
FiscalSemester
FROM
dbo.DimDate
ORDER BY 1
TRUNCATE TABLE [dbo].[FactDateCalcs]
INSERT INTO [dbo].[FactDateCalcs]
(
DateKey,
MinDayLY,
MaxDayLY,
MinMTD,
MaxMTD,
MinMTDLY,
MaxMTDLY,
MinQTD,
MaxQTD,
MinQTDLY,
MaxQTDLY,
MinYTD,
MaxYTD,
MinYTDLY,
MaxYTDLY,
MinSTD,
MaxSTD,
MinSTDLY,
MaxSTDLY,
MinFQTD,
MaxFQTD,
MinFQTDLY,
MaxFQTDLY,
MinFYTD,
MaxFYTD,
MinFYTDLY,
MaxFYTDLY,
MinFSTD,
MaxFSTD,
MinFSTDLY,
MaxFSTDLY,
OverallMin
)
SELECT
ty.DateKey,
ly.DateKey AS MinDayLY,
ly.DateKey AS MaxDayLY,
tymtd.MinMTD,
ty.DateKey AS MaxMTD,
lymtd.MinMTD AS MinMTDLY,
ly.DateKey AS MaxMTDLY,
tyqtd.MinQTD,
ty.DateKey AS MaxQTD,
lyqtd.MinQTD AS MinQTDLY,
ly.DateKey AS MaxQTDLY,
tyytd.MinYTD,
ty.DateKey AS MaxYTD,
lyytd.MinYTD AS MinYTDLY,
ly.DateKey AS MaxYTDLY,
tystd.MinSTD,
ty.DateKey AS MaxSTD,
lystd.MinSTD AS MinSTDLY,
ly.DateKey AS MaxSTDLY,
tyfqtd.MinFQTD,
ty.DateKey AS MaxFQTD,
lyfqtd.MinFQTD AS MinFQTDLY,
ly.DateKey AS MaxFQTDLY,
tyfytd.MinFYTD,
ty.DateKey AS MaxFYTD,
lyfytd.MinFYTD AS MinFYTDLY,
ly.DateKey AS MaxFYTDLY,
tyfstd.MinFSTD,
ty.DateKey AS MaxFSTD,
lyfstd.MinFSTD AS MinFSTDLY,
ly.DateKey AS MaxFSTDLY,
@OverallMin
FROM
#DimDate AS ty
LEFT OUTER JOIN    #DimDate AS ly
ON        ty.DateKey = ly.DateKey + (CASE WHEN ty.DateKey – (ty.DateKey / 10000) * 10000 = 229 THEN 9928 ELSE 10000 END) –****Handle Leap year eg 20080229 – 20090301
LEFT OUTER JOIN #MTD tymtd
ON        tymtd.CalendarYear = ty.CalendarYear AND
tymtd.MonthNumberOfYear = ty.MonthNumberOfYear
LEFT OUTER JOIN #MTD lymtd
ON        lymtd.CalendarYear + 1 = ty.CalendarYear AND
lymtd.MonthNumberOfYear = ty.MonthNumberOfYear
LEFT OUTER JOIN #QTD tyqtd
ON        tyqtd.CalendarYear = ty.CalendarYear AND
tyqtd.CalendarQuarter  = ty.CalendarQuarter
LEFT OUTER JOIN #QTD lyqtd
ON        lyqtd.CalendarYear + 1 = ty.CalendarYear AND
lyqtd.CalendarQuarter = ty.CalendarQuarter
LEFT OUTER JOIN #YTD tyytd
ON        tyytd.CalendarYear = ty.CalendarYear
LEFT OUTER JOIN #YTD lyytd
ON        lyytd.CalendarYear + 1 = ty.CalendarYear
LEFT OUTER JOIN #STD tystd
ON        tystd.CalendarYear = ty.CalendarYear AND
tystd.CalendarSemester   = ty.CalendarSemester
LEFT OUTER JOIN #STD lystd
ON        lystd.CalendarYear + 1 = ty.CalendarYear AND
lystd.CalendarSemester = ty.CalendarSemester
LEFT OUTER JOIN #FQTD tyfqtd
ON        tyfqtd.FiscalYear = ty.FiscalYear AND
tyfqtd.FiscalQuarter   = ty.FiscalQuarter
LEFT OUTER JOIN #FQTD lyfqtd
ON        lyfqtd.FiscalYear + 1 = ty.FiscalYear AND
lyfqtd.FiscalQuarter = ty.FiscalQuarter
LEFT OUTER JOIN #FYTD tyfytd
ON        tyfytd.FiscalYear = ty.FiscalYear
LEFT OUTER JOIN #FYTD lyfytd
ON        lyfytd.FiscalYear + 1 = ty.FiscalYear
LEFT OUTER JOIN #FSTD tyfstd
ON        tyfstd.FiscalYear = ty.FiscalYear AND
tyfstd.FiscalSemester   = ty.FiscalSemester
LEFT OUTER JOIN #FSTD lyfstd
ON        lyfstd.FiscalYear + 1 = ty.FiscalYear AND
lyfstd.FiscalSemester  = ty.FiscalSemester
WHERE
ty.DateKey IS NOT NULL AND
ly.DateKey IS NOT NULL
GO
</code>

Execute the stored procedure:

<code>
EXEC [dbo].[PRC_FactDateCalcs_Populate]

GO

</code>

Now we have our Date calcs table and we are ready to add it to our cube and get cracking with a “better” way of performing time calculations in SSAS.

I’m assuming that everyone reading this article knows how to add this dbo.FactDatecalcs table as a measure group in a cube, so I won’t explain that, but just ask you to do it now.

Once you have added it and set up its relationship with the Date table in the Dimension Usage section of the cube we need to also set all the aggregation types, this too should be pretty self explanatory, all measures prefixed with “Min” and the “OverallMin” measure need to have a MIN aggregation and all those prefixed with “Max” need a MAX aggregation.

Now click on the Adventureworks cube in Visual studio and then on the “Calculations” tab, then select “Script View” and scroll down to the bottom of the page.

I have coded up the “.CURRENTMEMBER” style of a LY calculation and MTD calculation as well as my new style equivalents of each (using the “Fact Calendar Calcs” measure group method).

Copy the code below and paste it into the code page:

<code>
--********************************************************
--***** Time calculation for sales
--********************************************************
--***** The .CurrentMember "hard coded" way.
--******************************************************** 
Create Member CurrentCube.[Measures].[LY Reseller Sales Amount (Calendar)]
As Case
        WHEN [Date].[Calendar].CurrentMember.Level.Ordinal = 0 --***All Member
        THEN "NA" 
        WHEN [Date].[Calendar].CurrentMember.Level.Ordinal = 1 --***Calendar Year
        THEN ([Date].[Calendar].CurrentMember.LAG(1), [Measures].[Reseller Sales Amount]) --*** Sales for 1 year back                  
        WHEN [Date].[Calendar].CurrentMember.Level.Ordinal = 2 --***Calendar Semeste
        THEN ([Date].[Calendar].CurrentMember.LAG(2), [Measures].[Reseller Sales Amount]) --*** Sales for 2 semesters back (1 year)
        WHEN [Date].[Calendar].CurrentMember.Level.Ordinal = 3 --***Calendar Quarter
        THEN ([Date].[Calendar].CurrentMember.LAG(4), [Measures].[Reseller Sales Amount]) --*** Sales for 4 Quarters back (1 year) 
        WHEN [Date].[Calendar].CurrentMember.Level.Ordinal = 4 --***Calendar Mont
        THEN ([Date].[Calendar].CurrentMember.LAG(12), [Measures].[Reseller Sales Amount]) --*** Sales for 12 Months back (1 year)
        ELSE --***Calendar Date
             ([Date].[Calendar].CurrentMember.LAG(365), [Measures].[Reseller Sales Amount]) --*** Sales 365 Days back (1 year) Not catering for leap year 
    End,
Format_String = "#,##0.00",
Associated_Measure_Group = 'Internet Sales' ;      
Create Member CurrentCube.[Measures].[MTD Reseller Sales Amount]
 As Case
        WHEN [Date].[Calendar].CurrentMember.Level.Ordinal >= 4 --***Calendar Month
        THEN SUM(MTD([Date].[Calendar].CurrentMember), [Measures].[Reseller Sales Amount])
        ELSE --***anything higher than month
             "NA"
    End,
Format_String = "#,##0.00",
Associated_Measure_Group = 'Internet Sales' ;      
 --********************************************************
--***** Time calculations without using .CurrentMember
--********************************************************
Create Member CurrentCube.[Measures].[LY Reseller Sales Amount (Calendar)(DD)]
 As
    IIF
    (
        [Measures].[Min Day LY] < [Measures].[Overall Min]
        ,
            IIF
            (
                [Measures].[Max Day LY] < [Measures].[Overall Min]
                ,
                NULL
                ,
                SUM(STRTOSET("{[Date].[Date].&[" + STR([Measures].[Overall Min]) + "]:[Date].[Date].&[" + STR([Measures].[Max Day LY]) + "]}"), [Measures].[Reseller Sales Amount])
            )
        ,
        SUM(STRTOSET("{[Date].[Date].&[" + STR([Measures].[Min Day LY]) + "]:[Date].[Date].&[" + STR([Measures].[Max Day LY]) + "]}"), [Measures].[Reseller Sales Amount])
    )
,
Format_String = "#,##0.00",
Associated_Measure_Group = 'Internet Sales' ;
Create Member CurrentCube.[Measures].[MTD Reseller Sales Amount (Calendar)(DD)]
 As
 SUM(STRTOSET("{[Date].[Date].&[" + STR([Measures].[Min MTD]) + "]:[Date].[Date].&[" + STR([Measures].[Max MTD]) + "]}"), [Measures].[Reseller Sales Amount])
 ,
Format_String = "#,##0.00",
Associated_Measure_Group = 'Internet Sales' ;
</code>

If you go through the above code, you will notice that the traditional style is full of “.CURRENTMEMBER” references and lots of references to hierarchy names etc, which means that more work will need to be done if Hierarchies are added or removed from the time dimension.

The method using the Fact Date Calcs measure group is referencing the “OverallMin” measure in the LY calculation to determine whether it must ignore the calculation, start it at “OverallMin” or perform it normally, it only needs to do this on the LY calculation as MTD should never reference a date member less than the lowest date member on the time dimension.

Now let’s test these

If you run this piece of MDX from a management studio session:

<code>
SELECT
{
          [Measures].[LY Reseller Sales Amount (Calendar)],
          [Measures].[LY Reseller Sales Amount (Calendar)(DD)],
          [Measures].[Min Day LY],
          [Measures].[Max Day LY]
} ON 0
FROM
          [Adventure Works]
WHERE
{
          [Date].[Calendar].[Calendar Year].&[2007]
}
</code>

You should get something like this:

LY Reseller Sales Amount (Calendar) LY Reseller Sales Amount   (Calendar)(DD) Min Day LY Max Day LY
24,144,429.65 24,144,429.65 20060101 20061231

 

All seems fine with one date member selected, notice I included the Min and max day ly in the query, just for interest sake, if you put this solution into production you should hide the fact date calcs measures as users don’t need to see them.

Now let’s run the code with a multiple member selection of the Date dimension:

<code>
SELECT
{
          [Measures].[LY Reseller Sales Amount (Calendar)],
          [Measures].[LY Reseller Sales Amount (Calendar)(DD)],
          [Measures].[Min Day LY],
          [Measures].[Max Day LY]
} ON 0
FROM
          [Adventure Works]
WHERE
{
          [Date].[Calendar].[Calendar Year].&[2007],
          [Date].[Calendar].[Calendar Year].&[2008],
          [Date].[Calendar].[Calendar Year].&[2009]
}
</code>

You should get something like this:

LY Reseller Sales Amount (Calendar) LY Reseller Sales Amount   (Calendar)(DD) Min Day LY Max Day LY
#Error 72,385,161.68 20060101 20081231

Notice that my version suffixed with “(DD)” works perfectly where as the traditional version fails.

Now for our MTD calculations:

<code>
SELECT
{
          [Measures].[Reseller Sales Amount],
          [Measures].[MTD Reseller Sales Amount],
          [Measures].[MTD Reseller Sales Amount (Calendar)(DD)],
          [Measures].[Min MTD],
          [Measures].[Max MTD]
} ON 0,
{
          [Date].[Calendar].[Date].&[20060401]:[Date].[Calendar].[Date].&[20060404]
} ON 1
FROM
          [Adventure Works]
</code>

You should get something like this:

  Reseller Sales Amount MTD Reseller Sales Amount MTD Reseller Sales Amount   (Calendar)(DD) Min MTD Max MTD
April 1, 2006 $882,899.94 882,899.94 882,899.94 20060401 20060401
April 2, 2006 (null) 882,899.94 882,899.94 20060401 20060402
April 3, 2006 (null) 882,899.94 882,899.94 20060401 20060403
April 4, 2006 (null) 882,899.94 882,899.94 20060401 20060404

 

All seems fine only sales on the 1st day of the month the other days had no sales, so the MTD figure remained unchanged.

Notice how the MinMTD number remains unchanged and the MaxMTD number increases by 1 on each row.

Now when the Days aren’t broken down into a row each time, but all used in a multi select filter:

<code>
SELECT
{
          [Measures].[Reseller Sales Amount],
          [Measures].[MTD Reseller Sales Amount],
          [Measures].[MTD Reseller Sales Amount (Calendar)(DD)],
          [Measures].[Min MTD],
          [Measures].[Max MTD]
} ON 0
FROM
          [Adventure Works]
WHERE
{
          [Date].[Calendar].[Date].&[20060401]:[Date].[Calendar].[Date].&[20060404]
}
</code>

You should get something like this:

Reseller Sales Amount MTD Reseller Sales Amount MTD Reseller Sales Amount   (Calendar)(DD) Min MTD Max MTD
$882,899.94 #Error 882,899.94 20060401 20060404

Notice again that the traditional form of calculating can’t handle multi select, but the method using the fact Date Calcs measure group handles it fine, also take note of the Min and max MTD measures.

That’s all folks.

I welcome any feedback.

Duane

 

 

 

Advertisements

About ditchie2013

A Microsoft SQL Server Analysis Services developer.
This entry was posted in Analysis Services, MDX Performance, MDX Time calculations, SQL, TSQL. Bookmark the permalink.

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