Showing posts with label Power BI. Show all posts
Showing posts with label Power BI. Show all posts
(2019-Mar-01) The first time I heard about the Data For A Cause initiatives was at one of the Power BI meetups in Toronto. And I actually didn't attend that meeting myself, but only saw pictures and learned a bit more about this virtual community of data professionals and Olga Tsubiks, who is the lead of this group. They help not-for-profit organizations to explore their datasets and create visualizations to assist them build awareness and better understanding. 

This time the Data for a Cause challenge was to work with Global Fishing Watch and visualize the fishing activity of vessels fishing in other country's waters. I was able to create a Power BI report using their data, however, this blog post is not about best practices of creating analytical reports in Power BI, but it's rather a data story of working with provided datasets and a thinking process of creating an interactive tool to visually explore fishing data.

Link to Global Fishing Power BI report.


Fishing dataset had been presented in a flat file format with the following list of attributes:
      ,fishing_date: a string in format “YYYY-MM-DD”
      ,fishing_lat_bin: grid cell
      ,fishing_lon_bin: grid cell
      ,fishing_mmsi: unique ID of the fishing vessel, AIS tracking device
      ,fishing_fishing_hours: hours the vessels were present in this location on this day
      ,vessel_mmsi: same as fishing_mmsi
      ,vessel_flag:  the country of the fishing vessel, in iso3 value
      ,vessel_registry_geartype: vessel gear types. The column Vessel Registry Geartype refers to the official purpose of the vessel. By analyzing the movement of the boat, GFW identified the vessel as a fishing vessel (even if registered otherwise) - Vessel Inferred Geartype.
      ,ISO_Ter1: the country where the fishing occurred (an exclusive economic zone of, in iso3). 

Data file contained 6,929,885 rows and it covered 5 years of fishing activities from 2012 to 2016.

Additionally, I pulled the list country codes with their names from this Wikipedia resource -
And I also found a Capture fisheries production dataset from the World Data Bank that contained data for the period of time from 1960 to 2016. This datasets was in an Excel file format and where one of the worksheets contained list of countries with some additional attributes, which I thought would be a good candidate for my country profile data table.

Eventually, after pulling data from the main data file, Wikipedia county codes list and fish capture data, my final data model in Power BI looked the following way, where I replicated the county code table both for Vessel & Fishing Area country info.

Metrics & Relationships between different data attributes

This was my thinking process to create a visual story with this data model:
1) Metrics:
    - Total Fishing Hours
    - Counts of Vessels (by Country, by Types/SubTypes)
    - Fish Caught (other data set)
2) Relationships:
    - Fish Caught by Country and its fishing activity on the map
    - Relationships between Vessel countries and Fishing area countries
3) Additional consideration to use maps since the Latitude/Longitude coordinates were provided.

Creating visualizations in Power BI

Summary report

For the summary page report, I used the following custom and standard visual components:
- Information cards to show counts of vessels, fishing hours and fish caught. 
Enlighten World Flag Slicer to filter vessel countries
Drill-down donut chart by ZoomCharts to show country fleet vessel distribution by vessel type
- Area and Bar charts to show fish capture data
- Narrative Science for Power BI component to create natural language narratives based on fish capture time series. I really liked this component in Power BI since it allows me to automatically and interactively generate a textual description of my numeric data, it also helps to show any potential trendlines in my dataset. And I had already blogged about this Narrative Science component for Power BI in the past:

Example of narratives generated for one selected country:

Heat map

For the second page of my report, I decided to use Heatmap visualization to show fishing hours density across different regions of the world. Latitude & longitude coordinates helped me to plot vessel fishing events on this map along with fishing hours to show different scales of heat.
Timeline slicer was used to select a date range for my heat map using the fishing_date attribute.

Flow map

Knowing that my main data set contained information about vessel and fishing area countries, it came naturally to assess how both of them relate to each, i.e. to analyze how each vessel country fish in another country's waters.

And Flow map visualization worked well for this type of analysis, where you can set Origin and Destination fields from your dataset. Also, you can set whether your connections would be in a form of straight or curved lines. 

In overall this Flow Map visualization helped to analyze fishing activities of richer countries in poorer countries' waters. And you could select a country vessels flow with the help of the filter at the top of this report page.

- Working with open data sets is always an interesting experience, especially when it contains geo coordinates that you can utilize with various map visualizations.
- I hope that this Power BI report does help to analyze fishing activities in the world. And if you are aware of other nonprofit organizations that I can help with this type of data analytics, please let me know, it would be my way of giving back to the community.

