Tags: ETL, Power BI
One of the largest North American agricultural enterprises has been envisioning and designing a new cloud-based Business Intelligence and Data Warehousing architecture. They were using a fragmented system, based on Oracle database, while the reports were presented as a mix of SQL Server Reporting Services (SSRS) and Excel dashboards.
We came up with a solution that allowed to consolidate all reports with Power BI dashboards. In addition, we proposed to migrate the original database to Azure SQL Data Warehouse. This helps reduce license costs and improve the performance of the newly created cloud database system.
Learn more about how we approached this migration project from the following video.
Overview of the Original System
Our customer was using Oracle data warehouse that included over 200 tables that included 14 dimensions and 1 fact table with 4 measures.
The original system supported the data uploading from various sources, such as flat Excel and CSV files.
The customer used SSRS and Excel dashboards to generate analytic reports based on the data from Oracle DW. They could review these reports only from the local network and needed a way to provide broader access to others outside of the network.
New Architecture
Our solution included migration of an old and expensive Oracle data warehouse to Azure SQL Data Warehouse.
After converting database schemas, we updated the ETL scripts to run against the Azure Blob Storage. Then the data from the Azure Blob Storage was loaded directly into the newly created Azure cloud. We utilized the Azure Data Factory pipelines to schedule the ETL jobs. Actually, we used the same solution with minor changes to migrate data from the original database to the Azure cloud.
Based on this cloud storage, we created the Power BI dashboards and moved the reports from Excel and SSRS to a single place. Thus, these analytic reports became available from anywhere on the Internet. Apart from that, the reports looked awesome both on PCs and mobile devices, keeping the business analytics on top of changes as they happen.
Results and Benefits
Using the full stack of Microsoft solutions allowed to bring the fragmented bunch of previously used services under control. Moreover, this proved to be less expensive for our customer. This came out to be not the only benefit, since all their reports were now covered in Power BI dashboards, maximizing their availability on various devices including smartphones and tablets. Also, the cloud solution allows maintaining fast query performance as database sizes grow.
We’ve crafted an ETL solution leveraging Azure Data Factory and implemented some automation tools to migrate the data and convert the original Oracle schemas to the Azure cloud. This approach allows for making the migration project cost-effective and completing it shortly. We can use the same approach to deliver your database migration projects.
