Like the LEAD() and LAG() functions we looked at in the previous post, the PARALLELPERIOD() function is also useful for travelling through ordered hierarchies. However, there is one really important difference. While LEAD() and LAG() use the hierarchy level of the member passed to the function to determine the hierarchy level to be traversed, the PARALLELPERIOD() function lets you specify the level as an argument. Because of this, the hierarchy level traversed can be different from the level of the member passed and the resulting member. This can be a bit confusing at first. The examples will help, though.
The PARALLELPERIOD() function takes three inputs:
- [Level]: The hierarchy level to traverse
- [Index]: an integer value indicating the number of members to travel
- [Starting Member]:The starting member
It returns the member that is [Index] members of the [Level] hierarchy level previous to the [Starting Member] member.
Let’s suppose we find ourselves on March 10th, 2011, as shown in the calendar in Image 1.
Image 1
The MDX expression for this member of my date hierarchy would be:
[Date].[Date Hierarchy].[Full Date].[2011-03-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 travel back in time two months, we could make use of the PARALLELPERIOD() function:
ParallelPeriod(
[Date].[Date Hierarchy].[Calendar Month] -- Level
, 2 -- Index
, [Date].[Date Hierarchy].[Full Date].[2011-03-10] -- Starting 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; }
Notice we are traversing the Calendar Month while passing a starting member that is a Full Date.
The resulting date is shown in Image 2.
Image 2
The MDX expression for the resulting member 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; }
Since we specified a level of [Calendar Month] and an index of 2, we travelled 2 calendar months back within our Date Hierarchy. Since our starting member was the tenth day of its parent month, the result was the tenth day of its month. Thus, we travel from March 10th, 2011 back exactly two months to January 10th, 2011.
Let’s take a look at my Borg cube.
In Query 1, we start on the [Calendar Month].[1990-01] member. We then travel one member back on the [Calendar Year] level of the hierarchy.
Query 1
SELECT
[Measures].[Episode Count] ON COLUMNS
, ParallelPeriod(
[Date].[Date Hierarchy].[Calendar Year] -- Level to traverse
, 1 -- How many members to go back on the Level
, [Date].[Date Hierarchy].[Calendar Month].[1990-01] -- 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
Since we started on the first calendar month of 1990, when we travel one calendar year back, we get to the first calendar month of 1989.
In Query 2, we once again start on the [Calendar Month].[1990-01] member. This time, instead of traversing at the [Calendar Year] level, we use the [Calendar Quarter] level.
Query 2
SELECT
[Measures].[Episode Count] ON COLUMNS
, ParallelPeriod(
[Date].[Date Hierarchy].[Calendar Quarter] -- Level to traverse
, 1 -- How many members to go back on the Level
, [Date].[Date Hierarchy].[Calendar Month].[1990-01] -- 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
This time, we travel back one calendar quarter. Since we started on the first [Calendar Month] of the first quarter of 1990, travelling back one quarter takes us to the first [Calendar Month] of the 4th quarter of 1989: October.
That’s really all there is to this one. It can just get a little confusing when the level being traversed can be different than the level of the starting and ending members.
Next time, in the final post in this series, we will play with the mighty PERIODSTODATE() function.
Mark Vaillancourt 
1 reply »