Return cross product of two or more sets in MDX using “CrossJoin”

Posted on 23. August 2014 by Sindre • Posted in MDX • Tagged CrossJoin, MDX

One of the functions I use almost all the time when I am writing MDX is the crossjoin function. This function returns the cross product of two or more sets in MDX.

Lets look at an example

SELECT [Measures].[Internet Sales Amount] ON 0,
NON EMPTY [Date].[Calendar].[Calendar Year] ON 1
FROM [Adventure Works]

This code will give us sales per year. But what if we want sales per year per city? Lets introduce the crossjoin function!

SELECT [Measures].[Internet Sales Amount] ON 0,
NON EMPTY CrossJoin([Date].[Calendar].[Calendar Year], [Customer].[City].[City])  ON 1
FROM [Adventure Works]¨

And that would gives ut the result we want

How about sales per contry per city per year? Then we will have to nest 2 crossjoins like this.

SELECT [Measures].[Internet Sales Amount] ON 0,
NON EMPTY CrossJoin([Customer].[Country].[Country], CrossJoin([Customer].[City].[City], [Date].[Calendar].[Calendar Year]))  ON 1
FROM [Adventure Works]

Instead of crossjoin you can also use a ‘*’. That is the way I ususally write my code

Here is an example on that

SELECT [Measures].[Internet Sales Amount] ON 0,
NON EMPTY [Customer].[Country].[Country] * [Customer].[City].[City] * [Date].[Calendar].[Calendar Year]  ON 1
FROM [Adventure Works]

“Year to date” and “Last Year To Date” in MDX

Posted on 17. August 2014 by Sindre • Posted in MDX • Tagged Last year to date, LYTD, ParallelPeriod, Year To date, YTD • Leave a comment

To get “Year To Date” and “Last Year To Date” in MDX we can use the YTD function. To get “Last Year To Date” we also have to use the “ParallelPeriod” function. The “ParallelPeriod” gives us the same dates for an other period. In this case we switch back one year.

Here is a sample

WITH MEMBER [Year To Date] AS
( Sum (
Ytd ( [Date].[Calendar].CurrentMember ),
[Measures].[Internet Sales Amount]
) )
MEMBER [Last Year To Date] AS
( Sum (
Ytd (
ParallelPeriod ( [Date].[Calendar].[Calendar Year], 1, [Date].[Calendar].CurrentMember )
),
[Measures].[Internet Sales Amount]
) )
MEMBER [Change] AS
divide ( [Measures].[Year To Date], [Last Year To Date] ),
FORMAT_STRING = 'Percent'
SELECT
{ [Measures].[Year To Date], [Measures].[Last Year To Date], [Measures].[Change] } ON 0,
[Date].[Calendar].[Date].&[20120214] ON 1
FROM [Adventure Works]

And here is the result

This code will work in Analysis Services Multidimensional. If you want to do the same for “Month to date”, “Quarter to date” or similar you can change YTD to MTD or QTD. If you plan to use week you should use an other hierarchy and then change to WTD.

I would recommend you to add this functionality as calculations in the cube. There are several great scripts out there for you to copy. Such as DateTool by Marco Russo.

Comparable day in MDX using “lag” or “ParallelPeriod”

Posted on 14. August 2014 by Sindre • Posted in MDX • Tagged Aggregate, Comparable days, lag, MDX, ParallelPeriod • Leave a comment

In many companies people want to compare todays sales with the comparable date last year. To find out what date the comparable day is we usually goes 364 days back from today.

In our sample today is 14 february 2012. And to see what the comparable date is we can use this query in SQL.

select '2012/02/14' as TodaysDate, DateName(dw, '2012/02/14') As 'DayName', 
dateadd(day, -364, '2012/02/14') As ComparableDay, DateName(dw, dateadd(day, -364, '2012/02/14')) As ComparableDayName

This will give this result

To check the sales for these days in MDX we can use these queries:

SELECT [Measures].[Internet Sales Amount] ON 0,
[Date].[Calendar].[Date].&[20120214] ON 1
FROM [Adventure Works]

SELECT [Measures].[Internet Sales Amount] ON 0,
[Date].[Calendar].[Date].&[20110215] ON 1
FROM [Adventure Works]

The final query that compares today sales can look like this (using “lag”)

WITH MEMBER [Sales comparable day] AS
  ( [Date].[Calendar].CurrentMember .Lag ( 364 ),
  [Measures].[Internet Sales Amount] ),
  FORMAT_STRING = "Currency"
