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,

Speaking Events: SQLRally Amsterdam and SQLRally Sweden

I’m delighted to announce that I am speaking at two major SQL events in Europe in the next month, on data visualisation and power view. I will also be holding a Women in Technology event in Sweden and there will be more details to follow on this. So where am I going?

  • SQLRally Sweden
  • SQLRally Amsterdam

SQLRally Nordic are back, and this time it is in Stockholm, Sweden. I used to work in Stockholm, and it ha become one of my most favourite places on earth. What is SQLRally Nordic offering you?
Well, come and learn from some of the world’s most renowned SQL Server experts, including Jim Karkanias, Adam Machanic, Brent Ozar, and Kalen Delaney. Some of the SQL Learning goodies involve:

1 day of deep-dive pre-conference seminars
2 full days of top technical sessions:
   – BI Platform Architecture, Development and Administration
   – Database and Application Development
   – Enterprise Database Administration and Deployment

SQLRally Amsterdam, hosted by the same crew organizing the Dutch SQLSaturday events, welcomes you to Amsterdam on November 6-8. If you are a SQLZaterdag fan, you’ll already know about the fun event that takes place every year. The SQLRally Amsterdam team are building on their previous successes, and they’re also offering sessions with some of the world’s most renowned SQL Server experts, including Adam Machanic, Brent Ozar, Thomas Kejser, and Denny Cherry. Join SQLRally Amsterdam for:

1 day of deep-dive pre-conference seminars
2 full days of top technical sessions:
   – BI Platform Architecture, Development and Administration
   – Enterprise Database Administration and Deployment
   – Database and Application Development

I look forward to seeing you at both events!

Eating the Elephant: Totally free videos showing an introduction to Visualising Big Data for Business Intelligence Professionals

Continuing my ‘eating the Elephant, one bite at a time’ series, which focuses on Microsoft Business Intelligence and Hadoop, I’ve put together a series of totally free videos, to help people who are interested in visualising Big Data using familiar tools in Microsoft. The purpose is to take data from various data sources, including SQL Server, HDInsight (Microsoft’s distro of Hadoop) and Excel, and visualise the data via PowerPivot and Excel.

Self Service Bi – and Big Data – a Business Intelligence person’s dream! Well, it is for me!

As I say in these videos, Excel is the world’s favourite software for Business Intelligence, and it must surely rank as one of the most favourite software applications of all time. Excel is used (and abused!) more than any other software I’ve seen.

I hope that you will enjoy the videos and I look forward to your feedback. You can access them on YouTube here

Please note: in the process of practising for my Big Data precon at SQLPass Summit in Charlotte on 15th October, I reused the material from a fantastic blog post by Cindy Gross of the SQLCat team and I’d like to thank Cindy and her team for writing this material.

The blog post is here and I recommend that you go through it – I just videoed it, but the material belongs to them so I’d like to make sure that they get credit for the blog post, so that’s why I’m emphasising that I’m calling it out.

Please note that this isn’t material from my actual precon – it’s simply a way of me to work my way through preparing for the precon I’m presenting jointly with Allan Mitchell (SQL Server MVP). I have simply put it in video format in order to practice my delivery, and then it struck me that people might find this useful. If so, look out for more videos in future!

I hope it helps.
Kind Regards,

Notes to Self: useful links for Power View and Multidimensional models

Here is a collation of some useful Power View and Multidimensional Models literature from TechNet and
Quote from the above article: This does not support connecting to and creating reports for multidimensional models. Power View for multidimensional models supports browser based Power View clients only.
 In light of this information, it makes sense that you will need to create RSDS files in SharePoint and here is the link on how to do this: Create a Shared Report Data Source Connection to a Multidimensional Model 
Once you have set up your connection, you can create your Power View report: Create a Power View Report with a Multidimensional Model Data Source
To get an overview of how Power View in SharePoint works with Analysis Services Multidimensional models, take a look here: Understanding Multidimensional Model Objects in Power View

Explore the Adventure Works Multidimensional Model by using Power View – This article walks users through creating a Power View report and exploring the sample Adventure Works multidimensional model.

Big Data for the Business Intelligence Professional SQLPass Summit Preconference Day

I’m delighted to announce that Allan Mitchell (blog ¦ twitter) and I are giving a Big Data for the Business Intelligence Professional preconference session at SQLPass Summit 2013 in Charlotte, NC.

What will we be covering?

