Power View: Creating Maps

Power View, in addition to GeoFlow Power Maps, has the ability to take geospatial data and graph it to view data from a different perspective. You can’t create tours and some of the features that you can in Power Maps, but you can still demonstrate performance across a geographical location relatively simply. In this blog post, I’m going to take some geographical data around the Adventure Works database and graph it in Power View.

 

Initially, I’m going to create a connection to my Adventure Works tabular database and select Power View report as my import connection type. Under Geography, I’m going to select the StateProvinceName field, along with the Sales Amount field. My initial table looks like:

 

image

 

Up in the ribbon, on the Design tab, the fourth icon from the left for my report is the Map icon. When I click this, Power View is going to open up a connection with Bing maps, and attempt to graph my data geographically. Clicking on it, I get a very simple map showing my sales amount by State or Province around the world.

 

image

 

There are now a couple of different options that I can do. If I look at my field list, I can tile by something, create vertical or horizontal multiples, or drop in longitude and latitude, if I have it for example. I can also create a pie chart in each State/Province showing the breakdown of an additional category. If, for example, I take the Product Subcategory column and drop it in the Color section of the Field list, I get a map showing a Pie chart breakdown of each state/province’s sales:

image

 

Zooming in, I can see each countries breakdown by state or province. The only issue I see with this report is that everything is so spread out, it’s hard to see each country or region’s detail and keep the whole picture in focus. However, if I drag Region into the Vertical Multiples section, each Region becomes it’s own sub-map inside of the main Power View map:

 

image

 

There we go, much better. Now I have a nice visual Power View map that breaks it down but still keeps it easy to see the whole picture as well. One thing to note though, if I zoom in on France, there is some strange stuff going on with the province’s in the country. The same issue could be encountered with cities. The most accurate representation that we can use in mapping is Long/Lat, which if we had the fields available, should be what we use. If you don’t have Lat/Long (which, unfortunately, is more common than actually having it), we can pass concatenated field to Power View by setting them up in our model appropriately. The reason for this is that the same city, state, or province could happen multiple times throughout the world. How does bing maps know that we mean London, UK as opposed to London, Kentucky or London, Ohio? Back in the tabular model, I’ve created a concatenated field for “City State Country” and one for “State Country” by using the following DAX expressions:

City:

=[City]&","&[State Province Name]&","&[Country Region Name]

State:

=[State Province Name]&","&[Country Region Name]

 

Build and deploy, and refreshing my Power View model and replacing the old State field with my new State Country field fixes this inaccuracy issue and gives me a much clearer picture of, in this case, my sales in France, thereby leading to a much cleaner report:

image

 

In summary, while not as geographically amazing as Power Maps, creating maps in Power View is an excellent way to get some quick geographical reporting and visualization for a presentation, especially if the data model has already been built and is available in a tabular database or Power Pivot. However, when giving data to Power View to give to bing, ensure that you give Power View the most accurate geographical value of the data as possible.

Power View: Using the Play Axis

One of the most talked about features of Power View that I always hear client’s rave about is the play axis. For those that aren’t familiar with it, the play axis in a Power View report allows you to make a scatter chart about some key metrics (number of sales, dollar amount, etc.) and show it’s progression over time across 3 or 4 different axes. For this blog post, let’s walk through the different features of the chart, and how we can configure them. I will be using Excel 2013 Professional for this demo, but if you’re developing a Power View report in a SharePoint environment it would be identical.

 

To start, open up an Excel workbook with your Office 2013 Data Model. I’m going to be using the Adventure Works 2012 tabular database for this demo. My initial Power View worksheet looks like this:

image

 

Next I’m going to create a very quick table showing the amount of my internet sales, the number of units sold, along with the date that they sold and the sales territory region. My resulting table looks like this:

image

 

You can see from the picture, not very much exciting about it in its current format. Let’s change that. Up at the top of the page, under “Other charts”, select the scatter option to turn it into a scatter chart.

 

image

 

Once you have a scatter chart up, your initial picture should look something like this:

 

image

 

Down on the bottom right hand corner, you will see a list of all of the different fields that are available to you:

 

image

 

This is what we can use to configure the chart. Below is a breakdown of each type:

 

X Value: The horizontal value across the bottom of the report to generate the size of the x axis.

Y value: The vertical value across the top of the report to generate the size of the y axis.

Size: The size of each detail circle.

Details: Each category or attribute that you would want to see data reported on.

Color: You can use this field to identify what color you want to use. For example, you can use Product Sub Category as the details attribute and the Product Category as the color.

Play Axis: This is the date field that is used to configure and move the report accordingly.

 

For this report, my configuration area looks like the below:

 

image

 

Once my configuration looks like the above, my resulting Power View report will look like the below. Click the play axis, and WHAMMY! One awesome report that will get the whole company talking about some data!

 image

 

 

 

 

 

 

 

 

 

 

 

Power View: Using Slicers and Tiles

Previously I introduced what Power View is, how to enable it, and how easy it is to create awesome visualizations in just minutes. However, Power View offers so much more in terms of functionality for what you can do with your data, from just adding slicers to creating rich, colorful tiles that can be used to visually browse your data. In this blog post, I intend to show how to create slicer and tiles in your own Power View reports. For this demo, I’m going to use the AdventureWorks tabular database demo available on codeplex, connected to the Inventory perspective.

 

