Data Platform Development
About the client
PSP is a Georgian company offering European-quality medicines. PSP produces a wide range of products for consumers and is continually expanding its product line to match global medical advancements. With over 300 pharmacies across the country, PSP provides guaranteed quality medicines, hygiene items, cosmetics, and nutrition at low prices, serving two million buyers monthly. The business maintains a high service standard, is regularly audited by foreign specialists, and holds the ISO 9001:2000 certification from the TUV-Reinland/Berlin-Branderburg Group. The company’s mission is to support a healthy society through accessible health and beauty products, upholding values of fairness, honesty, and commitment to the country and its people.
![[object Object] on the map](https://static.andersenlab.com/andersenlab/new-andersensite/bg-for-blocks/about-the-client/georgia-tablet-2x.png)
Business Context
Due to stable business growth and expanding cooperation with various counterparties, the customer realized that their old ERP system could no longer support operations effectively. Consequently, PSP Pharma initiated a migration to a new ERP system, which would go on to be gradually implemented across the entire business.
The new ERP system was necessary in order to offer advanced functionality to meet evolving needs. However, the old ERP was closely integrated with the legacy Data Warehouse (DWH), which provided critical analytical reporting. Transferring this legacy DWH to the new ERP was not feasible due to a lack of support and architectural design errors.
In order to maintain access to analytics after the ERP migration, the customer engaged Andersen for assistance in implementing a new DWH for the new ERP system.
Challenges
Two challenges were successfully tackled during the project:
- There was no main data source to analyze in order to build the DWH;
- The customer had limited knowledge of the new ERP system and had to learn to use it while development was ongoing.
Despite these impediments, the initiative was implemented in full and on time.
Project overview
The project aimed to establish a robust DWH to enhance control over company data on wholesale operations, focusing on modern BI-driven analysis and accommodating future scalability and predictive analytics. The customer had been using an outdated solution for reporting based on a legacy ERP system.
The target end-users were Middle and Senior managers in various departments, who used the reports for strategic planning and decision-making.
The developed solution offered many advantages over the legacy system. The main benefit was that it allowed the company to switch to a new ERP system without losing historical information.
About the project
Andersen chose to implement the Data Vault Architecture, which consists of four primary layers and two additional databases, for developing the DWH:
- Raw Data Layer;
- Staging Layer;
- Core Layer (Data Vault);
- Data Mart Layer;
- Meta Data and Archive Databases.
The Raw Data Layer contains data from two sources. All required transformations are executed through each layer, with final data consolidation occurring in the Data Mart Layer. This approach was necessary due to the significant differences in the data structures of the two sources and the limitation of only using aggregated data from the legacy source. Reports and analytics are based on the Data Mart Layer.
Development methodology: Scrum with 2-week sprints.
Non-functional requirements: While no specific non-functional requirements were provided, the Data Vault Architecture facilitates easy scaling of the solution by adding new data tables (horizontal scaling) or integrating additional data sources.
Main security decisions: Implementation of an authorization mechanism for Business Intelligence (BI) tools.
High-level description of integration capabilities (API): Integration with two data sources (a legacy DWH and a new ERP system).
Data privacy requirements and limitations: Masking of any sensitive data within the Data Vault Layer.
Challenging technical details: Designing a model to integrate two different and inconsistent data sources.
App functionality
Andersen's team developed:
- DWH layers and ETL processes for loading and transforming data;
- A Meta Data Database (DB) for storing all system metrics generated by the DWH;
- An Archive Database and data delivery mechanisms for the legacy DWH;
- A CI/CD pipeline for periodic loading of new data from sources, which was also automated;
- A framework for data testing based on dbt, designed for low-level data validation at the Data Mart Layer;
- A mechanism for error notifications in ETL processes, including audit mechanisms for pipeline operations;
- Six main reports and dashboards for business use, based on the new DWH, with capabilities for easy customization using data blocks stored at the Data Mart Layer and detailed reporting options;
- A system dashboard for visualizing DWH operations based on system metrics;
- Recovery procedures for the DWH and mechanisms for backing up data from data marts.
Solution
The overall solution delivery process included two main phases. The first phase was an 8-week-long discovery, where the legacy DWH data and existing reports were studied. This phase culminated in the development of the project's architectural vision.
In the second phase – the implementation phase – the key results included:
- Introducing a new DWH with Core (BI) and Mart (Analytics) Layers. It was integrated with both the MS Dynamics Legacy ERP System and the new LS Central ERP;
- Integrating the existing BI tool and implementing dynamic reporting with role-based security. This included the generation of tabular reports and dashboards.
Project results
As a result of Andersen's involvement, the following goals have been attained:
- Implementation of a new DWH and establishment of BI reporting in accordance with the expected timing of the business transition to a new ERP system;
- Consolidation of dispersed data sources at the Data Mart Layer, allowing the business to obtain analytics without downtime.
The resulting solution was launched by the customer, whose team was trained by us during the project development phase. Andersen's team provided support to the customer's team during this launch, which helped to mitigate risks.
Let's talk about your IT needs
What happens next?
An expert contacts you after having analyzed your requirements;
If needed, we sign an NDA to ensure the highest privacy level;
We submit a comprehensive project proposal with estimates, timelines, CVs, etc.
Customers who trust us
