Extract transform load process in banking reporting system

Abstract

Banks must maintain, calculate, and monitor liquidity using the Liquidity Coverage Ratio (LCR) indicator. In Indonesia, they reported daily, monthly, or quarterly online through a paper template prepared by the Financial Services Authority (OJK). This reporting must accurate and on time or be subject to penalties. For banks that are still processing LCR semi-automatically, this reporting system is an obstacle that they continue to face and resolve. This article discusses the automation process method developed using the concept of Extract Transform Load (ETL), with a waterfall software development model, so that daily reports are generated automatically. This article proposed a methodology to anticipate problems in integrating banking with regulators by applying one of the Basel III frameworks, primarily based on Indonesia's case studies. The finding of the research is a method for the LCR report process through ETL. The proposed ETL method in this research had proven used success in processing LCR in the banking industry. This method is a solution and recommendation for banks in making reports based on Basel III to complete LCR reporting through the ETL method.

• •o Maintain, calculate, and monitor liquidity using the Liquidity Coverage Ratio (LCR) indicator in Banking .
• •o Automation process method developed using the concept of Extract Transform Load (ETL).
• •o Recommendation for Banks to complete LCR reporting through the ETL method).

Graphical abstract

Specifications table

Subject Area:	Computer Science
More specific subject area:	Automation process that developed using the concept of Extract Transform Load (ETL)
Method name:	Automation process of Extract Transform Load (ETL)
Name and reference of original method:	P. Grundke and A. Kühn, “The impact of the Basel III liquidity ratios on banks: Evidence from a simulation study", Quarterly Review of Economics and Finance, vol. 75, pp. 167–190, Feb. (2020), doi: 10.1016/j.qref.2019.02.005.
Resource availability:	If applicable, include links to resources necessary to reproduce the method (e.g. data, software, hardware, reagent)

*Method details

Recently, there has been a financial crisis in banks in various countries globally [1,2], including the Subprime Mortgage [3], trade wars between countries, including those that were seen as high between the United States and China [4], and finally the emergence of the Covid 19 pandemic [5]. It could disrupt the continuity of the Bank's business [6]. The government as the regulator implements the Bank to prepare a Daily Liquidity Coverage Ratio Report are an implementation of Basel III [7], namely a global and voluntary framework [8] on bank capital adequacy [8], stress testing [7], and market liquidity risk [1], [7].

Meanwhile, with its respective authorities, the banking system has differed from the platform and technology [1]. With so many transactions that take place in banks that involve many stakeholders, this reporting is, of course, a problem in itself for banks [1]. Based on the problems in terms of each bank's different platforms and technologies and the number of transactions, then research Extract Transform Load Process in Banking Reporting System conducted.

This article is a strategy that raises the methodology to anticipate problems that integrate banking with regulators by applying one of the Basel III frameworks [9,10], primarily based on case studies in Indonesia. This methodological solution developed in the banking world, particularly for the Daily LCR Report process submitted by banks to regulators [4,11].

Liquidity Coverage Ratio (LCR) introduced in Basel III [12,13], where the use of the ratio is to ensure the adequacy of high-quality assets that can be converted into cash to meet bank obligations for 30 days during stressful conditions [14]. This LCR measurement is a minimum requirement that must be met by the bank [15]. Banks also expected to conduct separate stress tests to analyze the liquidity level that must hold above the minimum standards by developing their scenarios regarding matters that can interfere with bank business activities. The internal stress test must use a longer time horizon [16].

To create a healthy banking system, the bank must develop and compete nationally and internationally [17]. Banks need to have adequate liquidity to anticipate the occurrence of crisis/stress conditions that occur on an individual bank (distinctive) and widespread stress conditions that occur in financial markets overall that can be domestic or international (market-wide shock). Banks need to increase the quantity of High-Quality Liquid Assets (HQLA) following international standards to anticipate Net Cash Outflow [18], [19], [20].

The LCR calculation scenario is a combination of idiosyncratic and market-wide shock, which will cause [18]:

• (a)Partial withdrawal from a retail deposit
• (b)The loss of some of the capacity to get funding comes from unsecured wholesale funding.
• (c)Partial loss of short-term funding sources that guaranteed by specific collateral.
• (d)Additional contractual cash outflows resulting from the Bank's downgrading up to 3 (three) notches, including collateral requirements.
• (e)Increased market volatility which has an impact on the quality of collateral or potential future risk for derivative products, thus requiring more magnificent collateral haircut, additional collateral, or other liquidity requirements
• (f)Withdrawal of unscheduled credit commitments and liquidity facilities provided by the Bank to third parties
• (g)Potential Bank needs to repurchase debt or non-contractual obligations in the interest of mitigating reputation risk.

