One of the benefits of an interactive visualisation is the ability to zoom in and out of the data, altering the scale of analysis. From summaries and averages to individual data points or specific periods in space and time.
Whilst sifting through my Delicious links archive (in the process of moving those still relevant into the library direct on this site), I came across this Twitter ‘Just landed’ data visualisation, courtesy of O’Reilly Media’s Strata Gems. Interesting to see how popular Twitter is on internal flights across North America
The O’Reilly post goes on to explain how to write your own (links at the end of this post).
Hans Rosling’s TED talk on global economic and health trends was one of the first presentations to bring statistics to life by using timelines in motion.
Seeing data presented this way is still a rare event yet showing changes across a period of time in motion rather than a series of static charts can deliver a much clearer perspective. And there is software available that doesn’t require money or clever coders.
The image below shows the difference between putting £1,000 in a savings account earning 2% interest versus an ISA with 6% interest over 3 years. (Please don’t laugh, I created it at the beginning of 2008, the decimal point needs moving to the left.)
Click the Play icon to run the motion.
OK, maybe not quite as artistic as the Twitter video. It’s simple little chart but the motion helps emphasise how the gap in interest earned grows over time. How did I create it? Using a Google Docs spreadsheet and its built-in Motion Chart tool. In 2007, Google acquired Trend Analyzer from the GapMinder Foundation. The chairman of GapMinder? Hans Rosling.
If the same can be done in Microsoft’s Excel, I haven’t found it. Leave a comment if you know how. Excel trumps Google Docs in many ways, displaying motion charts doesn’t appear to be one of them.
Hot on the heels of announcing the new PowerPivot for Excel at the SharePoint conference recently, Microsoft’s Live Labs have announced another new tool with a very similar name: Pivot. So it seems we have PowerPivot for Excel and Pivot for the web.
There’s no mention of whether or not the products are technically related or just share the same name. What they do have in common is the goal to easily visualise massive amounts of data.
Side note: I wanted to embed the video (you can see it on the Pivot web site – link below) but the embed code provided didn’t work. Wish Microsoft could use a simple standard for embedding like everyone else…
References:
So much for publishing soundbites during the recent SharePoint 2009 Conference. What can I say, I got distracted 🙂 Here’s a delayed one.
Microsoft announced a new product at the conference that has been going under the codename Project Gemini – PowerPivot
PowerPivot is being released as an add-on component to Excel 2010 (licensing not available at time of writing). It’s purpose: analysing massive sets of data using familiar tools. It brings business intelligence (BI) into Excel. Historically, to do such large scale analysis has required specialist tools.
PowerPivot enables incredibly fast filtering and sorting of spreadsheet data extending to 100 million rows. That’s a pretty big dataset for Excel to handle. PowerPivot includes some nifty compression algorithms and the working data set is read only. There are features to enable you to edit related tables that feed into it. With SharePoint 2010 you will be able to display the content and analysis in web parts for browser-only scenarios. And whilst its title suggests it’s a giant PivotTable, PowerPivot is not your traditional Excel pivot table. You can have multiple slices based on related tables to cross-analyse the data. Here’s a couple of images taken from the conference:
In the image above there is the main pivot table (selected in blue) summarising and filtering total purchases by selected continents. To the left and top left of it you can see two slices that are being used to further filter the data by genre and rating
In the image above you can see one of the new functions that are included with PowerPivot, it is creating a sum by matching values in a related table. It’s hard to visualise how different this is to the traditional Excel pivot tables and formulas. One example given during the talk was that PowerPivot could enable historical comparison analysis such as comparing accounting information across financial years.
This is an interesting move for Microsoft as we enter an era where massive amounts of data are being created and shared across the Internet. Finding easy ways to visualise such quantities of data is a hot topic. Microsoft is not the ony one coming up with new tools…
Some reading on how massive amounts of data is challenging conventional wisdom:
Side note: Whilst I can see where Microsoft got the name from, I can’t help but keep calling it PivotPoint instead. Blame SharePoint, PowerPoint and PerformancePoint for that 🙂
Whilst most of my software-specific consulting activities revolve around the luscious SharePoint, I’m always on the lookout to bring Excel to the party. Excel 2007 introduces some new visualization capabilities to make it easier and quicker to analyze large data sets without requiring specialist tools. If you haven’t seen what Excel can do, here’s a brief overview of some of the new features. They can be found on the Home tab in the ribbon – Conditional Formatting.
A simple example – some data about product sales. Here’s a traditional approach: a table of data and a chart visualising the results.
Now let’s see what we can do with Excel 2007.
Data bars are great for showing quick comparisons between data without needing to create a separate bar chart. In the example above, we can see that we are selling more nuggets than anything else despite the budget suggesting widgets would be the best seller. To set up data bars, simply select the columns and, from the Home tab in the Office ribbon, select Conditional Formatting and choose Data Bars. Then select the color you want to use.
Icon sets are key performance indicators (KPIs). Great for showing if the data represents good, bad or indifferent results.
Tip: The default behavior for icon sets is to display green when the value is above 66%, amber when it is above 33% otherwise red. In just about every case, you will want to change the behaviour. To do so, in the ribbon go to Conditional Formatting and select Manage Rules. For the icon set you want to modify, click Edit rules and make your changes. In this example, I want a green light if the sales variance is 1 or above (i.e. met or exceeded target), amber if sales are within 20% of budget, otherwise it’s a red light for the sales team.
Icon sets have two major shortcomings. First, there are only 3 indicators of performance. In the above example, widget sales are far closer to hitting the amber mark than gizmos, and gadgets only just reached amber. An alternative feature to use is called ‘Color Scales’. Not as pretty, but gives a better range of indicators:
Here you can see gadgets are amber, but widgets are nearly there too. Gizmos are deep red – beyond saving.
The second shortcoming with icon sets as key performance indicators is that they give no indication about trends. Sales of nuggets look great, but is it expected or a surprise? In the image below, I have create line charts for each product and stripped away all the chart gumpf to convert them into ‘spark lines’. Now I can see that widgets have had a sudden drop whilst nuggets have had a sudden spike. And gizmos seem to be heading to oblivion.
Compare this information to the original example – It contains exactly the same data but you can instantly interpret a lot more from the information and it takes up less screen space.
For more information about designing useful visualisations, including spark lines and bullet graphs, see previous blog post: Designing Dashboards
Related blog posts:
Filed in library under: Microsoft Office; Microsoft BI
Technorati tags: Excel
Joining Dots 