Time in Analog using the built in geometry CLR functionality in SQL Server

This is an upgrade to a piece of code that I wrote in 2004 which did the same thing using ascii characters.
It can be read here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=33357

See attached document.

time in analog using geometry

Run the code and click on the spatial tab to see the results.
Posted in SQL, TSQL | Leave a comment

MDX Performance improvement on MAX aggregation

Today I offered what I think was quite a clever solution to improve a fellow community members MDX query using a MAX aggregation.

It took his query time down from 8 minutes to a fraction of a second ;)

 

Read about it at the link below:

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/aef61262-6a42-4348-9426-9a7a9b5bf41f/performance-issue-on-max-function-mdx

Posted in Analysis Services, MDX Performance | Leave a comment

MDX Time Calculation example Last Year equivalent week day

This example runs on the adventureworks 2008 R2 database.

It returns last year sales aligning week days ie Last years equivalent Saturday with this years equivalent Saturday etc (generally the type of comparison that a retailer might be interested in).

If the 31 Days of July 2008 are selected then the LY equivalent will also be 31 days, but lining week days up ie it will be 2 July to 1 August as it works exactly 52 weeks back – 364 days.


WITH MEMBER [Measures].[LY Internet Sales Amount]
AS
CASE
	WHEN [Date].[Calendar].CurrentMember.Level.Ordinal = 0 THEN "NA" --All Member
	WHEN 
	[Date].[Calendar].CurrentMember.Level.Ordinal = 1 --Calendar Year
	THEN 
	SUM({[Date].[Calendar].CurrentMember.FirstChild.FirstChild.FirstChild.FirstChild.FirstChild.Lag(364):
	[Date].[Calendar].CurrentMember.LastChild.LastChild.LastChild.LastChild.LastChild.Lag(364)}, 
	[Measures].[Internet Sales Amount])
	WHEN 
	[Date].[Calendar].CurrentMember.Level.Ordinal = 2 --Calendar Semester
	THEN 
	SUM({[Date].[Calendar].CurrentMember.FirstChild.FirstChild.FirstChild.FirstChild.Lag(364):
	[Date].[Calendar].CurrentMember.LastChild.LastChild.LastChild.LastChild.Lag(364)}, 
	[Measures].[Internet Sales Amount])
	WHEN 
	[Date].[Calendar].CurrentMember.Level.Ordinal = 3 --Calendar Quarter
	THEN 
	SUM({[Date].[Calendar].CurrentMember.FirstChild.FirstChild.FirstChild.Lag(364):
	[Date].[Calendar].CurrentMember.LastChild.LastChild.LastChild.Lag(364)}, 
	[Measures].[Internet Sales Amount])
	WHEN 
	[Date].[Calendar].CurrentMember.Level.Ordinal = 3 --Calendar Quarter
	THEN 
	SUM({[Date].[Calendar].CurrentMember.FirstChild.FirstChild.Lag(364):
	[Date].[Calendar].CurrentMember.LastChild.LastChild.Lag(364)}, 
	[Measures].[Internet Sales Amount])
	WHEN 
	[Date].[Calendar].CurrentMember.Level.Ordinal = 4 --Calendar Month
	THEN 
	SUM({[Date].[Calendar].CurrentMember.FirstChild.Lag(364):
	[Date].[Calendar].CurrentMember.LastChild.Lag(364)}, 
	[Measures].[Internet Sales Amount])
ELSE
	SUM([Date].[Calendar].CurrentMember.Lag(364), 
	[Measures].[Internet Sales Amount])
END, FORMAT_STRING = "Currency"

SELECT
NON EMPTY
(
	[Date].[Calendar].allmembers
)
ON ROWS,
(
{
	[Measures].[Internet Sales Amount],
	[Measures].[LY Internet Sales Amount]
}
)
ON COLUMNS
FROM
	[Adventure Works]

Posted in Analysis Services, MDX Examples, MDX Time calculations | Leave a comment

MDX Time calculation example LY equivalent day

This example is done on the adventureworks 2008 R2 cube.

It shows how to calculate the LY equivalent sales lining the same day of the previous year up with the current day.

Example: LY sales for 20130515 will be the same sales that happened on 20120515.

This code takes care of leap years.

Example: LY Sales of 20080229 will be the same sales that happened on 20070228 and LY Sales for 20080228 will also be the same sales that happened on 20070228.