Constraints occur at the Regional Development Bank related to daily LCR reporting. The LCR calculation process is done semi-automatically, where data withdrawals are carried out based on a schedule. However, data processing to match the OJK version of the template format working paper is still done manually. This process impacts Operational Risks, such as employee productivity, and affects other jobs that have a more significant effect on a bank-wide basis.

Operational Risk is one of the risks that must be managed by the bank [21] that occurs due to lack of and/or malfunctioning of human errors, internal processes, system failures, and/or the existence of external events that affect the Bank's operational activities and/or have a functional loss [22].

The thing to consider is the inadequate quality of the Regional Development Bank infrastructure and does not yet accommodate all categories requested by the Regulator. Including submission of reports that are still manual and require a long time. It is necessary to avoid operational risk and compliance with late reporting sanctions. It is essential to develop and create a system that can automatically report LCR daily, monthly, and quarterly basis, accurately and on time.

Problem identity

This research's priority here is to build a suitable and powerful report to measure the ultimate outcomes of the bank-regulatory reforms. Therefore, it is primary to identify what characteristics of the data model should have. The data model should allow for cross-sectional and intertemporal coordination failures that lie at core banking's heart to monitor business fluctuations and financial instability [23]. Investigating the effects of such liquidity regulation on bank balance sheets, we find cointegration of liquid assets and liabilities to maintain a minimum short-term liquidity buffer. The adjustment in the liquidity ratio is skewed towards the liability side [24].

Based on the background of the above research, the formulation of the problems that will be reviewed and analyzed include:

• (a)How can banks report Liquidity Coverage Ratio daily, monthly, and quarterly in a timely, accurate, and informative manner?
• (b)How can banks make Data Warehouse a source of data from Core Banking to be analyzed and processed automatically?
• (c)How can banks monitor and control LCR daily so that banks can anticipate decreases in ratios and regulator sanctions by seeking available risk mitigation tools?

The research objectives to be achieved based on the identified formula are:

• (a)Develop a daily, monthly, and quarterly Liquidity Coverage Ratio reporting system in a timely, accurate, and informative manner.
• (b)Extract the Load Data from Core Banking automatically and schedule into the Data Warehouse for analysis and further processing.
• (c)Analyze and process the Liquidity Coverage Ratio data sources from the Data Warehouse to produce a Liquidity Coverage Ratio Report daily to monitor and control Net Cash Outflow.

Liquidity Coverage Ratio (LCR)

Liquidity Coverage Ratio, after this abbreviated as LCR [14,25,26], is a comparison between High-Quality Liquid Assets (HQLA) with total Net Cash Outflow for the next thirty days in a stress scenario [20,27]. High-Quality Liquid Asset, abbreviated as HQLA, is cash and/or financial assets that can be easily converted into cash with little or no reduction in value to meet the Bank's liquidity needs next thirty days in a stress scenario [28], [29], [30]. Net Cash Outflow is the total estimated Cash Outflow reduced by the likely Cash Inflow expected to occur over the next thirty days in a stress scenario [31]. LCR fulfillment is determined to be at least 100% (one hundred percent) on an ongoing basis [18].

Banks are required to inform the Financial Services Authority of the Bank's liquidity in terms of not being able to meet LCR up to 100% (one hundred percent) [32], [33], [34]. Banks are required to analyze the Bank's liquidity conditions. Analyze include reasons or factors that have the potential or cause of the Bank's failure to meet LCR requirements, report on steps that have been and taken to improve liquidity conditions, and project the period of liquidity stress predicted by the Bank. The steps needed to enhance liquidity shortage conditions include reducing the Bank's exposure to liquidity risk [35], strengthening the Bank's liquidity risk management policies [34], processes and procedures, and/or improving the Bank's contingency funding plan [26,36].

The components of the Liquidity Coverage Ratio are divided into several categories, namely [18,26] High-Quality Liquid Asset (HQLA), Cash Outflow, and Cash Inflow.

The determination of the LCR aims to ensure that the bank has sufficient HQLA stock, consisting of cash or assets that quickly converted into cash. Mainly, this HQLA stock meets liquidity needs in the 30 days calendar period scenario.

$$\text{LCR}\text{calulation}\text{formula}:\frac{\text{High}\text{Quality}\text{Liquid}\text{Asset}}{\text{Net}\text{Cash}\text{Outflaws}\text{in}30\text{days}}$$

