SSAS Calculated Measures for Business (III): Average Inventory

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

This is a third post in a series about implementing business oriented calculations in SQL Server Analysis Services. See the first and second posts for more information about where this is coming from, and leading up to.  This post is going to investigate how I calculated an average inventory level that I'll be using in my Inventory Turns calculation.

According to my organization (as well as Wikipedia), inventory turns are a measurement of how often you "cycle through" or "turn over" your inventory.  A simple example can be shown by assuming you sell $100 of product over a certain period, like a year.  If you had to keep an average of $50 worth of product on your store shelves for that year, then you have "turned over" your inventory twice.  If you were able to get by with only having $25 worth of inventory on the shelf, then you acheived four turns.  The higher the turns you achieve, the better you're managing your inventory while maintaining sales.

A Wrench in the Average

One of the keys to this calculation is determing what your average inventory was.  In my case, I have a periodic snapshot table for inventory, which has inventory levels taken at specific times.  The curve ball here is that the period isn't consistent.  During the time I've been collecting data from our live business system, I've been able to collect inventory information daily.  I'm choosing to only store weekly snapshots in my fact table, so I discard a lot of that daily information - but I still need to extract it from my source system, because it's intertwined with information I need to populate my dimension tables daily.  However, I've only been accumulating this weekly information from partway through the year.  I have been able to extract older inventory information from backup snapshots - but it's on a monthly basis, not a weekly one.  Therein lies the rub.

A simple average aggregate will definitely NOT work when the periodicity of the inventory levels is inconsistent.  I can't sum all of the inventory measurements within a time period and divide by the number of measurements.  If I do, any sub-period within that period that happened to measure weekly levels will be weighted much more heavily (about 4 times greater) than the sub-period that only captured monthly inventory levels.  I have to compute a weighted average.

Forcing Weekly Periodicity?

One way to weight an average is to make sure that the snapshots all occur at the same periodicity.  If you can do that, then you might be able to actually use a simple average aggregation to calculate average inventory.  I say "might be" because there's a caveat here.  Remember that the SSAS average is the total divided by the number of rows.  That means that for every item you might want to calculate an average for - you have to have one row for each and every period - even periods before the item existed or after it "expires".  That's because your crafty business users may (unintentionally, of course) pick a period that extends beyond the life of your item... resulting in too few "rows" to divide the total by.

In order to get the correct average, I'm going to have to be able to figure out a weekly number even for those periods where I have only monthly information.  Luckily, this is easy to do with SSAS, and is actually a side-effect of something you're going to have to do with non-additive measures like inventory levels.  (You can't be summing them up, can you?)  When configuring a measure like inventory level, you're going to use a non-default non-additive aggregation method - in my case, LastNonEmpty.  That aggregation type will give me the last specified inventory level regardless of whether there's an inventory record for the specific date I ask for.

Calculating the Correct Average

Now that you have a measure that can supply a value for any date you specify, you'll need to get an average by specifying a specific periodicity.  Specifying a particular periodicity - weekly in my case - will result in the average function getting the right denominator, and therefore dividing by the right number.  The calculated measure I'm defining will do both those things:

Avg(
   Descendants([Date].[Fiscal Calendar].CurrentMember, [Date].[Fiscal Calendar].[Fiscal Week], SELF),
  [Current Amount Material Cost On Hand])

The Descendants function returns the set of descendants (children) at the level that I've specified.  In the above, I've told the function to return all of the Fiscal Week descendants included within the current date slice.  The Avg function then looks at the [Current Amount Material Cost On Hand] measure - which I've defined with the LastNonEmpty aggregate - for each Fiscal Week returned.  In months where I just have the one snapshot, it add up the prior month's inventory level, one time for each week, then divide that by the number of weeks.  Exactly what I'm looking for.

AlmostThere!

I believe I'll have just one more post after this one to arrive at my desired calculated measures: GMROI and Inventory Turns.  This post resulted in a suitable calculation for average inventory, and prior posts solved the annualized sales figure issue.  I have all the parts - now it's time to put them together.

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