What does Big Data mean for business intelligence professionals? Come see the “whats,” “hows,” and “whys” as this full-day pre-conference session demystifies Microsoft’s Big Data technology offerings. 
Microsoft has partnered with HortonWorks to bring Big Data into the hands of business users via their favorite BI tools: Power View, PowerPivot, and Excel. This session will cover both on-premise and Azure-based technologies, examining the Microsoft distribution of Hadoop through a range of technologies such as HDInsight, Hive, and Azure through to Excel, PowerPivot, and Power View. 
The first half of the session will be dedicated to the “what” and “how” of Hadoop, using HDInsight. With Hadoop comes a whole ecosystem of tools, and we’ll look at how to use them in a BI context to support users’ Big Data needs. We’ll also look in detail at MapReduce to parallelize complex Big Data queries across many servers. After you have Big Data, how are you going to serve it to business users to derive value for the enterprise? 
The second half of the session will be dedicated to the “why” of Big Data. We’ll visualize Big Data in our favorite BI tools: Excel, PowerPivot, and Power View.

Would you like to join us? 

We’d love to see you there! Visit the PASS Summit 2013 website for the latest updates.  You can also take advantage of early registration, so book now to register at $800 off the final registration rate by June 30.

What is SQLPass Summit? 

It is the conference which is the pinnacle of the SQL Server world, featuring over 125 hand-picked sessions to help data professionals advance their careers and make the most of their Microsoft SQL Server implementations. The conference takes place on October 15th – 18th.
Presentations across 6 tracks will cover everything from database administration best practices, new SQL Server features, and the latest database and application development techniques to what you need to know about building and managing effective BI solutions and being successful in the cloud.

PASSBAC keynote: The Microsoft data story, and the next chapters

I attended the keynote from the PASS Business Analytics Conference in Chicago, April 2013. For those of you who missed it, here is some of the content of the keynote.
The take away point is that Business Intelligence must be simple. It is important to make it fun, and we are drowning in data. Not being able to read and understand data, is like being disadvantaged in today’s world. We have to move beyond what we now think about Business Intelligence. We have to get inside our data. 

The keynote was opened by Bill Graziano ( twitter ¦ website ) who underlined the importance for a need amongst Business Analytics professionals for knowledge and support. PASS is helping to formulate a community of Data Professionals. If you’re a Business Analytics  professional, you can be a part of the shaping of this community and be a part in it. If this interests you, could can take a look at joining up a Virtual Chapter and receive monthly webcasts, for example. This can help you to stay connected once PASSBAC is finished.
Next, we had Dell appearing. They have over 15 years of IM software experience. 

Dell has over 15 years of IM software experience, after their purchase of @Quest, for example. Dell constantly monitor their own brand in social media. For example, they have 6 years experience in watching their brand online, and anticipating with customers from a support and brand engagement perspective. They made the following observations in the social media market:

Dell’s Observations
Data Type Proliferation
Vendor Proliferation
Data-Location proliferation
IT and LOB challenges
Snap into existing environments

Given these observations, they then moved to address these points in the market:

Analysis of social media needed to be:
Data-Type Agnostic
Vendor Agnostic
Data-Location Agnostic
Capability needs to be at the tools layer

The takeaway point from the Dell part of the keynote is: Make the hard things simple to allow for more collaboration, exploration, analysis and communication
The final part of the keynote was presented by Amir Netz, who is a Distinguished Technical Fellow at Microsoft. The few times I’ve been lucky enough to speak with Amir in person, I’ve found him to be a very approachable and fun guy, and this came across clearly in the keynote, which was probably the most engaging I’ve seen (and I see a lot!) Netz was accompanied by Kamil Hathi who knows his stuff inside out and is a ‘go to’ expert for Analysis Services. I was really excited about this keynote since, whilst individually they are both excellent speakers, the idea of a joint presentation sounded fun and informative. They started off by emphasising how ‘simple’ attracts people. The strategy is to go back to Excel, thereby capturing the simplicity once again.  
How can we make the spreadsheet really interesting again? Well, we can add in lots of unstructured data! To do this, we can use Hadoop, which is a file system, essentially a shoebox of unstructured data. A lot of data, all different kinds! Using Hadoop, you can easily transform the unstructured data. Structure on extraction, rather than a structured data model before extraction. Therefore, you’re not imposing a structure on data that the ETL meets via the load; you’re structuring the data once it is in Hadoop.

The team then did a great demo of PowerView using data from a dataset of music and songs. We learned that Mariah Carey has had more weeks in the charts than luminaries such as Elvis, the Beatles and U2.
This showed the power of the interaction of the data. In Amir’s example, the kids had fun learning about their music idols. This was easily demonstrated in the keynote audience, who were cheerfully shouting out band names. We heard all sorts of names being shouted out: Willie Nelson, Johnny Cash, Madonna and even Milli Vanilli!