Banks are required to calculate and report LCR on a daily, monthly, and quarterly basis. Banks must submit daily LCR reports online through the Financial Services Authority Online Reporting Application (APOLO). Banks are required to prepare monthly LCR reports based on the daily average of the report. Banks that do not meet reporting obligations and violate regulatory provisions are subject to administrative sanctions in the form of [18]:

• (a)Written warning.
• (b)Prohibition of profit transfer for branch offices of banks domiciled abroad.
• (c)Postponement of distribution of dividends on all share ownership of shareholders making capital contributions.
• (d)Freezing of certain business activities.
• (e)Ban on opening office networks.
• (f)Decreasing Bank soundness.
• (g)Inclusion of Bank management and/or shareholders in the list of prohibited people from becoming Bank shareholders and control according to the provisions governing the fit and proper test.

In addition to administrative sanctions, Banks that are late in submitting LCR reports are subject to sanctions in the form of fines of Rp. 1000,000. - (one million rupiahs) per working day of delay or a maximum of Rp. 50,000,000. - (fifty million rupiah).

Extract, Transform, Load (ETL)

Along with developing networks and the internet, many systems have been built that operate in real-time and online. This situation is allowing people to access data and get information. Extract, Transform, Load (ETL) is a set of processes passed in forming a data warehouse. ETL aims to collect, filter, process, and combine relevant data from various sources/databases to be stored in a data warehouse. Following is an explanation of each process in ETL [37], [38], [39].

Extract, Transform, Load (ETL) is highly demanded, especially in a comprehensive and heterogenic network. Many vendors facilitate operating systems and applications used in a computer, and users are asked to fill in the information in each of the different platforms they intended to use [38]. Extract, Transform, Load (ETL), an automated process that takes raw data, extracts the information required for analysis, transforms it into a format that can serve business needs, and loads it to a data warehouse. ETL typically summarizes data to reduce its size and improve performance for specific types of analysis. When you build an ETL infrastructure, you must integrate data sources and carefully plan and test to ensure you transform source data correctly [40,41].

The data warehouse is not possible without ETL because the process is the foundation of a data warehouse [37,38]. This ETL process is fundamental because it plays a significant role in the quality of data in the data warehouse so that the data warehouse is used for business intelligence or other analytical activities. As a series of processes, the data warehouse is divided into several stages includes Data Cleaning, Data Integration, Data Selection, Data Transformation, Data Mining Data Process, Pattern Evaluation, and Knowledge Presentation, which are interactive. Users are directly involved or through the knowledge base [42], [43], [44].

An ETL process runs properly if the process involves several things: extracting data from a source, maintaining the quality of the data, applying standard rules, and presenting data in various forms used in the decision-making process.

Core Banking

Core Banking is one of the vital elements making up information technology at the Bank [45]. Core Banking is the heart of a bank [17]. Includes and is not limited to various things such as algorithms used [46], risk control assessment [47], and various capabilities, both limited and optimized from the expert system side [47]. In the core banking data stored customers and their accounts and all transactions, the customer opens an account at the bank to close it. Core Banking System is a back-end system that processes bank transactions every day and updates accounts and other financial data [48,49].

Core Banking is generally associated with retail banking. The most fundamental core banking function is to serve customers for funding, lending, and money deposit needs. Another role of core banking is to record all transactions in customer accounts in the form of savings, loans, housing loans, and payment transactions. Access to core banking is done through many channels such as Teller (Branch), ATM, Internet Banking, Mobile Banking, and others [50,51].

At this time, access to core banking is done in real-time through an online process. The online process is supported by advances in computer and communication technology that allow information to be shared between branches quickly and efficiently. A few decades ago, it usually took at least one day to see transactions in accounts because each bank branch has its server, and new data updated through the End of Day (EoD) process that sends branch data in the batch form to the data center [52].

Core Banking stands for ``Centralized Online Real-time Electronic Banking'', which means that all banks will access this application from a centralized data center. This system does those transactions that occur immediately updated to the bank's server, and each customer can make transactions from any branch around the world. With this progress on the retail side, corporate customers tremendously benefited, and banks can provide comprehensive bank solutions for corporate customers [53,54].

This study is qualitative research, which approaches used to understand an issue or problem using a case [55]. The situation can be an event, process, activity, program, or one or several people. Furthermore, to understand the issue or problem in-depth, this study investigates and explore one or several cases in a certain period and collect data from various sources (observations, documents, reports, or interviews)

Based on the number of cases and the purpose of doing a case analysis, the case study approach is divided into three types, they are Single instrumental case study, Collective or multiple case study, and Intrinsic case study.

The procedure for carrying out case studies as adapted from Stake [55] is as follows:

