Data Warehouse

Published On December 8, 2014
- Posted in Case study

About the Client

Leger des Heils is an international charitable organisation and one of the greatest Non-Governmental Organisations in the Netherlands. Their activities include social relief, elderly care, mental healthcare, youth care, rehabilitation and addiction treatment, prevention and social recovery, social work, international development services and recycling.

The Backstory

Since Leger des Heils had a new Enterprise Care Management System developed by Qubiz, they also had an extensive need for business intelligence. The main focus of the business intelligence solution, from a balanced scorecard perspective, was on:

  • monitoring and controlling the financial production
  • monitoring the quality of care and allowing the customers (patients) to indicate how can the care services be improved
  • examining the degree in which all the departments of the organisation were working in the agreed standard way and correcting deviations from this standard
  • assessing the level of their personnel’s learning and growth needs

The Challenge

In order to give Leger des Heils the required controls and monitoring facilities, Qubiz developed a new Data Warehouse and Business Intelligence solution consisting of:

  • standard and ad-hoc reports for management and executives
  • an Online Analytics Processing (OLAP) tools for business analysts
  • a dashboard with key performance indicators (based on a balanced scorecard)

The solution consists of several components that allow Leger des Heils to be ready for the future and that fit their extension plans. These component are:

  • a replication operational data store (for near-realtime reports)
  • data warehouse with data marts for historical data collection
  • SQL Server Integration-based Extract, Transform and Load (ETL) packages
  • SQL Server Analysis-based Cubes for analytical processing
  • SQL Server Reporting-based reports for standard and ad-hoc reports
  • Microsoft Excel-based dashboard (on top of the cubes) for business analysts

The Process

Since Business Intelligence involves a lot of business knowledge, we spend quite some time with our client so we could understand its decision-making needs. Using an agile project management approach, we identified the main user scenarios. Step by step, the ETL, the Data Warehouse and the Cubes were developed. We worked in multiple sprints (stages). After every sprint, which took 2 to 3 weeks, we presented the results to Leger des Heils’ business managers and analysts, so that they could steer for the next sprint.

The Solution

The solution we built for Leger des Heils was designed from a business process-oriented point of view. Our focus was to create an information system that will provide full data history needed for compliancy and auditability.

Starting from Leger des Heils’ operational database (ODS), we built a data vault that could enable historical detail reports, audit logs and more.

Technologies Involved

For this data vault we built several data marts that will allow the data warehouse’s users to retrieve the necessary information. For instance, we created a current financial monitor featuring multiple dimensions, including the organisation, the product/program, the time, and the client dimension.

For each data mart we created an OLAP (Online Analytical Processing) cube, which allows users to retrieve and analyse multidimensional data quickly and from multiple perspectives. In other words, the cube helps users understand certain trends and fenomena (e.g. the sales went up) by showing their underlying causes.

 

 

 

Related Case Studies