Maps in Power BI. Do I really need them?

(2018-Aug-30) While preparing for a recent Power BI Toronto meetup session, I found a very valuable whitepaper on Maps in Power BI written by David Eldersveld from the BlueGranite consulting company, and I had used some of the ideas and examples from that document in my presentation.

There is one idea that I couldn't agree more which could be traced and found in many of my Power BI report developments (I admit) and reports developed by others. We've integrated external data source systems, created key metrics in our data model, identified that some of the data elements could be categorized as geolocations in our datasets, and... then we want to start using maps visualizations right away, but do I really need them?

If my dataset contains a customer location info or list of countries where all the tweet messages came from based on a recent marketing campaign then I'm tempted to visualize them. Let's take an example of the following report with the Canadian population by Province. What would be the best way to analyze the population itself, a table, a map, a bar or a pie chart or combination of all of them?





I could easily sort the table by the Population column or look at another non-map visualization (Donut chart, for example, developed by ZoomCharts company) to realize that Quebec is the second largest province by its population.




or I could just select the least populated provinces and territories to find out that their ratio vs. other populated area is less than 1%.



So I wouldn't suggest using Map visualization in Power BI just because my data can allow this to happen. Use other more effective tools or visual components to present your data; combine them with maps, and most of all create a story with your visualizations that will help your audience to understand it better.

It only takes to learn maps visualizations to understand that sometimes something else could be used instead :-)

ArcGIS Maps for Power BI: Plus subscription (2nd part); What's in it for me?

(2018-July-29) My initial attempt to explore and test the ArcGIS Map paid version continued after receiving a response from the ESRI support team. Initially, I couldn't see all the additional features of the Plus subscription and I wrote about that in my last blog post - ArcGIS Maps for Power BI: Free vs. Paid version



But then I received a response from the Canadian Tech ESRI support team on my request to look into the issue of non-connectivity to Plus subscription content in Power BI. Basically, it communicated that all things were OK with the map itself and I needed to check further with Microsoft Power BI support team.



Well, I was a bit disappointed with this outcome; in addition to this, I received a few comments from other people about their unsuccessful attempts to resolve the very same issue with ESRI support team.

The next day I received two other messages: one with a confirmation that my ArcGIS Map Plus subscription had been activated and another email from a business analyst from the ESRI team in the US with a request to check if I still had any issues connecting to my account and additional help was offered if I needed. This restored my faith in people, and they willing to help others :-) Plus, after quickly checking my original Power BI report, I was able to connect to the Plus subscription of ArcGIS MAP, yes!





So, this is my story of checking all the new features available in the paid version of the ArcGIS Map in PowerBI.


Basemaps

First, the basesmaps add a contextual background to your map geo points. And with the Plus subscription you have access to 8 new basemaps, identified by a Plus badge on the screen:



New basemaps are:
- Imagery
- Imagery with Labels
- National Geographic
- Oceans
- Terrain with Labels
- Topographics
- USA Topo Labels
- USGS National Map (USA)

Source: http://doc.arcgis.com/en/maps-for-powerbi/design/change-the-basemap.htm

And my favorite basemep among those eight ones is the National Geographic, it hard to tell why. Probably, because it combines both technical geocoding details like any standards maps, but at the same time, it bears that graphical look from past historical sea voyages into the future endeavors to explore oceans, and I just like it :-)


Reference Layer

With Plus subscription you have access to Live Atlases, which can enrich you map look with other graphical layers on the top of your existent basemep. The search option allows you to examine available layers that you can use. Currently, most of the layers are US based data, but I found a North America Ecoregions layer that showed Canada in so many different colors. Just take a look!



Source: http://doc.arcgis.com/en/maps-for-powerbi/design/add-a-reference-layer.htm


Infographics



Infographics are just "floating" data cards that you can add to your map with some statistical data  (population, age distribution, income levels, etc.) to support either your whole map visualization or a part of it.



The beauty of those infographics data cards is that their content is updated depending on what you're doing with your ArcGIS. If you select one or more features on the map, the cards will show demographic information for an area around each selected location. See, how the Toronto infographics data gets changed when I zoom my view in to a core downtown area.




Source: http://doc.arcgis.com/en/maps-for-powerbi/design/add-infographics.htm

