Client – Custom chemical solutions manufacturer

Problem

Our client employs 3 mission critical systems: an on prem ERP, a SaaS CRM, and a custom LIMS application.

Each application performs its own “job” well and each has it’s own data structure for customer, vendor, and product data. 

Each comes with robust reporting capability at the application level, but there wasn’t a reporting system to join the data from all three systems into valuable, actionable KPIs or metrics. 

This forced manual and redundant efforts by staff to merge data from the 3 systems using tools like Excel to harvest the metrics they desired to run their business.

Solution

Keypress and the client collaborated on the development of a modern data warehousing and business intelligence reporting solution:

• The client had staff that were capable of building reports and dashboard using Microsoft Power BI.
• Keypress worked with the client to identify the business metrics and KPIs they were looking to report on.
• Keypress leveraged Azure Data Factory to design and build an ETL (Extract, Transform, Load) process that pulls data from the various business solutions, transforms it into a data structure for reporting, and loads it into Azure Data Analysis services for high performance access.
• Keypress worked with the client staff to review the new data structure and integrate it into Power BI.

Capabilities Demonstrated

• Microsoft Azure Software Development
• Microsoft SQL Development
• Business Intelligence Software Development
• Business Process Improvement Software Development
• Custom Software Integration
• ETL (Extract, Transform, Load) Process Development

Overview

This solution provides:

• Accurate and up to date data metrics to help decision makers make informed decisions.
• Easily accessible but secure metrics.
• The ability for the client to continue to enhance and develop new metrics as needed to manage their business operations.

Requirements and Data Review

Our team started by meeting with the directors, managers and leads of each department to identify which KPIs (Key Performance Indicators) they were looking for from a Business Intelligence Solution.  We asked questions about the formulas they use to measure the metrics, the system of truth for each data point. and made suggestions on other metrics. 

Once our team understood the client’s end goal, we reviewed the data from the various systems and began to lay out a star schema for the data warehouse, identifying fact and dimension tables and how the data would be structured for reporting.

Development

We leveraged Azure Data Factory to develop the ETL process to ingest the data and transform it from its source structure into the star schema for reporting.  Along the way, we matched the data that represents that same entity in the various source systems.

Testing

We reviewed the results both during and after the development of the ETL process to ensure they were in line with the reporting from each of the source systems. 

Training, Launch and Support

Once the data warehouse was ready for production, we met with the client’s team responsible for developing the Power BI dashboards and reports and trained them on:

• the new star schema data structure,
• how to connect to the data warehouse,
• and what its capabilities were. 

We deployed the data warehouse into a production environment, and continued to support issues and additional requests from the client.