[ Case Study ]
Optimizing International Biopharma company's Data Warehousing:
Transitioning from Views to Tables
Customer Request
Our client approached us with a critical issue concerning their data warehouse. The existing structure, predominantly reliant on inefficient View storage resources, led to severe performance issues. Processing a single ordinary request сould took up to 2 days, causing a complete blockage of database access. The client sought a solution to enhance the efficiency of their data warehouse and expedite data processing.
Project Goal
Our goal was to transition the data warehouse from View to Tables, implementing an incremental load strategy to streamline data processing. The ultimate aim was to support the client in building comprehensive customer dashboards, particularly focusing on Customer 360.
Solution and Technologies
This schema shows how the data warehouse worked before our intervention: a single view could be utilized in several others, resulting in a complex network of dependencies.
This structure of the data warehouse was challenging to maintain and update, as alterations to one view could potentially disrupt the entire process. And optimizing the structure proved difficult, leading to performance issues.
This schema showcases the outcomes of our work: increased efficiency in loading data by parallel processing and generating a final data representation from separate components.
This approach enables us to modify data sources without risking disruptions to other loading processes. Additionally, this structure utilizes tables as data objects, eliminating the need to construct them dynamically when querying for a final data representation
Conclusions on the Project
Throughout our collaboration with the biopharmaceutical company, we successfully transformed their data warehouse operation.
The project resulted in:
1. Mitigating performance issues;
2. Ensuring the security and stability of the database;
3. Substantial enhancement of the data processing speed.
The transition from View to tables, coupled with the incremental load strategy, has empowered the client to efficiently build and access Customer 360 dashboards to provide real-time insights into each client's profile: live calls, email marketing, advertising, events, and more.