I won't be making any conclusions at the end. If you prefer using MapBox in Power BI, it's your choice; if you want to give a try to ArcGIS Plus subscription or other map visualization in Power BI, go for it!

For me, this version of the ArcGIS Map also reminded me that I needed to purchase a National Geographic Magazine subscription for my daughter :-)

Happy data adventures!

ArcGIS Maps for Power BI: Free vs. Paid version

(2018-July-25) I like the ESRI company slogan, "The Science of Where". Hopefully, it will help someone not to get lost in the myriads of geo points on their maps. 

Working with ArcGIS map visualization in Power BI, you always hit that notification point that tells you about more features available in the non-free version of the ArcGIS map, so I wanted to explore this.



I pulled an opened dataset of TTC Routes and Schedules with the route definitions, stop patterns, stop locations, and schedules of the Toronto TTC ground buses and streetcars into my Power BI data model.



With the following table count statistics:
Stops - 10,614 records
Trips - 134,949 records
Routes - 200 records
Stop Times - 5,584,011 records

And this data model helped me to plot all (almost all) stops' geo locations in my ArcGIS map:



My next step was to sign up for the Plus subscription (Paid version of the ArgGIS map) in Power BI. The registration went smoothly, my credit card information has been collected, no warning signs. However, when I tried to sign in again and authenticate myself with my registration email address, I received an error message that something went wrong with ArcGIS Maps for Power BI with apologies for the inconvenience and request to refresh the page or check back later. I contacted the Canadian ESRI tech support but still haven't received any updates for this issue.

So, I was only left with the option to trust the ESRI documentation and check limitations of the free version of the ArcGIS Map in Power BI. If you read the documentation, basically you will get an idea of "more", there a couple of additional new features, but the Plus version of the map has all the standard features with more capacity:

Included with Power BI	With a Plus subscription
Basemaps
4 basic basemaps	4 basic basemaps and 8 others, including satellite imagery
Geocoding
1,500 features per map	5,000 features per map
100,000 features per month	1 million features per month
Reference layers
10 reference layers that contain U.S. demographics	Access to Esri Living Atlas maps and layers (feature services)
Publicly shared feature layers on ArcGIS	Publicly shared feature layers on ArcGIS
Infographics
Curated gallery of U.S. demographics variables (7 categories)	Full access to the ArcGIS GeoEnrichment data browser, including U.S. and global demographics variables

Source: https://doc.arcgis.com/en/maps-for-powerbi/get-started/about-plus.htm

More basic maps, more geo coordinates to map, more reference layers. The good thing about the reference layers is that you get exposed to the same set of publicly shared feature layers in both free and paid versions of the map, which is a cool thing! There is a search field in the Edit mode of the map that allows you to select and view one of those public layers within your map.

So now, knowing what my limits are, I switched my map theme to clustering and realized that it also had another limit that is not listed on the official ESRI site, the clustering feature in my case was limited to 10,000 geo points vs. the actual dataset of 10,614 stop locations. You can check those numbers if you like.




I still hope that ESRI tech support team will get back to me with my inquiry, otherwise, I will need to find a way to cancel my Plus subscription.

Happy data adventures!


(2018-July-29) Update: the ESRI support team finally reached out to me and helped to resolve the issue, please read my 2nd part of this story: http://datanrg.blogspot.com/2018/07/arcgis-maps-for-power-bi-plus.html

SQL Server data flow into Excel template with Power Query function-based parameter

(2018-July-15) Your case is to create an Excel template with data extracted from a backend SQL Server database; it takes a few minutes and Power Query is a very handy tool to connect/shape and extract your data into a worksheet format. What if you need to create multiple files where your SQL bases data source is only one parameter different (like a report date or a product name).

In my case, I have a list of 7000 geo stations across the globe, with my default extract logic I was able to pull a list of Canadian stations in my Excel file. Just connect to a SQL Server using Power Query, and the job is done. But that is still part of the problem to solve.







What if you need to create individual files for each of the main parameter's values, in my case, it's a country name. Let's explore if we can add some flexibility to the existing Power Query with Excel-based parameters.

1) A new Power Query is created based on my existing Excel table by pressing the "From Table" option:


