Dubai – Importance of Open Data

Living in Dubai for almost a decade I realized that the forward looking and innovative approach has always created opportunities and made Dubai ahead of its competitors. The initiative of Smart City, Data Strategy, Artificial Intelligence and 3D printing are few important areas where Dubai is far ahead and having proper road map in place.

Since I moved towards data focused learning and personal grooming in terms of data visualization and reporting, I found Dubai a place where you have every opportunity to realize your dream of being known and appreciated in your field if your are doing exceptional work.

First I evaluated data available on Dubai Statistics Department’s site which was in much organized manner and was ready to be used for impressive visualizations. I created report based on RTA’s data couple of years back which provide great insight to the operations of RTA in terms of Metro, Taxis and Water Transports.

Water fleet

When I read Dubai’s Data Strategy and Its road map to enable Dubai Government’s entities share and collaborate data, I started thinking of using this data in best possible ways. Smart Dubai is working on different initiatives among those Dubai Pulse stand out as it provides repository of open datasets which can be of great use if utilized properly. Since open data is available on As-Is basis you cannot expect it to be in perfect form, however information hidden in these files need to be cleverly extracted.
The important part is designing meaningful report with simple story to tell which can grab attention and explain its purpose without the help or assistance. For this I selected number of entities to work on their data. Following are couple of examples for the same.
Following are few examples of reports created using open data of Dubai Pulse. Reports are fairly simple in terms of understanding information hidden inside open data.
Dubai Customs
DC Report based on Dubai Pulse

Click here to view report in action

 

Dubai Health Authority

Licensed Professionals Registered with DHA

Click here to see report in action

 

Department of Tourism and Commerce Marketing – Dubai

Epermit Report

Click here to see report in action


Dubai Electricity and Water Authority – DEWA

Dewa Report Snapshot

Click here to see report in action

I am presently working on other reports which may be updated soon.

Keep following this post for more examples.

Impact of External Dimensions on Analysis Reports

kyle-popineau-29640-unsplash

Photo by Kyle Popineau on Unsplash

In most of the cases we are very much focused on the data we own. In case of large enterprises, we get access to different entities and sections but still the same is considered internal data.  This gives you mostly a tunnel view by restricting your analysis and ability to find answers for bigger problems.

During my career I have noticed that getting access to external data can hugely impact your findings and enhance your analysis to the next level. Following are few examples of external data.

 

  • Supply Chain Data

Most of the organizations are depending on critical supply chain for their production units. For example, a food manufacturing company is dependent on ingredients supplied by different suppliers to produce different products. If you are depending on internal data only you might spot dip in production of certain products which can only inform you about low production on certain dates. Even if you add procurement data, you can only spot low purchases resulting in slim production. However, if you get access to your partner data or productions or crops then you can find exact answer regarding reason of low production. This is also true for cases when you have access to government statistics regarding commodities.  United Nations and World Bank also gather valuable data which can be used to add a different dimension to your analysis. Following links are worth consideration. Adding such data sources to your model can help you in broader analysis.

http://www.fao.org/faostat/en/#data

https://data.worldbank.org/indicator

 

  • Weather and Environment Data

Using weather and environment related data can be helpful in your reports to find certain answers which are usually not possible with internal data only. For example, if you are creating a report of your farming and related outputs then having weather data can be of great value. This is also good when you are forecasting future outputs. If you have a damaged crop this year then heavy rains or harsh weather conditions can be one of the reasons. Incorporating this data with your internal report can help you identify which crop can sustain tough weather conditions and help you in making proper contingency plan to protect your crops.

 

Interestingly, weather related data can also help you in finding reasons of rising accidents in certain dates. This may be due to low visibility, heavy rain or high temperature in different days and months or even timeslots. For example an unexpected heavy shower for an hour can cause long traffic jams and spike in accidents. However, without having such data you may able to know when you experienced more accidents but you will not be able to find a reason for that.

Furthermore, if a hospital is experiencing more number of asthma patients in certain months, having weather and environment data can give you exact reason for this traffic. This will help that hospital plan in better way for future to handle increased number of patients in similar weather condition.

 

World Bank provide Historic and Projection Data which can be of great help when doing weather related analysis.

https://climateknowledgeportal.worldbank.org/download-data

 

 

Using Tickers in Power BI

Tickers are useful when you want to show information in floating form. Almost everyone is used to ticker like visuals in reporting as in situations like stock exchange information, game scores, weather report, news headline or otherwise tickers are fairly useful.

Click here to see report in action. 

