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

Customer

The end Customer is a North American company that specializes in producing high-quality phytotherapy products.

Challenge

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

Solution

The project started with the defining of the Customer’s short-term and long-term business objectives and requirements elicitation. In the course of business intelligence consulting, ScienceSoft’s team analyzed the Customer’s needs and designed the architecture of the future BI solution (Figure 1) that would allow integrating the Customer’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 enable integration capabilities, speed and fault-tolerance of the solution.

Figure 1 – Solution architecture.

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

After that, the team set up ETL processes from scratch to process and cleanse data from the Customer’s numerous applications (ERP, CRM, supply-chain management system, manufacturing execution system, accounting software, etc.). To eliminate the risks of data leak 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 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 the 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 Customer 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.