Slicers exist in Power View reports, exactly as they do in Excel. As an example, I’m going to use this chart as my base that I want to be able to report on, showing total units in inventory and the total inventory value by product class:

 

image

 

Now let’s say that I want to add Product Category as my slicer. I’m going to click on Product Category in the fields list on the right, and drag it into a new field list, as shown below:

 

image

Now we have the base we can use for our slicer. Unfortunately, it’s not automatically a slicer, we have to tell Power View that that’s what we want. If we just click on any Product Category right now, none of the other tables will update:

 

image

 

Up at the top in the Excel ribbon there is a button called “Slicer”. This will turn our list of Product Categories into the slicers that we want to see. Click anywhere in the Product Category table and then go up into the ribbon and find Slicer, then click it:

image

 

Once you click it, you’ll notice that each of the items in the Product Category list now have a little blue square next to them. This indicates that they’ve been turned into a slicer:

 

image

 

Click one and you’ll notice that now our table updates with each Product Category we select:

 

image

 

Nifty! However, there is something even more exciting in Power View, in a feature called tiles. Tiles allow you to create slicers, but to turn your slicer into something more visually compelling. To start, click on the Product Category name on the right hand side and delete it from your list, by selecting “Remove field”. Then click on the original report that we created to the left showing value by Product Class:

 

image 

 

On the right hand side under the field list section, you’ll notice an empty area above the fields we’re using called “Tile By”. Find Product Category, and drag it into that section:

 

image

 

Now we can browse this particular section of the report by each category, with a nice rolo-dex style tile list at the top of the page. Additionally, if we wanted to change how our tiles look, we can go up to the “Tile Type” section in the ribbon and change it appropriately:

 

image

 

Changing this report to “Tile Flow” from “Tile Strip” changes our tile list to look like this:

 

image

What’s nice about the tile flow option is that you get the nice same functionality that the Power Pivot Carousel gallery in SharePoint gives you, so it’s very visually appealing to browse. Sweet!

 

In conclusion, if you want the ability to slice the entire report by a certain attribute, slicers are probably you’re better way to go, as they affect everything on the page. But if you just want the ability to change the parameter in a given “sub-report”, use tiles.

Creating your first Power View report

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:

 

image

 

Once your on the Add-Ins tab, at the bottom of the tab under Manage, select COM Add-Ins, and click Go:

 

image

 

When the COM Add-Ins management screen appears, find “Power View” and check the box, then click OK:

image

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:

 

image

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:

 

image

 

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”

image 

Review and Click Finish:

 

image

 

Now once you click finish, on the next page we get a new option, added specifically for Power View:

 

image

 

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:

image

 

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:

image

 

Now let’s add Product Category and Year, and we wind up with something that looks like the below:

 

image

 

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:

 

image 

 

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:

 

image

 

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.

image

 

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:

 

image

 

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:

image

 

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!

PowerView as a SharePoint web part

Today I got a very interesting request from the client I’ve been working with. They loved the new features of PowerView, and wanted to embed the image of their powerview report on the home page of their BI Center site. My first reaction was, this is SharePoint 2010 and PowerView came out in 2012, so no way, ain’t gonna happen. But then I thought about a few more minutes and thought, why not? All PowerView really is to SharePoint is a Silverlight plug in, so it should be doable, right?

The answer lies in the “Other Settings” property in the Silverlight web part, which just requires a little bit of custom configuration to tell it to play nicely with the other kids. I’m going to go through creating it here:

On your site homepage (or wherever you want to create the web part, click on the page tab and then click edit. If there is no browse or page tab, click on the Site Actions dropdown and select “Show Ribbon” to make it appear.

Once you’ve clicked on edit, click the “Add a web part link”

Under “Media and Content”, select the Silverlight web part component, and click Add:

2012-12-04T18-17-22_0

After you do that, you’ll get a pop up asking you to enter the Silverlight web part URL. To find that URL, you’ll need to open up another browser tab, go to your PowerPivot gallery, and open the PowerView report. Once it opens, click on Edit Report, and once it loads, go up to (in IE, Page –> View Source). Look for a larger paragraph towards the bottom, and find the tag that starts with param name = “source”) Right after that it will say Value = “something”, where the something in quotes will end with a .Bootstrapper.xap? with a guid at the end of it. Put your Sharepoint browser connection string in front of it, and then copy it into your web part URL you still have open on your other tab.

2012-12-04T18-17-22_1

So your final connection string will look something like: http://MySharePointBIPortal/_layouts/ReportServer/My.Bootstrapper.XAP file.

After you’ve pasted it in you’ll get an error message that says “An Unexpected error has occurred.” That’s expected, so don’t worry about it for right now.

2012-12-04T18-17-22_2

Click on the dropdown and select “Edit Web Part” and a properties window will show up. At the bottom there is a + button with Other Settings on it, but all that’s there is a blank text box with an ellipses. Click the ellipses and then go back to your “view source” window that you have up from earlier.

2012-12-04T18-17-22_3

This time, look for the tag that says something like Init Parameters or Initialize Parameters and copy the entire piece out the value section. It will end after it says something like ReportSection=ReportSection. Paste that into the the text editor, making sure to replace the special characters with the correct colons and whatnot. I found it easier to just copy the URL for the Sharepoint site and kind of massage it a little.

After you’re done with that, click Apply and Voila! You have an incredibly awesome Power View report embedded as a SharePoint web part for your page. If it doesn’t work right away check the ItemPath and ReportServerURI settings, they’re usually the big culprits of it.