Building an enterprise data warehouse and creating analytical tools

All integrations and the warehouse architecture were developed by a single individual, so proper task prioritization is crucial.

At the project's outset, participants lacked knowledge of Power BI, which they acquired during the development process.

Since all data sources have different interfaces or may not have them at all, various tools had to be used, and new ones had to be learned during the project's implementation. For example, writing data collection robots, which also help monitor unauthorized sales.

The speed of connecting a new data source ranges from 1 day to 2 weeks.

A similar implementation by external resources would be 7 to 10 times more expensive.

At the initial stage, integration with the secondary sales storage system was developed.

Following this, work began on creating reports using Power BI for the sales department. The transition to automatic calculation of all KPI tasks based on the DWH database was completed. In the MDS, directories were implemented for managing tasks, their weights, employees, and responsibility zones.

During the second stage, alongside report creation, the 1C ERP system was integrated with the data warehouse. Initially, financial data and planning data were integrated.

Based on this data, financial reports in Power BI were developed, enabling analysis from macroregions down to SKU in one place. Three OLAP cubes were created for convenient data analysis.

Production data is transferred to the DWH. A report with plans and actuals for each production line, with automatic data updates every minute, was implemented.

Additionally, the SFA system was connected to the DWH. This allowed consolidating data from three different systems into a single report and calculating visit efficiency based on various criteria obtained from different sources.

Other data in the DWH includes:

• Logistics data (shipment control based on warehouse load).

• Marketing data (promo analysis, promo calendar, market positions).

• Data from online platforms (inventory control, auto-orders for marketplaces).

• Internet store price monitoring (tracking prices in 20 popular online stores).

• Data from all consumer communication channels (analysis of incoming consumer requests).

• Integrations with national networks (inventory analysis, sales at RC and stores).

• SEO (analysis of search demand on European platforms).
  
  

Since the project's launch, 54 reports have been developed in Power BI based on the DWH database.

Upon the completion of each report, training is conducted for employees, and recordings are made. A convenient report catalog is created based on SharePoint, and each report is accompanied by instructional videos.