By: Scott Murray | Updated: 2013-02-08 | Comments (9) | Related: > 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.
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.
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.
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.
--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.
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!
--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.
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.
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.
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.
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:
- the level of the opening period to return and
- 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.
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.
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
- SQL 2012 Function Reference--http://msdn.microsoft.com/en-us/library/ms145970.aspx
- SQL Server 2012 Functions - Lead and Lag -- http://www.mssqltips.com/sqlservertip/2639/sql-server-2012-functions--lead-and-lag/
About the author
Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2013-02-08
