Analytics Dashboard for a Shipping Corporation
Microsoft Power BI, Azure Data Factory, Logic Apps, Azure Data Bricks, SQL Data Warehouse
The Challenge
The client was looking to build a near real-time Operations Dashboard for the Operations as soon as the data arrived. Their data was both in structured and semi-structured formats. The semi-structured data was delivered throughout the day in XML or CSV formats. The structured data was sourced from an on-prem SQL Server. The client wanted an Operations Analytics Dashboard built from the above data sets that could show how efficient the operational process is while loading and unloading train cars that load materials in the vessels, how efficient is the handover process from one shift to another, the number of safety incidents, and maintenance over the period, such as daily, weekly, and monthly.
The Solution
- On a file copy event on OneDrive folders Logic Apps reads the file and pushes it to ADF for further processing
- We poll the SQL Server database for any changed data using CDC every 5 minutes.
- The semi-structured data is then merged via Azure Data Bricks and the final data is stored in Azure SQL Data Warehouse, from where the Power BI Dashboard is refreshed.
- The data is then used to provide several metrics for improving operational efficiency.
Safety Dashboards, # of incidents per day
Dumping, Net dump time in hours
Ship loading, Tons Loaded in kilo tons.
Maintenance, coal system availability
The Extra Mile
- The requirement was to process data every 30 minutes. We were able to complete the pipeline every 15 minutes much earlier than the required SLA.
- Users can drill through to Details on a particular day.
- Users can view KPIs side by side over different periods such as daily, weekly, and monthly against a baseline at the same time.
- Users can interact with the charts by selecting different sections on the chart.
