ABC Classification

I was asked to make a simple ABC classification for a sales measures. I combined exists with TopPercent to identify:

• Class A = between the top 0 and 20 %
• Class B = between the top 20 and 80%
• Class C = between the top 80 and 100%

Calculating an Age

One of the questions you often get is how to calculate a customers age on a certain date. Luckily Chris Webb published a great example: http://cwebbbi.wordpress.com/2014/06/09/calculating-ages-in-mdx/.

I made a small extension to this by making it level aware, this means you can also use it at year, semester, quarter and month level.

To make it level aware I use the .Level.Ordinal extension on the Currentmember. This returns the Level of the member within the Hierarchy (0 = All, 1 = Year, 2 = Semester, 3 = Quarter, 4 = Month, 5 = date).

CASE WHEN and NULL / 0 (zero)

When you have a CASE WHEN in MDX you basically have two options:

The Simple or Searched statement:

CASE [<<Set To Be Evaluated>>]

WHEN [<<condition1>>] THEN [<<action>>]

WHEN [<<condition2>>] THEN [<<action>>]

ELSE [<<action>>] END

Calculating Median and Percentile

MEDIAN or 50th Percentile

According to Wikipedia the median or 50th percentile is the numerical value separating the higher half of a data sample, a population, or a probability distribution, from the lower half. Some examples:

Set	Median / 50th percentile
1,2,3,4,5	3
1,2,3,3,4,5	3
1,2,3,4,4,5	3,5
1,2,3,3,4,4,5	3
1,2,3,3,4,4,5,5	3,5

In this example you see that if the set contains an even number of members the median is the average between the numbers left and right from the centre.

YearToDate (YTD) Previous Year (also QTD, MTD, WTD)

This is simple is you combine ParallelPeriod with the YTD functionality:

WITH
MEMBER [Measures].[Sales Amount YearToDate]
AS
    SUM(YTD( [Date].[Calendar].CurrentMember),[Measures].[Sales Amount])

MEMBER [Measures].[Sales Amount Previous Year]
AS
    SUM(ParallelPeriod( [Date].[Calendar].[Calendar Year], 1,[Date].[Calendar].CurrentMember),[Measures].[Sales Amount])

MEMBER [Measures].[Sales Amount YearToDate Previous Year]
AS
    SUM(YTD( ParallelPeriod( [Date].[Calendar].[Calendar Year], 1,[Date].[Calendar].CurrentMember)),[Measures].[Sales Amount])

PeriodsToDate, YearToDate (YTD), QuarterToDate (QTD), MonthToDate (MTD), WeekToDate (WTD)

PeriodsToDate, YearToDate (YTD), QuarterToDate (QTD), MonthToDate (MTD), WeekToDate (WTD) are all MDX functions which aggregate date from de first date of a chosen period up and an including a given end date.

Percentage difference from previous parallel period

Let’s start by getting the parallel period:

WITH MEMBER [Measures].[Sales Amount PY]
AS
(
    [Measures].[Sales Amount]
    , ParallelPeriod
        ([Date].[Calendar].[Calendar Year] // Level
        , 1 // number of periods back
        , [Date].[Calendar].CurrentMember) // Start member
)
, format_string = "Currency"

ParallelPeriod

The ParallelPeriod functionality is closely related to the Cousin functionality. The main difference is that the ParallelPeriod function expects a hierarchy of the type Time.
It will get you the value of a measure based given top-level.

Percentage of one level up

One of the most common calculations is calculating the percentage of the current value to the total one level up. For instance [Measures].[Sales Amount] for the quarter compared to the semester.
This gives use several challenge's.
1. What is the total amount one level up. With the .CurrentMember property we know where we are. If we combine this with the .Parent property we can calculate the total amount: ([Measures].[Sales Amount],[Date].[Calendar].CurrentMember.Parent) .

RANGE

The Range functionality allows you to define a set of member based on the first and last member of the range. You do this by using the colon “:”.
Fixed Range:
SELECT
[Date].[Calendar Year].[CY 2011]:[Date].[Calendar Year].[CY 2014] // FIXED RANGE
ON COLUMNS,
[Measures].[Sales Amount] ON ROWS
FROM [Adventure Works]

UNION

The UNION functionality is of the three Venn Collection functionality's (EXCEPT, INTERSECT, UNION).
UNION will give all the members of SET1,SET2…. SETn. By default it will remove any duplicates.

Intersect

The Intersect functionality is of the three Venn Collection functionality's (EXCEPT, INTERSECT, UNION).
Intersect will give all the members of SET1 which are also in SET2. By default it will remove any duplicates.

Except

The Except functionality is of the three Venn Collection functionality's (EXCEPT, INTERSECT, UNION).

Except will give all the members of SET1 which are not in SET2. By default it will remove any duplicates which are in SET1.

Cousin

The Cousin functionality will get you the member on same position as the starting based on a new toplevel member. Most of the time you use it in the same way you use ParallelPeriod.

If you look at a date or calendar dimension is might look like this: All ==> Year ==> Semester ==> Quarter ==> Month ==> Date.

If you use a query like this:

Descendants

The Descendants functionality although in name de reverse of the Ascendants functionality has a lot more options.

Before you read on just a reminder the Geography hierarchy in the Adventure Works sample is: All ==> Country ==> State-Province ==> City ==> Postal Code.

Let’s start with the base:

