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 makes 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.
This month's puzzle is to 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.
