Development of a data warehouse and reporting

acterys, Data science a AI, microsoft fabric

Worldline Czech Republic

Worldline Czech Republic is a subsidiary of the international company Worldline, which is one of the largest global players in the field of acquiring services. In the Czech Republic, it operates under the business name KB SmartPay. Thanks to the integration of a wide range of platforms and acquisitions of many financial technology companies, it is able to provide a wide range of services and products. The functioning and development of this type of company necessitate a quality data base and dynamic development of its information flows.

worldline - úvodní obrázek

Client’s requirements

Due to the growing volume of data and time spent on regular report preparation, Worldline Czech Republic decided to automate its data processing and create a database of client transaction history to be tailored for adhoc inquiries as well as regular reporting purposes.

The existing solution had been largely based on manual work and was limited by the technical possibilities of Excel. This also meant it was labour intensive and had a high risk of errors. During the design process for the new solution, emphasis was placed on the accurate and uniform definition of key performance indicators across the company and tracking client profitability. The solution included the development of a series of reports. This included e.g. an Attrition report including prediction of potential outbound traders based on tracking their behavior and evaluating a number of other parameters. The main users of the data outputs are members of management, marketing, sales, finance and risk departments.

The main objectives of the project were data support for business and marketing strategies, increasing the effectiveness of marketing campaigns, and growth in profitability and overall profit. Secondary benefits included saving skilled analysts’ time spent preparing regular reports, simplifying data cleaning and standardizing the definition of monitored indicators.

Solution

The solution involved an in-depth analysis of data sources, which led to clarification of many hidden aspects and behaviours of the source systems that had been impossible to uncover previously due to the large volume of data. Subsequently, a data warehouse was created, which is designed to be used universally: as a source for reporting in Power BI, as well as for creating partial views for analyses based on consolidated data sources, controls, or other adhoc solutions according to requirements.

The implementation included the following areas:

  • analysis;
  • architecture design and efficient storage of transactional data;
  • installation and configuration of the database system, ETL tools and configuration of the reporting tool throughput
  • Integration of data from source systems / extracts (transactional data, CRM system, public datasets, manually managed dials);
  • Data cleaning, validation procedures to maintain data quality, a correction layer for adhoc and permanent solutions to errors in source data;
  • Transformations and calculations of data into DWH;
  • Consolidation of clients from multiple platforms; definition of generic tables;
  • Initial transaction load in full detail from early 2019;
  • Connecting the prepared data source to the MS Power BI reporting platform;
  • Development of reports in Power BI technology for management as well as marketing, sales, finance and risk departments
  • Training in Power BI technology according to client requirements
  • Ongoing implementation of requirements for partial changes in reports
  • Handing over the developed solution to the company’s own management.

Benefits

The solution led to a significant simplification of work, reduction in error risk, a single view of all current and historical data, a comprehensive and clear basis for tracking the activity and profitability of traders, strategic planning and automation of reporting to the parent organization. Data from three different platforms are consolidated within the data warehouse. Reporting solutions allow users to monitor aggregated results while allowing them to view detailed values down to the merchant, branch or specific terminal level. Most reports are calculated on a daily basis. Factual information in foreign currencies is automatically converted in the background according to the CNB exchange rate valid on the day of the transaction. This comprehensive solution is used by both top management and analysts in different departments.

Technologies used

The data warehouse is built on a Microsoft SQL Server database operated on-premise, Pentaho Data Integration CE was used as an ETL tool for system integration where it was not possible to use database components. The reporting system was implemented in the Power BI environment.

Subscribe to our Newsletter!

Get a regular stream of interesting information from the world of data technology directly to your inbox.

  • Every month:

  • Latest news on innovations in Business Intelligence and data technologies.

  • Exclusive invitations to professional events, seminars and workshops.

  • Case studies and practical examples from real projects.

  • Inspiration and tips for your data strategy.

Want to contact us?

Drop files here or
Max. file size: 100 MB.
    This site is registered on wpml.org as a development site. Switch to a production site key to remove this banner.