Construction of a new Data Warehouse

Challenges

Pekao Bank Hipoteczny and Bank Pekao independently grant mortgages, with registration and servicing taking place in separate systems. As of September 2020, the Pekao Group began successively transferring mortgage loan portfolios previously granted by Bank Pekao S.A. to Pekao Bank Hipoteczny S.A.. The loans after the transfer are still handled in Bank Pekao's systems, as they were before - however, they enter the balance sheet of Pekao Bank Hipoteczny and it is there that they are reported.

The bank had a Data Warehouse with a limited scope containing mainly financial data. However, it did not meet many of the requirements necessary for the bank to function properly. In 2018, Pekao Bank Mortgage made decisions on the need to implement a new Data Warehouse that would include data from several new source systems - both from Pekao Bank Hipoteczny and primarily from Pekao Bank - and have a significantly expanded scope.

Solutions

The project implemented a 3-tier data warehouse consisting of: 

• Stage layer used to temporarily store data taken directly from source databases and external files, 
• DWH layer built in a relational model according to Immon's methodology with full data versioning, in which data from different systems are collected and grouped in common tables,
• layers of Data Mart subject areas organized into fact tables and dimension tables so that the data can be accessed by users in the most convenient way and so that this access is as fast as possible.

A technical metadata layer for feed processes has also been created, which includes tables that control the feed process, packets used to feed each layer, and tables that log ETL processes. 

• The wholesaler is fed from four of Pekao Bank Hipoteczny own systems and from the Pekao Bank system, from which data is issued in the form of flat files. 
• The wholesaler supports 3 account plans and provides the ability to load adjustments for each of them - a dedicated application in Phyton was made for this purpose.
• The implementation team conducted training for Pekao Bank Hipoteczny IT team, which looks after the wholesaler.

There are plans to implement a Power BI tool in the future.  

Effects

• A single source of truth for all corporate data.
• The possibility of joint reporting required by the NBP and the FSC from data in the systems of both banks. 
• Provide access to many new data areas relative to the old warehouse.
• Easy access for business users to the data collected in the warehouse by creating a Data Mart layer.
• Thanks to the wide range of data included in the Data Warehouse layer of the warehouse, it is possible to enrich the Data Mart layer with new functionalities. 

Technologies and tools used 

The data warehouse was implemented on the Oracle platform using the GIT tool for code versioning

Customer:

Pekao Bank Hipoteczny

Pekao Bank Hipoteczny was established in 1999, initially as part of the Pekao SA group, and since 2007 it has had its current name and has been in the Pekao group. It is a specialized bank that provides mortgages for residential or commercial purposes. In addition to providing residential loans, the bank finances commercial real estate throughout the country. The source of the bank's lending is funds raised on the capital market, including through the issuance of mortgage bonds.