EZPowerBI

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

Click Here to View Report in action

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

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] & ”

</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.

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.

You may download complete files including sample Excel file from the following link.

Download Sample File

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.