Mapping in Power BI and Power BI Desktop is one of the features that gets you the “WOW!” response. Sometimes it really is nothing more than providing some great visual context to well-known data, but I find that when folks get to see a map and then work with the simplest features of filtering or selecting data from clicking an element of interest, that’s when you really see the interest in the product take off.
Unfortunately, for some customers a connection from their data to the Internet isn’t possible. The Bing maps powering the default map visualization work well, but only if you can access the Bing maps servers.
So what do you do to display geographic data when you don’t have an Internet connection?
This was a recent challenge my customer gave me so I started to make sure I understood the options and the challenges, and now I’m sharing that with you.
If you’re the kind of person who skips to the end of a novel, scroll to the bottom and take a look at the report I’ve linked there. And when you’re ready, come back and learn about what I wanted to show.
I wanted to start with the simplest of data here since I wanted to focus on the presentation pieces, and so I started by making a quick list of European cities I’ve visited. This is pretty similar to the template the Sam Lester discussed in his 12 Days of Dashboards series where he was using a spreadsheet to capture the countries he visited.
Default Map Visualization:
This should be the easy one, pick the Map icon and feed it some geographic data. This is the first of the 5 items shown below. And it requires an Internet connection with Power BI desktop to display.
You get a couple of interesting chances to work as a good data scientist here, since you will learn you need to clean up your data a little. I wanted to focus on the maps, but needed some data, so I started an Excel sheet with city names. You might think that “Berlin” in a list of cities was obviously referring to the capital city of Germany, but you may learn that there’s a city in New Jersey also named Berlin. Similarly for Granada not showing up in Spain, or Brest not showing up in France, but instead on the eastern edge of Poland as a reference to the Brest-Litovsky treaty .
There are a couple of ways you could deal with this, but the easy one may be to check out the map, and then if you see an obvious error, change the data to something less ambiguous like “Berlin, Germany” or “Granada, Spain”.
Globe Map (Custom Visual)
Similar to the default Map, you could install the custom visual Globe Map from https://app.powerbi.com/visuals/ . It will also require an Internet connection.
I learned here that I needed to spend some more time on my data entry since the view of just the cities on the map wasn’t very inspiring or obvious. So I made some quick estimates of the number of days or visits I had to the cities and used that as the “heat” for the heat map.
Interesting view of the same data, from which I conclude I should make an effort to stay in Southern Europe more often, but still not useful for my customer.
Another custom visual, Shape Maps are an option for my customer’s scenario where we want to use Power BI Desktop and not connect to the Internet.
I learned some more here about data cleanup and entry, specifically that I need to learn how each of the available shape maps encodes the region since that’s the part the “lights up” when you use the Shape Maps.
You’ll see I pulled in the Shape Maps of France, Germany, Italy, Ireland and the Netherlands. There are a couple more European countries available, but not all.
Some nice functionality here, including being able to select the region and have the chart reflect the data for the region. Or to use a slicer for the country name and see the regions change colors based on the data.
On the data entry side, I did have to learn from the shape maps what the region names were. And where there were some interesting differences to what I expected:
If you have a great monitor and eyesight to match, you’ve probably already seen an error which took me a long while to find — while “Île-de-France” is listed in the name column, the id column has “Ile-del-France” which isn’t the name of the region. Or correct French. But once you know that, you can add the column to your data to correctly represent Region names, in this case the region that includes those 8 departements (Paris, Essonne, Hauts-de-Seine, Seine-Saint-Denis, Seine-et-Marne, Val-de-Marne, Val-d’Oise, and Yvelines) or 75, 91, 92, 93, 77, 94, 95, and 78 to use the numbers.
This made me realize that with the shape map, you’re bound to whatever divisions appear in the published map. It’s been awhile since the 18th Century reorganization of the former Provinces of France into the départements (and the mysterious ways non-consecutive numbers align so 75 is next to 92 but 76 is further away in Normandy), but if you want a Shape Map which uses those smaller divisions than the current French Regions, you’ll need to make your own map. Which the Synoptic Panel (below) may allow you to do.
Similarly for the other European countries, you’ll need to do some data entry/cleanup to make sure that you’re capturing DE-SL for Saarland in Germany and NL-LI for the Dutch province of Limburg.
On the Shape Maps below, I also used the new Matrix view of the data for fun, so you can see how many days I guessed I spent in the provinces of Netherlands.
Synoptic Panel for Power BI and the Synoptic Designer
If you’ve read this far, you probably, like me, really needed to find a solution where you could define your own map areas for a Power BI Desktop report where you didn’t have an Internet connection. Luckily there is another option here, which may be adaptable for your needs.
Full marks to the folks at http://okviz.com/synoptic-panel/ for providing this since you have a lot more flexibility here in creating or adapting a map or drawing and using Power BI to “light up” the regions you want to.
You will need another round of data entry first, though, since the default setup for the Synoptic panel is to have a named region — like a country — and a measurement, and a target. So if you’re trying to plot sales of an item or the density of population, you may have ready-made data for this. For my demo data, I decided to use my existing estimate of the days or trips to a location and then throw in a target of 365 days for all of the cities. So my display is some sort of visual percentage of how much of a year I’ve spent in the location.
Since we saw in the Shape Maps the need to identify the region correctly, it’s not surprising we have to do that here, too. And, like the Shape Maps, you’re bound to the choices that have been made for the .svg file.
The easy solution here I wanted to show was to use the publicly available map of Europe. You can extend this by making your own drawing, selecting the regions, naming them and then using that as your base map, and I will cover that in a future post, but for this one I just chose one which is already available.
Once I learned which country was which number — a fun game to try and you hover over countries in the former Yugoslavia you might not be as familiar with — I just had to add a column to my simple worksheet for the “Area Number”.
You’ll notice that not every European country is visible on the map — on this resolution Lichtenstein and Andorra aren’t easily visible, and the island of Malta got left off — but this does a pretty good job of what my customer first asked for: Data mapped to countries on a map with the ability to filter either by clicking the country on the map or the filter on the dashboard.
Clearly, this isn’t the most elegant solution yet, and the “Unmatched Areas” is something which gets a little confusing. In this map part of Russia is visible in the map, but isn’t available as an Area. There’s an option in the configuration for the custom web part to hide the unmatched areas, but I didn’t select it here since I wanted it to be obvious that this was a map of Europe not just selected countries.
My next request from the customer is going to be some more detailed maps with specific data. I’ll work through the Synoptic Panel parts of that, see what other options I can come up and then report back what I find.
Until then, happy mapping!