Inventory Management Calculations in SQL Server Analysis Services 2005 (by Richard Tkachuk)-SSAS

Inventory Management Calculations in SQL Server Analysis Services 2005 (by Richard Tkachuk)

Written by Richard Tkachuk

Saturday, 19 May 2007 18:50

A common business problem is how to calculate inventory based on incoming and outgoing counts. At any point in time, inventory is not simply summed from the deltas because inventory on a particular day is the sum of the deltas across time. Additionally, inventory is counted on a regular basis to account for errors in deltas, shrinkage, or other discrepancies.

For example, consider a warehouse containing widgets and sprockets. As widgets and sprockets are shipped in, a positive increment is recorded. As they are shipped out, a negative increment is recorded.Once in a while, a snapshot count is made as a new baseline from which future increments are added and subtracted. The number of widgets and sprockets in the warehouse at any point in time is the sum of the most recent snapshot (or tally) plus the positive and negative increments that happened after that snapshot.

This document explains how these problems can be solved with MDX Scripting in Analysis Services 2005. The reader is assumed to possess moderate to advanced MDX skills.