How do you choose the right data visualisation in Power BI to show your data?

How do you choose the right visualisation to show your data? Usually the customer wants one thing, the business user want something else, the business sponsor wants something flashy…. and it’s hard to tease out the requirements, and that’s before you’ve even opened up Power BI such as Power View, Excel, Tableau or whatever your preferred data visualisation software.

In other words, there are simply too many charts to choose from, and too many requirements to meet. Where do you start?

I found this fantastic diagram which can help you to choose the right visualisation. I’m often surprised to see that people haven’t seen this before. Note: this diagram was done by Andrew Abela of Extreme Presentation and the source is here and his email address is on the slide, so be sure to thank him if you’ve found it useful. If you can’t see it very well, click here to go to the source.


Chart Choosers should not replace common sense, however, and Naomi Robbins has written a nice piece here which is aimed at the wary. However, diagrams like Abela’s can really help a novice to get started, and for that, I’d like to thank him for his work.

How does it related to Microsoft’s Power BI? If you look at the visualisations that are available in Power View, you can see that most of the visualisations in the diagram are available in Power BI.  The ones that are excluded are the 3D graphs, circular area charts, variable width charts, or the waterfall chart.

Why no 3D? I personally hope that Microsoft will leave 3D out of Power BI tools, unless of course it is in Power Map.  With 3D on a chart, it is harder to identify the endpoints, and it can take us longer. It might also mean that points are occluded. If you’re interested and want to see examples, here is one by the Consultant Journal team or you can go ahead and read Stephen Few’s work. If you haven’t read anything by Stephen Few, get yourself over to his site right now. You won’t regret it. Why is it different from Power Map? 3D maps provide context, and they are the exception where I will use 3D for a data visualisation showing business data. I’m obviously excluding other types of non-business data here, such as medical imaging and so on.

Why no circular area or variable width charts? I am not a fan of variable width of circular area because we aren’t very good at evaluating area when we look at charts and graphs, and Robert Kosara has an old-but-good post on this topic here.

This blog is mainly for me to remember stuff but I hope it helps someone out there too.

Best Wishes,

Data Visualisation: lifting the curse of Cassandra

CassandraInformation is the new currency, the lifeblood of organisations. However, it has to be explored, and evangelised throughout the organisation before it can have any real impact. SQL Server 2012 now helps business users to access the data; a real paradigm shift in the ‘umbrella’ of users who touch SQL Server.
However, does that mean that the users will be believed? The ‘messenger’ of the information can have a great influence on how the information is – or is not – propagated throughout the organisation. Sometimes, people are simply not believed, or their ideas entertained. This may be due to the way that they put the message across, or simply due to the fact that they can’t get the message to the right people without upsetting the apple cart. 
This is about the person (or group) in the organisation, who might meet one of these criteria:
  • see a ‘train crash’ going to happen in the organisation, but can’t should loudly enough to avert it happening.
  • see room for improvement in the business, but find it hard to get their message across
  • have a ‘gut feel’ about what customers are telling the enterprise, but find it hard to prove, demonstrate or research this ‘gut feel’

This leads to the Cassandra Complex.  Quick history lesson: according to Greek mythology, Cassandra was the beautiful daughter of King Priam and Queen Hecuba of Troy. She refused the advances of Apollo, who set a curse on her: that she would always tell the truth, but never be believed.
There are parallels with this mythological figure in the workplace, which may engender your sympathy or empathy. You might see this in yourself or in someone else. Do you see the ‘train crash’ in the organisation before it happens, but can’t get the message cross? Do you see patterns in the data, and find it hard to evangelise your findings throughout the organisation?
If so, you could be the ‘Cassandra’ in your organisation, or a customer or associated company, for example. It is tremendously frustrating to see issues in the organisation, but not get the message across. So, if you see someone banging their head against a wall, trying to show problems before they take hold: they do this because they care, but perhaps that isn’t the best way to get the message across.  It also helps business users to test out their theory by allowing the users to explore their findings properly, before publicising them.
A better way to get the message across is to research, demonstrate and uncover the findings in the data using data visualisation technology such as Power View, which can help. It is the new part of SQL Server 2012 which allows users to touch their data; it isn’t just about techies any more. By showing the ‘truth’ of the data, hopefully this would cure the curse of Cassandra: to be heard and also to be believed. Visualising data can bring insights, and attention, into data that can show where the problems reside in the organisation.
Data Visualisation can help to make Cassandra speak – and be believed. 
Sometimes people need to ‘see’ the problem before they understand it. Data Visualisation makes the insights accessible. It’s harder to ignore Cassandra if the the message is shown inescapably to all, particularly when it’s right in our favourite Office tools such as PowerPoint or Excel.
Making data insights accessible means that data visualisation are used to make analysing data simple, assuming the data is properly organized, cleansed and sanitised. The beauty of these solutions is that it’s fast to get results, and easy to show them off. If you’re interested in looking at data visualisation technology, then the Gartner Report is a good place to start. I tend to think that the technology should support the business requirements, within the budget set by the organisation for purchasing software. That’s why it’s difficult to recommend one over the other, since the answer is usually ‘it depends’…
Whether or not the organisation works on the insights, of course, is never guaranteed. As always, life isn’t that simple, but it’s a new angle that might help push problems forward and turn them into solutions. 

