In the Microsoft Office 2013 Professional release, we have a new tool on the market that can really help to allow us to get good visualization into our data. It’s called Power View, and was created by the Microsoft team responsible for SQL Server Reporting Services. I admit, I was skeptical when I first heard that, as I’m not the world’s biggest SSRS fan, but I was pleasantly surprised with how much fun it is to use. I can see using this on top of Power Pivot workbook that you use to personally analyze your data, and then use Power View to get a really cool report that you can personally use wherever and whenever you want.
First, we need to enable PowerView in Excel. Assuming you’re starting from a blank workbook, go up to File-> Options, and then select the Add-Ins tab on the left:
Once your on the Add-Ins tab, at the bottom of the tab under Manage, select COM Add-Ins, and click Go:
When the COM Add-Ins management screen appears, find “Power View” and check the box, then click OK:
Once you click OK, you’ll be back to a blank workbook. Your Power View icon is on the Insert tab, about half-way across on the page:
Now that we have Power View enabled, let’s get some data! For this example, I have the Adventure Works tabular data solution deployed on my local machine. This is available on codeplex for download here.
Go up to Data, and under the Get External Data section select the “From Other Sources” button, and then click on Analysis Services. Tabular databases are accessed the same way that multidimensional databases are, if you are used to that. Type in the name of your server, and click Next:
Select the name of the cube or perspective that you want to browse, and click Next. I’m going to leave it on “Internet Operation”
Review and Click Finish:
Now once you click finish, on the next page we get a new option, added specifically for Power View:
Select “Power View Report”, and then click Ok. Excel will think for a few minutes and then give us a blank worksheet canvas to work with. For those curious, SharePoint Power View looks identical to this. What’s nice about it is that you can get a SharePoint/SQL Server Enterprise feature on your local Excel 2013 Professional install! This is really cool if you want to show what you can do if you’re building a POC for a business intelligence project. The blank canvas will look like this:
On the right hand side you’ll see all of your fields that are available from your data source. To start, let’s create a report that shows the Gross Profit for each product category by year, and give our Power View report a title. To give it a title, just click at the top and start typing what you want your title to be, and to create a new “report”, just click on the field you want to add and drag it into the Fields section underneath or anywhere on the white canvas to the left:
Now let’s add Product Category and Year, and we wind up with something that looks like the below:
Easy to create with the click and drag interface, but hardly anything I’d want to go running through the halls about yet! Let’s give our report some flair (I think the minimum is 6 pieces, Office Space aficionados?) Click anywhere in the table, and then at the top under Design, let’s change the chart type to something more visually appealing:
Select Other Charts, and then click Line. Power View automagically changes our data into a line chart that we can use. Now we have a small chart that we can use, but we can’t see anything as it’s pretty small:
Fortunately, Power View was built with dashboards in mind, and at the top right-hand corner of the containment box there is an option called Pop-Out, which blows the small chart up to the size of the entire screen.
Another thing you’ll notice is that down at the bottom-right hand corner, our options have updated to give us the choice on what we want to see under each axis:
Since this chart isn’t showing us very much, let’s move some of these around. To move something, just click and drag from the location where it is, to the location where you want it. Power View will automatically update your graph. Our new graph looks a bit better:
With this, we can visually see what our gross profit margin is for each Product Category by year. What’s more is that it only took us about 5 minutes to create this report! Cool!