MEMBER [Percent change] AS
  [Measures].[Internet Sales Amount] / [Measures].[Sales comparable day],
  FORMAT_STRING = "Percent"
SELECT
{ [Measures].[Internet Sales Amount], [Measures].[Sales comparable day], [Measures].[Percent change] } ON 0,
[Date].[Calendar].[Date].&[20120214] ON 1
FROM [Adventure Works]

Or this (using ParallelPeriod and Aggregate)

WITH MEMBER [Sales comparable day] AS
  Aggregate (
    ParallelPeriod ( [Date].[Calendar].[Date], 364, [Date].[Calendar].CurrentMember ),
    [Measures].[Internet Sales Amount]
  )
MEMBER [Percent change] AS
  [Measures].[Internet Sales Amount] / [Measures].[Sales comparable day],
  FORMAT_STRING = "Percent"
SELECT
{ [Measures].[Internet Sales Amount], [Measures].[Sales comparable day], [Measures].[Percent change] } ON 0,
[Date].[Calendar].[Date].&[20120214] ON 1
FROM [Adventure Works]

These samples can be run against the AdventureWorks cube in Multidimensional mode. To avoid error messages please make sure your time dimension has the members for the date you are comparing with. In this case 15 february 2011 has to exists in the time dimension. The -364 approach will have issues if last year was a leap years.

Running total in MDX

Posted on 12. August 2014 by Sindre • Posted in MDX • Tagged MDX, Running total • 1 Comment

Here is a sample on how you can get running total in MDX. The query below gives sales and running total for january 2011 per date.

WITH MEMBER [Measures].[Running Total] AS
  Aggregate (
    { [Date].[Calendar].CurrentMember.Parent.FirstChild : [Date].[Calendar].CurrentMember },
    [Measures].[Internet Sales Amount]
  )
SELECT
{ [Measures].[Internet Sales Amount], [Measures].[Running Total] } ON 0,
[Date].[Calendar].[Month].&[2011]&[1].Children ON 1
FROM [Adventure Works]

And the result will look like this:

A query for month level can look like this:

WITH MEMBER [Measures].[Running Total] AS
  Aggregate (
    { [Date].[Calendar].CurrentMember.Parent.FirstChild : [Date].[Calendar].CurrentMember },
    [Measures].[Internet Sales Amount]
  )
SELECT
{ [Measures].[Internet Sales Amount], [Measures].[Running Total] } ON 0,
[Date].[Calendar].[Calendar Quarter].&[2011]&[1].Children ON 1
FROM [Adventure Works]

This will look like this

Because the parent level of month is quarter you will only get 3 months. In the cubes I make for customers I always have hierarchies for “Year – Month – Date” and then it should not be any problem 🙂

Please note that this sample has to be run against a multidimensional instance of Analysis Services.

One of my favourite “Mosha tricks”. Fast count in MDX

Posted on 9. August 2014 by Sindre • Posted in MDX • Tagged Count, Filter, Iif, Sum. MDX • Leave a comment

