Sunday 6 May 2012

A chart is worth a thousand words - only when it's used appropriately (Part 1)

People love charts and graphs. They are intuitive, less intimidating (compare with big fat spreadsheet) and more informative. However, every now and then, I've seen people force data into unsuitable charts, not for revealing business insight purpose but for just having some color and graph on their BI reports. In those cases charts provide no value add but duplication or confusion, in terms of analysis. In this post, I will examine a few OBIEE chart types which I believe are important but sometime not used in the most appropriate way by end users.

Line Chart

Lines work better than any other means to make visible the sequential flow of values as they have changed with the passage of time. So, Line Chart will be the best choice if your objective is to see how quantitative values have changed during continuous period of time (in another words time series analysis). Time series analysis majorly reveals patterns such as Trend, Variability, Rate of Change, Co-variation, Cycles etc. Today I will do a bit drilling on Trend and Rate of Change.

A trend is the overall tendency of a series of values to increase, decrease or remain relatively stable during a particular period of time. For example, it is common to refer to sales revenue during a 12 months period as trending upward, downward or remaining flat. Trends are often obvious from the general slope of a line, but when the line moves both up and down throughout the period, the overall trend might be difficult to determine based on the appearance of the line alone. At such times, you should consider to use moving average function on the measure for Y axis so that a more obvious trend line can be visualized on the chart.

On the example chart below, the weekly numbers of orders for year 2008 are fluctuating therefore it is not easy to tell the trend from blue spiky line. But when I add Moving Average result of order number as derived measure and display it as red line on the chart, a much more obvious Bell Curve reveals the trend for my customer's order pattern.
Trend Line

Moving Average function in OBIEE Answers
The Rate of Change from one value to the next can be directly expressed as the percentage difference between the two. It is often enlightening to view change in this manner, especially when comparing multiple series of values such as sales per region. However, when the measure values being compared are in different order of magnitude, the slope of lines will become misleading and may make people draw wrong conclusion for the comparison between rates of change. For an instance, in a graph with a standard linear scale, the slope of a line that increased from $1000 to $1100 is less steep than on that increase from $10000 to $11000. Although the rate of change for both are 10%, our eyes will make us believe the growth rate for the second measure($10000) is quick than that for the first one($1000). In this case, we can create the line chart with a logarithmic scale. Using a lot scale, the rate of change will appear as accurate slope, no matter how much the actual values are or how great the difference between them.

Look at the example below, this line chart shows the comparison of Rate of Change between revenue and billed quantity of product for the first half of year 2008. When the line char is drawn with default scale, people will easily jump into the conclusion that the growth rate of revenue is much faster than that of product billed quantity.
Rate of Change Default Scale

However, when I use Logarithmic scale to display same underlying data, the graph contains two lines that exhibit precisely the same visual patterns and slopes, which reveals that the rate of change for revenue and billed quantity are the same.
Logarithmic Scale in OBIEE Answers

Rate of Change Log Scale

Pie Chart

Pie chart is also wildly adopted in the reporting world. Every year when I receive the government spending report for my local city council, pie chart is everywhere. Pie char is commonly used to display part-to-whole relationships. Without need to move eye sight around, the report reader can quickly identify which group contributes/consumes the largest/smallest slice of the pie. Look at the example below, this chart shows the operational costs associated with customers from different industries. Just at a glance, I can reach the conclusion that among all the customers, Government generates more than a quarter of the total cost while Distribution only contributes less than 5% of it.
Pie Chart


Pie chart is very intuitive in terms of revealing the top and bottom player in a whole. However, Pie Chart still has a deadly defect which make it very confusing under some circumstances. Let's look at the same example chart and ask this question: what is the difference between categories "Commercial", "Industrial" and "High Tech"? It's not easy for an ordinary users isn't it? The user needs to compare the 2D areas or the angles formed by the slices, but human being's visual perception doesn't accurately support either of these tasks very well. On the other hand, the difference between those three "close competitors" will be much more obvious if the same information was put on a Bar Chart, as we are normally good at comparing 1D lengths.

What if I want to not only leverage the advantage of Pie Chart (quickly reveals the part-to-whole relationship for "winner" and "loser") but also minimize the defect of it (challenges human being's capability of identify insignificant differences on 2D areas or angles)? A proper setting of data label on Pie Chart can probably help achieve what we want.
Data Label setting in OBIEE Answers

Pie Chart with percentage data label

In the next coming one, I will discuss about the features, tips and best practice of another three OBIEE chart types. Please stay tuned.