Optimizing OLAP Cube for Supporting Business Intelligence and Forecasting in Banking Sector

Introduction

On-Line Analytical Processing is a powerful tool for performing data analysis, complex analytical calculations, predictive analysis and forecasting business decisions. All Applications based on Business Intelligence (BI) have common technology behind them i.e. On-Line Analytical Processing (OLAP). It is an intricate tool which provides user analysis for making decisions for huge amount of data (Chaudhuri & Dayal, 1997). It follows a multidimensional strategy to arrange and analyse the information. Thus, due to its multidimensional view it has a very pivotal role in taking important business decisions. On-Line Analytical Processing allows end-users to carry out ad hoc prognosis of knowledge in more than one dimensions, thereby offering the perception and working out they want for higher resolution making. The idea of the decision support system represents the visualization of complex data in a highly structured manner. Considering the example of education institutions, (Tohir et.al, 2017) has used the On-Line Analytical Processing tool for modelling and representing graduation data of students to simplify the searching of students for business placement centre in an educational institute. Data mining techniques can also be used for measuring the performance of students in educational institutions (Aggarwal et.al, 2019).

On-Line Analytical Processing can also be considered as a software technology which helps the executives and analysts to look in the historical data through fast, precise and interactive access to large amount of feasible views of information extracted from raw data in order to represent different organization dimensions with the users perspective (Gupta, 2012). On-Line Analytical Processing technology has been used to improve the process of taking optimal management decisions with the help of algorithms considering the forecasting of the accepted management decisions on the basis of the resources of the enterprise (Pishchukhin & Akhmedyanova, 2018).