In Power BI we have limited possibilities of animated content like tickers. In order to produce ticker like visual, I have tried two different approaches.

  • Using Card Visual and Play Axis Custom Visual
  • Using SVG through HTML Viewer Custom Visual

 

Using Card Visual and Play Axis Custom Visual

Ticker Power BI

In this method I have created different custom columns and measures.

“What if parameter” was used to create a table to help in animation of the text.

Length = GENERATESERIES(0, 150, 1)

“Ticker” measure was used to create content for ticker which will run from left to right. DAX REPT was used to create empty spaces on the right side as card visual always keep text in center. While next measure “LeftToRightTicker” will add spaces to the left of the ticker content based on changing values of “Length Value” in response to Play Axis visual. You can set play axis animation settings based on your needs.

Ticker = CONCATENATEX(VALUES(Outlets[CityOutlets]), Outlets[CityOutlets], ” | “) & REPT(” “,60)

LeftToRightTicker = REPT(” “, Length[Length Value]) & [Ticker]

Play Axis visual was used to change values in Length table created through “What if parameter”.

Using SVG through HTML Viewer Custom Visual

tickers in power bi

In SVG example, HTML is generated through custom columns for both simple and TV like visual. A custom column “Ticker” is used to create concatenated text for use in ticker which is ingested in the HTML to produce ticker.

SVGContentSimple = “<svg width=””100%”” height=””100″” style=””background: white””>

<text x=””10″” font-size=””10pt”” y=””50″” fill=””black”” >” & SummaryTable[Ticker] & ” 

<animate attributeName=””x”” from=””-120%”” to=””120%”” dur=””25s”” repeatCount=””indefinite”” fill=””freeze”” >

</text>

</svg>”

Source file can be reviewed for more details. Click here to download.

Reference for SVG images used in this example http://thoughtbot.github.io/foundry/

 

 

 

 

Animated Tooltips in Power BI

Tooltips are always good when you wish to keep your report viewers focused on a report by providing extra information on the same page through mouse over. Although default tooltip and custom tooltips are good for showing static information, you sometimes need to show animated content like trends and behaviors based on historic data.

Recently I did an experiment with tooltips by incorporating animated content. The experiment was fairly successful as lot of viewers on LinkedIn viewed my post and requested details about how this was implemented. For those who wish to experiment tooltip I have decided to pen down steps to help enthusiasts and professionals to try animated tooltips in their reports.

Mall Traffic Image

Click here to see report in action!

For this example I have created a sample report with fictitious data to show mall traffic. Nowadays, lot of tools are available to capture mall traffic with intelligent cameras and applications to recognize type of visitors. This example assumes that data file is having records of mall visitors bifurcated based on gender and age.

I have used calculated columns and measures to create dynamic titles of visuals to behave according to the selected content. A source pbix file is also included to enhance this example further.

Visitors were classified through following table. Unicode characters were used to show respective icons.

Table: Classification

Classification Unichar
Infants 128118
Boys 128102
Girls 128103
Men 128104
Women 128105
Cars 128662

 

Mall Traffic is shown in following table assuming that data is classified based on visitor type. Additionally a column is added to show traffic in terms of cars parked in the mall parking area. This kind of example may help mall management in identifying peak parking months.

Table: Mall Traffic (Note: this is not a full list)

Year Month Boys Girls Men Women Infants Cars
2016 1 23000 19000 34000 39000 5000 800
2016 2 18900 18000 40000 42000 7000 1100
2016 3 14800 17000 46000 45000 9000 1200
2016 4 10700 16000 52000 48000 11000 1450
2016 5 6600 15000 58000 51000 13000 900
2016 6 11000 14000 64000 54000 15000 1000
2016 7 21000 17000 59000 49000 14000 1100
2016 8 31000 20000 54000 44000 13000 670
2016 9 41000 23000 49000 39000 12000 800
2016 10 23000 19000 34000 39000 5000 930
2016 11 18900 18000 40000 42000 7000 1060
2016 12 19000 22000 46000 45000 9000 1190
2017 1 10700 16000 52000 48000 11000 1185
2017 2 21000 15000 58000 51000 13000 1180
2017 3 11000 14000 64000 54000 15000 1175
2017 4 21000 17000 59000 49000 14000 1170
2017 5 31000 20000 54000 44000 13000 1165
2017 6 41000 23000 49000 39000 12000 1160
2017 7 11000 15000 58000 51000 13000 1200
2017 8 11000 14000 64000 54000 15000 1215
2017 9 21000 17000 59000 49000 14000 1230
2017 10 31000 20000 54000 44000 13000 1245
2017 11 41000 23000 49000 39000 12000 1260
2017 12 23000 19000 34000 39000 5000 1000

 Model Diagram

