Decathlon

Intro To MDX Decathlon–PERIODSTODATE() Function (10/10)

By on • ( 14 Comments )

For the final post in this series, we’ll take a look at the PERIODSTODATE() function. Your skills of predictive analytics should suggest that this is a handy one to use for reports needing calculations for year-to-date, month-to-date, etc.

The PERIODSTODATE() function takes two inputs:

  • [Level]: The hierarchy level defining the “Period” part of the period-to-date
  • [Ending Member]: The member that will form the end of the resulting set as well as the level at which the members will reside

It returns the set of members at the same level as the [Ending Member] going back to the beginning of the [Level] hierarchy level. That’s a fun one to try to explain in one sentence. Let’s look at an example and it will become clearer.

Let’s suppose we find ourselves on January 10th, 2011, as shown in Image 1.

Image 1

image

The MDX expression for this member of my Date Hierarchy would be:

[Date].[Date Hierarchy].[Full Date].[2011-01-10]

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

If we wanted to find all of the [Full Date] members for the month of January in 2011 up to and including the [2011-01-10] member, we could use the PERIODSTODATE() function like this:

PeriodsToDate
(
  [Date].[Date Hierarchy].[Calendar Month]         -- Level Boundary
, [Date].[Date Hierarchy].[Full Date].[2011-01-10] -- Ending Member
)

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

The expression above specifies that we want to return the set of members that would defined the month-to-date starting with January 10th, 2011.

Our resulting set is shown highlighted in Image 2.

Image 2

image

An MDX expression for the resulting set would be:

{
  [Date].[Date Hierarchy].[Full Date].[2011-01-01]
, [Date].[Date Hierarchy].[Full Date].[2011-01-02]
, [Date].[Date Hierarchy].[Full Date].[2011-01-03]
, [Date].[Date Hierarchy].[Full Date].[2011-01-04]
, [Date].[Date Hierarchy].[Full Date].[2011-01-05]
, [Date].[Date Hierarchy].[Full Date].[2011-01-06]
, [Date].[Date Hierarchy].[Full Date].[2011-01-07]
, [Date].[Date Hierarchy].[Full Date].[2011-01-08]
, [Date].[Date Hierarchy].[Full Date].[2011-01-09]
, [Date].[Date Hierarchy].[Full Date].[2011-01-10]
}

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

You will note that the resulting set includes all of the members back to the beginning of January in 2011. Since are Ending Member was at the [Full Date] level of the hierarchy, the resulting members are at that level as well. And, since the Level specified was [Calendar Month], we therefore get all [Full Date] members from our Ending Member back to the very first [Full Date] within our Ending Member’s  [Calendar Month].

Note: Since you have been following this series with the utmost diligence for the last ten weeks, you will recall the explanation of the Range operator in the Intro To MDX Decathlon – Sets: A Shirt of a Different Color (4/10) post. Therefore, you will know that a more succinct expression for the above set would be:

{
  [Date].[Date Hierarchy].[Full Date].[2011-01-01]
: [Date].[Date Hierarchy].[Full Date].[2011-01-10]
}

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

Let’s look at some examples from my Borg cube.

Query 1 uses the PERIODSTODATE() function to return all of the [Calendar Month] members of the 1990 [Calendar Year] up to and including the [1990-04] member, ie April.

Query 1

SELECT
      [Measures].[Episode Count] ON COLUMNS
    , PeriodsToDate
        (
          [Date].[Date Hierarchy].[Calendar Year]            -- Period to encompass
        , [Date].[Date Hierarchy].[Calendar Month].[1990-04] -- Where to start
        ) ON ROWS
FROM [Borg]
WHERE [Series].[Series Name].[Star Trek: The Next Generation]

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

Result 1

image

Our result is January thru April of 1990.

Query 2 uses the same ending member but this time specifies [Calendar Quarter] as the level.

Query 2

SELECT
      [Measures].[Episode Count] ON COLUMNS
    , PeriodsToDate
        (
          [Date].[Date Hierarchy].[Calendar Quarter]         -- Period to encompass
        , [Date].[Date Hierarchy].[Calendar Month].[1990-04] -- Where to start
        ) ON ROWS
FROM [Borg]
WHERE [Series].[Series Name].[Star Trek: The Next Generation]

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

Result 2

image

Since the [Calendar Month] member specified, [1990-04], is the first [Calendar Month] member within its [Calendar Quarter], our result is just the [1990-04] member we originally specified.

This brings my first blog series to a close. I hope I was able to show that MDX does not have to be intimidating. I encourage you to try the MDX examples you saw here or use them as a model for trying MDX in your own environment.

Categories: Decathlon, MDX

Tagged as: ,

14 replies »

  1. Thank you for such a concise and interesting look into MDX. Having worked with with MDX for just over a year, my only complaint is that I wish you’d done this one year earlier (grin). I thought the examples where very easy to follow as as the pace. If you ever consider doing an advanced look into MDX I’d be very interested in reading it.

    Like

  2. Thank you very much, Preet. I learned a lot in creating this series and it is great that others are benefiting from it as well.

    Like

  4. Don’t you think, Query 1 and Query 2 has wrong comment….

    — Where to start it should be –Ending Member based on your example.

    Like

  5. Hi, Anoop. Chronologically speaking, you are correct that the date specified would be the END date. In the post, though, I am viewing it from the perspective of going back on time, in which case you would START at [1990-04] and then proceed BACK from there. I hope that helps. Thanks much.

    Like

  7. Hello Mark V, i was one of the participants at the sql Saturday recently held in boston. I was very impressed with your presentation and how you make the whole concept more digestible.
    One question regarding the Periods to date is, when i try to use multiple periodstodate ( one for MTD other for YTD) there is a error thrown stating that the Time interval hierarchy was used more than once . How do we have both MTD and YTD showup side by side? If you are ok with it i could send over a more detailed explanation of what i m trying to achieve.

    Thanks
    Srikanth

    Like

  8. Hi, Srikanth. Thanks for attending my session. Glad you found it helpful. In terms if displaying two periods at different levels side by side, I have never done that in MDX. I got the same type of error you are getting. But I have used MDX from inside a T-SQL stored procedure at a client. That was excellent in that we could hit the cube multiple times and then tie those resultsets together in T-SQL after the fact. You can read about it on Dan English’s blog here: http://denglishbi.wordpress.com/2008/03/01/using-reporting-services-ssrs-with-ssas-data/ I hope that helps. My MDX isn’t much beyond what is in this series, so I am probably not the best person to try to solve this on in MDX. You could also post to MSDN and ten share that post on Twitter or something. That has been a helpful avenue in the past.

    Like

  9. Hello Mark,

    Thanks for your quick response. I will go through the link you have provided. Appreciate your feed back 🙂

    Like

  10. Came from a T-SQL background and started working on analysis cubes. I could not get my head around using MDX using the standard MSDN tutorials. However, I found your tutorials simple, engaging and funny. I have learnt a lot more in 1-2 hours using your tutorial then the several days spent on the MSDN site. Thanks you. Cheers!

    Like