On- Line Analytical Processing basically consists of multidimensional data from various sources thus it needs a special method of storing data (Pedersen & Jensen, 2001). For storing data in a tabular format, spreadsheets were used but in case of multidimensional data, On-Line Analytical Processing cubes are used. The cubes are structured in such a way that various data types from different sources are stored and analysed in a logical manner one by one. On-Line Analytical Processing technology is being used in various areas like it is being used in construction business for predicting business decisions (are using this technology (Konikov et.al, 2018) and on the other hand the performance of the students and their eligibility to pass the graduate examination has been analysed using data warehouse On-Line Analytical Processing technology (Chandra et.al, 2018). As the data is voluminous, Hadoop and map reduce algorithms are used for storage and retrieval of software components (Singh & Bali, 2017).

The financial sector performs many services which are all customer centric. The services offered by banks may range from simple deposit and withdrawal of money to offering loans and maintaining fixed deposits of the customers. For providing any service to the customer bank needs to introspect the consumer information and credential and then figure out whether the requested service shall be provided to the customer or not. Cheque verification is one of the crucial services of bank officials. For speeding up this decision process of offering services, On-Line Analytical Processing serves as a powerful tool to verify and validate the parameters associated in verifying the credentials of the customer in order to provide him the banking services (Berson & Smith, 1997). The functionalities of the analytical tools can be enhanced by applying semantic web technology to analytical tools. Thus, business rules can be flexibly represented to get pro-active information and suggestion during decision making process.

Through this research paper, we have tried to define the parameters for accessing the credentials of the customers by performing three-dimensional analysis on the given data set which can help in forecasting and predicting sound financial decisions in the banking sector using the On-Line Analytical Processing tool.

Related Work

Upon processing of the business data, it turns out that On-Line Analytical Processing, and along with data warehousing environment, are two of probably meaningful unexplored technological areas (Thomas & Datta, 2001). The essential good thing about the On-Line Analytical Processing repository is that it does not take very long time to carry out diagnosis and come across developments and patterns throughout the business, but the downside is that information will not be current to qualify for real-time information being used for business intelligence functions (Berson & Smith, 1997). On-Line Analytical Processing allows end-users to carry out prognosis of knowledge on more than one dimension, thus offering just the right perception for working out of information thereby serving to in higher decision making, predictive and forecasting analysis (Mathur et al., 2016).

On-Line Analytical Processing in Business Intelligence: Business Intelligence (BI) is the new unearthed technology for figuring out the previous and predicting the long-term trends. Business Intelligence is used to increase the performance of the organization by using and comprehending the existing data and converting it into valuable information (Osterfelt, 2000). It is an extensive class that permits gathering, storing, gaining access to & examining information to lend a hand to customers to obtain higher decisions.

On-Line Analytical Processing is a device used by retailer to organize information that will be used to generate facts which will probably be used for decision making. With respect to Business Intelligence, On-Line Analytical Processing sits between the data warehouse and the analysts. On-Line Analytical Processing through Business Intelligence remodel the information into multi-dimensional cubes. On-Line Analytical Processing summarizes pre-aggregated and derived data. It supplies fundamental constructing blocks to allow analysis (Mansmann, Neumuth, & Scholl, 2007). Mainly the top executives in an organization work on recording data through Mining Tools, efficiency reporting tools, etc. which makes On-Line Analytical Processing a consumer industry intelligence interface. Experiments to analyze data with respect to their organizational processes and the organization data was addressed by (Ravishankar, 2013; Gurudatt, Ravishankar, & Jayathirtha, 2013).

On-Line Analytical Processing in Banking Operations: On-Line Analytical Processing has pivotal role in banking operations. Due to tough competition, various services are provided by the banks to attract customers. Lot of changes has occurred recently in the field of banking industry. The Banking system is reassembling the information exchange through Internet as the medium of executing transactions. The growth in Information and Communication technology has led to two different sectors in banking. Firstly, vast improvement in communication and connectivity and secondly, in reengineering of business process, both focusing on customer centric market growth (Bharathi & Akolkar, 2004).

The bank intelligence represents the process of gathering, managing and analysing huge amount of data of a bank related to its customers, services, operations and all transactions among them. Thus, banks are adopting innovative ideas for improving their services and to acquire faith of their customers. Thus, On-Line Analytical Processing technology holds a great importance and significance in day-to-day functions of a bank (Mathur et. al, 2018).

New Generation in Business Intelligence: Business Intelligence focuses on structured data as it would provide meaningful information to the business analysts but on the other hand visualizing external data for contextualizing analysis of data may provide huge benefits to the business environment (Pérez-Martínez et.al, 2008). Few bank areas covered under Business Intelligence are Customer Relationship Management (CRM), Asset and Liability Management (ALM), Risk Management (RM), Performance Management (PM) and Compliance. On-Line Analytical Processing data warehouse are the informational basis for the application of business intelligence (Ubiparipović & Đurković, 2011). Thus, with the advent of the technological revolution, tools are being developed for clearing the bank cheques with minimum human intervention (Agrawal et al, 2020). Image processing techniques are being used to validate the clearance of bank cheque (Chaudhary et al., 2019). Likewise the data analysts should study the voice of the customers along with the market voice to overcome competitors. This will help in incorporating the opinion of the customer and shall help in identifying market scenarios that affect the organization business. Cloud computing is being used for data warehouse application in order to make data warehouse as a service. An architecture has been proposed for Cloud Data warehouse (Kaur, 2012) in order to expand the business intelligence services. Many applications have been developed to perform analysis on social space. The widespread use of social websites like Facebook, Twitter and YouTube have provided a platform to study views of customer and market data. Thus, in-depth analysis can be done of external business data like customer blogs, Facebook news feed and other data sources. An effective BI application should be able to collect the data from in different formats from these operational databases, web logs, websites of social media and other useful sources.

Methodology

Data warehouse is a “subject-oriented, integrated, time varying, non-volatile” sequence of knowledge that is used essentially in organizational decision making (Inmon, 2005) a knowledge warehouse is utilized by organizations to offer environment friendly records diagnosis to decision makers (Senn, 1997). A conventional three tier structure of knowledge warehouse the place on the initial stage the info is gathered from plenty of records sources (any group database e.g. CRM, Bank, ERP Systems, etc.) after which reworked into the specified layout via Extract, Transport and Load (ETL) course of on the second level. Then the specified information is saved in the data warehouse and on the third level, information diagnosis is carried out utilizing determination enhance programs and reports are generated for the customers for the aim of deterministic decision making.

On-Line Analytical Processing is an expertise that allows resolution enhance and reporting mostly upon a data warehouse (Codd, Codd & Salley, 1993). On-Line Analytical Processing is a preferred tool used to build the decision support systems (DSS) (Hasan & Hyland, 2001). OLAP is legendary for the quick prognosis of communal multidimensional information. It is far quick as a result of it takes practically 5 seconds to ship a lot of the process responses to the users, whereby the most simple prognosis doesn't seize greater than 1 second and only a few prognosis taking greater than 20 seconds. However, the rate of the process utilizing On-Line Analytical Processing may change resulting from manner hardware configurations also. the most important function of On-Line Analytical Processing is that it offers a multidimensional, conceptual view of the data, together with complete improve for hierarchies and a couple of hierarchies.

A diagrammatical illustration of On-Line Analytical Processing interacting with the data warehouse is represented in Figure 1. Its structure is underlined on the design of a cube (Gray et.al, 1997). A cube is outlined by any selection of information dimensions which is just not microscopic to three and from time to time the On-Line Analytical Processing cube will have lower than three dimensions also. the information dimensions of an On-Line Analytical Processing dice cover the width, height, and depth of the info attributes which shall be organized into any collection of stages (hierarchies). Thus, an On-Line Analytical Processing cube is amalgamation of details and dimensions. It stores the ideas and lets in looking at totally conceptual levels. Data in an On-Line Analytical Processing cube is arranged around indicators (named as measures) and the analysis axis (named as dimensions). The dimension attributes can be either descriptive or can be in the form of a hierarchy. Views of the data can be obtained at different levels of granularity with the help of hierarchies and can be summarized or represented in detail with the help of roll-up and drill-down operations respectively.

Figure 1. OLAP and Data Warehouse Framework

Implementation

A case study of bank data base of loan department for four years 2015-2018 has been considered for analytical purpose where historical bank data is considered in the database. The database consists of records of customer who have applied for loan in the bank. Two branches of the bank – North branch and South branch have been considered for better result and reporting analysis. Thus, this research paper models the loan approval process of the bank based on certain parameters and defined business rules to make it efficient and fast.

Customer Data Set

Data warehouse is a data repository which consists of records collected from numerous data sources, integrates, and cleans data using the Extract, Transform and Load process (ETL). Thus, for our research we have considered a loan department bank database and time period considered is previous four years of various branches of the bank. The data source files can exist in .csv format and are stored in the data warehouse. As we are considering a data warehouse thus the banks database would not consist information of present financial transactions but shall consist of historic financial records. After data collection, using Extract, Transform and Load (ETL) operations data is cleaned, transformed and converted into the final format for performing data analysis. Thus, the quality of data increases after the ETL operation as all redundant, null, invalid and inaccurate data is filtered from the bank database.

Table 1 Bank Customer Dataset

The hypothetical set of bank customer database is represented in Table 1. On-Line Analytical Processing as a tool can be used for expediting the loan approval process thereby enhancing business intelligence in banking processes. Thus, this research paper models the loan approval process of the bank based on certain parameters and defined business rules to make it efficient and fast.

Data Warehouse Schema and Data Cube

The database of loan department of data warehouse includes multidimensional database where On-Line Analytical Processing (OLAP) can be used to visualize a data cube from the given dataset. On-Line Analytical Processing is multi-dimensional information modelling program and it represents the information as a data cube (Dev& Mishra, 2011).

Figure 2. Schema for Loan Management Database

The data cube is represented as an amalgamation of facts and dimensions as represented in Figure 2. The data used the data cube can be analysed at various conceptual levels.

Figure 3. Loan Management Data Cube

Various parameters for approving the loans are predefined as represented in Figure 3 as a data cube. Data cube represents substantial banking information that can be utilized by the administrator or the management analysts.

The performance of the loan greatly impacts the profitability and stability of a bank. Loan losses have significantly increased over the years increasing the risk of failure of the bank (Keeton & Morris, 1987). Thus, loan losses sharpen the evaluation of the banks (Bhat et al., 2019). Available Thus if the loans are approved or denied based on certain business rules which are predicted by analysing the customer credentials defined according to the business rules, there is a probability to a large extent that the approval of loan can be streamlined and the rate of risk of failure of a bank may decrease. To ensure a safe and sound banking system we need to decrease the bank failures as they affect the society to a large extent and thereby, we should focus on increasing the profitability and growth of the bank.

For approving of loans, customer and bank information along with the loan approval parameters are considered. Various granular level of the system can be seen through On-Line Analytical Processing operations. In this case a hypothetical bank database has been considered and data cube is implemented through queries. The management uses the final reports and graphical representations for analysing data and making decisions.

Assumptions for the Model Formulated

The decision to approve a loan is taken based on certain specific parameters as defined below:

1. Age – The age of the customer is the key factor which evaluates the loan premium amount to be charged along with the repayment period required.
2. Credit Score – It represent the credit history of the customer represented as a three-digit number. It is also called as CIBIL Score and it ranges from 300-800 points.
3. Experience – It evaluates the experience of working of the customer in an organization.
4. Monthly Salary – It is the total monthly salary generated for a customer and net income can be computed for a financial year. It helps in determining the maximum loan value.
5. Loan Amount – It is the amount of money which the customer borrows from the bank.
6. Years of repayment – It is the time period required by the customer to repay the loan taken from the bank.
7. Down Payment – It is the direct initial payment amount that the customer gives, and the remaining amount is converted as loan. Monthly installment is less if down payment is more and interest paid to the bank is also less and vice versa.
8. Collateral – An asset as a security for loan is given to the bank. The bank can seize the collateral and resell it to recover its loss if the borrower defaults on the loan payment.

Table 2 Loan Approval Process Parameters

The conditions for sanctioning of the loan amount is shown in the Table 2. In lieu of the above parameters and the business rules defined for the loan approval process as represented in Figure 4 and decision can be taken for sanctioning of loan to the customer. Thus, with the help of business intelligence, sound business decisions can be taken.

Results and Discussion

The analytical goal of the application is to determine the customers to whom the loan should sanctioned. Based on the business rules all the parameters are considered as input variables, that are checked for the loan approval process before sanctioning of loan to any customer. If the customer fulfils all the criteria’s then finally the loan is sanctioned by the bank. The business analyst can choose any of the business rules as shown in Figure 4 and test data will get loaded in data warehouse and result is generated. Reports can be generated on the basis of the complex queries using aggregation executed using the On-Line Analytical Processing for generation of the customer satisfying the chosen business rule.

Figure 4. Business Rules for Loan Approval

Evaluation of credit risk is a crucial factor in the success and failure of banking industry. The evaluation of credit has still been a challenge for all the banks till now. Credit score which can also be called as CIBIL score of an individual is computed after undergoing various process of evaluation (Abdou & Pointon, 2011). Credit evaluation is based on five C’s which are – character, credit report, capacity, cash flow and collateral (Purohit & Kulkarni, 2011). Thus, after evaluating an individual on these factors CIBIL score is generated based on which the loan is approved or rejected. By using On-Line Analytical Processing and executing complex queries reports can be generated by choosing one of the parameters for loan approval process.

The decision-making process consisting of business rules of loan approval process is shown in Figure 4. The decision algorithms can then be applied using the business rules. The data which needs to be analysed can be then straight away fed into the On-Line Analytical Processing cube for decision making purposes.

Figure 5 represents the choosing of CIBIL Score as one of the parameters of loan approval. If the CIBIL Score is low the person is denied loan and loan is sanctioned for an individual whose CIBIL Score is high. The reports can be generated using On-Line Analytical Processing by execution of complex queries to generate the valid records for the parameter selected for the loan approval process. The reports generated can be visualized graphically for analysis purposes.

The reports are generated for a time period from 2015 to 2018 for South branch of the bank are shown in Figure 6 and for North branch in Figure 7. Policies and decisions can be made by studying the visualization by the higher management.

The loan applications qualifying the parameters for the approval of loan for the period of four years is being depicted in Figure 6 and Figure for the South branch and North branch of the bank respectively. Now we consider the CIBIL Score parameter as one of the important criteria for approving the customer loan. Based on this parameter the loans are approved quarterly by the bank.

Using OLAP, reports can be generated for the north and south branch of the bank based on CIBIL score parameters as represented in Figure 8 and Figure 9 respectively for the four quarters Q1, Q2, Q3, Q4 of the year 2015-2018. Similarly, reports can also be generated for all the parameters taken into consideration. Comparison of both the bank branches can be done - the south branch and the north branch. It will be reflected through the graphical analysis wherever the bank branches are facing competition and lacking in their growth. Thus, higher management can focus more on those parameters and help the bank to increase their revenues and compete with their competitors.

Data can be seen as in a multidimensional format using OLAP which can help in decision making process. Some outcomes can be predicted after analysis of the graphical representation as represented in Figure 8 and Figure 9. The outcomes are based on the CIBIL parameter for the bank over the period of four years.

• The CIBIL score has drastically increased in the south branch as compared to the north branch representing that the repayment capacity of the customers have increased in the south branch area.
• Thus new schemes should be launched in north branch of the bank to attract customers having higher incomes and motivate them to take loans from the bank providing them loans at a lesser interest rate compared to other bank branches.

Thus, by using OLAP technology, which provides a multidimensional view of the data higher management can forecast and predict various outcomes from the bank data warehouse. Reports can be analyzed and patterns can be studied by the analysts to predict business decisions. Existing policies of the organization can be updated by the management to increase the profitability of the bank. The developed bank model can help in increasing the potential customers to apply for loans. Reserve requirements of the bank can be evaluated from the loans granted to customers. Performance of each branch can be evaluated which can help the bank to identify new selection parameters to extract more knowledge from data warehouse. For future work more test cases can be generated to execute on the data warehouse and results can be analyzed to further enhance banking practices and operations.

Discussion

To stimulate the framework proposed in this research paper a case study has been formulated. A small-scale bank having two branches have been considered in this case study. The two branches are named as North Branch and South Branch. The time period to analyze the results has been taken for four years from 2015 to 2018. A hypothetical customer data set consisting of 4000 records for a period of four years, 2015-2018 is considered for analysis and decision making.

The parameters that are to be considered for the approval of loan are mentioned below and on the graphically it is represented in Figure 6 and Figure 7.

1. Age – Loan can only be approved if the age of the customer is between 25-60 years. The customer loan applications that qualified the age criteria in 2015 are 587, 2016 are 878, 2017 are 457 and 2018 are 965.
2. Credit Score –The range of the CIBIL Score should be in between 300 – 900 for qualifying this parameter for approval of the loan. Loan is not approved for customer whose CIBIL Score is less than 300. The customer loan applications that qualified the CIBIL Sore criteria in 2015 are 954, 2016 are 785, 2017 are 654 and 2018 are 1045.
3. Experience – Working experience of more than 4 years is required for approval of the loan. The customer loan applications that qualified the Experience criteria in 2015 are 879, 2016 are 457, 2017 are 657 and 2018 are 987.
4. Monthly Salary – The salary of the customer should be above Rs. 15,000 for approval of the loan. The customer loan applications that qualified the Monthly salary criteria in 2015 are 857, 2016 are 785, 2017 are 475 and 2018 are 854.
5. Loan Amount – The eligibility of loan amount is 60 times of net monthly salary. The customers whose loans have been approved on the basis of monthly salary, their loan amount is also approved.
6. Years of repayment – The eligibility for sanctioning of loan is high is repayment period is shorter. The customer loan applications that qualified this criteria in 2015 are 754 2016 are 235, 2017 are 478 and 2018 are 685.
7. Down Payment – Minimum 10% down payment is mandatory. The customer loan applications that qualified the down payment criteria in 2015 are 568, 2016 are 754, 2017 are 245 and 2018 are 854.
8. Collateral – An asset as a security for loan is given to the bank. The bank can seize the collateral and resell it to recover its loss if the borrower defaults on the loan payment. The customer loan applications that qualified the collateral criteria in 2015 are 562, 2016 are 865, 2017 are 984 and 2018 are 754.

The Steps for Implementation of the Case Study are as follows:

1. A hypothetical customer data set for a bank is considered.
2. Data is collected at the source it is cleansed and transformed into the desired format consisting of required attributes of the bank database.
3. On-Line Analytical Processing cube is used to view data multidimensionally.
4. The parameters for approving loan are considered.
5. A loan is approved on the basis of the business rules defined.
6. The analytical goal of the model is to find customers to whom loan can be sanctioned.
7. Reports can be generated by selecting any of the parameters of the loan approval and these reports can be used for decision making purposes
8. Reports can also be generated for the last four years for customers whose loan has been sanctioned in the various branches of the bank.
9. Two cases have been considered and compared in the case study:

   Case 1: Loan applications for North branch of the bank

   Case 2: Loan applications for South branch of the bank

9. All parameters of the loan approval process are compared graphically for both the bank branches.
10. The visualization can help the higher management in framing policies and taking decisions.
11. The graphical analysis and visualization can help the bank to increase their revenues and in framing policies to compete with their competitors.

Conclusion

On-Line Analytical Processing is an extremely highly effective software that has revolutionized in which records is saved and mined. Industry intelligence functions play a very important function in companies at this time and own enabled companies to achieve a perception in records besides analyze them at very tall speeds. On-Line Analytical Processing just isn't most effective a device that's utilized in industry intelligence but utilizing it with determination enhance algorithms; it may well be used for determination making purpose. Banking operations sectors sort out big quantity of quantity of knowledge and require quick selections to be made. On-Line Analytical Processing gives the suitable choices in response to the information and procedures implemented at impulsively elevated speeds. This range of choice making is very vital for the banking sectors. By utilizing such tools, appropriate choices will probably be made at the appropriate time expeditiously that permits the group to earn earnings and fare higher with their competitors. It can indubitably be regarded as an excellent monetary instrument that can vary the best way companies used to function. Existing policies of the organization can be updated by the management to increase the profitability of the bank. The developed bank model can help in increasing the potential customers to apply for loans. Reserve requirements of the bank can be evaluated from the loans granted to customers. Performance of each branch can be evaluated which can help the bank to identify new selection parameters to extract more knowledge from data warehouse. For future work more test cases can be generated to execute on the data warehouse and results can be analyzed to further enhance banking practices and operations. The focus of the future work will be analysis on prediction of Non-Performing Assets (NPA) and Loan-To-Value (LTV) of loan department of the banks.