(2019-Jan-28) When you work with maps using ArcGIS visual in Power BI, you always have a feeling that it is a tool within another tool. On a surface level, you have options to set data attributes for geo coordinates, coloring and time controls. However, when you go into the Edit mode of ArcGIS, the possibility to adjust your map visualization is expanded to setting base maps, location types, map themes, symbol styles, and pins as well as infographics and reference layers

Reference layers are the additional shape/geo objects that you can to add in ArcGIS Power BI to enhance your data story with more contextual elements related to your existing maps.
Here is an extract from the official ERSI documentation:
"When you add a reference layer to the map, you're providing context for the data you're already displaying. Reference layers can include demographic data, such as household income, age, or education. They can also include publicly shared feature layers available on ArcGIS Online that provide more information about areas surrounding the locations on your map. For example, if your data layer shows the location of fast-food restaurants, you could add a reference layer that shows the proximity of high schools and universities. Reference layers allow you to dig deeper into your data to provide a greater picture of your business information".

How can add my own custom layer (shape) to ArcGIS and use them further down in my Power BI report? This was a point of my interest and a result of questions from other people! To make my further attempts to explore this very topic I owe to this blog post: written by David Eldersveld where he shares very detailed steps of creating custom reference layers to be further found in Power BI:
  Step 1 – Sign in to ArcGIS Online
  Step 2 – Choose the source file from your computer
  Step 3 – Share your feature layer
  Step 4 – Search your reference layer in Power BI

