Tuesday 6 November 2012

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. 

3 comments:

  1. Dear friend. I truly just like your posting and your current web page all in all! That write-up is really plainly composed and without difficulty understandable. Your Wordpress style is awesome as well! Would be awesome to discover exactly where My partner and i are able obtain this. Please hold up the very good work. We all need much more this kind of website owners just like you on the web and much less spammers. Wonderful man!
    http://sqlservermasters.com/

    ReplyDelete
  2. Hi
    I read this post two times.
    I like it so much, please try to keep posting.
    Let me introduce other material that may be good for our community.

    < a href="http://sqlservermasters.com/" >msbi online training < /a>

    ReplyDelete
  3. The Teradata migration helps enterprises to harness the benefits such as scalability, flexibility, and cost efficiency. The process mainly involves data assessment, transformation, and ensuring compatibility with the target platform. Website: https://www.leaplogic.io/modernization/video/automated-workload-transformation-teradata-databricks

    ReplyDelete