2) One conversion step is removed from this table in Power Query:


3) A specific cell is selected from sourcing Excel table, which converts the table into a scalar value in Power Query:

4) A Custom function is then created based on the sourcing scalar value:




5) Then an explicit filtering condition in the main data query is replaced with the newly created function:

6) And now I can choose any country from the list that I want and see their geo stations, even Greenland :-)


Happy data adventures!

Power BI with Mapbox and Volcanic Ring of Fire

(2018-May-27) With recent Hawaiian volcano activity, it would be interesting to see how other similar events have shaped our Earth planet. The NOAA's National Centers for Environmental Information (NCEI) provides public access to the Volcano Location Database - https://www.ngdc.noaa.gov/nndc/struts/form?t=102557&s=5&d=5. And currently, it contains information about 1571 known volcanic eruptions.







I was able to extract this dataset into my Power BI data model and decided to use the new custom map visualization created by Mapbox that became available with this monthly Power BI update. 

The working experience with Mapbox is similar to other custom and native map visualization, however, it has one new additional feature that would require you to register and get your personal Mapbox access token.

As soon as you place this visualization control into your report canvas and then link it with your geo dataset, the following initial screen will show up:




Then after the initial steps of registration, you are able to get this access token to place into the Power BI Viz format panel:





Going back to the actual volcanic map, I found the proof, that the Ring of Fire does exist and it's around the Pacific Ocean.



And here is the actual Power BI report with the volcanic interactive map using Mapbox (which currently is only supported in Chrome and Firefox) :



Happy data adventures!

Power BI with Maps: Choose Your Destination

(2018-May-20) A childhood dream to travel around the world fueled by reading Gulliver's Travels stories and Robinson Crusoe attempts to survive on a deserted island. Those books were filled with geographical description and map locations. I think I always loved working with maps and I still do! There is something that could captivate your mind and propel your imagination for traveling when you immerse yourself in maps.

Power BI itself a great tool that could feed your appetite for an adventure using built-in and custom map visualizations.

The built-in (or native) map visualizations are available right away when you install the Power BI desktop for the first time and Microsoft has provided sufficient information for you to get started working with them:
- Map
- Filled Map
- ArgGIS Map
- Shape Map

And there is another set of custom map visualization that we can use in building data analytics in Power BI (not a complete list):
- Flow map by Weiwei Cui
- Heatmap by Weiwei Cui
- Globe Map by Microsoft 
- Icon Map by Altius
- Mapbox Visual by Mapbox

Since there is more information about the built-in map visualizations then I'd like to share my experience working with the custom ones.



But before I do this, let me spend a few minutes describing how the Shape Map (still in Preview at the moment) works.

Shape Map
https://docs.microsoft.com/en-us/power-bi/desktop-shape-map

Once you enable this Preview feature in your desktop version of the Power BI then can you use a predefined set of Shapes:

or you can free your mind and upload a custom shapefile with Canadian provinces and Northen Territories, which I did from this site http://mapstarter.com/. 





Flow map by Weiwei Cui
https://appsource.microsoft.com/en-us/product/power-bi-visuals/WA104380901
https://weiweicui.github.io/PowerBI-Flowmap/

Flow map is a special type of network visualization that portrays movements of objects among geo-locations. 

I built a simple dataset with the list of Provinces and Territories capitals and linked them all to Ottawa (Canadian capital). The component itself has a very rich set of formatting settings which I liked the most; so the final visualization could look totally different by changing one of them.




Heatmap by Weiwei Cui

https://appsource.microsoft.com/en-us/product/power-bi-visuals/WA104381072
https://weiweicui.github.io/PowerBI-Heatmap

Heat maps are a type of visualization to show data density on a map. They are particularly helpful when you have a lot of (e.g., tens of thousands of) data points on the map and are mainly interested in their overall distribution. Technically, in a heatmap, data points are aggregated locally and mapped to colors (either gradient or quantile), so that we can make better sense of the density of the data from the colors while still being able to see and use the map. 

I already blogged about the use of the heatmaps in Power BI - http://datanrg.blogspot.com/2018/01/when-its-cold-outside-perhaps-power-bi.html and we can always use the built-in ArgGIS heat maps option as well to see alternative ways for your dataset density.



