SSRS and Excel: Business Intelligence is more than just producing reports!

One of the key issues in Business Intelligence is the display of data concisely, appropriately and correctly. In particular, Reporting Services (SSRS) and Excel, in the wrong hands, can lead to incorrect data visualisations since it’s very easy to produce charts, graphs and so on, without thinking about the data in question, and the business question that you’re trying to answer. Both SSRS and Excel is that they allow users a generous amount of control over the report itself. In particular, SSRS provides so many controls and options in the ‘Properties’ window of each element of the report that it might be overwhelming for novice users. However, Excel’s ‘ease-of-use’ and SSRS ‘fine-grained control over reports’ can lead to the production of visual displays which are not suited to the data in question, or in some cases, just wrong! Thus, reporting does not always constitute a part of business intelligence; if it does not help to move the business forward, then it can’t be said to be real business intelligence.

Here is an example from my presentation at SQLBits 7, which is a Reporting Services chart which shows the top 5 most frequently appearing Doctor Who Villains since the series’ inception in 1963:

Those of you who have had lots of caffeine will notice that the line graph is not a suitable method of displaying this data. Line Graphs are better for displaying continuous data, not distinct categorical data. If you are a fan of the Doctor Who series, you will know that the Daleks and the Cybermen are separate types of villains. If you are a real Doctor Who obsessive, then you will be wondering whether the count of Ice Warriors excludes the number of episodes where the Ice Warriors appeared in a benevolent capacity; this graph is specifically aimed at displaying a count of appearances rather than an analysis of the degree of ‘villain-ness’.

Since the villains are constituted of distinct categories, then the same data would be better placed as a bar chart in an SSRS report, which you can see below:

This SSRS column chart report is better because it emphasises the distinct categories being measured. Further, the ‘left to right’ reading methodology is easier for us here in the West. Also, it facilitates cognitive integration of the chart itself, by allowing sorting and easy comparison. This isn’t so straightforward in the line graph example given earlier. So, even though line graphs and column charts are very simple displays, it is easy to use them incorrectly.

To summarise, it is very easy to do visualisations which are not being representative of the data sample. Of course, the users have an option to amend the underlying data structure that they are looking for, so that they can obtain the particular visualisation that they would like to see. However, SSRS and Excel, in the wrong hands, can lead to reports that simply do not answer the business question, or help the business users to drive the business forward; that’s the heart of business intelligence.