Analytics as a Service Helps an Industrial Construction Company Reduce ETL Time and Enable Effective Decision-making using Real-time Reports, Scorecards, and KPIs

Business Need

The construction company had already implemented an Enterprise Data Warehouse using data from their Legacy eCMS system and Microsoft Dynamics AX 2012. However, they were struggling with ETL performance, data quality, and validation issues. Given the fact that they had more than 500 tables to extract data from, the ETL process was extremely time-consuming. In addition, there was a lot of pressure from business users for real-time dashboards and reports.

The company was looking to have their data warehouse expanded, optimized, and validated with the source data, so they could create SSRS and Power BI from the expanded data warehouse. They were also looking for an optimized solution that could enhance ETL and allow them to add more data from AX and other custom LOB applications. They also wanted ETL job monitoring support and wanted to ensure that the ETL process is completed before business hours begin.

Synoptek stepped in to provide the construction company with complete support and rapid expansion of EDW with new data source systems and to meet their evolving business reporting requirements.

Solution and Approach

Using Azure Services and Advanced Analytics to solve the various business requirement, Synoptek performed optimization on ETL packages and was able to reduce the ETL execution time significantly – while allowing the construction company to have the data warehouse ready before business hours for data analysis and reporting.

As part of the engagement, we have developed over 250+ reports and dashboards for business stakeholders to enable effective decision-making based on analytics, scorecards, and KPIs – that helped in unearthing data-driven insights. In addition, we:

• Implemented a hybrid data warehouse architecture with on-premise and Azure services
• Expanded the existing data warehouse by adding various new data sources and analytics on top of existing data
• Added project productivity data, AX HR module data as well as data from a newly developed custom application TMS in the data warehouse and analysis service
• Created a Power BI report for TMS and HR module
• Moved application data from Azure SQL to Azure Managed instance

Business Benefits

With Synoptek’s Analytics as a Service offering, the construction company is able to:

• Consolidate data from multiple sources and enable reporting on the latest data
• Implement row-level security that allows them to roll out reports to different user groups and allow them to view data based on their roles
• Enable self-service BI for business analysts and users, allowing them to create their own reports on top of well-designed data warehouse/data marts with required business calculation and measures in place
• Receive complete support and maintenance of data warehouse and reporting that resulted in a more stable and reliable environment and led to increased usage of reports/analytics throughout the organization
• Enjoy better turnaround time of issues and fix ETL job failures before business hours