Tech Stack: Power BI, Microsoft Azure SQL Database/Data Warehouse, Oracle BI, Azure Data Factory, Azure Blob Storage, Azure Data Pipelines

About the Client

The client is a leading firm in the aviation industry that focused on aviation management products and services including maintenance management, flight scheduling and inventory management. The client wanted to move from their legacy Oracle BI system to Power BI.

Business Need

The client had an older legacy system that needed to be replaced by a modern reporting system that could produce near real-time reports. The client chose the Power BI platform and wanted to embed reports into their application with a live connection to the database along with row-level data security. Since Power BI embedded with a live connection to the source only works with Azure SQL Database/Data Warehouse, the client had to move terabytes of operations and compliance-related data from Oracle Database to Azure SQL Database/Data Warehouse.  This was a challenging task for their in-house team.

Our Technology Solution

Despite having an in-house engineering team, the client was unsuccessful in moving the data from the Oracle Database to Azure and faced multiple failures even after trying out different approaches due to the huge amount of data being dealt with.  This proved to be extremely time consuming due to repetitive network failure while the load ran for long hours.

Despite the challenges the client faced, we chose to use Azure Data Factory to load the data from Oracle to Azure Data Warehouse. Being aware of the huge size of data and of the capabilities of Azure Data Factory, we performed the following activities:

1. Initially established the connection to the Oracle source system
2. Provided a Blob storage as a destination where the blocks of data is to be stored. Each block here creates a new file in the Blob.
3. Used Azure Copy data component to copy the data from the source to the Blob Storage in blocks.
4. Once all the blocks of data were moved to the Blob Storage, we created Data Pipelines to pull data from the Blob to the Azure SQL.

Additionally, the client wanted to create reports based on this data, which could provide near real-time and actionable insights. To embed these reports into the app, we provisioned a Power BI Workspace Collection on the Azure portal and imported the created reports into the workspace and configured row-level security.

Result

The architecture solution system that we designed ensured scalability and better performance. With our solution, the reports were successfully embedded into the client’s application by specifying the embed token in the application code. This solution also resulted in:

• Ease of managing large volumes of data accumulated from the aircraft sensors into log files
• Real-time business insights to boost sales and optimize operational & functional costs
• Eliminating data loading errors and significantly reduced data load time
• Building reports in Power BI that provided near real-time analytics and actionable insights for its aircraft maintenance needs