Self-Service Dashboards in Excel 2013

One of the more difficult problems in BI is keeping up with the demands on the users. As a consultant, I talk to the users on a daily basis to find out how they are using the system and how it could be improved. I rarely run into negative comments, but what I do hear a lot about is “Can we see this?” or “Can you show me this on the dashboard as well?” Of course, I would love to be able to cater to all of my clients’ needs, but unfortunately, there is only one of me and only so many hours in the day. This is where we really need to be able to utilize ad-hoc reporting tools and other BI tools that make visualizing the data extremely easy. If all the tools we used had zero learning curve, then the users themselves could explore data and create their own visualizations without running into technical issues or having to know much about the data. Many of the dashboarding and reporting tools out there attempt to do this, but no matter what tool you are using, nothing is easier for the users than Excel. Now, if only Excel had a dead-simple dashboarding feature. Enter Power View! Power View has been around as a feature in Microsoft Office Excel since they released Office 2010, but in order to use it, you had to have SharePoint 2010 available to you. Now, with the release of Office 2013, Power View can be used without a server component. This means that you can take advantage of the easy reporting capabilities of Power View by using your own Excel Spreadsheet data.

Setting up the Data

First, a quick note about Office versions. Power View is available to users running the Professional Plus edition of Office 2013. If you do have that edition, you will also have access to GeoFlow as well. Before you can use Power View, you are going to need to have access to the data that you would like to visualize. There are number of ways to access the data, the easiest of which is to just have the data in an Excel spreadsheet, but if you have access directly to the database, you can select the tables/views to use and set up a quick data model in PowerPivot. In addition to those methods, you can also use the new Tabular Data Models available in SQL Server 2012 (or the upcoming SQL Server 2014), but that is beyond the scope of this article. To make this process as simple as possible, my recommendation is to create a view in the database that contains the data you wish to look at, then create a data connection to that view and import it into the spreadsheet.

Getting Started with Power View

To launch Power View, all you have to do is to highlight the data you want to analyze and click on [Power View] from the [Insert] tab on the ribbon. You can see that Power View will show you a list of your fields on the right and start you off with a tabular view of your data in the center. The first thing I’ll do is to clear out the check boxes from the field list and remove everything that I don’t want to see. For this reason exactly, I’ll leave [StateProvidenceCode] and [TotalDue]. This will show me Total sales for each state in my region. You’ll see that I now have something that I can analyze on my report: The next step I would want to take is to turn this into a bar graph. From the [Design] tab on the ribbon, I have access to several different types of graphing options. I can select [Bar Chart] / [Stacked Bar Chart] from the available visualizations, and Power View will immediately turn my table into a bar chart that can be sorted and resized to fit the layout. The real advantage here is that I don’t have to worry about all the formatting options that I would normally have to mess with if I was using a normal Excel graph. Instead, Power View makes it dead-simple to create a

number of charts. It will also link the charts together automatically. So, if I wanted to add a table of data that showed the number of customers for each city filtered by the state that I selected from the bar chart, I can just add a new table, select the fields I want to show, and Power View does all the work for me. I’m sure you can tell we’ve just scratched the surface on what you, as a user, can do with Power View. Keep up to date with this blog as we’re sure to post more in-depth looks at the other features of Power View.