Building A Simple BI Dashboard With Visio 2013 And Visio Services

Let me start this post by saying that I am a long way away from being a Visio expert – I’ve used Visio, of course, to create diagrams and I’ve also played with its BI capabilities in the past, but nothing more than that. A recent post on the Visio team blog reminded me about Visio’s BI capabilities and Jen Underwood then mentioned that Visio 2013 has some new functionality for BI, so I thought I’d check it out in more detail and blog about what I found. I’ve never seen Visio used to build dashboards or reports in the real world, but a quick search shows that the Visio pros out there have been doing this for years, so maybe it’s time us BI folks learned a few tricks from them? Visio 2013 is by no means a perfect tool for BI but I was pleasantly surprised at what it can do: you can create data-linked diagrams/dashboards in Visio on the desktop very easily, and then publish them to Visio Services in Sharepoint where they can be viewed in the browser and where the data can be refreshed.

First of all, the PowerPoint deck here is a good place to start to learn about Visio and Visio Services 2013 dashboards, as is the Visio team blog and Chris Hopkins’ blog. There’s also a walkthrough of how to link data to shapes here, and a lot of other good posts out there on creating charts and graphs in Visio such as this one.

Here’s what I did. To start, I created a few tables with data in in Excel to act as a data source, then published the workbook up to Excel Services in Sharepoint Online (I have an Office 365 E4 subscription). The data looked like this:

I then opened up Visio 2013, connected to the workbook in Excel Services and imported the data from these two tables. With that done, I was able to select a shape, and then drag a row of data from the External Data pane onto the sheet, which gave me a data-linked shape. It was then fairly easy to configure the data graphic associated with each shape – for example, in the diagram below, I selected a City shape, then dragged the row containing sales for London onto the sheet, which gave me a City with the data for London linked to it, and next to the City shape I had an associated data graphic which I configured as a Data Bar of type Multi-bar Graph.

The text next to the frowning face is also linked to data from Excel. I could then publish this to Sharepoint Online, and view the diagram in the browser just by opening it from the document library:

All very straightforward. In Visio Services you can add comments, and also refresh the data. Data can be refreshed manually or on a schedule; I used Excel Services data in this demo because in Office 365 only Excel Services and Sharepoint List data sources can be refreshed but the story is much better in on-prem Sharepoint (the PowerPoint deck I linked to above has all the details). Weirdly, I found that if I modified my Excel data source in the Excel Web App it took a few minutes for the new data to come through in Visio even with me clicking Refresh, although it did eventually.

Obviously this is a very basic (and badly designed) dashboard that works within the limitations of Visio and Visio Services, and if you want to learn how to do this properly I suggest you check out the links above. But there are two important questions that now need to be answered:

Why, as a BI pro, would I want to create a dashboard in Visio rather than, say, Excel or Power View?

I suspect Visio isn’t used more widely in MS BI circles is 20% down to ignorance of what it can do, 30% the cost of licensing and the Sharepoint dependency, and 50% the fact that there are only a limited number of scenarios where it is the right tool to use. So when would you actually want to use it? The risk of using Visio is that you end up with a visually appealing infographic that is actually very bad at conveying the information you want to convey, the kind of thing Stephen Few is complaining about here. You’d probably only want to use it if the nature of the diagram contributed to your understanding of the data. For example if you wanted to look at which seats were filled more frequently than others in a theatre or an aeroplane, it might be useful to have a diagram showing the seat layout and colour the seats that get filled. I guess these scenarios are very similar to the kind of scenarios where it makes sense to plot geographical data on a map.

What functionality is Visio missing for it to be a serious BI tool?

Quite a lot. Leaving aside PivotDiagrams, there is no proper support for SSAS or PowerPivot for data linking and that’s a big problem in these days of self-service BI. I also don’t link the way you have to import data into Visio before it can be used: I’d want to be able to select the data I want using a PivotTable-like interface (generating MDX or DAX queries) and then bind it to shape, so that I could slice and filter my data inside Visio without having to keep on importing it; I imagine being very similar to Power View today, but where you could drag data-driven shapes onto a canvas instead of tables and graphs. Maybe Power View and Visio need to get together and have children?

I don’t want to finish this post on a critical note, though, because I’ve had a lot of fun learning more about Visio and its BI capabilities, and I hope to use it on a project soon. Now that Sharepoint and especially Office 365 are being pushed so heavily for BI (and being used more widely), maybe we’ll see a lot more of Visio dashboards?