Clickstream

Using Excel for Graphing

Most business intelligence products I've worked with do really simplistic graphing, decorated with a lot of useless [chart junk]. I often find myself pulling numbers and feeding them into Excel for better manipulation and graphing. Your typical BI tool can't automatically handle some incredibly useful charts which I learned years ago in Total Quality Management training and find myself reusing every year.

One of these is Shewhart control charts. These were originally aimed at dealing with variation in manufacturing processes. The basic idea of this chart is to show the normal operating boundaries of a process. You have upper and lower control limits as two lines, and your measurements as a third line that is (hopefully) between the other two. The control limits show the expected variation of measurements, which means that you can ignore your line if it bounces between the two, but if your graphed measures go above or below the limits you have a real problem.

The same numbers underlying the charts can be used as event triggers to notify someone that something may be a problem. I've used this technique with manufacturing data, with warranty data and I'm mulling it over for managing perishable inventories.

No BI tool I've seen knows how to do this kind of chart. You can fake it by creating metrics for the control limits and then graphing all three, but this isn't as easy as it sounds due to the calculations involved. It's much easier to extract the measurements from the data warehouse, generate the control limit data, and graph that.

Overall, I've been underwhelmed by the ease and functionality of charting in Excel. I use it because it helps do the calculations and graph the data more easily. Then I came across a few really helpful articles on exactly these topics.

The first is from an old issue of Quality Digest and shows how to set up Shewhart, pareto and box and whisker charts pretty easily. This, with the information from a recent article on making charts in Excel look the way you want provide all the information needed to use these techniques and do a good job.

I particularly liked the InformIT article on graphing support in Excel because it dives down into some of the technical details, showing some simple techniques and useful VBA that can be applied to really work the Excel chart engine. You can set up auto-expanding charts, multiple chart types on one chart and even charting functions. Worth a read if you have to prepare data for others and want to communicate it effectively.

Posted by Mark Friday, March 25, 2005 1:57:00 PM | permalink |