SSAS Calculated Measures for Business (I): Introduction

Reposted from Todd McDermid's blog with the author's permission. 

Using SQL Server Analysis Services to present a cube to business users can be extremely informative for them.  Such a cube is typically constructed from a data warehouse - in my case, a dimensionally-modeled Kimball Method warehouse.  That warehouse has a lot of data in it, but it's all "simple" data: direct costs and sale amounts of every invoice line item and periodic snapshots of inventory levels.  Using SSAS, aggregations of those facts can easily be presented: "total sales", "total costs", and "average inventory".  With a little more elbow grease, you can do some more complex calculations involving ratios (like price and margin) that aggregate correctly.  (Hint: prices and margins don't sum or average, and shouldn't be a column in your fact table!)  Using more complex calculations, it's also possible to project values into the future, and combine aggregated facts from different measure groups into a new measure.

In a series of blog posts starting with this one, I'll be describing how I put together some business calculations that matter to my organization - GMROI and Inventory Turns.  I'll start with some simple ones in this post, but then get into the projections and combined measure group calculations in subsequent posts.  I'm almost positive I've gone about them in a less than optimal manner - so if you know a better way, don't hesitate to leave a comment!  For a fantastic series on the power of SSAS, Bill Pearson has a virtual encyclopedia written on Database Journal - and more gets added every month.  Scroll down to the Introduction to MSSQL Server Analysis Services Series section on his bio page.

Simple Aggregates

Any column you've got in your fact table that isn't a foreign key is automatically set up as a measure by SQL Server Analysis Services.  Most of the time, the default aggregation (sum) is what you want to do to those facts, because they're usually dollar amounts or quantities.

If you think you want an average, or some other form of aggregate, be very mindful of what you're asking for.  Something as simple as an average may not be so simple to implement.  The built-in average aggregate in SSAS will divide the sum of the selected rows by the number of rows.  There isn't any way to change the choice of denominator - it's always based on row count.

Simple Calculated Measures

For anything more complex than the simple aggregates, you're going to have to get dirtier and make a calculated measure.  For those of us who aren't MDX wizards, the recommended solution of constructing an MDX query in SQL Server Management Studio isn't a great option.  I'd never get the calculation past a syntax check - I just can't drive MDX well at all yet.

What I can do is use the semi-structured interface in BIDS' Calculations tab in the cube.  For simple calculated measures, like one that gets margin or price, the setup is quite easy.  You pick a name for your measure, then type in the "expression" for it.  For our simple calculated measures, we don't need any fancy MDX functions - just simple division will do.  A margin calculated measure expression would be something as simple as "([Amount Sold] - [Amount Cost]) / [Amount Sold]".  (I'm assuming the sold amount will never be zero - you may find you have to use an IIF function here to protect your calculation.)

Earlier I said that you shouldn't have a measure like "price" in your fact table.  You shouldn't have a "margin" measure in there either, because you can't aggregate either of them.  It simply makes no sense to sum them - it doesn't result in a meaningful number.  You also can't use the average aggregate unless you can guarantee that each row in your fact table refers to only one "unit" - whatever your price is based on.  If you can't guarantee that, then an average will be incorrect.

What's Next?

Eventually, I want to get to a calculation for GMROI and Inventory Turns - but I'm going to lead you there slowly instead of by the zig-zag repetitive iteration that I went through to figure it out.  Be patient - next up is projecting measures out into the future.

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/ .