Scenario

Our client is an environmental engineering company that specializes in cleaning of hazardous environments. One of their systems needs its performance monitored by independent laboratories. Each lab provides their analytical results via email, and each lab does so in different formats.

They needed a solution that was capable of ingesting the multiple lab reports  and consolidating them into one format to enable reporting that allowed for cross site reporting. They also required the process to be fully automated and able to accommodate new sites.

Challenge

The client was using PowerBI to monitor one of their projects and wanted to build on the existing reporting and be able to include other current and any future projects within the same report. This would allow the ability to compare site performance, create benchmarks and detect anomalies.

The client currently had a SharePoint Site to collect data for the current project and was looking for an alternate solution that could support their growth in projects. They had used PowerAutomate to capture the projects data files from a shared mailbox but needed to extend the solution. 

Solution

Using our expertise, we were able to automate the data processing using a combination of Azure Logic Apps flows and Microsoft Outlook rules. It moved the email attachments to an Azure Blob Storage location to provide better scalability. By fully automating this process, it allows the Power BI file to consistently ingest new data as it is being received,  removing the risk associated with a manual process. 

Through careful data modelling we were able to configure the Power BI file to ingest multiple different data file formats from multiple countries in different languages. This data is consolidated into one central data source that drives all reporting

Outcome

The client now has a solution that:

  • Removes manual processing
  • Scales to any number of data sources
  • Allows reporting across all their sites in a single report

Technology