Multi select friendly – time calculations in Analysis Services.

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:

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]

}

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:

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]

}

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:

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]

}

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:

SUM(STRTOSET(“{[Date].[Date].&[” + STR([Measures].[MinDayLY]) + “]:[Date].[Date].&[” + STR([Measures].[MaxDayLY]) + “]}”), [Measures].[Reseller Sales Amount])

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:

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

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):

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

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:

select * from dbo.DimDate order by 1

–Hole in datekey from 20080901 to 20101031

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:

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

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.

EXEC dbo.PRC_DimDate_FillHoles ‘2008-09-01’, ‘2010-10-31’

GO

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:

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   MinFQTD

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

Execute the stored procedure:

EXEC [dbo].[PRC_FactDateCalcs_Populate]

GO

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:

–********************************************************

–***** 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 Semester

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 Month

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’ ;

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:

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]

}

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:

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]

}

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:

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]

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:

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]

}

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

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