In my new case, I wanted to test out my own geo shapes that I had already created using QGIS application (blogged about this already: So, can I transform my Giza Pyramids shapes into the ArcGIS reference layers and find them in Power BI?

Before you sign yourself into ArgGIS Online, there a few things you will need to decide on what type of account you can use there.

ArcGIS Public Account:
- ArcGIS Public Account is a personal account with limited usage and capabilities and is meant for non-commercial use only.
- You can still create feature layers and maps and further share them publically
- You shared feature layers will be stored in the public Feature Collection.

ArcGIS Organizational Account:
- As a member of an organization, you will have access to the organization's geospatial content that you can use to create maps. You can also share your work with other members of your organization, participate in groups, and save your work.
- You can create feature hosted layers and maps and further share them publically
- You hosted shared feature layers will be stored in the Feature Services.

And here is a very important thing, the only way for Power BI to see your created feature layers is when they are created in your organizational ArcGIS workspace and shared as a hosted feature layer. Public ArcGIS account access won't provide you with this functionality. 

The trial of ArcGIS Online, which would give you an organizational account for 21 days: Your content would be lost after the 21-day period, however.

So, following David's Eldersveld initial set of steps:
  Step 1 – I've accessed ArcGIS and applied for their organizational account trial.

  Step 2 – I've created a new geo item in my ArcGIS workspace and selected a zip file with my shapefiles of Giza Pyramids that I had previously created:

I provided tile and tags for this new item, and I have also selected a checkbox to make this feature a hosted layer.

  Step 3 – By clicking [Share] button I made it available and searchable to ArcGIS map in Power BI:

  Step 4 – Search your reference layer in Power BI
I've added and publicly shared another pentagon-shaped layer that I manually created in QGIS before. Both feature layers looked this way in my ArcGIS workspace content:

And this a culminating moment for me: I was finally able to locate

And use my publicly available reference layers in Power BI:

It is always a rewarding feeling to experience when a quest to validate something unknown results in a successful outcome. However, it's too sad that sharing custom feature layers in ArcGIS Online using personal access account doesn't allow to publish your shared layers to ERSI hosted feature service repository. And yes, currently this is possible through ArcGIS organizational account access only.

Perhaps this will be improved in the future. In either way, after working both in QGIS and ArcGIS tools, this whole GIS technology is no longer rocket science to me. It's only a matter of time to get more experienced with it! :-)
(2019-Jan-14) Dynamic maps with dynamic visualization usually attract my attention, especially when I look at the satellite images of various cloud formations in the upper levels of the atmosphere. Heatmaps could be another example where animation could play a supportive role to point geo data changes as well as certain areas with high or low density of data activity. Basically, animated maps could hold your attention for a few moments in order to reveal an additional data story to you while you're looking at some static facts.

Possible options to make time animations using available maps in Power BI are:
- Use of time slicers, time sliders, other UI temporal controls that would help to interact with existing maps within your reports;
- Change filter values of your dataset time-related attributes (but very quickly :-), it won't be a legit animation though).
- Or use a time animation option of the ArcGIS Map for Power BI.

This particular feature of the ArcGIS Map is often overlooked. Or sometimes, Power BI developers are aware that this mapping functionality does exist there, however, it is still missed in data visualization storytelling. Let's try to explore and become more familiar with this feature of the ArcGIS.

For this example, I will be using an open data set of the Ottawa OC Transpo transit company:, with bus schedules data (bus routes, stop locations, trips for a particular day). In most cases, major cities present this type of data in the General Transit Feed Specification (GTFS) format.

OC Transpo GTFS data is a combination of 7 flat files:
- agency.txt
- calendar.txt
- calendar_dates.txt
- routes.txt
- stop_times.txt
- stops.txt
- trips.txt

Data Model
Those seven files get extracted and become a data model of my Power BI report:

Map Visualization
By using stop_lat and stop_lan geo-coordinates from the stops table, I plot OC Transpo bus stops on my map of Ottawa (capital city of Canada).

I can add the arrival_time column of the stop_times table to the Time fields of the ArcGIS Map visualization, yes, it exists there! Technically you can use Time or DateTime data type fields of your dataset to see it changes over time. The time slider appears as an animation control overlaid on the map. 

And here is the easy tricky part, when you click Play button, the animation shows features on the map in the current time interval. The time slider divides the time data into 10 intervals. When the animation plays, each interval is shown on the map for two seconds. You can also use Pause, Next and Previous buttons to control your time visualization.

Here is a brief glimpse of how it works through a simple animated gif visualization. Please try this with your Power BI report, it's fun! This animated map is now showing me a density of bus stops within a time period that I selected, or I can choose another scenario as well.

This note comes form the official ESRI web site:
When the time animation is playing, you cannot select features on the map; selection is available only when the animation is paused, and you can only select features visible on the map during that time interval. Click Play, forward, or back buttons, or move the slider handles to view and select features from a different time interval. Previously selected features are cleared when the new time interval appears.
So, now you have a way to see your Power BI maps live and moving! 

However, it still doesn't resemble a similar more smooth experience if you had built the same map visualization using Power Map (3D Map) in Excel. Back in 2016, I created this visualization for the SQL Saturday training event in Ottawa, using only Excel application and a similar open dataset; this tool has a way to create an MP4 video file, which later I uploaded to YouTube. Unfortunately, this visualization is not available in Power BI and I have doubts if it ever will be there.

So at the moment, you're given with options to rely on a manual changing of your existing Power BI report filter values, or let the ArcGIS map to automatically move through the time of your dataset, or possibly create a new data visualization in the Power Map in Excel (the latter has not been updated for a long while though). 

Final decisions are always yours!
(2019-Jan-06) In the past, I used to always rely on shapefiles crafted by others to create my map visualizations in Microsoft Power platform. City regions, country province territories, other shapefiles came from open data repositories and constructed by other people. After writing my last blog post on Using WGS 84 shape maps in Power BI and getting more experience with QGIS, I thought that I could create my own map shapefiles and test them in Power BI too.

Hypothetically, I could try to locate and create a polygon of a pentagon-shaped building, or simply follow a circular shape of the corporate office of the Apple company in California. Or maybe, just by going to the southern hemisphere in order to try and replicate a sea-shell-like-roof of the Syndey Opera House in Australia; or reproduce square formation of the Egyptian pyramids.

I've created shapefiles for a couple of those buildings, however, I will show the simplest one in this blog post and lead you into a journey to Pyramids of Giza in Egypt, which from the airview is a simple structure of three square shapes, very easy to start!

About a year ago, I had already blogged about using QGIS tool to create shape figures and test them visually in SQL Server - Geometry Objects in SQL Server using Latitude/Longitude coordinates. So, I won't get into much of details of leveraging features of the QGIS tool, you can try and use other GIS software applications as well.

1) Open Street Maps (OSM) layer in QGIS
Once I open the application and I can add the OSM layer to my project by going to Web > QuickMapServices > OSM > OSM Standard. Then I visually locate three Giza Pyramids on my map.

2) Shapefile layer 
Then I add a new shapefile layer to create three square polygons and name this layer as Pyramid_shapes_wgs84.

