The need for real-time insights and scalable solutions
Our client, a leader in the energy and utilities sector, offers secure and innovative IoT solutions that fill
crucial gaps in the industry. They provide devices, networks, software, and services that have been proven
effective in challenging environments across the globe.
Our client needed help extracting real-time insights from large volumes of data generated across diverse
applications. To maintain a competitive edge, our client sought a scalable solution with enhanced data latency
and accelerated data processing, enabling efficient management of the substantial data volume.
The ask
• Stage IoT data and consume it in Power BI
reports.
• Reduce the time taken to refresh Power BI
datasets.
• Handle dynamic schema
changes in incoming data.
Addressing the challenges
After exploring various techniques using MongoDB, Cosmos DB, Synapse, and SQL Server, our client encountered obstacles while attempting to address the data analytics needs. Recognizing the limitations of these approaches, they turned to our expertise for a robust and effective solution.
Following an extensive analysis, we identified the following solutions to effectively overcome the primary
challenges:
 |
| Figure 1: Key Technologies |
• Ingest and
transform semi-structured JSON data obtained from network logs
We developed a script to ingest and stage data in Azure Data Explorer (ADX) while ensuring integrity and reliability. The script appends new data, handles dynamic schema changes, and logs all the data staging steps. This approach could easily accommodate data with a new schema into the existing table without data loss. Setting up partitioning and merge policies on the table helped improve ingestion time. It also empowered us to effectively manage a year's worth of data in Power BI, facilitating comprehensive analysis.
• Ability to
handle large volumes of data in Azure Data Explorer (ADX)
We leveraged Power BI Direct Query mode to utilize the computing power of the ADX cluster. It handled large volumes of data which were difficult to achieve when using MongoDB, Cosmos DB, Synapse, or SQL Server.
Our winning solution
We successfully harnessed the power of Power BI and Azure Data Explorer (ADX), to analyze and visualize semi-structured upstream data. Our solution not only facilitated efficient data ingestion and transformation but also effectively tackled issues pertaining to data refresh and dynamic schema of data.
Our client needed help extracting real-time insights from large volumes of data generated across diverse
applications. To maintain a competitive edge, our client sought a scalable solution with enhanced data
latency and accelerated data processing, enabling efficient management of the substantial data volume.
 |
| Figure 2: Impact |
Flow diagram
• JSON files are present in the local
system.
• Data will be ingested into the temp
table in a single column using a Python ingestion script.
• Details will be
written to the Main table ingestion failure logs in case of any failures in the
ingestion.
• Data will be populated into derived
tables using functions in ADX.
• The status of the derived table
ingestions will be written to Derived Table Ingestion logs.
• Power BI report will follow the
following data-sourcing approach:
• Data will be sourced from ADX using a composite model.• Larger tables will be sourced through DirectQuery mode.• Smaller dimension tables will be sourced through Import mode.
 |
||
| Figure 3: Solution
Architecture |