• (a)Ensuring that an issue, case, or problem is suitable for research using a case study approach. It noted that the case study approach is proper when a case under study is an identified cause and when the researcher wants to gain an in-depth understanding of one or several instances with certain limitations.
• (b)Choose a situation and type of case study to use. The selected case should be a case that can show various points of view of the problem or event photographed.
• (c)Collect data from various sources through observation, in-depth interviews, or from documents.
• (d)Conduct analysis of data collected. Data analysis do holistically or accurately.
• (e)Perform interpretation. The researcher reports the results of the meaning of a case.

Waterfall model

With clear rules, the Software Development Life Cycle (SDLC) used in this study is the Waterfall model, which is a linear-sequential life cycle model [56,57]. The Waterfall Model was the first Process Model to be introduced. It is referred to as a linear-sequential life cycle model. It is straightforward to understand and use. Each phase is completed in a waterfall model before the next step can begin, and there is no overlapping in the levels. The Waterfall model is the earliest SDLC approach used for software development.

The waterfall Model illustrates the software development process in a linear sequential flow. This process means that any phase in the development process begins only if the previous stage is complete. In this waterfall model, the steps do not overlap.

Some situations where the use of the Waterfall model is most appropriate are:

• (a)Requirements are very well documented, clear, and fixed.
• (b)The product definition is stable.
• (c)Technology is understood and is not dynamic.
• (d)There are no ambiguous requirements.
• (e)Ample resources with the required expertise are available to support the product.
• (f)The project is short.
• (g)The following Fig. 1 is an illustration is a representation of the different phases of the Waterfall Model.

Fig. 1.

ETL Process in Waterfall Model.

The sequential phases in the Waterfall model are [58]:

• (a)Requirement Gathering, all possible requirements of the system to developed are captured in this phase and documented in a requirement specification document.
• (b)System Analyst & Design, the requirement specifications from the first phase studied in this phase, and the system design prepared. This system design helps in specifying hardware and system requirements and helps in defining the overall system architecture.
• (c)Implementation, with inputs from the system design, is first developed in small programs called units, which are integrated with the next phase. Each group developed and tested for its functionality, which is referred to as Unit Testing.
• (d)Integration and testing, all the units designed in the implementation phase integrated into a system after testing each group. Post integration of the entire system checked for any faults and failures.
• (e)System Deployment, once the functional and non-functional testing is done, the product is deployed in the customer environment or released into the market.
• (f)Maintenance, some issues come up in the client environment. Patches are released to fix those issues, some better versions are released to enhance the product, and maintenance is done in the customer environment to deliver changes.

Method validation

Liquidity Coverage Ratio Worksheet. LCR working papers are divided into three main categories: HQLA, Net Cash Outflow, and Net Cash Inflow. Each component has three parameter values: Haircut or Run-off Rate or Inflow Rate, Outstanding Value or Market Value, and Value after Haircut or Run-off Rate or Inflow Rate. The appearance of working papers seen in Fig. 2 below.

Fig. 2.

LCR worksheet.

Expected outputs from this study include:

• (a)Provision of Report Templates. Provision of templates for ready-to-use reports complied with following Basel III and OJK provisions, which consist of daily, monthly, and quarterly report formats.
• (b)Comparison menu. This menu is used to compare the results of stress tests (before and after) then compare LCR data for the past 90 days.
• (c)Export Reporting. Reports exported into several file extensions, especially Ms. Excel and pdf.

The workflow implementation of the LCR application development is seen in Fig. 3 below.

Fig. 3.

Implementation workflow method.

Infrastructure requirement

Hardware recommendations for production environments recommended based on ETL process standards in banking are seen in Table 1 below.

Table 1.

Production environment requirement.

Specification	Application Server	Calculation Server	Database Server
Number of Server	1	1	1
CPU	2x Intel e5–26xx (dual socket) or Intel e5–46xx (quad socket) with total of 16 cores or more	2x Intel e5–26xx (dual socket) or Intel e5–46xx (quad socket) with total of 16 cores or more	2x Intel e5–26xx (dual socket) or Intel e5–46xx (quad socket) with total of 16 cores or more
Memory (GB)	64 GB	64 GB	64 GB–128 GB - SQL Server: 28 GB - Analysis Server: 28 GB - Windows & Other: 8 GB - Additional 64 GB added for ETL
Diskspace (GB)	100 GB	100 GB	2.0 TB for Data and Log - System- SAS 15k Disk 200 GB size (RAID1) - Log- SAS 15 k Disk 800 GB Size (RAID1) - Data- SAS 15 k Disk 1000 GB size (RAID5 or 10)
Architecture	x64	X64	X64
Windows	- Windows Server 2012 R2, or Windows Server 2016 - .Net Framework 4.6.2	- Windows Server 2012 R2, or Windows Server 2016 - .Net Framework 4.6.2	- Windows Server 2012R2, or Windows Server 2016 - Net Framework
Database	-	–	- MS SQL Server Business Intelligence Edition 2012/2014 or - Enterprise Edition 2012/2014 or - Enterprise Edition 2016
Software	- Internet Information Service (IIS) - Microsoft Excel (2010 or 2013) - Microsoft Internet Explorer 8/9/10/11 with Java Enabled - Visual C++ Runtime Libraries	Visual C++ Runtime Libraries

