Data Visualization – a real world example

In the following example we work through a real world example of a data visualization. We’ve chosen an example that involves Operations data – this is fairly non-domain specific so hopefully it can demonstrate some important points. The first, and most important point is that you have to define your audience.

We receive many questions about “what is the best chart for this situation” or “what colour should I use for emphasis”. These questions are usually attacking the problem from the wrong angle. The one question you need to ask before anything else is “who is this visualization going to be seen by and how?” Is it in a boardroom on a printed sheet or across a trading floor on a plasma screen. Are the consumers domain experts?

This example features data about an investment bank’s operations processing, the audience being the clients of the Operations department.

Starting Point

Initially the project started out as simply trying to record what operational problems were encountered on a daily basis across different product lines. A reporting system was built and various generic reports produced:

DVBlog1

Unfortunately the reports either didn’t contain data at a granular enough level or it was difficult for the product managers to see where the issues were occurring and what the trends were. In reality the report showed what the major problems had been – unfortunately this was already known, as when something major goes wrong you remember getting shouted at!

What was requested

The client wanted a report that showed where the problems were occurring across business lines (rather than operational units) and how they were doing historically in a single page that could be included in a weekly MIS pack (they currently had four pages per product line (8) so a total of 32 pages. As a first pass they simply wanted an Excel worksheet they could update manually:

DVBlog2

We felt this solution lacked clarity and it was very difficult to spot trends across products.

What we proposed

We designed a solution using MicroCharts to allow small multiples of charts to show a variety of views:

DVBlog3

This solution allowed the user to view the data simply as a cumulative set of data by Product (top line) or by Root Cause (vertically) and then look deeper into historical trends in the centre of the chart. For example, its fairly easy to see spikes in the Root Cause data historically and see that the overall trend has improved over time. By ranking the Products and Root Causes you immediately give some sense of scale to the data. For example you can see that there are many more Application failures than any other type of problem, but the majority of root causes are otherwise fairly evenly distributed.

One other point worth noting was that the original colour scheme was much more muted, but the client got very upset that it looked like a competitor’s corporate colour and wanted it to be “louder”.

What was the user reaction…

Ecstatic, 1 page replaced 34 and they could see at a glance how the entire (large) organisation was working but also quickly find out detail for a particular area and identify trends.

Gemini – Smarter Excel Dashboards with End-User BI

I attended beginning this week the Microsoft BI Conference in Seattle where Microsoft presented an interesting new product that might change the way you build your Excel dashboards in the future: “Gemini”, an End-User Business Intelligence plug in for Excel.

End-User Business Intelligence has been around for quite a while with some of the most prominent Applix TM/1 , PALO, PowerOLAP and MIS Alea. These tools where tailored for non-IT proficient Excel users to allow them to build real Business Intelligence solutions with Excel, without leaving Excel or having to learn complicated BI or Data Warehousing techniques. All you have to learn is that a cube is like a very powerful multidimensional spreadsheet that can easily aggregate and hold large amounts of data. To pull out data from the cube you use a formula like this…

=LookupCube(“Sales”, “Units”, “Hats”, “Store 19”, “Oct-2007”)

…returns unit sales information consolidated by product line and region for the month shown. And this formula…

= LookupCube(“Sales”, “Sales USD”, “Hats”, “Store 19”, “Oct-2007 YTD”)

…returns year-to-date sales in US dollars for the same product line and region

Continue reading “Gemini – Smarter Excel Dashboards with End-User BI”

Smart Dashboard Ranking Tables

Chandoo and Robert over at the PHD blog have a nice a 4 post series of posts about Creating KPI Dashboards in Microsoft Excel.

I really recommend you to read Robert’s articles. Having scrolling in your sorted table is just a really smart addition to your Excel dashboard.

Yesterdays post was about Adding One Click Sort:

Continue reading “Smart Dashboard Ranking Tables”

Excel Dashboard Competition: Bank Dashboard

This blog post is the first in a series of blog post that features the winners of the 2008 Excel dashboard competition.

“A dashboard is a visual display of the most important information needed to achieve one or more objectives; consolidated and arranged on a single screen so the information can be monitored at a glance.”

Stephen Few, Information Dashboard Design (2006)

The dashboards were judged on the clarity and effectiveness of their design, particularly

  • Clean and clear organization
  • Effective table and chart design
  • A single-screen display, properly designed for the web, screen or print outs

Furthermore we honored the technical aspects of the dashboard, did it use effective (Excel) techniques for

  • The Dashboard layout
  • Data management, pulling data from a database or data warehouses
  • Data logic and calculation : YTD figures, variances, etc….
  • Dashboard delivery: Sharing the dashboard via PDF, the web or as an Excel Workbook

Today we will review the winning entry, Wades Stokes Bank Dashboard:

image


Continue reading “Excel Dashboard Competition: Bank Dashboard”

The Dashboard Squint Test

Before we go and review the 2008 Excel Dashboard Competition Winners I have to make you familiar with  the Dashboard Squint Test.

Software usability experts and web designer use a quite effective way to assess the organization of a web page or a user interface, the so called Squint Test. You squint your eyes and make an assessment on the overall layout, of elements that stand out, the visual balance and other characteristics of an effective user interface.

SquintTestScreen0001

 
This test can be easily extended and applied to dashboards. Squint your eyes and assess the overall layout.

SquintTestScreen0002


Continue reading “The Dashboard Squint Test”

Excel Dashboard and Visualization Boot Camp

Here the announcement of the Excel Dashboard Boot Camp hosted by the two experts Jon Peltier and Mike Alexander:

Microsoft MVPs Jon Peltier (Peltier Technical Services) and Mike Alexander (DataPig Technologies) are joining together to bring you our first annual Excel Dashboard and Visualization Boot Camp!

Continue reading “Excel Dashboard and Visualization Boot Camp”

The Missing Link (Part 1)

Every good discipline needs a missing link. Evolutionary biology had a missing link between humans and the ‘lower’ animals. Physics has a missing link between quantum mechanics and general relativity. The Information Visualization community discovered the Missing Link Between Information Visualization and Art.

Now we discovered the missing link in Excel Data Visualization.

As we can see in Wades winning Bank Dashboard we can greatly increase the amount of information that can be included in a dashboard by using sparklines in an overview table

image

 

Continue reading “The Missing Link (Part 1)”

2008 Excel Dashboard Competition Winners

After much deliberation and debate, we are pleased to announce the winners of the 2008 Excel Dashboard Competition. We were impressed by many of the entries, and thanks to all of you who entered. It’s good to see MicroCharts being put to effective use, and adding value in such a variety of business scenarios and sectors. We had entrants from a broad range of industries including Banking, Insurance, Healthcare, Manufacturing, Oil and Gas and Pharmaceuticals.

The winners are:

1) Wade Stokes – Bank Dashboard

 

 

 

 

 

 

 

 

 

 