Important thing: please make sure to set your Coordinate Reference Systems (CRS) to EPSG 4326 It will provide Latitude & Longitude of your GIS data. This could be done either on your GIS project level or shapefile layer level. Otherwise, If you don't use WGS 84 (EPSG 4326) coordinate reference system, Power BI shape map projection would be visually skewed. 

3) Shapefile conversion to TopoJSON format
With the help of MapShaper, I convert my set of shapefiles
- Pyramid_shapes_wgs84.shp
- Pyramid_shapes_wgs84.qpj
- Pyramid_shapes_wgs84.prj
- Pyramid_shapes_wgs84.dbf
to the new TopoJSON Pyramid_shapes_wgs84.json file.

4) Testing custom shapefile in Power BI
And then I successfully test my own newly created map shapefile by using Shape Map visualization in Power BI.

a) Custom map shapefiles can be created by regular people like me :-) 
b) Power BI is a good tool to validate those shapefiles along with using additional data metrics based on your own data case scenario.

It is my new happy data adventure!
(2018-Dec-31) Back in July of 2018, Ontario provincial government of Canada decided to decrease the number of Toronto city wards from 47 to 25. That was affirmed by conducting the municipal election a few months later in October of 2018 to select councils for those newly shaped 25 wards.

This whole story triggered my interest to see if these new 25 wards could be portrayed with the shape maps in Power BI. 

The updated Toronto City wards geo-information can be found in the city Open Data Catalogue. And I plan to be working with a widely used WGS 84 format for geo-coordinates. This format is directly supported by 3D Maps in Excel, however, for Power BI consumption it has to be additionally converted.

Usually, the WGS 84 geo shape data is represented by a set of files:

where each of them contributes to the process of locating points, lines and polygons on a map. 

Shape filename extensions:
.cpg - used to specify the code page for identifying the character encoding to be used
.prj - projection format; the coordinate system and projection information, a plain text file describing the projection
.dbf - attribute format; columnar attributes for each shape
.shp.xml - geospatial metadata in XML format
.sbn and .sbx - a spatial index of the geo features
.shp - shape format; the feature geometry itself
.shx - shape index format; a positional index of the feature geometry to allow seeking forward and backward quickly
.txt - metadata of the .dbf file, list of columns

Just to be sure that WARD_WGS84.dbf file contains a list of new Toronto city wards and their geo descriptive attributes, we can open and check this file in Excel:

And to be fully immersed into the geo shape data we can always double check its layout using one of the available GIS tools. In my case, I've used QGIS, and it helped me to see all 25 polygons that are being drawn using geometry data from the WARD_WGS84.shp file. 

File conversion:
Currently, we can use custom shape maps in Power BI as long as they are in the TopoJSON format. And there are several tools that we can use to convert WGS 84 shapefiles into TopoJSON files, Map Shaper is one of them.

I would suggest not to convert individual shapefiles with this tool, but to provide a whole zip file instead that would help to include both geo-coordinates for polygons as well as textual and key descriptors from a .dbf file too. As a result, you will get a single TopoJSON file that can be further used in Power BI.

Power BI custom shape map:

First, let's build a data model in Power BI using data from the WARD_WGS84.dbf file.

1) Data extraction: the WARD_WGS84.dbf file can be easily converted into a CSV alternative version and connected to:

2) Data Table: after removing some of the attributes in Query editor, the final table is left with six columns:
- Ward Number
- Ward Name
- and X & Y geo coordinates 

3) Custom Shape Map: by clicking [ + Add Map] button in the Power BI Shape Map visualization UI you can add a custom geo map to your report. This will be the time to use newly converted TopoJSON file from the WGS 84 shapes.

4) Shape Map Keys: it's worthwhile to visually explore your custom map keys by clicking the "View map keys..." highlighted link in your shape settings:

5) Shape Map: after validating my custom shape map keys and placing [Ward Name] data column from my dataset into the Location field I can visually confirm the Power BI shows all 25 Toronto City wards (geo polygons) correctly:

The only thing that I believe is lacking in the current version of the Shape Map visualization in Power BI is the way legend information for each individual geo polygons is being shown. At this moment this type of information is only available by hovering over a shaped polygon of your interest and a popup tooltip will provide this data for you.

Perhaps, this can be improved in future releases. 
Let's keep exploring map visualization capabilities in Power BI!

(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 :-)
(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.


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)


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!



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.


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!