Thoughts On Visio And How It Could Integrate With Power BI

The recent release of Visio Online (which means most people with an Office 365 subscription can now see Visio diagrams in the browser) and updates for Visio Pro for Office 365 (the desktop version of Visio, needed for creating diagrams, available on subscription but annoyingly not included in any of the Office 365 Enterprise plans) made me wonder if there’s anything new for BI pros in the latest version of Visio. This is a subject I come back to every few years – the last time was in back in 2013 – but I’m still a bit disappointed by some aspects of Visio when it comes to working with data even though there’s a massive amount of potential there.

To recap, there are actually two features of Visio that are interesting for BI purposes. First, you can import data into Visio and then link rows of data to shapes in your diagram (see here for a basic intro; this presentation has a lot more detail and is a must-read) to create data-linked diagrams; if your data source is an Excel workbook stored in SharePoint Online/OneDrive for Business or a SharePoint Online list then you can even refresh the data in Visio Online in the browser. While it doesn’t do exactly the same thing, the fact that the Synoptic Panel custom visual for Power BI is so popular shows that people really like the idea of seeing their data visualised in diagram form. If you know what you’re doing, you can do some really amazing stuff with Visio data-linked diagrams: not just change shape colours but also their formatting, geometry, size and more.

Secondly there are Pivot Diagrams, the Visio equivalent of Excel’s PivotTables. This blog post by David Parker is a really good guide to what they can do; like PivotTables they can even be linked direct to SSAS. The enduring popularity of the old Proclarity Decomposition Tree and the fact that so many people want something similar to be added to Power BI shows the appeal that this way of analysing data has for BI pros and Visio Pivot Diagrams are much more powerful than any custom visual the Power BI team could hope to develop.

So where does Visio fall short and what could be done about it? Here are some thoughts:

  • The data import options are limited, both in terms of where you can get data from and what you can do with it after that. These are all problems that Excel has solved in recent years with Power Pivot and Power Query, so why not borrow what Excel have implemented? Power Query/Get & Transform functionality for data loading and transformation, and a Visio Data Model (like the Excel Data Model/Power Pivot) would solve all these problems at a stroke and mean that anyone who had experience with Excel or Power BI would be able to move over to using Visio for BI easily.
  • Publishing to Office 365 is all well and good, but publishing to Power BI would also be useful. This might involve:
    • The ability to publish Visio diagrams to Power BI in the same way we can publish Excel workbooks to Power BI today.
    • A Visio equivalent to “Analyze in Excel” so you could create Pivot Diagrams connected to Power BI datasets. This should be straightforward to implement because Pivot Diagrams already work with SSAS, so all you would need to do is fire the same MDX at Power BI.
    • Once published, the ability to pin Visio diagrams to a Power BI dashboard.
    • The ability to schedule data refresh in the same way you can today with Power BI datasets and Excel workbooks.
  • The separate and relatively pricey ($13USD per month) subscription needed for Visio on the desktop suggests that Microsoft now thinks of it as a niche product, not something that even a top-of-the-range Office 365 E5 subscriber gets by default. I guess they’re allowed to make that assumption but it will be very hard for a BI pro to justify that expense for a product that isn’t essential for their job. Adding Visio into the top Office 365 SKUs would expose it to a much wider audience including BI pros. Incidentally the same argument can be made about Visio’s newly re-acquired ability to create diagrams from relational databases: it looks nice, but how many DBAs will be able to justify the cost of a subscription just for it?

In summary, while I know that I’m guilty of looking at Visio from a BI pro point of view and that data visualisation isn’t necessarily Visio’s primary purpose, I do think both Visio and Power BI could both benefit a lot from closer integration just as Excel and Power BI have done in recent years. With a bit of investment from both teams we could have another killer feature added to Power BI, one that none of Microsoft’s competitors could begin to match.

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:

image

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.

image

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:

image

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?