WITH
MEMBER [Measures].[LY Internet Sales Amount]
AS
CASE
	WHEN [Date].[Calendar].CurrentMember.Level.Ordinal = 0 THEN "NA" --All Member
	WHEN [Date].[Calendar].CurrentMember.Level.Ordinal = 1 --Calendar Year
	THEN SUM([Date].[Calendar].CurrentMember.Lag(1), 
	[Measures].[Internet Sales Amount])
	WHEN [Date].[Calendar].CurrentMember.Level.Ordinal = 2 --Calendar Semester
	THEN SUM([Date].[Calendar].CurrentMember.Lag(2), 
	[Measures].[Internet Sales Amount])
	WHEN [Date].[Calendar].CurrentMember.Level.Ordinal = 3 --Calendar Quarter
	THEN SUM([Date].[Calendar].CurrentMember.Lag(4), 
	[Measures].[Internet Sales Amount])
	WHEN [Date].[Calendar].CurrentMember.Level.Ordinal = 3 --Calendar Quarter
	THEN SUM([Date].[Calendar].CurrentMember.Lag(4), 
	[Measures].[Internet Sales Amount])
	WHEN [Date].[Calendar].CurrentMember.Level.Ordinal = 4 --Calendar Month
	THEN SUM([Date].[Calendar].CurrentMember.Lag(12), 
	[Measures].[Internet Sales Amount])
	WHEN
	INT(INT([Date].[Calendar].CurrentMember.properties("Key") 
	/ 10000) / 4) * 4 = 
	INT([Date].[Calendar].CurrentMember.properties("Key") / 10000) AND
	INT([Date].[Calendar].CurrentMember.properties("Key")) - 
	INT(INT([Date].[Calendar].CurrentMember.properties("Key") 
	/ 10000) * 10000) > 228 --29 Feb to 31 Dec on a Leap year
	THEN 
	SUM([Date].[Calendar].CurrentMember.Lag(366), 
	[Measures].[Internet Sales Amount]) --* 366 days in a leap year 
	WHEN
	INT([Date].[Calendar].CurrentMember.properties("Key") / 10000) / 4 -
	INT(INT([Date].[Calendar].CurrentMember.properties("Key") / 10000) / 4)  = 0.25 AND
	INT([Date].[Calendar].CurrentMember.properties("Key")) -
	INT(INT([Date].[Calendar].CurrentMember.properties("Key") 
	/ 10000) * 10000) <= 228 --1 Jan to 28 Feb following a Leap year
	THEN
	SUM([Date].[Calendar].CurrentMember.Lag(366), 
	[Measures].[Internet Sales Amount]) --* 366 days in a leap year 
ELSE
	SUM([Date].[Calendar].CurrentMember.Lag(365), 
	[Measures].[Internet Sales Amount])
END,  FORMAT_STRING = "Currency"

SELECT
NON EMPTY
(
	[Date].[Calendar].allmembers
)
ON ROWS,
(
{
	[Measures].[Internet Sales Amount],
	[Measures].[LY Internet Sales Amount]
}
)
ON COLUMNS
FROM
	[Adventure Works]
Posted in Analysis Services, MDX Examples, MDX Time calculations | Leave a comment

MDX Scope VS Pre Aggregated measures.

MDX Scope VS Pre Aggregated measures.

So I looked into the use of Scope in MDX and compared it to using pre aggregated measures in the cube.

I am a big fan of handling any type of row level business rules in SQL and any type of set level business rules in MDX calculated measures. Period to Date sales should definitely be coded up in MDX calculated measures whereas calculating discount for products based on what category they belong to should be done at row level in SQL before loading the data into the cube.

Scope seems to be quite good at handling the row level type calculations though, there are pros and cons to both approaches though.

The example I will use is not a good “real life” example, but it’s as good as any for a technical example.

On the “Internet order Quantity” measure in the AdventureWorks 2008 R2 cube database, we will “discount” the quantity based on which category the products belong to.

The Scope approach.

1.)      On the Internet sales measure group, duplicate the “Internet order Quantity” measure and call it “Internet Order Quantity2”

2.)      Add the following code to the cube in the calculations tab:


–-***Accessories 0 % discount

–-***15% Discount on Bikes

SCOPE({[Product].[Product Categories].[Category].&[1].FirstChild.FirstChild:
[Product].[Product Categories].[Category].&[1].LastChild.LastChild}, 
[Measures].[Internet Order Quantity2]);

THIS = [Measures].[Internet Order Quantity2] * 0.85; 

END SCOPE; 

–-***10% Discount on Clothing

SCOPE({[Product].[Product Categories].[Category].&[3].FirstChild.FirstChild:
[Product].[Product Categories].[Category].&[3].LastChild.LastChild}, 
[Measures].[Internet Order Quantity2]);

THIS = [Measures].[Internet Order Quantity2] * 0.9; 

END SCOPE; 

–-***5% Discount on Components

SCOPE({[Product].[Product Categories].[Category].&[2].FirstChild.FirstChild:
[Product].[Product Categories].[Category].&[2].LastChild.LastChild}, 
[Measures].[Internet Order Quantity2]);

THIS = [Measures].[Internet Order Quantity2] * 0.95; 

END SCOPE; 

 

3.)      Save, publish and deploy the cube.

4.)      Open a new mdx query connection in Management Studio and run the following code:


