Debunking the Myths: Cloud HA and DR common misconceptions

Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips






Learn more about SQL Server tools








Learn more about SQL Server tools


   Got a SQL tip?
            We want to know!

SQL Server Analysis Services-Lead, Lag, OpeningPeriod, ClosingPeriod Time Related Functions

MSSQLTips author Scott Murray By:   |   Read Comments (6)   |   Related Tips: > Analysis Services Development
Problem

Working with SSAS time related accounting functions can be tricky. SQL Server 2012 now offers Lag and Lead functions. Are these functions available in SSAS? What other related functions are available?

Solution

Most accounting and financial types will always want comparisons to prior periods of financial data.  In this tip we will cover the Lead and Lag functions along with the OpeningPeriod and ClosingPeriod functions which all assist with these comparisons. Surprisingly, the Lag and Lead functions have been available in SSAS for several versions and are only now making their way into regular SQL Server. One other closely aligned function is the ParallelPeriod function; Ray Barley did an excellent tip on the ParallelPeriod function which can be found at http://www.mssqltips.com/sqlservertip/2367/building-calculated-members-for-a-ssrs-report-using-mdx/  .

Lead and Lag

The Lead and Lag functions work as siblings or counter weights when processing data.  The Lead function moves n number of positions forward as compared to the original value.  The Lag function moves, accordingly, n number of positions back from the current member value.  The position is always in relation to the original value; additionally the current position is base zero and increments from that point. Both functions take two arguments. First is the value to be evaluated and the second is number of positions to move from the current member. Of course, a few examples would be helpful. These examples come from the Adventure Works SSAS 2012 database. Using the Lead function, we will first write the MDX to move 3 Quarters from Quarter 4, 2007. Now let us run the same code but use Lag and go 3 Quarters in the other direction.   


--Lead 3 quarters from Calendar Year 2007 Quarter 4
--Lag 3 quarters from Calendar Year 2007 Quarter 4
SELECT
{} ON COLUMNS,
{[Delivery Date].[Calendar].[Calendar Quarter].&[2007]&[4].LEAD(3),
[Delivery Date].[Calendar].[Calendar Quarter].&[2007]&[4].LAG(3)}
 ON ROWS
FROM
[ADVENTURE WORKS]

The results for each of the functions are illustrated below.    

Lead Lag Simple

Those examples are pretty straightforward, but likely not very useful.  The power of using these functions occurs when they are used in conjunction with actual measure values, such as Internet Sales in the Adventure Works database. 

--Lead & Lag of Calendar Year 2007 Quarter 4 with Measures
SELECT
{
[Measures].[Sales Amount] ,
[Measures].[Tax Amount]
} ON COLUMNS,
{
[Delivery Date].[Calendar].[Calendar Quarter].&[2007]&[4].LEAD(3),
[Delivery Date].[Calendar].[Calendar Quarter].&[2007]&[4],
[Delivery Date].[Calendar].[Calendar Quarter].&[2007]&[4].LAG(3)
}
 ON ROWS
FROM
[Adventure Works]

As the below illustration shows, this updated MDX shows the Sales Amount and Tax Amount broken out by the three quarters requested.  

LeadLagwithMeasure

Our examples up to this point have been Adhoc MDX queries. However, most report writers and users will want the values to be even more dynamic. We can utilize the power of the date dimension and a named set to explore the range of values in the date dimension. As noted in the below MDX code, the WITH function creates a Named Set which takes the Internet Sales Amount and traverses two periods prior from the CurrentMember. This named set value along with the current Internet Sales Amount are displayed on the columns while the Calendar Month is displayed on the rows. 

--Lead with Measures
--Named Set for Prior Period Measures

WITH
 MEMBER [Measures].[Internet_Sales_Amount_Lag_2_Periods] AS
 ([Date].[Calendar].CurrentMember.Lag(2), [Measures].[Internet Sales Amount]),FORMAT_STRING = "Currency"

SELECT
 {
 [Measures].[Internet Sales Amount],
 [Measures].[Internet_Sales_Amount_Lag_2_Periods] --named set
 } ON COLUMNS
 ,
 [Date].[Calendar].[Month]  -- Adjust for date hierarchy needed
 ON ROWS
FROM [Adventure Works]

The results of this statement are as follows.  Notice how the Lag columns match the original value from 2 months prior.

the Lag columns match the original value
 

By using the Lag function on the Calendar Date hierarchy within the named set, we have the flexibility to adjust our row value to other attributes within the dimension hierarchy.  So for instance, we can adjust the row value to Calendar Quarter from Month!

--Lead with Measures
--Named Set for Prior Period Measures
WITH
 MEMBER [Measures].[Internet_Sales_Amount_Lag_2_Periods] AS
 ([Date].[Calendar].CurrentMember.Lag(2), [Measures].[Internet Sales Amount]),FORMAT_STRING = "Currency"

SELECT
 {
 [Measures].[Internet Sales Amount],
 [Measures].[Internet_Sales_Amount_Lag_2_Periods] --named set
 } ON COLUMNS
 ,
 [Date].[Calendar].[Calendar Quarter]  --**changed to Quarter!! -- Adjust for date hierarchy needed
 ON ROWS
