| Downloads |
|---|
| 37802.zip |
Businesses often use analysis tools for monitoring purposes. You can define central business metrics that change over time, then monitor those metrics regularly. If a metric goes "out of bounds," the decision maker institutes business changes to correct the problem. So how do you know whether a metric is out of bounds? One technique is to use a control chart. You generally use control charts in quality-assurance applications to determine whether a particular failure rate is acceptable, but you can apply the same principle to business metrics.
The puzzle: Write a query that returns data for a control chart. I'll tell you how to make a simple control chart, and you write the query. The metric we're monitoring is Unit Sales in the FoodMart 2000 Sales cube. The MDX query should contain three values for each month in 1997. Put the months on the columns and the measures on the rows. The first measure is the upper control limit, the second is the unit sales value, and the third is the lower control limit. Determine the control limit by taking three standard deviations from the mean. Your query should calculate the standard deviation and the mean on all the months up to and including the currently displayed month. In other words, the calculations include more information as you progress through the year. After you've written the query, display it as a line chart (if you don't have an OLAP tool, you can use Excel) and determine whether the Unit Sales line crosses outside the control limits.
Listing A shows a query that returns the control-chart data you want. The key to this MDX query is the LASTPERIODS() time-series function. LASTPERIODS(3) returns the last three time periods, including the current member in the Time dimension. With this function, the Rolling Sales measure can average the store sales for the last three periods. Using a technique similar to Rolling Sales, I created a Deviations measure that evaluates to three standard deviations from the last 12 time periods. The Upper Limit and Lower Limit measures then contain the Rolling Sales plus or minus the three standard deviations. The rest of the body of the query includes all the months of 1997 on the columns and displays the Upper Limit, Store Sales, and Lower Limit sales on the rows. If you run this query in a tool that can display the result in a line chart, you'll see that the Rolling Sales value bounces up and down but stays safely between the upper and lower control limits.