background Layer 1

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
The international IT professionals' community has announced the 'Top 100 IT Leaders' project. It is a global initiative that allows top IT managers to share their experience, expand their professional network and showcase the best digitalization practices of their companies. Here we will answer the basic questions about the project.

The editors of Global CIO discussed with Ahmet Hakan Göral, CIO at Boutique Rugs and winner of the Project of the Year 2023 contest, his current projects, the approach to digital transformation and the specifics of the role of CIO in retail.

April was full of new initiatives from vendors as well as some fascinating news on the technology front. With Olympics in Paris approaching, more  news will be flowing in from the capital of France.

Given the current job market situation, one may find a need to maintain a strong LinkedIn profile. AI can streamline the process and make it easier to connect with the right people and opportunities.

The integration of Artificial Intelligence (AI) into business operations marks a transformative era, enhancing efficiency and innovation across industries. From revolutionizing HR with automated recruitment to aiding early disease detection in healthcare, AI's impact is profound. It enables predictive cybersecurity, personalized customer interactions, and accelerated software development in IT. 

We use cookies for analytical purposes and to deliver you the best experience with our website. Continuing to the site, you agree to the Cookie Policy.