SSAS Calculated Measures for Business (II): Annualized Measures
Reposted from Todd McDermid's blog with the author's permission.
As part of my SQL Server Analysis Services cube, I need to base some measures on how much of the current time slice occurs in the future, and how much occurs in the past. The measures I'm talking about are "projections" of normal measures - such as "projected sales" for 2009, when all of 2009 hasn't happened yet. These aren't forecasts or budgets, they're just straight-line projections based on elapsed and remaining time. I have other measures that build on those - GMROI and Inventory Turns, for example. This is the second post in a series related to twisting SSAS into calculating those business measures - catch up by reading the first post.
Annualized Sales
The intent of creating this calculated measure is to have an annualized value for any time period sliced by the date dimension. For example, if the "current date" is December 15th, 2009 and I want to know the "2009 annualized sales" (based on the calendar year), I would take the total sales from January 1st to December 15th, and multiply that by 365/349. I do that because the total sales figure I get from Jan 1 to Dec 15 isn't the complete year, it represents only 95.62% of 2009 (approximately). In order to "annualize" my sales figures, I have to multiply by the reciprocal (or divide by that percentage). To complete the example with real numbers: if my total sales up to and including Dec 15th were $123,000, my annualized sales are $128,638.97. Numbers like that are valuable when comparing projections to targets and budgets.
My fumbling about in MDX books and even a question or two in the forums didn't turn up any magical functions or techniques for accomplishing this. So I continued my fumbling about with MDX and calculated measures and ended up with the following. I can't vouch for the scalability or relative performance of this method - I only know it arrives at the right answer! If there are any real MDX experts out there who happen upon this post - I'd love to hear how you would improve rewrite this.
Time Intelligence?
A great deal of my problem probably started with trying to figure out what the "time intelligence wizard" capability in the Analysis Services designer (BIDS) generated. I still can't quite figure out how a subset of your time dimension ends up as a calculated measure... and how in order to use a "YTD" column, it had to be tacked on to every slice of a date dimension... and how "YTD" stopped adding up numbers at the beginning of the time slice you're looking at - rather than "today". I'm sure I missed something very basic - and I fear someone will tell me most of the work I did getting what I need was already available, and I was just too thick to understand it.
Annualized Margin
To supply a concrete target for this scenario, I'll talk about annualized margin dollars. For those who need it, a little background: Margin is the difference between what you paid for a product or service, and what you sold it for. Margin can include only "direct" costs (gross margin), or can include all of the other indirect costs of providing the product or service for sale (net margin). Margin can also be expressed as a percentage or an absolute dollar value. Another word often used for margin is profit.
In my calculation of this metric, I've broken it out into several steps and intermediate calculated measures. This is almost entirely due to my attempts to verify that the numbers I arrive at are correct. It may very well be possible to combine the entire series of calculated measures into one calculation, and one calculated measure. Given my lack of experience with MDX, attempting that would be sheer folly, and an invitation to need to relearn everything all over again should I suspect a calculation problem.
Step 1: Determining "Today" (part 1)
Unfortunately for me, this is actually difficult in MDX (at least to my freshman brain). I had to do this in two steps - it didn't seem to work at all when I nested them. That didn't turn out to be a bad thing however, as the first step did allow me to have a calculated measure that informs the business users when the cube was processed last:
"[Date].[Calendar]">
+ ".[" + Format(Now(), "yyyy") + "]"
+ ".[Q" + IIF(Month(Now()) <= 3, "1", IIF(Month(Now()) <= 6, "2", IIF(Month(Now()) <= 9, "3", "4"))) + "]"
+ ".[" + Format(Now(), "MMMM") + "]"
The above simply turns the current date into a string which is the specific, explicit incarnation of today's date within my (standard) calendar hierarchy. For example, December 15th 2009 would be "[Date].[Calendar].[2009].[Q4].[December].[Tue Dec 15, 2009]". I called this calculated member "Today Calendar Date Member As String", and use it in step 2.
Step 2: Determining "Today" (part 2)
As a second step, I turn the above string into it's counterpart in the fiscal calendar hierarchy, prepared as a set for use later in the calculations. To do this, I use the StrToMember function to turn the above string into the "object" which is the MDX member the string represents, then use the LinkMember to get the equivalent fiscal calendar member, which I then enclose in a set:
{ LinkMember(StrToMember([Today Calendar Date Member As String]), [Date].[Fiscal Calendar]) }
Just a quick note as to why steps one and two were necessary, as you may be asking why I just didn't pick a member of the Fiscal date hierarchy directly. It's because it's a fiscal calendar, the month, quarter, and even year that some dates belong to is not what you'd think, so I can't directly determine those just by looking at a date. I actually have to look them up in a "calendar date to fiscal date" table - which is almost exactly what I'm doing here with the LinkMember function.
Step 3: Fiscal Days Prior To Today
In order to annualize anything, I need to know both the total number of days within the current slice, as well as the number of days that are in the past within that slice. The result of that will be my divisor in any annualization calculation. The calculation I have here is likely the one that will make an MDX expert puke at, or send to the Daily WTF's CodeSOD.
Intersect({ [Date].[Fiscal Calendar].[Day].MEMBERS.Item(0) : [Today Fiscal Calendar Set].Item(0) }, Descendants([Date].[Fiscal Calendar].CurrentMember, [Date].[Fiscal Calendar].[Day])).Count
Essentially what I'm doing here is intersecting two sets. The first set is the set of days from the "beginning of time" (according to my date dimension) up to the first member of the time slice I'm currently looking at. The second set is the complete set of days in the date dimension. The end result of the Count property applied to the intersection is to give me the total number of days in the current date slice that occur in the past.
Step 4: Annualized Margin $
The last step is to put some of those intermediate calculations together, and do a little bit more.
IIF([Fiscal Days Prior To Today],
([Amount Sold Gross] - [Amount Material Cost])
/ ([Fiscal Days Prior To Today]
/ Descendants(
Ancestor([Date].[Fiscal Calendar].CurrentMember,
[Date].[Fiscal Calendar].[Fiscal Year]),
[Date].[Fiscal Calendar].[Day]).Count),
0)
Unwinding this from the inside, we'll start with Ancestor call. This function finds the fiscal year member in the date dimension that my current slice belongs to. So if I'm looking at a column for [December 2009], this function returns [2009]. It functions regardless of the level in the dimension you're slicing at, so a slice looking at [Q4 2009] or [Week 49 2009] would still return [2009].
The second step of unwinding this is to look at the Descendants call. This function returns all of the Day-level members included in the Year-level member I just found with the Ancestor call. If this were a standard calendar-based date dimension, I'd expect this call to return 365 or 366. Since this is a fiscal calendar, and our fiscal calendar is based on complete weeks, I expect 364 or 371.
One step further in the unwinding is a little easier on the brain - it's just regular math. I'm taking the difference between what I sold and what it cost (the margin dollars), and dividing it by the percentage of the year that's gone by.
The last step out is the IIF, where I only go through this complex calculation if there is any valid data in this date slice I'm looking at. (In MDX, 0 = True.)
Annualize That!
Using the above has provided me with a relatively easy way to calculate important metrics like annualized margin dollars, projected annual sales, and projected annual costs. Watch for business calculations that build off of this calculated measure in future installments of this series.
 |
Todd McDermid is BSc, MCSD.Net, MCTS (SQL 08 BI), MVP with 20 years experience in Software Development and Databases. Currently part of a very lean (and getting leaner) IT department in a building products distributor. Coordinator of the Kimball Method SSIS Slowly Changing Dimension Component project on CodePlex. Moderator on the Microsoft SSIS Forums. His blog can be found at http://toddmcdermid.blogspot.com/ . |