BI Solution for Phytotherapy Products Life Cycle Analytics with 20+ User-Specific Reports

About Our Client

The Client is a North American company that produces high-quality phytotherapy products.

Challenge

Because of the data located across various applications, the Client lacked transparency into the full product life cycle – from growing plants to managing customers – and thus, couldn’t conduct internal benchmarking and optimize internal business processes.

Solution

The project started with the definition of the Client’s short-term and long-term business objectives and the requirement elicitation. In the course of business intelligence consulting, ScienceSoft’s team analyzed the Client’s needs and designed the architecture of the future BI solution (Figure 1) that would allow integrating the Client’s disparate data sources and transforming them into actionable and easy-to-digest reports. As the next step, our BI team drew up a technology stack that would support integration capabilities, speed, and fault-tolerance of the solution.

Figure 1 – Solution architecture.

ScienceSoft’s team started the development of the solution by creating a cloud data warehouse based on Azure SQL Server, which became the central storage for company-wide data.

After that, the team set up ETL processes from scratch to process and cleanse data from the Client’s numerous applications (ERP, CRM, supply-chain management system, manufacturing execution system, accounting software, etc.). To eliminate the risks of data leaks and breaches for the Client, ScienceSoft’s experts employed Azure Key Vault and Azure Active Directory.

For managers to get the ability to conduct near real-time analytics, ScienceSoft’s BA experts employed the Power BI Desktop application and the Power BI service and delivered more than 20 different report templates to cater to the needs of the following departments:

• Sales
• Production
• Product quality management
• Resource management
• Security and access

To ensure the solution’s easy adoption, our BI team created a set of guides with detailed information on the solution’s components and functions, published them on the company’s intranet, and continuously monitored and analyzed users’ feedback regarding the BI solution.

Results

With the delivered business intelligence solution, the Client could embrace company-wide reporting, check their actual performance against the planned one, improve planning, and optimize their internal processes and operations.

Technologies and Tools

Azure SQL Server, Azure Data Factory, Azure Linux VM, Azure Web App, Azure Key Vault, Azure Active Directory, Azure Automation, PowerShell, Power BI.