Future-proof data warehousing in professional services

Context & Objectives

A company involved in human resources management aimed to centralize their data environment for easy access by different agents, particularly for reporting purposes. Additionally, they desired a flexible system that could accommodate future uses, such as AI applications.

The project started because they were planning to transition their human resource management to a new system, which posed the risk of data loss. To mitigate this risk, we developed a modern data warehouse, providing a robust foundation for effective data management.

Approach

First, we analyzed the data in the source databases. This analysis considered factors such as the data's size, the presence of indices, and the potential for implementing incremental logic for daily data updates. This approach allows us to avoid downloading entire tables every time we need an update.

Next, we analyzed the reports utilized by the company's agents and back office to prioritize the workload while maintaining a comprehensive view of the long-term goal. We organized the data from the SQL database, documented any transformations made, determined the frequency of usage for each report, and prioritized them based on business insights, query complexity, and frequency of usage.

Finally, we performed a database redesign to update the data fields and set up the infrastructure in Azure with an appropriate data model. Additionally, we migrated the key reports identified initially to the new framework. These reports will serve as a tangible example for end-users and internal development teams to leverage.

Results

The updated data platform offers a range of features, including infrastructure setup, implementation of a data warehouse data model, designing an architecture with multiple options, and providing the best recommendation for the client. It ensures data governance and creates documentation for the client's team. The cloud-based data warehouse architecture brings together different data sources into one ETL framework, enabling the generation of analytical reports and analysis of insights.

We migrated seven important and complex reports from the old database structure to the new one. These reports are used by the client's agents and back-office staff for their daily analysis.

Our team efficiently coordinated the process of daily data ingestion, handled the logic for transforming the data, and established a secure network for seamless communication between various components. Additionally, we set up a Key Vault to securely store all essential credentials and configurations.