Below you will find an overview of some projects carried out in recent years.
Maintenance for Core DWH
As an expert for all Core DWH processes third-level support for incidents and other failures recorded in the central service request management tool
- Analyzing the incident
- Proposal for a solution
- If necessary develop a software solution resp. modify existing software
- Deployment of solution
Expert for External audits
Regularly there are taking place external audits for the Bank. Depending on the requirements of the auditors data have to be extracted from mostly all core banking systems after a given plan from the auditors. In general the following steps have to be executed in those projects:
- Analysing the requirements together with business
- Creating a data extraction plan
- Developing corresponding extraction programs in SAS
- Testing samples of the data extract by business areas which are involved in the audit
- Creating data extracts and reports
- Implementing short-term ad-hoc requirements issued by the auditors
Examples for those audits are:
- Yearly Anti Money Laundering audit (requirements varying from year to year)
- Audits from the German Deposit Protection Fund (EdB)
- BaFin audit according to §44 KWG
- ‘Außenwirtschaftsprüfung’ by Bundesbank
- Yearly Payment Statistics for Bundesbank/EZB
ETL Specialist
Bank has implemented a new interactive/batch tool for the management of the collection process. But the collection business department requires the data to be available in the core data warehouse in order to make further data analysis and to create appropriate reports. Therefore, an ETL Process based on SAS was implemented for providing the data in data warehouse:
- Maintenace and implementation of a process of integration of the data delivered daily by the collection system into data warehouse
- Implementation of a monthly historization process
- Integration of a restart facility in case of an error
SAS Implementation
This project was initiated by the department Insurances and refers to prematurely cancelled CPI (Credit Protection Insurance) either by the customer or by the bank. In case of the cancellation of a policy the refund for the customer has to be calculated with respect to legal regulations. This is and was done by a TPA, which sends monthly lists to the Insurance department containing details of the contracts and the corresponding reimbursement. But due to an audit requirement a controlling facility has to be implemented by the bank in order to validate the refunds calculated by the TPA. It was decided to use the already existing data in data warehouse, where all insurance contracts are stored, for re-calculation of the refunds and compare them to the values calculated by TPA:
- Implementation of an ETL process to load the data provided by the TPA
- Selection of the cancelled policies in data warehouse
- Application of different algorithms for re-calculation of the refund
- Creating and providing several reports for the business department
Migration into Cloud
The current DWH was running on various servers on-premise. As the amount of data was continously growing, limitations according to disc space and performance issues became more and more important and prevent new applications to be deployed. Therefore Management finally decided to move the whole DWH into a Microsoft Azure Cloud and using SAS GRID (load balancing). An appropiate project team was arranged for realizing the following tasks (overview):
- Review of all production processes and SAS applications with creation of a plan for necessary adaptions and deployment (about 400)
- Review of all interfaces (> 5.000)
- Creation of an automated process for comparison of "old" and "new" DWH data
- Implementation of batch processes in SAS GRID (Scheduler Control-M) taking into account dependencies between different processes
- Stepwise migration, no big-bang, business was involved through close cooperation in all steps
SAS Implementation of an EU Directive regarding Insurances
Objective of this project is to fulfil the mandatory legal requirements
with respect to Insurance Distribution Directive II. With IDD II, the legislature has provided new
requirements regarding the brokerage and sale of insurances:
- Designation
of acquisition costs of the insurance in product information sheet PIB (Produktinformationbslatt)
- IPID: Fact
sheet for property insurance
- All costumers with an insurance contract must be informed about their rights of revocation and receive the PIB again after conclusion of the contract. Only after having received this second information (“Welcome letter”) the 30-days term of revocation starts. The letter has to be send to the costumer not earlier than 7 days after conclusion of the contract
- Main tasks for the application in DWH are to identify new insurance contracts on a daily basis from all involved core banking systems, collect all required data for the "Welcome Letter", carry out quality assurance of the selected data, provide the data via interface to further processing. All jobs have been designed to run automatically in a batch environment.
Main focus in this project was to create a detailed proposal for the realization of the GDPR requirements in SAS DWH. DWH contains many personal data about customers from all involved core banking systems. This relates not only to active contracts, but also to closed contracts which have been historized. Even restores from backed up data have to be considered. The functional requirements are described in
relation to initial cleansing, periodic cleansing, establishing a control model
and defining a procedure for restore requests. Therefore, in order to reach GDPR compliance, two principles are applied: locking and deletion.
- Locking: The locking period starts when a contract is
closed. When data is locked, it means that the
information is only accesible by a restricted group of business or technical
users
- Deletion: Once a contract is closed or the relation with
the customer is finished, the data has to be deleted after a defined period of
time. For contracts a legal requirement says that contracts have to be held
available for 10 years