Scalable BI Solution and Customizable Reports for a Food Manufacturer and Distributor

Customer

The Customer is a large food manufacturer and distributor operating across the US.

Challenge

As the Customer’s business kept expanding, it was becoming more challenging to analyze the ever-growing volumes of data scattered across multiple on-premises and cloud data sources. The legacy approach to data analytics had many error-prone manual steps and lacked transparency in data flows. To boost enterprise-wide decision-making, the Customer was looking for a reliable tech partner to design and develop a robust BI solution.

Development of a BI Solution

Trusting ScienceSoft’s 17 years in BI services and a vast portfolio of success stories in data analytics, the Customer turned to us for BI implementation.

BI Solution Design and Planning

ScienceSoft’s consultants started with an in-depth investigation of the Customer’s business needs and expectations. Within a series of meetings with key project stakeholders, a clear concept of a BI solution was created. As a result of the discovery stage that involved extensive reverse engineering of the legacy system, the Customer received:

• An inventory of data sources and existing data management flows.
• A feature set for the BI solution, including data processing and analytics features.
• Architecture design of the solution and its integrations with multiple on-premises and cloud data sources.
• Recommendations on optimal tech stack for each component.
• A detailed project implementation roadmap with costs, timelines, and resources estimated, as well as a risk mitigation plan.

BI Solution Development and Launch

In six months, ScienceSoft’s team (a project manager, a business analyst, three data engineers, and a DevOps specialist) delivered a fully functional BI solution that included the following components:

• A data source layer for the internal and external data sources (ERPs, SharePoint, internal databases and Excel documents prepared by different departments, Excel reports from distribution partners, etc.).
• A data ingestion layer with easy-to-configure ETL pipelines enabling automated and semi-automated collection of the data from the sources and scheduled data refreshment.
• A data lake to hold business data in a raw format available for further processing.
• A data warehouse (DWH) to store the processed data in a highly structured format and ready for analytical queries.
• Three OLAP cubes for regular and ad-hoc reporting on the enterprise finance metrics, sales in online stores, market data and sales by a major distributor.
• Power BI reports and applications that cover all the current analytical needs.

One of the key advantages of the new BI solution over the legacy system is the ease of data management and report customization. The platform enables greater responsiveness to the evolving business needs, significantly reducing the workload on the Customer’s ICT team. The ICT team is now able to alter the current reports, add new data sources, manage the logic of data transformations and data access rules in a single place, and also build reports and analytics processes much faster.

To help the Customer achieve high data security, ScienceSoft introduced role-based data access control and data change tracking. Another security feature – analytics sandbox – was implemented to let the Customer’s data analysts run safe data experiments without affecting the production environment.

Once the BI solution was launched, ScienceSoft’s team conducted a series of user training sessions and provided the Customer with all the required technical documentation. Currently, the company considers further cooperation with ScienceSoft to upgrade the solution with new functionality.

Results

Within six months, the Customer received an easy-to-scale BI solution that serves as a single point of truth for business data and drives enterprise decision-making. With ScienceSoft’s help, the Customer has increased transparency of data flows across the company, improved constituency and security of business data, and got new possibilities for safe data experiments.

Technologies

Microsoft Blob Storage, Microsoft Azure file share, Microsoft Key Vault, Azure Event Grid, Azure Analysis Service, Azure Data Factory, Azure SQL Database, Microsoft Power BI, Microsoft SQL Server, Azure Active Directory, Microsoft SharePoint, Microsoft Business Central.