Globe Map by Microsoft 
https://appsource.microsoft.com/en-us/product/power-bi-visuals/WA104380799

One the best features of the GlobeMap is that it allows you to rotate the Globe and see it from different angles, and this is right within your Power BI report.

Globe Map is a 3D Map that makes the map exploration experience more immersive and magical. It provides the sense of connection to the data with the physical world. This, combined with our spatial ability, brings a new perspective to the data when presented as 3D objects. Globe Map also supports heat map on the spatial map. You can use a second measure for heat intensity and draw immediate attention to the right areas.

I used the Berkley Earth datasets - http://berkeleyearth.org/data/ for this 3D reporting which was already featured in my previous blog post - http://datanrg.blogspot.com/2016/02/excel-power-map-vs-power-bi-globe-map.html 



Icon Map by Altius
https://appsource.microsoft.com/en-us/product/power-bi-visuals/WA104381497

The Icon Map visual shows data-bound images & lines or circles on a map and allows you to render images and lines or circles on the map. 



Mapbox Visual by Mapbox
https://appsource.microsoft.com/en-us/product/power-bi-visuals/WA104381472
https://github.com/mapbox/mapboxgl-powerbi
https://www.mapbox.com/help/power-bi/

The Mapbox makes heatmaps, circles, and clusters using big location data.
Your location data is more than just points on a map. With the Mapbox Visual for Power BI, quickly create beautiful custom map visuals that answer your business question with fast performance.



And here is the actual Power BI report with some of the custom map visualizations:

Happy data adventures!

Geometry Objects in SQL Server using Latitude/Longitude coordinates

(2018-Mat-31) Support for spatial geometry functions was initially introduced in SQL Server 2008 and then it was greatly improved in Denali (SQL Server 2012) version of the product - https://docs.microsoft.com/en-us/sql/relational-databases/spatial/spatial-data-sql-server

Back then, when I was reading about the spatial features in SQL Server, I wondered where I could use this functionality with the creation of geo points, line polygons; however, working with geometry objects a bit more helped me to see some real use cases where this could be applicable.

Let's say you have the London Tower Bridge and you want to know when a taxi cab with your very important guest crosses this bridge. By creating a geo polygon for the bridge you can use spatial functions in SQL Server to check if a taxi cab GPS tracker coordinates intersect with the bridge polygon geometry object - STIntersects (geometry Data Type).





First, I've located my map objects in the QGIS tool using Google Satelite layer:




Then I created a polygon that would resemble the bridge area over the Thames River:




Then by extracting the nodes of my polygon, I can see each individual geo points coordinates:

And then after migrating this dataset into my dbo.LondonBridgeCoordinates SQL Server table, I can use this script to create the very same geometry object in my database:

-- POLYGON definition
DECLARE @coords nvarchar(max);

-- POLYGON CREATION FROM LON/LAT COORDINATES
WITH polygon
AS (SELECT
  id,
  latitude,
  longitude,
  CONCAT(LTRIM(STR(longitude, 22, 6)), ' ', LTRIM(STR(latitude, 22, 6))) AS coords
FROM [dbo].[LondonBridgeCoordinates]),
-- LON/LAT CONCATENATION
polygon_coordinates
AS (SELECT
  STUFF((SELECT
    ', ' + coords
  FROM polygon p
  ORDER BY p.id
  FOR xml PATH (''), TYPE)
  .value('(./text())[1]', 'VARCHAR(MAX)'), 1, 2, '') AS polygon_coords,
  (SELECT
    CONCAT(LTRIM(STR(longitude, 22, 6)), ' ', LTRIM(STR(latitude, 22, 6))) AS coords
  FROM [dbo].[LondonBridgeCoordinates]
  WHERE id = 1)
  AS first_coordinate)

SELECT  @coords = (polygon_coords) FROM polygon_coordinates
-- LET’S SEE HOW A POLYONG WOULD LOOK LIKE
SELECT  geometry ::STPolyFromText('POLYGON((' + @coords + '))', 4326).MakeValid()

I can also save the output of the STPolyFromText function as a database object and use it with all other available spatial functions in SQL Server. 

Please let me know if you can find other uses besides checking geo objects intersection. There are so many possibilities!