Displaying many disparate Banking Key Performance Indicators, and designed as the basis for the Management review of business performance, it truly achieves More Information per Pixel.

2) Jim Uden – Outpatient Surgery Center Dashboard

 

 

 

 

 

 

 

 

Developed for Meridian Surgical Partners, as a one page snapshot for the review and presentation of partnership level business operations and trends.  Jim also includes probably the best associated description of dashboard content and the thought processes involved which we’ve seen.

3) Hitesh Patel – Pharmaceutical Sales Dashboard

 

 

 

 

 

 

 

 

 

Developed by Hitesh Patel and Mike Askew of Data Intelligence, for Bristol Myers Squibb. A key report for the Regional Sales Managers, containing the information required to run the business in terms of cash, growth, share, and competitive performance.

Congratulations to all 3 of our winners. Have a look at our competition page for screenshots and some background on the winning entries. Over the next few weeks we’ll be analyzing the entries in more depth at http://blog.xlcubed.com. We’ll overview each, cover some of the techniques used, and hopefully suggest some further improvements.

Sharing your Excel dashboard: from paper to the web

You have finished your great looking, very efficient Excel dashboard. Now, how do you share it? How do you make sure the users have a timely access to the latest update? This can be a serious issue, specially if you have a more tech oriented audience, and it must be addressed at the planning stage. Let’s browse the available options.

Good old-fashioned paper

For the tech savvy crowd out there this is something that doesn’t even cross their minds. But let’s face it, top managers are among the most computer illiterate groups in our society. If these are your users just handle them a sheet of paper to keep them happy and forget about those cool interactive charts. Make sure that your design accommodates this kind of use.

Since printed paper has a higher resolution than computer monitors you may want to create smaller charts and a more detailed answer to the question the dashboard is supposed to answer. I always wanted to create a dashboard to be printed in a A3 (or 11 x 17) folded sheet. Maybe one of these days… Remember that if you anticipate that users will print your dashboard you must test it for color and B&W printing.

PDF file

Don’t make the mistake of thinking of a PDF file just as an electronic copy of a sheet of paper. User experience is quite different, and you must prepare for it. Users will be able to zoom, pan, add comments, copy a chart to a PowerPoint presentation, etc. Also if, for example, you have a marketing dashboard and it is used for different markets you can set up a simple macro to print a multiple page PDF, and add hyperlink navigation. It is a rich environment and you should take advantage of it.

Email message

You can send your Excel dashboard as an attachment to an email message. This will allow the users to save it locally and, unlike paper or PDF, you can add interaction, like selecting different markets, time periods or regions. Do your best to know what monitor resolutions your users have, and make sure that the users have security level settings that allow them to run macros (if your dashboard needs it). A cool thing to do with macros is to greet the user with the last sales data and open the dashboard with the correct market for that user.