Power View is all about sharing information, and having fun with the data. 
Business Intelligence is elective – nobody forces an organisation to use Business Intelligence. It is about time management, and how much time people spend on Business Intelligence.
However, business intelligence can be fun can help you to get the ROI because it means that people will use it, and learn from it. Fun is important, and success is infectious.
It has to be more than fun, but it is a good starting place.  We can use it to start more investigations, and then lead to deeper questions.
The team then did a deeper exploration using Power View for sentiment analysis with Twitter data. Sentiment used as a means of prediction of outcomes. If you’d like to know more about this, I’ve written a two part MSDN article on the topic.

The most exciting part was the announcement of Codename Geoflow, which allows you to do location sensitive content to your data. In other words, it allows you to create 3D data visualisations based on maps. You have to see it to believe it, and if you’re looking for #Geoflow information, here you are … #PASSBAC #SQLBits #SQLPass #SQLFAQ
Amir did a great demo to show the changes in the ‘music chart songs’ data over time and over place. It is a wonderful story, brought to life by #Geoflow. it also looked great on the huge 81 inch touchscreen, and its a great way to drive visualisations of data. At the PASS BA Conference, we will be lucky enough to have the Microsoft Experience lounge, where we can go and try all of this gadgetry out! Like Amir says, it has to be fun too.

We don’t just think about business. Business Intelligence could also be called basic intelligence, but to achieve it, we need to get inside our data and let people work with it in familiar tools. 
This is the Microsoft story, and I’m excited to see the next chapter for our business users.

Up and coming PASS BA Sessions, and Data Visualisation and Microsoft Workshops

If you’re here looking for precon notes, I will be posting these up in the next few weeks after I’ve finished my precons at SQLSaturday Denmark, Databasedays Switzerland and SQLBits in Nottingham, UK. So please keep checking back, or email me at 

I’m delighted and proud to be presenting at the PASS Business Analytics Conference this week, in Chicago, Illinios, on 11th and 12th April. 

I love SQLPass Summit, and it is one of the highlights of my year. The Business Analytics conference is happening for the first time, and I can’t wait to meet the new #SQLFamily – or should that be #PASSBAFamily – that I will meet! I’m already making arrangements to meet fellow BA and Twitterati ‘in person’, which is one of my personal favourite things to do! See you there!

I’m pleased and proud to be presenting a preconference training in the Denmark in April 19th at the Microsoft Offices in Hellerup, Denmark, and a workshop in Pfaffikon, Switzerland.

With respect to Denmark, in case you’re not familiar, SQLSaturday events are often accompanied by one-day, very reasonably priced community training days.  This means that attendees get a deep-dive training day on a specialised subject on the Friday Preconference training day, as well as being able to attend the range  of sessions on the main SQLSaturday event.

The precon I’m delivering is an in-depth day, which is aimed at Business Intelligence specialists who focus on front-end reporting. We will be looking at some of the ‘new toys’ in the SQL Server 2012 toolbox, as well as having a fresh look at old favourites such as SQL Server Reporting Services.

Our data source is the Tabular Model.  I’m seeing more traction of the Tabular model at customer sites, but not everyone has had the chance to play with it yet. Therefore, I thought it worthwhile to use the Tabular model as a data source, in order to expose its usefulness to a wider audience. 
To register for the Denmark event, please click here
I’m holding a Data Visualisation workshop in Switzerland on 17th April.  

If you want to conduct advanced, business-oriented Business Intelligence analysis in SQL Server 2012, then it is essential to understand data visualisation. This session is aimed at developers who want to make informed data visualisation decisions about reporting, with supporting cognitive psychology theory where relevant.
The takeaways will focus on:
  • Introduction: Finding patterns in the data.
  • Further Data Visualisations: learn about visualisations that are perhaps not so well-known. This includes Stephen Few’s bullet charts and Tufte’s Sparklines in SSRS.
  • Multivariate data: We will cover the complexities of displaying multivariate data since is potentially more complex by visualisations. For example, we will look at Tufte’s “small multiples” in Power View and in Reporting Services.
  • Putting it all together: Considerations for Dashboards. This workshop aims to deliver a breadth of data visualisation knowledge, underpinned by cognitive psychology theory to provide deeper understanding.

To register for DatabaseDays, please go to

Last but not least, I’m holding a preconference training day at SQLBits in Nottingham, United Kingdom, on Thursday 2nd May. SQLBits is one of my favourite events, and I’m very glad to be going back to my roots after having been far travelled this year so far!
If you’d like to register for the SQLBits precon, the Training Day link is here. I hope to see you there!