Building an enterprise data warehouse and creating analytical tools
- IT Provider
- Splat Global
- Year of project completion
- 2021
- Project timeline
- January, 2020 - June, 2021
- Project scope
- 4200 man hours
- Goals
- Unify reporting and have quick access to data
The uniqueness of the project
The project is unique in terms of its scale and implementation speed. A corporate data warehouse (DWH) has been implemented, which accumulates data from almost all of the company's information systems, normalizes and aligns them in a master data system (MDS), bringing them into a unified format.
The warehouse has enabled the generation of reports, KPIs, and models that were previously inaccessible due to the complexity of calculations and the scattered nature of the data.
The warehouse contains 27 data sources and 931 data tables.
Changes to the warehouse are reflected within 1 hour after changes are made to the data source.
To implement the project, a single developer was hired. The rest of the team acquired new skills during the project: Power BI report development, DAX and T-SQL development, and working with OLAP cubes.
Project Team: 1 developer/architect, 4 analysts/Power BI developers.
- Used software
- С#, MS SQL, MS Power BI
- Difficulty of implementation
-
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.
- Project Description
-
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.
-
- Project geography
- Global, CIS