MDX Function, few more

After the post MDX introduction and MDX function, here in the same series I am going to explain few more function which will be very frequent.

PeriodsToDate

Is a time based function; it gives you till period values from the start of the period. A period can be any time based period like Quarter, Week, Month and Year.

Function takes a level and a member as input parameter. Level is the level you want to travel and member is till that member.

SELECT

      {[Measures].[Internet Order Count]} ON COLUMNS,

      PeriodsToDate([Date].[Calendar].[Calendar Year],[Date].[Calendar].[Month].&[2006]&[8]) ON ROWS

FROM      [Adventure Works]   

So here result will give you all the values starting from month 1 of year 2006 to the Month 8 of year 2006.

Interesting, isn’t it??

Well more understating will be when it is user dynamic.

To do so we have to create a calculated value. For crating a calculated value we use WITH MEMBER.

Following is Syntax of 

WITH MEMBER [Measures].[YourMeasureName]

AS

<>

 Than normal SELECT Statement which uses above calculation in any of their AXIS

Hear it how it comes together -

WITH MEMBER [Measures].[Internet Order Count YTD]

AS

aggregate(

PeriodsToDate([Date].[Calendar].[Calendar Year], [Date].[Calendar].currentmember)

,[Measures].[Internet Order Count]

)    

SELECT

      {[Measures].[Internet Order Count],[Measures].[Internet Order Count YTD]} ON COLUMNS,

      [Date].[Calendar].[Month] ON ROWS

FROM [Adventure Works]

WHERE {[Date].[Calendar Year].&[2007]}

Here we have simple used PeriodsToDate With in a calculation.

Here instead of a member we have used currentmember. This current member will help us calculate will you for every rows means for every member of Month attribute.

When you run this query result will give you cumulative of internet Order counts till year end.

Another important Time period based function is

PARALLELPERIOD

Return a data for  a period in the same relative position as a specified member, this period can be before or upcoming based on the input given.

SELECT

    PARALLELPERIOD([Date].[Calendar].[Calendar Year],1, [Date].[Calendar].[Month].&[2010]&[9]) ON COLUMNS

     

FROM [Adventure Works]

We can also have dynamic query here as well, see following

WITH MEMBER [Measures].[Internet Order Count PP]

AS

aggregate(

PARALLELPERIOD([Date].[Calendar].[Calendar Year],1, [Date].[Calendar].currentmember)

,[Measures].[Internet Order Count]

)    

SELECT

      {[Measures].[Internet Order Count],[Measures].[Internet Order Count PP]} ON COLUMNS,

      [Date].[Calendar].[Month] ON ROWS

FROM [Adventure Works]

WHERE {[Date].[Calendar Year].&[2010]}

Hope this will help you understand basic of MDX and also some of the Important MDX functions. 

MDX Functions

Once you know basics we can have look at the basic functions and properties of MDX.

I hope you have read the previous blog MDX  Introduction.  

In the last post I have shown you a very basic MDX, it just show one member, if you want to see all the members than

SELECT {[Measures].[Internet Sales Amount]} ON COLUMNS,

[Date].[Calendar Year].ALLMEMBERS on 1

FROM [Adventure Works]

Using . ALLMEMBERS to a dimension attribute will give you all the members of that attributes.

Just like this we have some more properties and function

PREVMEMBER

 Gives you previous member of a Dimension Member.  

SELECT {[Measures].[Internet Sales Amount]} ON COLUMNS,

[Date].[Calendar Year].&[2009].PREVMEMBER on 1

FROM [Adventure Works]

So you will get data across Year 2008.

RANGE

Range is not a function but it’s a way of writing MDX, by which you can see data for a range of dimension member.  

SELECT {[Measures].[Internet Order Count]} on 0,

{[Date].[Calendar Year].&[2005]:[Date].[Calendar Year].&[2007]} on 1

FROM [Adventure Works]

FILTER

If you want to filter data on Axis based on a condtion you can use filter function.  Like following

SELECT [Measures].[Internet Sales Amount] ON 0,

FILTER

(

[Date].[Date].[Date].MEMBERS

, [Measures].[Internet Sales Amount]<10000 o:p="o:p">

) ON 1

FROM [Adventure Works]

FILTER takes a set and a condition as input parameter.

In above example we are showing all the dates for which we have Internet sales amount more than 10000.

Instead of a measure you can also have Dimensions as Filter condition.

EXCEPT

Same as FILTER there is Except

--Except

SELECT [Measures].[Order Quantity]

   ON COLUMNS,

   Except

      ([Product].[Product Categories].[All].Children ,

         {[Product].[Product Categories].[Components]}

      ) ON ROWS

FROM

   [Adventure Works]

It gives you result except the condition, so its other way around of Filter.

So it takes a set and a condition as input parameter, but here result will exclude the condition and gives you result.

This are few function I have explained you above, in next post will see more of those. 

MDX Introduction

MDX: Well you must have heard of it. So like you need T SQL or SQL to communicate with a RDBS, same way you need MDX to communicate with Multi-Dimensional OLAP (MOLAP).

MDX is Multi-Dimensional Expression. .  It is part of the OLEDB for OLAP specification which was developed by Microsoft but is now supported by multiple vendors.

Main purpose of MDX is to create query statements for data retrieval. We can also perform some of the management functions, such as the UPDATE CUBE statement etc.

It is good if you have knowledge of SQL as that will help in learning MDX.

MDX seems to be tough, but believe me once you have an understanding of it, it will not be complex and you will enjoy writing it.

Let see how MDX statement looks like –

SELECT, FROM and WHERE clause you have seen in SQL as well, so they are self-explanatory.

 SELECT is to select , FROM is to from where do you want to select. In MDX it will a cube or a sub query. And WHERE to filter your selection that we call it SLICING.

In addition to it, here you define Axis. Well its Multi-Dimensional, isn’t it??  So need different dimensional Axis here.

Let see an example here -

 I consider you have basic knowledge of Facts and Dimensions and you have Adventure Works DB & Cube installed.

SELECT {[Measures].[Internet Sales Amount]} ON COLUMNS,

[Date].[Calendar Year].&[2009] on 1

FROM [Adventure Works]

Well its very simple SELECT statement, where we have selected measure Sales Amount with respect to Calder Year 2007 of date dimension.

Result will be as follows –

So writing first statement was not that tough??

You can put different member of different dimension and can try by your Self.

In the Next blog I will go more detail about MDX and will discuss some of the function of MDX.