I am quite sure that many of you MDX nerds have been reading articles and tips on Moshas old blog (http://sqlblog.com/blogs/mosha/). Mosha was one of the inventors of MDX.

If you are struggling with MDX performance take a look on Moshas blog.

One of my favourite trick on this blog is the alternative way of counting records in MDX. Off course you have the regular way using the COUNT function.

WITH MEMBER [Number Of Products] AS
  Count ( Filter ( [Product].[Product Name].[Product Name], [Measures].[Internet Total Sales] ) )
SELECT
{ [Measures].[Internet Total Sales], [Measures].[Number Of Products] } ON 0,
NON EMPTY Order ( [Geography].[City].[City], [Measures].[Internet Total Sales], DESC ) ON 1
FROM [Internet Sales]

The problem with this is that it does not evaluate as bulk mode. This will make the query run really slow.

One way of making this query faster is to use the SUM function with an IIF test.

WITH MEMBER [Number Of Products] AS
  ( Sum (
    [Product].[Product Name].[Product Name].Members,
    IIf ( [Measures].[Internet Total Sales] > 0, 1, NULL )
  ) )
SELECT
{ [Measures].[Internet Total Sales], [Measures].[Number Of Products] } ON 0,
NON EMPTY Order ( [Geography].[City].[City], [Measures].[Internet Total Sales], DESC ) ON 1
FROM [Internet Sales]

This reduced the query time for one of my queries from about a minute to 4 seconds.

Where clause in MDX

Posted on 7. August 2014 by Sindre • Posted in MDX • Tagged Where • Leave a comment

Lets take a look on how the where clause in MDX works. IN MDX we can filter on dimension members like in the sample below.

SELECT [Measures].[Internet Total Sales] ON 0,
NON EMPTY [Geography].[City].[City] ON 1
FROM [Internet Sales]  
WHERE [Date].[Calendar Year].&[2007]

This will give sales per city for year 2007.

We can also use a custom time interval.

SELECT [Measures].[Internet Total Sales] ON 0,
NON EMPTY [Geography].[City].[City] ON 1
FROM [Internet Sales]  
WHERE ([Date].[Date].&[2005-01-05T00:00:00] : [Date].[Date].&[2006-03-08T00:00:00])

If you need to have more statements this can be done like this

SELECT [Measures].[Internet Total Sales] ON 0,
NON EMPTY [Geography].[City].[City] ON 1
FROM [Internet Sales]  
WHERE 
	(
	   {[Date].[Calendar Year].&[2006], [Date].[Calendar Year].&[2007]}
	 , {[Date].[Month Name].&[April], [Date].[Month Name].&[June]}
	 , [Product Category].[Product Category Name].&[Bikes]
)

Dynamic date filter in MDX using VBA

Posted on 3. August 2014 by Sindre • Posted in MDX • Tagged DYNAMIC DATE, MDX, NOW, StrToMember, VBA • Leave a comment

When I create a new cube I always add a few named sets for getting time periods automatically. These are nice to have if I have scheduled reports that should present data for today, yesterday, last week or maybe current month.

An example could be a named set that returns yesterdays date.

CREATE SET CURRENTCUBE.[Yesterdays date] AS 
StrToMember("[Date].[Date].&[" + Format(now()-1, "yyyy-MM-dd") + "T00:00:00]", CONSTRAINED);

And the named set might be used like this

SELECT [Measures].[Internet Total Sales] ON 0,
 [Current Date] 
 * [Product].[Model Name].[Model Name] ON 1
FROM [Internet Sales]

You can of course also use this within a single query like this.

WITH SET [Current date] AS 
StrToMember("[Date].[Date].&[" + Format(now()-1, "yyyy-MM-dd") + "T00:00:00]")    -- minus 1 gives yesterdays date

SELECT [Measures].[Internet Total Sales] ON 0,
 [Current Date] 
 * [Product].[Model Name].[Model Name] ON 1
FROM [Internet Sales]

Please note that the format of the member must be correct. In my case the Date dimensions members look like this: [Date].[Date].&[2005-01-10T00:00:00]. If you try to run these samples against the sample database you will not get any result. Since the database does not have data for the current period. If you want to test a valid date you can check how many days you have to remove by running this SQL:

select DATEDIFF(day, getdate(), '2006/05/18')

And then you can change the code “now()-1” to “now()-[The number of days the query returned]”

Here are some other useful samples

//Sample date sets.
CREATE SET CURRENTCUBE.[Current date] AS 
StrToMember("[Date].[Date].&[" + Format(now(), "yyyy-MM-dd") + "T00:00:00]", CONSTRAINED);       
     
-- Must be used with [This Year]
CREATE SET CURRENTCUBE.[This month] AS 
StrToMember("[Date].[Month].&[" + Format(now()-1, "MM") + "]", CONSTRAINED);        

CREATE SET CURRENTCUBE.[This year] AS 
StrToMember("[Date].[Calendar Year].&[" + Format(now(), "yyyy") + "]", CONSTRAINED);      

-- Can you create a named set for current week? Last week?

Top products within each category in MDX

Posted on 2. August 2014 by Sindre • Posted in MDX • Tagged GENERATE, MDX, TOPCOUNT • Leave a comment

If you want to have top products for each model this is a simple way of doing that in MDX.

SELECT [Measures].[Internet Total Sales] ON COLUMNS,
NON EMPTY Generate (
Order (
{ [Product].[Category].[Model].Members },
[Measures].[Internet Total Sales],
ASC
),
Crossjoin (
[Product].[Category].CurrentMember,
TopCount (
[Product].[Product Name].Children,
2,
( [Measures].[Internet Total Sales] )
)
)
) ON ROWS
FROM [Internet Sales]

The result will look like this

A blog about Analytics, AWS and Azure

Category Archives: MDX