BI Implementation Services Enable an Industrial Contractor Improve Data Analysis

Business Need

A seasoned veteran in the industrial construction turnaround and maintenance arena, the contractor provides quality services for each phase of industrial construction and pipe fabrication to a diverse mix of industries, in over 30 states in the US.

With the rapidly changing market, globalization of commercial markets, and other similar factors, the business was compelled to review their approach and strategy in terms of data management. The contractor needed an optimized and user-friendly business intelligence (BI) solution that allows users to create reports quickly and efficiently for faster decision-making.

The major challenge was to extract data from their distinct applications and move that to the BI applications and data warehouse. Although there are several off the shelf data solutions that enable organizations to obtain valuable insights, many of them are still in their early stages of development. Therefore, it’s in high demand to develop new and advanced methods and frameworks to extract, transform, load, analyze, and visualize such data to obtain valuable insights.

The client had extensively large amounts of data in various databases scattered around the existing ETL (Extract, Transform, and Load) process that needed to be loaded in the data warehouse. The contractor migrated the data from their legacy system to Microsoft Dynamics AX ERP through a third-party vendor, but they were facing issues after go-live. They selected Dynamics AX primarily for its capabilities to assist them in managing a multitude of functions, such as finance, workflow-based project management, supply chain, and payroll from the year 2000.

Solution and Approach

The contractor entrusted Synoptek (formerly Indusa) with complete ownership of the BI implementation, from strategy planning to create the tabular data model required for support and maintenance to addressing their growing challenges.

SQL Data Warehouse Maintenance

In order to address issues identified through the data assessment exercise, there was also a task for developing the data warehouse maintenance and Power BI implementation to encompass all current and anticipated information needs by leveraging various technologies and reports in the business decision-making process. The solution included:

Report Generation using SSRS

• Creating and maintaining informative SSRS reports. The reports include maps and charts for rich data visualization.

ETL Process

• Optimizing and converting raw data from multiple Azure SQL databases (Microsoft Dynamics AX Tables, Legacy Systems, Microsoft Dynamics CRM, and, Shop Billing) to the SQL data warehouse. This is where the data is stored in a star schema (fact/dim tables) using the ETL process, which was executed through SSIS packages.

Data Warehouse Expansion

• Data warehouse expansion reduces maintenance costs by collecting and retaining data that was previously very expensive to store.
• Different data warehouses were designed in a star schema (fact/dim tables) for loading data from different sources such as:
  • Microsoft Dynamics CRM
  • Shop Billing Application Sources: STS, Field Tracker, SPMAT, Shop Billing
  • Safety application sources: Safety Observation, FEP Audit, JSA Audit, Safety Audit).

Data Warehouse Validation

• Validating the loaded data after entering the data in the data warehouse.
• T-SQL stored procedures were created to validate the data from sources to the data loaded in the data warehouse and SSAS Tabular Cube.

Power BI Reports

• Power BI reports were built with SSAS Tabular Cube, Azure SQL database, and CRM Online as sources and were shared in Power BI apps or workspace with business users.

Common Data Model Entities

• Leverage the common Data Model functionality provided by Microsoft to create a common entity that can be used across Microsoft Dynamics AX solution.
• Four entities that have textual information, supporting measurement data and providing detailed, qualitative information to assist in the decision-making process were used over the contactor’s environment.

PowerApps Form

The PowerApps Form was created in two categories:

• Force report summary: This form was created using the common data service entities and integrated within enterprise custom applications web app which is easily accessible to the contractor. This allows the users to add the man-hours for project unit, work area, shift and date.
• Employee training certifications: This form uses barcode scanner functionality to filter the data for an employee that redirects to different Power BI reports, which will be pre-filtered, based on the barcodes scanned.
• The barcode scanner is used for filtering the data to different Power BI reports with the filters selected from PowerApps are added in the PowerApps form.
• These reports are responsive in nature – can be accessed via the web, mobile and tablet devices.

The need for a robust yet cost-effective centralized solution that manages complex requirements had become paramount for this solution.

Business Results

With the need of performance optimization, Synoptek reduced the ETL execution time and significantly saved the time of the users who could now quickly access critical data from a number of sources – all in one place and have the data accessible for data analysis and reporting.

ETL consolidates data from numerous source systems into a common format. This allows users to easily access data from a centralized location. As a result of deploying the application, the availability of enterprise assets eliminates redundancy. Business leadership no longer has to make decisions based on inconsistent information; the decisions that affect strategy and operations are based on data-driven facts and supported by relevant information.

With Power BI and SSRS reports, the contractor was able to gain rich insight into data through interactive reports and PowerApps form for a responsive in nature and used across smartphones and tablet devices.

The data warehouse expansion reduced maintenance costs by collecting and retaining data that was previously too expensive to store from its applications.

Synoptek has provided support for over a year now through three dedicated resources.

Working with Synoptek has changed the way the contractor makes business decisions. They now have full control over what analytics they want to see – whether on the web or mobile.