FROM [Adventure Works]

The new results, illustrated subsequently, show the breakout now by quarter instead of by month.

Lag Measure with Quarter

SSAS Cube Calculated Measure

We can apply and extend this same functionality to an SSAS cube by adding a calculated measure. First we must open Visual Studio and open up our SSAS project; next we will double click on the cube (Adventure Works in this example), and then click on the Calculations tab.  Next, the expression box is edited to add our Lag formula, as noted in the below illustration.

SSAS Cube Calculated Measure

We can see the calculated measure in action by navigating to the Browser tab of the cube. Now by placing one of the Calendar Date hierarchy attributes, month for example, plus the Internet Sales Amount measure along with the Lag_2_Period calculated measure onto the browser window, the cube (or a related pivot table if used) produces output similar to the MDX. You can see the related results below.

CalculatedMeasureMonthCalculated Measure Quarter

One word of caution; if you do not include the Calendar Date Dimension within the cube browser or your pivot, no results will show for the calculated measure. This situation occurs because the calculated measure has no reference point to evaluate the Lag function. 

Calendar Dimension Not Selected

OpeningPeriod / ClosingPeriod

The OpeningPeriod and ClosingPeriod functions provide the report writer with the opening and closing values based on the period hierarchy.  This functionality can actually be a bit confusing as the returned value is actually the static beginning value or ending value respectively.  For instance, in our example below, both functions request the opening and closing periods for the Month attribute of the Date.Calendar hierarchy.  

These functions require two parameters:  

  1.  the level of the opening period to return and
  2. 2. the member to use as a reference.  

The below example displays the Opening Period Month (or Beginning) and Closing Period Month (or Final) Internet Sales Amount for each quarter.

--Opening Period / Closing Period
WITH
 MEMBER [Measures].[Begin Balance] AS
 (
 OPENINGPERIOD
 (
 [Date].[Calendar].[Month],
 [Date].[Calendar].CurrentMember
 ),
 [Measures].[Internet Sales Amount]
 ),FORMAT_STRING = "Currency"
 
 MEMBER [Measures].[End Balance] AS
 (
 CLOSINGPERIOD
 (
 [Date].[Calendar].[Month],
 [Date].[Calendar].CurrentMember
 ),
 [Measures].[Internet Sales Amount]
 ),FORMAT_STRING = "Currency"

SELECT
 {
 [Measures].[Internet Sales Amount],
 [Measures].[Begin Balance],
 [Measures].[End Balance]
 } ON COLUMNS
 ,
 [Date].[Calendar].[Calendar Quarter]
  ON ROWS
FROM [Adventure Works]

As illustrated below, the Begin Balance and End Balance of each quarter on the top screen print matches the 1st month in the quarter amount and the last month in the quarter amount displayed on the bottom screen print.  Thus, Q4 CY 2006 Begin Balance matches October 2006 value and Q4 CY 2006 End Balance matches the December 2006 value.

OpeningPeriodandClosingPeriodQuarterly

OpeningClosingPeriodMonthly

Conclusion

The SSAS Lag and Lead functions provide excellent ways to traverse or compare the measures from one position on a hierarchy to another place within the same hierarchy.  Lag moves in the reverse direction while Lead moves in the forward direction. The functions can be used in straight MDX or as a cube calculated measure. Similarly, the OpeningPeriod and ClosingPeriod functions provide initial and final values based on the levels requested.

Next Steps


Last Update: 2/8/2013


About the author
MSSQLTips author Scott Murray
Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Comments and Feedback:
Friday, February 08, 2013 - 10:08:28 AM - Gonšalo Ferreira Read The Tip

Lag and Lead MDX functions have been available since SSAS 2005... I think you're confusing them with the T-SQL functions of the same name, which are new to SQL Server 2012.


Friday, February 08, 2013 - 10:26:58 AM - Raj Read The Tip

*** Very useful article. The examples are great and the screen shots with actual data values help understand the concept even better. Thank you, keep up the good work..


Friday, February 08, 2013 - 11:08:36 AM - scott murray Read The Tip

Hi Goncalo Ferreria:

Thanks for you comment...  That is why I wrote"  "Surprisingly, the Lag and Lead functions have been available in SSAS for several versions and are only now making their way into regular SQL Server." in the first paragraph.  Maybe I should have been a bit more explicit, but I think this statements conveys how long the functions have been available.


Friday, February 08, 2013 - 12:03:33 PM - girijesh Read The Tip

Really helfull.


Wednesday, February 20, 2013 - 9:38:23 AM - Cary Davis Read The Tip

Great article Scott.  Very clear and concise.  I'm going to have to dig through any other MDX stuff you've posted now.  

Thanks so much.


Friday, January 03, 2014 - 6:51:32 AM - Parker Read The Tip

I love the ease of inserting them into the calculated measures of a cube, as it will greatly ease adoption by a wider audience. Thanks for the very clear articulation of implementing lag, lead and periods within mdx.



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 

Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.