(For more resources related to this topic, see here.)
Time handling features are an important part of every BI system. Programming languages, database systems, they all incorporate various time-related functions and Microsoft SQL Server Analysis Services (SSAS) is no exception there. In fact, that’s one of its main strengths.
The MDX language has various time-related functions designed to work with a special type of dimension called the Time and its typed attributes. While it’s true that some of those functions work with any type of dimension, their usefulness is most obvious when applied to time-type dimensions. An additional prerequisite is the existence of multi-level hierarchies, also known as user hierarchies, in which types of levels must be set correctly or some of the time-related functions will either give false results or will not work at all.
In this article we’re dealing with typical operations, such as year-to-date calculations, running totals, and jumping from one period to another. We go into detail with each operation, explaining known and less known variants and pitfalls.
We will discuss why some time calculations can create unnecessary data for the periods that should not have data at all, and why we should prevent it from happening. We will then show you how to prevent time calculations from having values after a certain point in time.
In most BI projects, there are always reporting requirements to show measures for today, yesterday, month-to-date, quarter-to-date, year-to-date, and so on. We have three recipes to explore various ways to calculate today’s date, and how to turn it into a set and use MDX’s powerful set operations to calculate other related periods.
Calculating date and time spans is also a common reporting requirement.
Calculating the YTD (Year-To-Date) value
In this recipe we will look at how to calculate the Year-To-Date value of a measure, that is, the accumulated value of all dates in a year up to the current member on the date dimension. An MDX function YTD() can be used to calculate the Year-To-Date value, but not without its constraints.
In this recipe, we will discuss the constraints when using the YTD() function and also the alternative solutions.
Getting ready
Start SQL Server Management Studio and connect to your SSAS 2012 instance. Click on the New Query button and check that the target database is Adventure Works DW 2012.
In order for this type of calculation to work, we need a dimension marked as Time in the Type property, in the Dimension Structure tab of SSDT. That should not be a problem because almost every database contains at least one such dimension and Adventure Works is no exception here. In this example, we’re going to use the Date dimension. We can verify in SSDT that the Date dimension’s Type property is set to Time. See the following screenshot from SSDT:
Here’s the query we’ll start from:
SELECT
{ [Measures].[Reseller Sales Amount] } ON 0,
{ [Date].[Calendar Weeks].[Calendar Week].MEMBERS } ON 1
FROM
[Adventure Works]
Once executed, the preceding query returns reseller sales values for every week in the database.
How to do it…
We are going to use the YTD() function, which takes only one member expression, and returns all dates in the year up to the specified member. Then we will use the aggregation function SUM() to sum up the Reseller Sales Amount.
Follow these steps to create a calculated measure with YTD calculation:
- Add the WITH block of the query.
- Create a new calculated measure within the WITH block and name it Reseller Sales YTD.
- The new measure should return the sum of the measure Reseller Sales Amount using the YTD() function and the current date member of the hierarchy of interest.
- Add the new measure on axis 0 and execute the complete query:
WITH MEMBER [Measures].[Reseller Sales YTD] AS Sum( YTD( [Date].[Calendar Weeks].CurrentMember ), [Measures].
[Reseller Sales Amount] ) SELECT { [Measures].[Reseller Sales Amount], [Measures].[Reseller Sales YTD] } ON 0, { [Date].[Calendar Weeks].[Calendar Week].MEMBERS } ON 1 FROM [Adventure Works] - The result will include the second column, the one with the YTD values. Notice how the values in the second column increase over time:
How it works…
The YTD() function returns the set of members from the specified date hierarchy, starting from the first date of the year and ending with the specified member. The first date of the year is calculated according to the level [Calendar Year] marked as Years type in the hierarchy [Calendar Weeks]. In our example, the YTD() value for the member Week 9 CY 2008 is a set of members starting from Week 1 CY 2008 and going up to that member because the upper level containing years is of the Years type.
The set is then summed up using the SUM() function and the Reseller Sales Amount measure. If we scroll down, we’ll see that the cumulative sum resets every year, which means that YTD() works as expected.
In this example we used the most common aggregation function, SUM(), in order to aggregate the values of the measure throughout the calculated set. SUM() was used because the aggregation type of the Reseller Sales Amount measure is Sum. Alternatively, we could have used the Aggregate() function instead. More information about that function can be found later in this recipe.
There’s more…
Sometimes it is necessary to create a single calculation that will work for any user hierarchy of the date dimension. In that case, the solution is to prepare several YTD() functions, each using a different hierarchy, cross join them, and then aggregate that set using a proper aggregation function (Sum, Aggregate, and so on). However, bear in mind that this will only work if all user hierarchies used in the expression share the same year level. In other words, that there is no offset in years among them (such as exists between the fiscal and calendar hierarchies in Adventure Works cube in 2008 R2).
Why does it have to be so? Because the cross join produces the set intersection of members on those hierarchies. Sets are generated relative to the position when the year starts. If there is offset in years, it is possible that sets won’t have an intersection. In that case, the result will be an empty space. Now let’s continue with a couple of working examples.
Here’s an example that works for both monthly and weekly hierarchies:
WITH
MEMBER [Measures].[Reseller Sales YTD] AS
Sum( YTD( [Date].[Calendar Weeks].CurrentMember ) *
YTD( [Date].[Calendar].CurrentMember ),
[Measures].[Reseller Sales Amount] )
SELECT
{ [Measures].[Reseller Sales Amount],
[Measures].[Reseller Sales YTD] } ON 0,
{ [Date].[Calendar Weeks].[Calendar Week].MEMBERS } ON 1
FROM
[Adventure Works]
If we replace [Date].[Calendar Weeks].[Calendar Week].MEMBERS with [Date].[Calendar].[Month].MEMBERS, the calculation will continue to work. Without the cross join part, that wouldn’t be the case. Try it in order to see for yourself! Just be aware that if you slice by additional attribute hierarchies, the calculation might become wrong.
In short, there are many obstacles to getting the time-based calculation right. It partially depends on the design of the time dimension (which attributes exist, which are hidden, how the relations are defined, and so on), and partially on the complexity of the calculations provided and their ability to handle various scenarios. A better place to define time-based calculation is the MDX script. There, we can define scoped assignments, but that’s a separate topic which will be covered later in the recipe, Using utility dimension to implement time-based calculations.
In the meantime, here are some articles related to that topic:
http://tinyurl.com/MoshaDateCalcs
http://tinyurl.com/DateToolDim
Inception-To-Date calculation
A similar calculation is the Inception-To-Date calculation in which we’re calculating the sum of all dates up to the current member, that is, we do not perform a reset at the beginning of every year. In that case, the YTD() part of the expression should be replaced with this:
Null : [Date].[Calendar Weeks].CurrentMember
Using the argument in the YTD() function
The argument of the YTD() function is optional. When not specified, the first dimension of the Time type in the measure group is used. More precisely, the current member of the first user hierarchy with a level of type Years.
This is quite convenient in the case of a simple Date dimension; a dimension with a single user hierarchy. In the case of multiple hierarchies or a role-playing dimension, the YTD() function might not work, if we forget to specify the hierarchy for which we expect it to work.
This can be easily verified. Omit the [Date].[Calendar Weeks].CurrentMember part in the initial query and see that both columns return the same values. The YTD() function is not working anymore.
Therefore, it is best to always use the argument in the YTD() function.
Common problems and how to avoid them
In our example we used the [Date].[Calendar Weeks] user hierarchy. That hierarchy has the level Calendar Year created from the same attribute. The type of attribute is Years, which can be verified in the Properties pane of SSDT:
However, the Date dimension in the Adventure Works cube has fiscal attributes and user hierarchies built from them as well. The fiscal hierarchy equivalent to [Date].[Calendar Weeks] hierarchy is the [Date].[Fiscal Weeks] hierarchy. There, the top level is named Fiscal Year, created from the same attribute. This time, the type of the attribute is FiscalYear, not Year. If we exchange those two hierarchies in our example query, the YTD() function will not work on the new hierarchy. It will return an error:
The name of the solution is the PeriodsToDate() function.
YTD() is in fact a short version of the PeriodsToDate() function, which works only if the Year type level is specified in a user hierarchy. When it is not so (that is, some BI developers tend to forget to set it up correctly or in the case that the level is defined as, let’s say, FiscalYear like in this test), we can use the PeriodsToDate() function as follows:
MEMBER [Measures].[Reseller Sales YTD] AS
Sum( PeriodsToDate( [Date].[Fiscal Weeks].[Fiscal Year],
[Date].[Fiscal Weeks].CurrentMember ),
[Measures].[Reseller Sales Amount] )
PeriodsToDate() might therefore be used as a safer variant of the YTD() function.
YTD() and future dates
It’s worth noting that the value returned by a SUM-YTD combination is never empty once a value is encountered in a particular year. Only the years with no values at all will remain completely blank for all their descendants. In our example with the [Calendar Weeks] hierarchy, scrolling down to the Week 23 CY 2008, you will see that this is the last week that has reseller sales. However, the Year-To-Date value is not empty for the rest of the weeks for year 2008, as shown in the following screenshot:
This can cause problems for the descendants of the member that represents the current year (and future years as well). The NON EMPTY keyword will not be able to remove empty rows, meaning we’ll get YTD values in the future.
We might be tempted to use the NON_EMPTY_BEHAVIOR operator to solve this problem but it wouldn’t help. Moreover, it would be completely wrong to use it, because it is only a hint to the engine which may or may not be used. It is not a mechanism for removing empty values.
In short, we need to set some rows to null, those positioned after the member representing today’s date. We’ll cover the proper approach to this challenge in the recipe, Finding the last date with data.
Calculating the YoY (Year-over-Year) growth (parallel periods)
This recipe explains how to calculate the value in a parallel period, the value for the same period in a previous year, previous quarter, or some other level in the date dimension. We’re going to cover the most common scenario – calculating the value for the same period in the previous year, because most businesses have yearly cycles.
A ParallelPeriod() is a function that is closely related to time series. It returns a member from a prior period in the same relative position as a specified member. For example, if we specify June 2008 as the member, Year as the level, and 1 as the lag, the ParallelPeriod() function will return June 2007.
Once we have the measure from the prior parallel period, we can calculate how much the measure in the current period has increased or decreased with respect to the parallel period’s value.
Getting ready
Start SQL Server Management Studio and connect to your SSAS 2012 instance. Click on the New Query button, and check that the target database is Adventure Works DW 2012.
In this example we’re going to use the Date dimension. Here’s the query we’ll start from:
SELECT
{ [Measures].[Reseller Sales Amount] } ON 0,
{ [Date].[Fiscal].[Month].MEMBERS } ON 1
FROM
[Adventure Works]
Once executed, the previous query returns the value of Reseller Sales Amount for all fiscal months.
How to do it…
Follow these steps to create a calculated measure with YoY calculation:
- Add the WITH block of the query.
- Create a new calculated measure there and name it Reseller Sales PP.
- The new measure should return the value of the measure Reseller Sales Amount measure using the ParallelPeriod() function. In other words, the definition of the new measure should be as follows:
MEMBER [Measures].[Reseller Sales PP] As
( [Measures].[Reseller Sales Amount],
ParallelPeriod( [Date].[Fiscal].[Fiscal Year], 1,
[Date].[Fiscal].CurrentMember ) ) - Specify the format string property of the new measure to match the format of the original measure. In this case that should be the currency format.
- Create the second calculated measure and name it Reseller Sales YoY %.
- The definition of that measure should be the ratio of the current member’s value against the parallel period member’s value. Be sure to handle potential division by zero errors (see the recipe Handling division by zero errors).
- Include both calculated measures on axis 0 and execute the query, which should look like:
WITH
MEMBER [Measures].[Reseller Sales PP] As
( [Measures].[Reseller Sales Amount],
ParallelPeriod( [Date].[Fiscal].[Fiscal Year], 1,
[Date].[Fiscal].CurrentMember ) )
, FORMAT_STRING = 'Currency'
MEMBER [Measures].[Reseller Sales YoY %] As
iif( [Measures].[Reseller Sales PP] = 0, null,
( [Measures].[Reseller Sales Amount] /
[Measures].[Reseller Sales PP] ) )
, FORMAT_STRING = 'Percent'
SELECT
{ [Measures].[Reseller Sales Amount],
[Measures].[Reseller Sales PP],
[Measures].[Reseller Sales YoY %] } ON 0,
{ [Date].[Fiscal].[Month].MEMBERS } ON 1
FROM
[Adventure Works] - The result will include two additional columns, one with the PP values and the other with the YoY change. Notice how the values in the second column repeat over time and that YoY % ratio shows the growth over time:
How it works…
The ParallelPeriod() function takes three arguments, a level expression, an index, and a member expression, and all three arguments are optional. The first argument indicates the level on which to look for that member’s ancestor, typically the year level like in this example. The second argument indicates how many members to go back on the ancestor’s level, typically one, as in this example. The last argument indicates the member for which the function is to be applied.
Given the right combination of arguments, the function returns a member that is in the same relative position as a specified member, under a new ancestor.
The value for the parallel period’s member is obtained using a tuple which is formed with a measure and the new member. In our example, this represents the definition of the PP measure.
The growth is calculated as the ratio of the current member’s value over the parallel period member’s value, in other words, as a ratio of two measures. In our example, that was YoY % measure.
In our example we’ve also taken care of a small detail, setting the FORMAT_STRING to Percent.
There’s more…
The ParallelPeriod() function is very closely related to time series, and typically used on date dimensions. However, it can be used on any type of dimension. For example, this query is perfectly valid:
SELECT
{ [Measures].[Reseller Sales Amount] } ON 0,
{ ParallelPeriod( [Geography].[Geography].[Country],
2,
[Geography].[Geography].[State-Province]
.&[CA]&[US] ) } ON 1
FROM
[Adventure Works]
The query returns Hamburg on rows, which is the third state-province in the alphabetical list of states-provinces under Germany. Germany is two countries back from the USA, whose member California, used in this query, is the third state-province underneath that country in the Geography.Geography user hierarchy. We can verify this by browsing the Geography user hierarchy in the Geography dimension in SQL Server Management Studio, as shown in the following screenshot. The UK one member back from the USA, has only one state-province: England. If we change the second argument to 1 instead, we’ll get nothing on rows because there’s no third state-province under the UK. Feel free to try it:
All arguments of the ParallelPeriod() function are optional. When not specified, the first dimension of type Time in the measure group is used, more precisely, the previous member of the current member’s parent. This can lead to unexpected results as discussed in the previous recipe. Therefore, it is recommended that you use all the arguments of the ParallelPeriod() function.
ParallelPeriod is not a time-aware function
The ParallelPeriod() function simply looks for the member from the prior period based on its relative position to its ancestor. For example, if your hierarchy is missing the first six months in the year 2005, for member January 2006, the function will find July 2005 as its parallel period (lagging by one year) because July is indeed the first month in the year 2005.
This is exactly the case in Adventure Works DW SSAS prior to 2012.
You can test the following scenario in Adventure Works DW SSAS 2008 R2.
In our example we used the [Date].[Fiscal] user hierarchy. That hierarchy has all 12 months in every year which is not the case with the [Date].[Calendar] user hierarchy where there’s only six months in the first year. This can lead to strange results. For example, if you search-replace the word “Fiscal” with the word “Calendar” in the query we used in this recipe, you’ll get this as the result:
Notice how the values are incorrect for the year 2006. That’s because the ParallelPeriod() function is not a time-aware function, it merely does what it’s designed for taking the member that is in the same relative position. Gaps in your time dimension are another potential problem. Therefore, always make the complete date dimensions, with all 12 months in every year and all dates in them, not just working days or similar shortcuts. Remember, Analysis Services isn’t doing the date math. It’s just navigating using the member’s relative position. Therefore, make sure you have laid a good foundation for that.
However, that’s not always possible. There’s an offset of six months between fiscal and calendar years, meaning if you want both of them as date hierarchies, you have a problem; one of them will not have all of the months in the first year.
The solution is to test the current member in the calculation and to provide a special logic for the first year, fiscal or calendar; the one that doesn’t have all months in it. This is most efficiently done with a scope statement in the MDX script.
Another problem in calculating the YoY value is leap years.
Calculating moving averages
The moving average, also known as the rolling average, is a statistical technique often used in events with unpredictable short-term fluctuations in order to smooth their curve and to visualize the pattern of behavior.
The key to get the moving average is to know how to construct a set of members up to and including a specified member, and to get the average value over the number of members in the set.
In this recipe, we’re going to look at two different ways to calculate moving averages in MDX.
Getting ready
Start SQL Server Management Studio and connect to your SSAS 2012 instance. Click on the New Query button and check that the target database is Adventure Works DW 2012.
In this example we’re going to use the Date hierarchy of the Date dimension. Here’s the query we’ll start from:
SELECT
{ [Measures].[Internet Order Count] } ON 0,
{ [Date].[Date].[Date].MEMBERS} ON 1
FROM
[Adventure Works]
Execute it. The result shows the count of Internet orders for each date in the Date.Date attribute hierarchy. Our task is to calculate the simple moving average (SMA) for dates in the year 2008 based on the count of orders in the previous 30 days.
How to do it…
We are going to use the LastPeriods() function with a 30 day moving window, and a member expression, [Date].[Date].CurrentMember, as two parameters, and also the AVG() function, to calculate the moving average of Internet order count in the last 30 days.
Follow these steps to calculate moving averages:
- Add the WHERE part of the query and put the year 2006 inside using any available hierarchy.
- Add the WITH part and define a new calculated measure. Name it SMA 30.
- Define that measure using the AVG() and LastPeriods() functions.
- Test to see if you get a managed query similar to this. If so, execute it:
WITH
MEMBER [Measures].[SMA 30] AS
Avg( LastPeriods( 30, [Date].[Date].CurrentMember ),
[Measures].[Internet Order Count] )
SELECT
{ [Measures].[Internet Order Count],
[Measures].[SMA 30] } ON 0,
{ [Date].[Date].[Date].MEMBERS } ON 1
FROM
[Adventure Works]
WHERE
( [Date].[Calendar Year].&[2008] ) - The second column in the result set will represent the simple moving average based on the last 30 days.
- Our final result will look like the following screenshot:
How it works…
The moving average is a calculation that uses the moving window of N items for which it calculates the statistical mean, that is, the average value. The window starts with the first item and then progressively shifts to the next one until the whole set of items is passed.
The function that acts as the moving window is the LastPeriods() function. It returns N items, in this example, 30 dates. That set is then used to calculate the average orders using the AVG() function.
Note that the number of members returned by the LastPeriods() function is equal to the span, 30, starting with the member that lags 30 – 1 from the specified member expression, and ending with the specified member.
There’s more…
Another way of specifying what the LastPeriods() function does is to use a range of members with a range-based shortcut. The last member of the range is usually the current member of the hierarchy on an axis. The first member is the N-1th member moving backwards on the same level in that hierarchy, which can be constructed using the Lag(N-1) function.
The following expression employing the Lag() function and a range-based shortcut is equivalent to the LastPeriods() in the preceding example:
[Date].[Date].CurrentMember.Lag(29) : [Date].[Date].CurrentMember
Note that the members returned from the range-based shortcut are inclusive of both the starting member and the ending member.
We can easily modify the moving window scope to fit different requirements. For example, in case we need to calculate a 30-day moving average up to the previous member, we can use this syntax:
[Date].[Date].CurrentMember.Lag(30) : [Date].[Date].PrevMember
The LastPeriods() function is not on the list of optimized functions on this web page: http://tinyurl.com/Improved2008R2. However, tests show no difference in duration with respect to its range alternative. Still, if you come across a situation where the LastPeriods() function performs slowly, try its range alternative.
Finally, in case we want to parameterize the expression (for example, to be used in SQL Server Reporting Services), these would be generic forms of the previous expressions:
[Date].[Date].CurrentMember.Lag( @span - @offset ) :
[Date].[Date].CurrentMember.Lag( @offset )
And
LastPeriods( @span, [Date].[Date].CurrentMember.Lag( @offset ) )
The @span parameter is a positive value which determines the size of the window. The @offset parameter determines how much the right side of the window is moved from the current member’s position. This shift can be either a positive or negative value. The value of zero means there is no shift at all, the most common scenario.
Other ways to calculate the moving averages
The simple moving average is just one of many variants of calculating the moving averages. A good overview of a possible variant can be found in Wikipedia:
http://tinyurl.com/WikiMovingAvg
MDX examples of other variants of moving averages can be found in Mosha Pasumansky’s blog article:
http://tinyurl.com/MoshaMovingAvg
Moving averages and the future dates
It’s worth noting that the value returned by the moving average calculation is not empty for dates in future because the window is looking backwards, so that there will always be values for future dates. This can be easily verified by scrolling down in our example using the LastPeriods() function, as shown in the following screenshot:
In this case the NON EMPTY keyword will not be able to remove empty rows.
We might be tempted to use NON_EMPTY_BEHAVIOR to solve this problem but it wouldn’t help. Moreover, it would be completely wrong. We don’t want to set all the empty rows to null, but only those positioned after the member representing today’s date. We’ll cover the proper approach to this challenge in the following recipes.
Summary
This article presents various time-related functions in MDX language that are designed to work with a special type of dimension called the Time and its typed attributes.
Resources for Article:
Further resources on this subject:
- What are SSAS 2012 dimensions and cube? [Article]
- Creating an Analysis Services Cube with Visual Studio 2008 – Part 1 [Article]
- Terms and Concepts Related to MDX [Article]