Tabular models and Tableau

I was recently asked how to connect Tableau to a Microsoft Tabular model. The concept itself is straightforward in Tableau. In my opinion, tabular models will become more prevalent, so I will start to look at them more detail.

It turns out that the individual who questioned me was struggling, unfortunately. The resolution was that he wasn’t including the instance name as part of the connection.

In order to help business users to connect Tableau to the tabular model, I have included a brief video on how to achieve connectivity between Tableau and the Tabular model.

The video does include my Scottish accent, so please feel free to turn down the volume!

The link to the video is here:

I’m blogging this on my iPad so please excuse that I haven’t inserted the video itself! I will do this when I am back online properly. I am on holiday just now, but thought it worthwhile just to get the information out to help the individual ASAP

Update 8th April: video inserted 🙂 enjoy


SQLUniversity: PowerPivot, Tableau and Jedi Knights

This blog will show an overview of how I mobilised PowerPivot using Tableau. I’ve previously given this session at SQLBits, NEBytes Microsoft Technology User Group, and SQLServerDays in Belgium but thought it would also be useful to supply the files for you. The steps are very simple since I intended to show the end-to-end solution simply as a proof of concept, as follows:

  • creation of a PowerPivot which mashed up UK Census data with geographical data
  • creation of the report in Tableau
  • deployed to Tableau Public for consumption by mobile devices such as the iPad
The example was deliberately kept simple in order to prove the concept of PowerPivot being mobilised. 
The data sample involved mashing up two sources:
  • Jedi Knight census, data, which can be downloaded from here This is a basic file but the final PowerPivot can be downloaded from a link later on in this article
  • Geonames offer an excellent free download service, which you can access here
The Jedi Knight data, along with the geographical data, were joined using the outcode of the postcode data. If you need more definitions of the UK postcode system, I’ve previously blogged about this here.  
Essentially, a very simple RELATED formula was used in order to look up the latitude and longitude from the UKGeography table, and put it into the Jedi Knights data, and produce the necessary data in a simple Excel table. The formula looks like this:
Once these very simple formula were put in place, it was time to load the data into Tableau.
Tableau can take both PowerPivot and Excel data – which driver to use?  I used version 6 of Tableau. Whilst this version of Tableau does see the PowerPivot correctly as an Analysis Services cube, it does not always read the date as a ‘date’ type, but instead as an attribute. There is a forum posting on the Tableau website which tells you how to fix this issue, which involves changing the date so it appears as a measure, which means it can then be used for trends and so on. 
However, I wasn’t comfortable with this solution because I like dates to be in date format. I’ve also run into this issue at customer site, where the customer wanted to use SSAS as a source and Tableau as the presentation layer. They were data-warehouse savvy and didn’t like the ‘measures approach’ fix. 
On customer site, I got around it instead by using the Excel data source, and importing all of the PowerPivot columns into an Excel 2010 sheet. By doing it in this way, date formats were preserved. In this example, I didn’t have date format so it didn’t matter – but this is a useful tip for the future if you are using PowerPivot with Tableau. The final data, in an Excel PowerPivot, can be obtained in zip format here or if you can’t access it, please email me at jenstirrup [at] jenstirrup [dot] com.
Once the data was accessible by Tableau, I used the Tableau Desktop version to upload the data into Tableau’s memory. I did this so that I could eventually upload the Tableau workbook to Tableau Public. The instructions to save to Tableau Public are given here
Once the data was in Tableau Public, I just needed to access the data using the Safari browser on the iPad. In case you are interested, the demos are publically accessible and you can access the final result by clicking on the hyperlinks below.
I hope that’s been a useful overview of PowerPivot, and the ease of which it was mobilised. This blog forms a use case of how it might be useful to use PowerPivot, since I think that people sometimes need examples of how PowerPivot can benefit them. In this case, the clear benefit of PowerPivot is to provide an easy way of mashing up different data sources.
I look forward to your comments and thank you for sticking with me for the PowerPivot SQLUniversity discussions!

SQLUniversity: Introduction to PowerPivot and Mobile Business Intelligence

Here is the second post for SQL University, where we introduce the topic of mobilising PowerPivot data. As an introduction, I thought it might be useful to share the presentations that I did recently. Tomorrow’s blog will talk more about the details of mobilising PowerPivot data using Tableau, but I thought that these articles might provide some introductory material for people to use, and re-use, in order to expose the usefulness of PowerPivot as a data source. Here is a brief overview:

