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