SELECT
{
      [Measures].[Internet Order Quantity],
      [Measures].[Internet Order Quantity2]
} ON 0,
NON EMPTY
(
{
      {[Product].[Product Categories].[All Products].FirstChild.FirstChild.FirstChild:
       [Product].[Product Categories].[All Products].LastChild.LastChild.LastChild}
},
{
      [Date].[Calendar].Members
}
)
 ON 1
FROM
      [Adventure Works]

 

I have included both variables (Original and discounted using scope) in the query to confirm that the results are as desired. The rows at the top have no difference, but that is because they belong to the Accessories department which wasn’t discounted, the rows further down show the clear difference.

What the scope code is doing: Quite simple really, inside each scope statement for each of the Poroducts derived by getting the Grand children of category (FirstChild.FirstChild to lastChild.LastChild) it is replacing the value of “Internet Order Quantity” with whatever code is specified in the piece of code followed by “This =”

The pre-aggregated approach.

1.)      Create a view in the sql database, based on the FactInternetSales table using the same logic used in the cube, the code should look like this:

 


CREATE VIEW dbo.vw_FactInternetSales
AS
SELECT
      fis.*,
      CASE
      WHEN dpc.ProductCategoryKey = 1 THEN fis.OrderQuantity * 0.85 
      WHEN dpc.ProductCategoryKey = 2 THEN fis.OrderQuantity * 0.95
      WHEN dpc.ProductCategoryKey = 3 THEN fis.OrderQuantity * 0.9
      ELSE
          fis.OrderQuantity
      End AS OrderQuantity3 
FROM
      dbo.FactInternetSales  fis
      INNER JOIN dbo.DimProduct dp
      ON    fis.ProductKey = dp.ProductKey 
      INNER JOIN dbo.DimProductSubcategory dpsc
      ON    dp.ProductSubcategoryKey = dpsc.ProductSubcategoryKey 
      INNER JOIN dbo.DimProductCategory dpc 
      ON    dpsc.ProductCategoryKey = dpc.ProductCategoryKey 

     

2.)      In the data source view of the cube replace the existing internet sales table with the view created in step 1.

3.)      Add OrderQuantity3 to the measure group, call it “Internet order Quantity3”, set up the format string and other attributes to be the same as Internet Order Quantity2.

4.)      Change the code in the partitions to include the “Internet order Quantity3” column and to use the view as opposed to the table it used to have (For this example just use select *)

5.)      Publish and process the cube.

6.)      Add “Internet Order Quantity3” to the column list being selected in the MDX statement executed in Management studio earlier, it should now look like this:

 


SELECT
{
      [Measures].[Internet Order Quantity],
      [Measures].[Internet Order Quantity2],
      [Measures].[Internet Order Quantity3]
} ON 0,
NON EMPTY
(
{
      {[Product].[Product Categories].[All Products].FirstChild.FirstChild.FirstChild:
       [Product].[Product Categories].[All Products].LastChild.LastChild.LastChild}
},
{
      [Date].[Calendar].Members
}
)
 ON 1
FROM
      [Adventure Works]

 

 

As you can see the pre aggregated approach returns exactly the same result as the scope version.

I tested the performance in MDX of both versions and i found that the pre aggregated version seems to perform a bit better. I did the tests with this code (I tried to make the query as granular as possible for a good test):

First clear the cache by following these instructions:

http://msdn.microsoft.com/en-us/library/hh230974.aspx

Then run this query in MDX to test the Scope approach:


SELECT
{
      [Measures].[Internet Order Quantity2]
} ON 0,
NON EMPTY
(
{
      [Promotion].[Promotions].[All Promotions].FirstChild.FirstChild.FirstChild:
      [Promotion].[Promotions].[All Promotions].LastChild.LastChild.LastChild
},
{
      [Customer].[Customer Geography].[All Customers].FirstChild.FirstChild.FirstChild:
      [Customer].[Customer Geography].[All Customers].LastChild.LastChild.LastChild
},
{
      {[Product].[Product Categories].[All Products].FirstChild.FirstChild.FirstChild:
       [Product].[Product Categories].[All Products].LastChild.LastChild.LastChild}
},
{
      [Date].[Calendar].Members
}
)
 ON 1
FROM
      [Adventure Works]

 

This query took 58 seconds on my pc. Now if i run the clear cache query again and change the “Internet Order Quantity2” measure to “Internet order Quantity3” the query completes in 47 seconds, not much of a saving, but it does make a differences.

Both approaches I believe are good, but I personally prefer the pre-aggregated in sql approach for a few reasons:

1.)      Performance

2.)      Deployment into production (a view in sql is deployed a lot easier than a cube), in both cases the cube needs to be processed though.

3.)      The sql approach will not be affected by a design change to hierarchies on the dimensions.

The scope approach however can change logic at any level in the hierarchies ie different logic at category level to product level, this will be a lot more difficult to achieve in sql though (probably need to do a bit in both parts cube and sql).

Aside | Posted on by | Leave a comment

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

 

 

 

Posted in Analysis Services, MDX Performance, MDX Time calculations, SQL, TSQL | Leave a comment