Essentially,the Ordnance Survey and Census data were mashed up together and linked together in PowerPivot. The PowerPivot was then used as a source to Tableau, which was used to visualise the data.  As an introduction to the process, I generated some presentations to use when I was talking around this solution. and I have provided these presentations here.
Here is a very introductory presentation on PowerPivot in Sharepoint. This presentation was designed to be given in around 5 minutes before being supported a series of demos, and the intended audience was people who hadn’t seen PowerPivot previous to the presentation.

The following presentation came from SQLBits, where I gave a discussion on mobilising business intelligence with PowerPivot. Tableau Software was used in order to display the data from the PowerPivot, and the next blog in the series will present more technical detail on how the solution was built.

Here is the presentation that I produced for SQL Server Days in Belgium in November 2011:

I hope that you enjoy these presentations, which I’ve provided for people to use and enjoy as they wish. Tomorrow’s SQLUniversity post will provide more detail on the mobilisation of PowerPivot with Tableau.

Windows Azure Marketplace – what data sources would you like to see?

During my presentations at SQLBits, SQLRelay and other UK User Group meetings, I have been dismayed by the lack of awareness of the Windows Azure Marketplace. This blog aims to explore some of the reasons that this may be happening, and I’d also like to canvass you, dear reader, so you can highlight the data sources that you would like to have in the Datamarket.
First of all, the Windows Azure Datamarket is not to be confused with the Datamarket, which is a company based in Iceland which sounds similar. The Windows Azure Datamarket is a broad reaching collection of subscription-based data services, including applications and a variety of data for consumers and businesses to utilise. It is available in 26 countries, as at the time of writing in October 2011. It is a marketplace in the sense that it is possible to purchase and sell data and applications. The types of data available include financial, property, geographical data, retail data and even fun sports data. The data from the Windows Azure Marketplace can be consumed by Excel, Tableau and Visual Studio.
One intention of the Windows Azure Marketplace is that it will support business analysts everywhere, in their quest for clean, up-to-date data. I believe it is potentially a very powerful source of data for enterprises. For example, by provisioning clean, “looked after”, up-to-date datasets, it can reduce the amount of effort in looking after external data. In other words, companies who already ‘clean up’ external data sets might look to the Windows Azure Marketplace in order to see if there are existing datasets that could be rented. It’s the old problem of ‘outsource or internal spend’ – but at least it is good to have options to explore.
So, given the potential for the Windows Azure Marketplace as a potential data store, why the lack of awareness or uptake? Out of my recent travels to various User Groups, SQLBits and so on, hardly anybody had heard of it, never mind actually used it in production.  I am guessing that one reason for this is that the data stores aren’t plentiful with UK-focused datastores.  My research showed that there were a number of UK data sources available. These included:
In other words, not very many sources! My search was hampered for the fact that the search string must contain at least three characters. Therefore, if you are searching for ‘UK’ then you are stuffed! I am guessing that the uptake isn’t very strong since the UK-focused data needs to be grown. In my opinion, I guess that this will happen over time.  Since there is an Excel add-in for the Marketplace, the route to uptake of this service is clear. I think that this will take time, and it is potentially a very powerful tool for analysts and researchers.
Hence this blog: I am wondering what UK data sources you would like to see? Here is my list of free data sources that I’d love to see on the Marketplace as a one-stop-shop for data requirements:
The Guardian Datastore – basically anything that they produce. Love it!
UK Census data – since the next Census is out soon in the UK, it would be particularly relevant to have this information
The Data Archive – Social Sciences and Humanities data for the UK. Not as esoteric as they might sound since they also discuss the future of data sources. This is a reflective data store, and I’d recommend that you take a look at it.
Health and Safety Executive Data – Risk Control, Public health and comparison with other European countries
Heidi – I have never been able to access this, but it is available to Education planners. 
The Treasury also offer UK data on finance and key financial indicators
The Bank of England offers a wealth of financial data, focused on the UK
Office for National Statistics – data on agriculture, children, economy, government, travel… you name it!
If you can think of any other data sources you would like to see on the Windows Azure Datamarket, then please leave a comment. I’d love to hear from you and you’d also satisfy my never-ending thirst for more data sources!

Mobile Business Intelligence – Try it out!

Thank you to everyone who attended my SQLBits ‘Mobile Business Intelligence in Action’ session recently. If you are interested to try out Mobile Business Intelligence on your iPad or mobile device, here are the links below:

Jedi Knight Actuals of UK Census 2001 Dashboard 
Jedi Knights Percentage of UK Census 2001 Dashboard
AdventureWorks Sales by Geography Dashboard
AdventureWorks Actuals Sales
AdventureWorks Analysis Dashboard

I haven’t tried this on every browser and every device, so I would be very interested in your feedback.
I look forward to hearing from you. Please leave a comment below, or email me at jenstirrup [at]