SELECT Descendants
   (
    [Geography].[Geography].[Country].&[United States]
   ) ON ROWS
   ,
    [Measures].[Reseller Order Count] on COLUMNS
FROM [Adventure Works]

Ascendants

The Ascendants functionality gives back all levels from and including the current level.
SELECT
   Measures.[Sales Amount] ON COLUMNS,
   Order(
      Ascendants(
      [Product].[Product Categories].[Product].&[448]
      ),
      DESC
   ) ON ROWS
FROM
   [Adventure Works]
where ([Date].[Calendar Year].&[2012])

Ancestor

The Ancestor functionality allows us to find, parents, grand parents, great grand parents etc.… The number of levels you want to go up can be a number or a dedicated level.
The Ancestor with a level of 1 is the same as the .Parent functionality
Let’s try:

AllMembers vs Children

Both do what their name implies .
AllMembers will return All Members on the current level and all levels below.
SELECT
    {       
        ([Date].[Calendar Year].&[2013], [Measures].[Sales Amount])
    }
ON COLUMNS
,
    {
        ([Product].[Product Model Lines].AllMembers)
    } ON ROWS
FROM
   [Adventure Works]

Parent, FirstChild and LastChild

Using the Parent functionality you can get the First Child and Last Child within hierarchy level. 15-JAN-2011 ==> Parent = JAN-2011 ==> FirstChild = 1-JAN-2011 ==> LastChild = 31-JAN-2011.
Here is an example:

LAG an LEAD

The LAG and LEAD functionality allow to jump back or forward a number of step on the same hierarchy level.
LAG(1) and LEAD(1) is the same as PrevMember and Next Member.

PREVMEMBER and NEXTMEMBER

The PREVMEMBER functionality will give you the previous member on the same hierarchy level. 1-JAN-2011 ==> 31-DEC-2010, 2-JAN-2011 ==> 1-JAN-2011, CY 2011 ==> CY 2010.
The NEXTMEMBER functionality will give you the next member on the same hierarchy level. 1-JAN-2011 ==> 2-JAN-2011, 31-DEC-2011 ==> 1-JAN-2012, CY 2011 ==> CY2012
We can use this calculate difference between periods.

MDX using member

The member function in MDX is comparable with the MS-SQL CTE functionality. It allows you to define a member before you use it in your query and thus keeping your query more readable.
An example:
WITH MEMBER [Measures].[Prior Year Sales] AS
    SUM(     
        ParallelPeriod
            (
                [Date].[Calendar].[Calendar Year],
                1,
                [Date].[Calendar].CurrentMember
            )
            ,
            [Measures].[Sales Amount]
       )
This calculates the SUM of the Sales Amount for the previous year using the ParallelPeriod functionality in combination with CurrentMember.

More dimensions on rows

Let’s add a second dimension to our query:

SELECT
    {
     [Measures].[Sales Amount]   
    } ON COLUMNS
    ,   
    NON EMPTY
    {
      [Date].[Calendar Year].Children
    , [Product].[Product Model Lines].Children
    }                    
    ON ROWS
FROM
    [Adventure Works]

Hmmmm, we get an error:

“Members, tuples or sets must use the same hierarchies in the  function.”

More Measure Columns

Adding more measure columns is simple, but you have to be aware of the extra Curly Brackets “{ }” which enclose your row or columns definition. Let’s try:
SELECT
    {
     [Measures].[Sales Amount]
    ,[Measures].[Tax Amount]
    ,[Measures].[Total Product Cost]    
    } ON COLUMNS
    ,   
    NON EMPTY
    {
    [Date].[Calendar Year].Children
    }                    
    ON ROWS
FROM
    [Adventure Works]

My first MDX query

Open up your SSMS (Sequel Server Management Studio) and make a connection to your “Adventure Works” OLAP database.

Click on the MDX query button: .

What is MDX?

According to Wikipedia MDX stands for MultiDimensional eXpressions. It’s a SQL dialect to access OLAP cubes.

So what’s different to “normal” SQL? Basically you don’t have any tables or views, you just have a collection of fact and dimensions.

A fact is the recording of the occurrence of an event: “On July 28th 2014, mrs Jones from 4 Sally Nogging Road, Dun Loaghaire, Ireland, at 10:21 am bought 4 pounds of Ben and Jerry Cooky Dough Ice Cream at mr. Humpries Butcher shop, she was helped by a store clerk called Maria etc… etc…”

A dimension is the description and categorisation of an element related to the fact.”mr. Humpries Butcher shop is owned by mrs. o’Conner, it’s a COOP franchise, it’s located at 12 Dublin Street, Dun Loaghaire, County Dublin, Ireland etc… etc…”

MDX allows you to organize these facts and dimensions in columns and rows.

Is MDX hard to learn? If you have a basic understanding of SQL, MDX is not hard to learn. The mean difference is that you have to start to think in more than two dimensions, but we will get to that later.

For the time being I will do all my posts on this blog against the SSAS 2014 demo cube “Adventure Works”.

Till Next Time.

Welcome to MDX101 / 101MDX

Welcome to a new 101 blog from me.

I know I've been a while, but let's pick it up again.

Why is this called 101MDX.blogspot.com? Simple MDX101.blogspot.com was already taken....

Why not use my SSAS101? Well I want to share my notes on MDX even if you use it with an other tool like Excel or cubus EV viewer.

Till Next Time