File size and network security can prevent you from sending your dashboard as an attachment. Current file size may be smaller than you are allowed to send, but if the file is likely to grow (because you are adding more data) at some point you’ll not be able to send it. You can zip it but the problem remains, and some network security settings may prevent the users from receiving zip files.

Online / Intranet

If you choose this option the users will have a known location where they can retrieve the latest dashboard version from. You can add some nice touches, like pushing a new version to the user’s computer as soon as he appears online, or automatically send an email when the dashboard is updated (you can do it from the dashboard itself).

Web publishing

Some time ago, I was reviewing a dashboard tool and, although the product was pretty lame, I found that it had a clear advantage over Excel: it could publish dashboards for online access, using Flash technology. This means that it couldn’t manage real-world data sets, but still…

Finding a way to publish online a fully functional Excel dashboard with minimal impact over the way I’m used to do things is something that I’ve been waiting for quite some time. And if you can link the dashboard to the data set, well, that means heaven: you don’t have to open the file, refresh it and save it again.

I was unaware that this functionality even existed until Andreas told me about XLCubed. Last week I was able to install it and start to play with it. I will not tell you that this is a great product (not yet, anyway). But it is a great idea, and my expectations are high. Over the next posts I’ll tell you all about this process of discovery, but just being able to add “web publishing” to your list of options is already remarkable.

Final thoughts

There is not a single best option to deliver your dashboard. It depends on your audience, file size, update frequency, implemented features, information infrastructure… That said, we know that the future belongs to web enabled applications and web delivery. Web publishing would be my personal choice, undoubtedly, but I’d try to help less tech minded users to feel more comfortable (like adding a visible Print button).

I like to create charts and dashboards, and I do my best to find solutions that answer user’s problems. But if I can publish a dashboard without worrying about access and updates, well, don’t try to find me here. There is a tropical island waiting for me.

Data sources for Excel dashboards: avoid spreadsheet hell

This is the first of two twin posts where we’ll discuss the alpha and omega of Excel dashboards: data access and dashboard publication. These are two weak areas in Excel, and they should be approached carefully when planning for a new dashboard. Let’s start by reviewing the available data access options.

Copy / Pasting data

Are you or some one in your organization populating the spreadsheet manually? Or are you copy/pasting the data into the spreadsheet? This is the simplest method of getting data into Excel, but it can be dangerous. It should be avoided when better options are available.

When you are dealing with some kind of structured data management (like you do when you create a dashboard) you have to plan ahead and make sure that when data changes it doesn’t break your well crafted dashboard. Each function, each chart, must know where the data is and adjust for these changes when needed.

When you are pasting data there is a a high risk of break something. The number of rows or columns in the new dataset may change, and things like a time series chart may not recognize the new time periods and probably you’ll have to update references manually. Again, plan carefully or you end up in a maintenance hell.

External table

You can create a link to an external table in Access, Oracle or other database tool via a standard ODBC connection. This will ensure that the data is correctly funneled into the spreadsheet, but with real-world data it is very easy to have more records than the Excel 2003 limit of 65,536 rows. You’ll be better off if you link not to the raw data itself but to a query/view that aggregates the data (one of the basic rules for dashboard design in Excel is to avoid calculations and derivative data; the data should come from the source already prepared to be displayed).

Once the data is in Excel, there is not much difference between this and the previous option. You still need to use use lookup functions to retrieve the data and use it in report tables and charts, and data integrity is a stressful thing that you must ensure all the time. When possible, use database functions like DSUM instead of lookup functions (there will be a post discussing this).

Pivot tables

For an out-of-the-box Excel installation you may want to consider pivot tables. They are an interesting option for smaller datasets and they have a nicely flat initial learning curve. Please note that pivot tables will make your file size much larger because they store all the data in the spreadsheet, so scalability can become a major issue. Also, they work best with a strict hierarchical data structure. If your data doesn’t fit exactly in this concept this may be a problem. If you have a larger dataset you should consider an OLAP cube instead.

OLAP Cubes

The concept of an OLAP cube can be something scary for the average Excel user, but once you start using them you’ll never turn back. Specially of you are using what Charley Kid calls an “Excel-friendly OLAP cube”.

Unlike the other methods, an Excel-friendly OLAP cube (like XLCubed) will not store the data in the spreadsheet, thus eliminating the need for the usual data refreshing methods (open the dashboard, refresh, save and close). The cube is automatically updated and you can query it using formulas similar to GETPIVOTDATA. This makes a huge impact on the way you work. You get all the benefits of a regular pivot table plus several life-saving extras. The dashboard will be simpler, cleaner and easier to maintain.

Final Thoughts

You have several methods for data management in Excel, and you must decide what is the best method for each specific dashboard. Scalability is always an issue, so be sure your data don’t outgrow your chosen method. An Excel-friendly OLAP cube may require some immediate investment but will save you a lot of hassle in the long run.

Data management in Excel is a critical factor, and it will discussed in detail in future posts.

The next post discusses the other end of a dashboard project: how to make the dashboard available to the users.