THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

Please visit the same blog at https://www.sqlbi.com/blog/marco/ - comments are now disabled here but you can comment on the new blog (all blog posts available here are available there, too).
You can follow me on Twitter: @marcorus

Display data on customized maps in #powerbi (also offline) leveraging SVG format

Last week Amir Netz presented in the Power BI blog a detailed introduction to the Synoptic Panel written by Daniele Perilli. This component won Power BI best visual contest in October, but since then we received many feedback and Daniele worked on it improving the editor and the available features. The big news is that now the Synoptic Panel supports the SVG format. You can still start from a bitmap and draw areas you want to bind to data in Power BI, but now the result of this job is an SVG file created by the Synoptic Designer. Graphical features of the designer are limited, so if you want a real arc, circle or complex shapes, you can create an SVG file with an online editor (such as http://editor.method.ac/ – but you can find many others) and then import it in Synoptic Designer. Since the SVG includes areas, the designer simply import these entities and allows you to edit their names.

Now, beside the technical details, why the impact of this feature is huge? Well, practically any vector format can be converted to SVG, so if you have a technical draw of a building, any planimetry built using a professional drawing software… chances are you can import them in Power BI now! But what if you don’t think this impact you?

imageWell, you might need to display data on a map: by country, territory, state, city, district, whatever… Yes, we have Map and Filled Map components in Power BI, but they are good if you want to display the exact geographical details and if you are online. If you want to create custom areas, you want to remove certain details, or you want to use it also offline (or without Internet) using Power BI Desktop, you were unable to do that.

For example, if you want to display USA data by state, you might want to move Alaska and Hawaii in a different position in your map, such as in this example. And the good news is that you don’t have to create this file from scratch: we imported this example from Wikimedia, which already has a good SVG format that you can import in Synoptic Designer: https://commons.wikimedia.org/wiki/File:Blank_US_Map.svg

The time you have to spend to create a new map depends on the quality of the initial file. The USA map is a very good example, because there is already one entity for each state, and each state has the 2-letter state code associated to it.

  1. Go in http://synoptic.design/
  2. Paste the URL of the SVG file (https://upload.wikimedia.org/wikipedia/commons/3/32/Blank_US_Map.svg) and click GET (or browse from your local drive if you have it there)
  3. Now you will see in the editor all the states and selecting each one you will highlight the corresponding area on the right pane. You can type the complete name of each state here, or change the code to identify the state. You can store two names, one to bind data in Power BI and the other to display the name of the area in the visual (if blank, the same name used to bind data will be used by default).
  4. You can also clear some area if you don’t need it. If you want to change the drawing, you should edit the SVG file first in an external editor (such as http://editor.method.ac/). Please note that certain states in this specific map have an additional dash character you might want to remove. 
    usa-1
  5. Click EXPORT TO POWER BI button (bottom right), and then press the DOWNLOAD SVG button
    22usa-2image

If you find a good map that you think could be useful to other people, please click SUBMIT TO GALLERY, too! We already have a number of maps available, and you can quickly use them by navigating in Gallery / Country/Territory:

image

You can also browse the gallery directly from the Synoptic Panel component. Once you chose the map, you bind to Legend a column containing state names, to Values the measure to display (it will appear in the popup window by moving the mouse over a state), and to Saturation Values the measure that defines the saturation of the color (you can configure limits in Format properties). In the following example you see the Quantity measure used for both Values and Saturation Values, while the mouse is on Florida.

image

I really look forward to see new territories published in the gallery!

Published Tuesday, December 15, 2015 2:35 PM by Marco Russo (SQLBI)
Filed under: ,

Comments

 

James Phillips said:

Great work guys --- love it.

December 17, 2015 2:43 AM
 

Brian Mather said:

Have done just this with UK and Ireland maps I grabbed online, incredibly simple yet powerful. Will look to see if I can find a version on Wikipedia, I'm not really clear on possible copyright issues.

December 19, 2015 8:18 AM
 

Brian Mather said:

Have done just this with UK and Ireland maps I grabbed online, incredibly simple yet powerful. Will look to see if I can find a version on Wikipedia, I'm not really clear on possible copyright issues.

December 19, 2015 8:18 AM
 

GregDF said:

Hello. This is really great, I can see a lot of applications to my reports. But when I tried it, I encountered a problem. I have a map (of France) that I cut into several regions, but I need to have 2 territories that are separated on the map to appear as one. (In my case it's corsica that needs to be the same territory as french riviera). I tried to put the same name on both, but it failed, only taking one into account.

Have you got a solution for this?

Thanks anyway, great tool!

December 22, 2015 6:55 AM
 

Marco Russo (SQLBI) said:

Thanks James!

Brian: you should read copyright disclaimer in the source.

Greg: you should be able to create an SVG with an object that has 2 shapes - you just need an SVG editor, and then import the SVG file in Synoptic Designer. If you start from a bitmap, the technique of creating two objects with the same name should have worked - if there is a bug, please contact us by mail to analyze it.

December 22, 2015 8:10 AM
 

Wenchi Wang said:

This is a great tool. Just download and try the us map and it works fine except the state of Alaska. My data has AK as state code which does the same as the map. However, when I do the chart in power bi, the AK state always shows as unmatched area. All the other states work as normal. Not sure if this is bug or something wrong with the data. I checked and re-type the "AK" on both the data file and the SVG file to make sure it is matched but still no luck on power bi chart. Any ideas?

June 8, 2016 2:29 PM
 

David said:

Wenchi I have exact the same problem, Did you resolve it?

June 10, 2016 11:30 AM
 

Marco Russo (SQLBI) said:

Wenchi Wang, David: one issue in Synoptic Panel (happening only online, not on Desktop) has been fixed by the latest release of Power BI service released on June 16, 2016. For the specific problem of AK, this is an issue related to certain type of entities in SVG file (group of shapes instead of single shape), which we should fix in an upcoming release of Synoptic Panel. I suggest you to register to the newsletter on www.okviz.com, so you will receive a notification when we'll publish the new version.

Thanks for the feedback!

June 17, 2016 7:05 PM
New Comments to this post are disabled

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog

Syndication

Archives

Privacy Statement