Fig. 4 below show the technical architecture, which consists of several components and relationships between elements. The configuration described is the technical architecture used in general and applied to a variety of installations.

Fig. 4.

Technical architecture.

The architectural drawing solution above consists of three essential components—client Workstation connected to the Application Server via Terminal Services or Citrix Application. Application Server transfers all computing tasks to Calculation Server. Application and Calculation Server retrieve and store data on the Database Server.

This application server is the central part of the server and connected to Terminal Services (Remote Desktop Connection) or Citrix. This component compiles the calculation process. The MS SQL Host Database Server is a database that is needed by this solution. The database's hardware specifications and configuration depend on the Position number / Instrument type, Number of archived Focus analyzes, and Cube reporting requirements (Results DB). Calculation Server performs calculations as instructed by the Application Server. Additional Calculation Server added to achieve good scalability.

Extract, Transform, Load Process from Core Banking

The Extract, Transform, and Load process in Core Banking seen in the following stages:

• (a)The method of uploading an Excel template to be stored in the LCR application database (Fig. 5)
• (b)The Process of Mapping Data to Data Mart (Fig. 6)
• (c)LCR Data Calculation & Aggregation Process (Fig. 7)
• (d)Upload LPS Data & Rate as Material for LCR Calculation (Fig. 8)
• (e)Data Warehouse Withdrawal Process to Database Staging (Fig. 9)
• (f)The Calculation Process for LCR Data Preparation (Fig. 10)
• (g)CR Data Final Calculation & Aggregation Process (Fig. 11)

Fig. 5.

Upload excel template process.

Fig. 6.

Mapping data to data mart.

Fig. 7.

LCR data calculation & aggregation.

Fig. 8.

Upload LPS data & rate.

Fig. 9.

Data warehouse withdrawal process to database staging.

Fig. 10.

The first calculation process for LCR data preparation.

Fig. 11.

LCR data final calculation & aggregation process.

Interface Implementation

The implementation of the ETL process in Core Banking through the LCR application developed, can be seen as follows:

• (a)The method of importing data from Input DB seen in the following Fig. 12.
• (b)The Generate Analysis process in the following Fig. 13.
• (c)Stage Clean-Up Process in the following Fig. 14.
• (d)Parameter Setting Process in the following Fig. 15.
• (e)LCR Report Generate Process seen in the following Fig. 16.

Fig. 12.

Data importing from input DB.

Fig. 13.

Generate analysis.

Fig. 14.

Stage clean up.

Fig. 15.

Parameter setting.

Fig. 16.

LCR report generate.

Based on the research results raised through this article, it is proven that the resolution of Basel III-related reporting problems can be solved using the Extract, Transform, Load (ETL) method from Core Banking to produce a Liquidity Coverage Ratio report.

The process that has been carried out and has been proven includes the Liquidity Coverage Ratio system generates reports daily, monthly, and quarterly in a timely, accurate, and informative manner. Then Extract Transform Load process data sources from Core Banking automatically and scheduled into the Data Warehouse for further analysis and processing. Furthermore, analysis and processing of Liquidity Coverage Ratio data sources from Data Warehouse produce Liquidity Coverage Ratio Reports daily to monitor and control Net Cash Outflow.

This article has provided a new method for completing Basel III-based reporting for banks through ETL. Banks can replicate this method by adjusting based on data and information processed based on applicable regulations. Using the method presented in stages from each step in this article, banks have a new method for creating and completing Basel III-based reporting.

Suggestions recommended for the research about Liquidity Coverage Ratio in the future be follow adding what-if analysis function to support the analysis of liquidity adequacy under stress conditions by adopting several scenarios. Moreover, adding the Net Stable Funding Ratio (NSFR) calculation as an analysis material related to more overall liquidity.

Declaration of Competing Interest

The Authors confirm that there are no conflicts of interest.