BI Solution for Enterprise-Wide Sales Analytics Ready in 7 Weeks

Our Customer

The Customer is a leading provider of manufacturing and supply chain management services. It helps businesses streamline the full cycle of SCM from sourcing and quality control to finishing and shipment by leveraging its global network of suppliers and logistics touchpoints. The Customer is among America’s fastest-growing private companies according to the Inc. 5000.

Lack of Visibility into Sales Operations

Operating across the Americas, Europe, and Asia, the Customer used to analyze the sales performance across all its locations by building reports in its SAP ERP. However, the ERP functionality could no longer satisfy the Customer’s analytics needs: it failed to provide the required reports that would allow users to drill into sales data.

The Customer turned to ScienceSoft to implement a BI solution with automated data workflows and tailored reporting capabilities. Delivering robust business intelligence solutions since 2005, our team had the necessary expertise to tackle the challenge.

BI Implementation for In-Depth Sales Analytics

ScienceSoft allocated a team of a Project Manager, a Senior Business Analyst, a Lead Data Engineer, a Senior Data Engineer, and a Senior DevOps Engineer to take on the project.

During the discovery phase, ScienceSoft’s team explored the Customer’s data sources, the existing data flows, and reports and elicited the requirements for sales analytics.

Since the Customer had a subscription to several Microsoft Azure services, its IT team asked us to give preference to Azure products, if possible. ScienceSoft’s experts confirmed that the required reporting functionality could be efficiently implemented on Azure.

With the Customer’s reporting needs and preferences in mind, ScienceSoft’s team developed and implemented a BI solution comprising the following components and processes:

Data sources

Data is acquired from the Customer’s six geographically distributed databases that hold business-critical information.

Data ingestion

The ETL tool (Azure Data Factory) is used for data extraction from predefined columns across the six databases.

Data storage (landing)

The extracted data lands in raw data storage (Azure Blob Storage) as CSV files. The storage has a dedicated folder for each database, which simplifies the mapping logic. After the data is tested and validated, the files are converted into the Parquet format to optimize the use of storage resources and achieve better query performance.

Data transformation (staging)

The data from the raw data storage is aggregated, processed, and loaded into a data warehouse (Azure SQL Database). The consolidated and structured data from the DWH is used for sales reporting.

Power BI reporting

Users can get insights from sales data based on the following types of detailed reports with drill-down capabilities:

The sales dashboard that displays:

• Global company sales detailed by country.
• Country sales detailed by Customer.
• Customer sales detailed by commodity.

The product margin dashboard that displays:

• Margin by Customer.
• Margin by item number.
• Margin by sales account manager.

Security

Data backup, user authentication, and authorization mechanisms are based on the native Azure services (Azure Backup and Azure Active Directory). Row-level security is implemented to enable tiered data access.

Enterprise-Wide Sales Analytics Implemented in 7 Weeks

Within only seven weeks, ScienceSoft developed a BI solution that helped the Customer to gain more visibility into its sales performance. Users can access all the necessary information from tailored, easy-to-use dashboards and get meaningful insights without manual navigation.

Meanwhile, the Customer and ScienceSoft are already discussing the next possible collaboration: developing a supplier portal with shipment date forecasting capabilities.

Technologies and Tools

Azure Data Factory, Azure Blob Storage, Azure SQL Database, Microsoft Power BI