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. They also required a flexible system that could accommodate future uses, such as AI applications.

The project began when the company planned to transition to a new human resource management system, creating a risk of data loss. To address this challenge, they needed a modern HR data warehouse providing a robust foundation for effective data management.

Approach

1. Data analysis

First, we analyzed the data in the source databases. This analysis involved factors such as:

• The data's size

• The presence of indices

• The potential for implementing incremental logic for daily data updates

This approach lets the user refresh only what's changed rather than reloading entire tables each time.

2. Data organization

Next, we reviewed the reports used by agents and back office staff to prioritize our work while keeping the long-term vision in focus. This entailed:

• Organizing the data from the SQL database

• Documenting any transformations made

• Prioritizing the reports based on business insights, query complexity, and frequency of usage

3. Database redesign

Finally, we performed a full HR data warehouse redesign to update the data fields and set up the infrastructure in Azure with an appropriate data model.

We also migrated the key reports identified earlier to the new platform. These reports now serve as concrete examples for end-users and internal development teams to follow.

Results

Our implementation included daily data ingestion processes, data transformation logic, secure network configuration, and a Key Vault for storing credentials and configurations.

The updated data platform delivered:

• A robust HR data warehouse model with infrastructure setup in Azure

• Multiple architectural options with our recommended approach

• Strong data governance and comprehensive documentation

• A unified ETL framework consolidating multiple data sources

We successfully migrated key reports from the old system to the new platform; these reports now support daily analysis for agents and back-office staff.