Scenario

Our customer is a global travel agency providing holiday tours, serving customers directly and through a network of travel agents. They were coming to a decision point on how to manage their sales reporting capability as their existing on premise SQL Server based solution was coming up for license renewal and they needed to decide whether continuing with the current state was going to be both cost effective and able to meet the future needs of the business.

Challenge

Their existing solution presented a few challenges. Most compellingly there were significant IT costs associated with maintaining an on-premise solution. These came from both software and hardware costs, but also the support costs due to dependency on an offshore service provider for build and maintenance. Providing reporting to Agents was also cumbersome and required a custom portal to deliver it securely.

There were also business concerns – the reports were not universally trusted, with disagreements on definitions of metrics and inconsistencies between reports. This issue was exacerbated by the lack of in house understanding of the system.

Solution

A cloud first solution was desired by the customer, so we proposed undertaking a Data Platform Modernisation exercise, shifting their existing ETL and Data Warehouse to PaaS services and providing more Self-Service reporting capability.

This was accepted, and our first step was to work with the business to define a data model which would enable a self-service reporting solution. This then formed the basis of our PowerBI reporting. All the fields and metrics in the model were to be described in an Azure Data Catalog for ongoing reference.

As we went through the build journey, we discovered the existing ETL processes were not utilizing SSIS’s capabilities extensively. It became simpler and more sustainable to build a metadata driven ETL process using Azure Data Factory which reduced many SSIS jobs to a single executable that could be extended without requiring code changes by the customer.

Throughout the process, the use of Azure DevOps enabled the team to work together on project activities and priorities, giving complete transparency to everyone involved.

Outcome

At the end of the process, the customer now benefited from a self-service solution using PowerBI that enabled access to reporting any time, any place, anywhere. Users were able to create their own reports from an agreed data model without requiring technical skill.

By having a single data model as a source, differences over definitions were removed from business conversations. By having it fully captured in an Azure Data Catalog clarity could quickly be obtained over meanings in the event of any uncertainty.

Use of ADO ensured that the customer had full ownership of the delivered code artefacts with traceability over changes and decisions made, enabling them to better support and understand the solution going forward.