There has been a lot of buzz in the BI community since yesterday,
and the reason is none other than the public preview of Office 2013 (Excel 2013
in particular). There’s been a lot of articles written on the new features
available and the integration of PowerPivot and Power View has really got
everyone talking about.
Power View in Excel 2013 has the ability to create maps from your
data and uses Bing maps for the same. Unlike the maps in SSRS, here you do have
the ability to zoom and pan as needed. Also, Bing maps automatically detects the
location and hence you don’t need to provide a shapefile or even the
latitude/longitude information. In this blog, I will take you through the steps
to create a map report in Power View.
Creating a basic map report
1) Open up a new workbook Excel 2013 and then enter the following data in the
cells
Country
|
City
|
Sales
|
USA | Charlotte, North Carolina | 100 |
USA | Madison, Wisconsin | 50 |
USA | Jacksonville, Florida | 140 |
USA | Rochester, New York | 40 |
USA | Philadelphia, Pennsylvania | 120 |
You should get a loading screen while it takes a couple of seconds to open Power View
3) Now click on the Country field, and then you should be able to see the Map icon appear on the menu above. Click on the Map icon as shown in the image below
You should get a warning to enable content as the data needs to be sent to Bing to get geocoded. Click on enable content to proceed. Note that you would need an internet connection for implementing this.
4) Now you can pretty much rearrange your fields by dragging them into the areas below. I have dragged Sales into the Size, City into Locations as well as Color. So I get a map report below which shows the cities as bubbles with corresponding colors and size as the amount of sales
5) You can also play around with other properties like Title, Legend, Data Labels and Map Background. They are present when you click on the Layout tab.
It is interesting to note that the data used for creating the Power
View report gets imported into PowerPivot by default. This is because Power View
can only communicate through DAX currently, and hence needs a tabular model
behind it. The PowerPivot model can be viewed by clicking on the PowerPivot tab
and then selecting the manage tab
Since there is no way to add additional data into this model
without deleting and recreating the table, it would be a good practice to create
the PowerPivot model first from linked tables, and then using the PowerPivot
fields to create the Power View report. This way, we will be able to keep on
adding data as long as there is a link between the table and PowerPivot. I will
be showing you how to do this in the next part
Creating a drill down in map report
1) Select the same set of data in excel, go to the PowerPivot tab and select the Add to Data Model icon as shown below.
Note that you can still create Power View reports
directly, but we will be using this technique for the reasons mentioned above
earlier.
2) Select the home tab, then click on Pivot table option and
select Power View to create Power View report
It is recommended to set the reporting properties of the Country
and City field, so that Power View can recognize them as geographical entities,
as shown in the image below
3) Now you should be able to see Power View (in case you don’t, you
can click on a blank cell, go to the Insert tab and click on Power View icon).
Notice that the Country and City fields have a map icon
4) Now click on the Country field, and then you should be able to
see the Map icon appear on the menu above. Click on the Map and then drag both
the country and city fields within locations. You can also put the Sales measure
in the Size area. Now you should have a map report which drills down from the
Country level to the city
If you double click on the blue dot in the center (which is USA),
you will get the drill down report by cities. Notice that the title has
automatically changed from Sales by Country to Sales by City. You can also click
on the up arrow (highlighted in the image below) to return to the parent report
You can also create hierarchies in your PowerPivot
model, and that can be directly added dragged and dropped to the Locations area.
This will ensure that you don’t have to drag and drop each field individually
and the drill down would be present across the entire hierarchy.
You can also see the benefit of adding the linked
table to the PowerPivot model instead of using a range. Now if I have to add
data, I can just go to the excel sheet and append the rows that I want as shown
below
1) Use the previous report, and then add the city to the Color
area. Now you can see that there is a pie chart by cities at a country level
You can also use the horizontal / vertical multiples location areas to split it by the selected field as shown below
Similarly, you can use the Tile By option also
(however, this feature is not available if you use the multiples
option)
Another best practice is to concatenate your city
names with state/country info also (as I have done in all the examples) as there
might be duplicate city names and doing this will help Bing in geocoding it
better. You can also do this operation as a calculated column in PowerPivot, if
you don’t want the city names to be displayed with their country/region info
appended
Nice post. Thank you!
ReplyDeleteI found so many interesting stuff in your blog especially its discussion. From the tons of comments on your articles, I guess I am not the only one having all the enjoyment here! keep up the good work... https://maps.lol/petco-us-nd-11228
ReplyDeletepusulabet
ReplyDeletesex hattı
https://izmirkizlari.com
rulet siteleri
rexbet
SG46J