In addition, the Company was implementing a strategy for the migration to cloud solutions, so it was easier to create a new system from scratch than to modify the existing one. The Data Warehouse was developed by ICL Services.
Key Challenges
- It was necessary to develop a system for collecting, storing and processing data that would allow the Company to generate sales reports in a timely manner and in a convenient format.
The project was launched in the summer of 2020. First, ICL Services analysts and system architects reviewed the documentation and analysed the current system and user requirements.
The main difficulty was that the Company had a large number of business processes and a lot of heterogeneous data. Therefore, it was important to understand where this data comes from (some data was filled in manually, while others came from external systems), where it is stored, how it is processed and in what form it should come to users. ICL Services analysts managed to describe the entire structure in just two months.
Also, at the initial stages, the technological stack was coordinated, since the Russian division planned to use the same tools as the global team of the Customer, therefore it was necessary to coordinate solutions with several teams responsible for various modules of the System.
After agreeing on the technologies and architecture of the System, ICL Services got access to the Customer's development environment and began to prepare the first releases.
The entire System consists of two large modules: the data warehouse itself and a web portal for users.
1) Data Warehouse. The Data Warehouse (about 3.5TB of storage with an inherent growth potential of 600-700GB annually) collects data from various external sources.
One of the main sources is a SaaS solution that delivers sales data from fiscal data operators, distributors and pharmacy chains. Thanks to this data, the Customer itself sees at what price, in what quantity and at what discount its goods are sold at the outlets, rather than just working with data from distributors.
Several auxiliary external systems help enrich the data. For example, Master Data Management supplements the existing data with reference data including lists of products, partners, etc. There are also systems that provide tools for forecasting and calculating balances.
2) Web portal.
End users (employees of Business Excellence, Marketing, and Sales divisions) can upload data from over 60 data marts that map data from the repository to the web portal.
Users can generate reports by setting filters and upload data in tabular form to work locally or further work with data using the Qlik Sense BI platform itself. Also, employees of sales and marketing departments work with these reports.
Other modules allow you to so the following:
- configure parameters for the data marts;
- manage directories;
- calculate bonuses for distributors;
- manage user access;
- manage data quality.
In total, 12 ICL Services employees were involved in the project: analysts, system architects, data engineers, front-end and back-end developers, DevOps engineers, testers.
Products and technologies
- AWS Redshift
- Informatica Cloud + AWS Lambda
- AWS Aurora DB
- AWS DynamoDB
- Amazon S3
- Amazon Cognito
- Amazon Cloud Watch
Results
- Within six months, an MVP (minimum viable product) was developed for the Data Warehouse. The architecture of the new System allows the Customer to quickly scale the solution and integrate new modules.
- Already at the MVP stage, the Customer can get a new type of data from the fiscal counters of cash registers, which show the actual sales.
- In the future, it is planned to implement automation of verification, relocation and relaying of data; notifications will also be implemented, and the functionality of the System will expand.