Model Diagram below is showing relationship between two tables used. I have used additional columns and measures to perform analysis. Tables were transformed through edit query to make it analysis friendly. You may find query editing steps in sample pbix file.

Mall Traffic

 Play Axis Visual

I have used “Play Axis” custom visual to create animation on tooltip pages (You can import the same from the marketplace).  It was deliberately kept behind the clustered bar chart to work in background. “Year” column was selected in the field well to run tooltip based on years. In other tooltips, I have also used visitors’ classifications for animation.

PlayAxis Tooltip

Following settings were done in the format section of “Play Axis”.

PlayAxis Tooltip2

Calculated Columns and Measures

Following calculated columns were used for different reasons.

Calculated columns for creating icons

UnicharIcon = UNICHAR(Classification[Unichar]) 

Measures used for dynamic titles of different visuals and tooltips. These measures show values based on selections made through “Play Axis”.
AllYears = CONCATENATEX(VALUES(MallTraffic[Year]),MallTraffic[Year],” | “, MallTraffic[Year])
AllClassifications = CONCATENATEX(VALUES(MallTraffic[Icon]),MallTraffic[Icon], “| “)
AllClass = CONCATENATEX(VALUES(MallTraffic[VisitorClassification]),MallTraffic[VisitorClassification],” | “)
How to see animated tooltips.
Following visuals (marked with red arrows below) were configured for tooltips. You can check with mouseover to see tooltips in action. Keep holding your mouse to see animation effects. For cards, you have to keep your mouse over the figures and for line chart, keep mouse over the required node.
tooltips
This document is not covering method for creating tooltips as this is assumed that you are aware of the normal procedure of creating tooltip pages.
Following picture indicate how you can associate tooltip with your visual. However, you have to be sure that the field(s) used in your visuals are used in your tooltips otherwise it will not work.
tooltipsconfig
You may download complete files including sample Excel file from the following link.
Keep following my blog for more exciting examples.

Using Unicode Characters in Axis and Slicers

I recently did an experiment with Power BI report by using “Unicode characters” which was shared on LinkedIn. In this example I used selected characters in slicers and axis instead of text. This has created lot of interest among followers and received numerous requests to share details.

In reality it is a fairly simple approach where different Unicode characters were selected for different items and reactions used in this example.

Snapshot

Following three tables were created in Excel which were used in this report. For each dimension table I have added Unicode to represent icon.

Table Name: Serving

Item code Item Unichar
100 Coffee 127854
101 Ice Cream 127846
102 Soup 127861
103 Pizza 127829
104 Donuts 127849
105 Burger 127828

 

Table Name: Reaction

Rcode Reaction Unichar
1 Smiley 128578
2 Sad 128542
3 Neutral 128528
4 No Feedback 128566

 

Table Name: Responses (Only few rows shown here. Check Excel file for full list)

ItemCode Rcode
100 1
100 1
101 1
101 2
101 2
101 3
101 3
101 3
101 4
101 4
101 4
101 4
101 1
101 1

After importing excel sheet in Power BI I have added couple of calculated columns and measures. To create icon I have used following DAX.

For Item Icon to be used for Axis

ItemIcon = UNICHAR(ItemDefinition[Unichar])

 

For Item Icon and name to be used for slicer

IconAndItem = ItemDefinition[ItemIcon] & UNICHAR(10) &  ItemDefinition[Item]

 

Similar is used for calculated column for Reaction icon.

ReactionIcon = UNICHAR(ReactionDefinition[Unichar])

 

For Reaction Icon and name to be used for slicer

IconAndReaction =
ReactionDefinition[ReactionIcon] & UNICHAR(10) & ReactionDefinition[Reaction]

 

Following is the snapshot of relationship.

Model Snapshot

You may download sample excel file and PBIX file to evaluate more. This example is just to show how you can implement Unicode character. There can be numerous implementations and scenarios where icons can add value to your visuals and keep your report simple to grab the information easily.

To download only Excel file click the following link ReactionData

Complete files including PBIX can be download through  the following link

Unichar Example from EZPowerBI.com

Click here to see report in action View Power BI Report

For finding appropriate Unicode characters check the following links.

http://xahlee.info/comp/unicode_index.html 

https://www.vertex42.com/ExcelTips/unicode-symbols.html

Will be sharing more examples in future.

Subscribe to my blog for more surprises!

Cheers!