Banking Products

BANKING PRODUCTS

AMEY ARAS

DEEPESH DHAKE

HATEM MURAD

NIRAV HIMLAI

Abstract:

The aim of this paper is to discuss how we build or datawarehouse and OLAP cube for Banking Products. As top level management want to make strategical decisions we have prepared this datawarehouse. To develop the datawarehouse there were 4 members assigned.

Introduction:

Banking Products keeps the data about the customers, branch and the products of the bank. These are the products of the “HAND” Bank. The purpose of this datawarehouse is to extract all the data from the transactional system and store it in the datawarehouse. . While the concept of a data warehouse sounds quite simple, in reality it is almost completely opposite. To store the data in the datawarehouse first the data should be transformed to the common format then cleansing should be done to allow it to generate queries and reports to take some strategical decisions.

Case Study: Banking Products

Business Scenario:

“HAND” Bank was established in 2009. It has around more than 20 branches in all around the United States. The Headquarter of this branch is in the Houston. The bank managers in each branch want to know to whom the credit card and loan should be given if they are applying for the same. They also want to find out most profitable customer, profitable branch etc. All the data of the customers are stored in the transactional system from where the bank managers cannot make any decisions. So, for that we require to build the datawarehouse to generate some strategical reports.

Why Datawarehouse?

HAND Bank’s managers need some useful strategical information to take decisions. The idea of the data warehouse is to extract the data from the transactional system and then transform the data into the common format using mapping concept. Then to load the data in the datawarehouse.This what the Bank managers are looking for. Managers need to query the datawarehouse and then generate the reports such as credit card and loan approval. This is what exactly the datawarehouse tools provide. The data in the datawarehouse is stored in such a way that managers will gets the strategical information of the customers.

Methodology:

Initially we did not have a clear idea about the datawarehouse for the bank. So, first we developed the transactional database so that we can extract into the datawarehouse. We used the bottom up approach to build the datawarehouse. We prepared first the small data marts to prepare the entire datawarehouse. Bottom-up approach is flexible and as this approach help to learn the building of datawarehouse very efficiently we used this approach. Each and every team member learned building of the datawarehouse. To prepare the transactional database we developed the mappings between the tables.

Dimensional Modelling and defining Data Structure:

Dimensional modeling is the design concept used by many data warehouse designers to build their data warehouse. Dimensional model is the underlying data model used by many of the commercial OLAP products available today in the market. In this model, all data is contained in two types of tables called Fact Table and Dimension Table. In a Dimensional Model, Fact table contains the measurements or metrics or facts of business processes. the measurements, the only other things a fact table contains are foreign keys for the dimension tables. In a Dimensional Model, context of the measurements are represented in dimension tables. the Dimension Attributes are used in report labels, and query constraints. The dimension attributes also contain one or more hierarchical relationships.Before designing your data warehouse, you need to decide what this data warehouse contains. In computing, the star schema (also called star-join schema, data cube, or multi-dimensional schema) is the simplest style of data warehouse schema. The star schema consists of one or more fact tables referencing any number of dimension tables. The star schema is an important special case of the snowflake schema, and is more effective for handling simpler queries.

STAR SCHEMA

Above figure is the star schema ofour project. Here Customer, Product, Branch and Time are the Dimension tables and Bank_fact is the fact table. From the star schema it is clear that the managers can find the information about the account balances of the customers corresponding the customer personal fields, branch name and product name. Following is the fact table data and the relationships between them.

Fact Table

Relationships

Then we went further to import the access database into SQL SERVER 2008.

Implementation in SQL SERVER 2008:

We first imported the Access database into the sql server. Then we established the relationships between the tables. Then we generated the cube. An OLAP cube for online analytical processing is a data structure that allows fast analysis of data. It can also be defined as the capability of manipulating and analyzing data from multiple perspectives. The arrangement of data into cubes overcomes some limitations of relational databases. A slice is a subset of a multi-dimensional array corresponding to a single value for one or more members of the dimensions not in the subset. The dice operation is a slice on more than two dimensions of a data cube. Drilling down or up is a specific analytical technique whereby the user navigates among levels of data ranging from the most summarized to the most detailed. A roll-up involves computing all of the data relationships for one or more dimensions. All these are the operations which can be done on the data of the cube.

Cube Implementation

Dimensions

Measures

Browsing the cube:

The Cube Browser is a tool provided within the Analysis Services to display the results of the cube process without the need to add external software. It is a relatively useful data analysis tool that can display the dimension data as required by the end user. The user can drill up and drill down and check the accuracy, completeness and effectiveness of the model design at the Analysis services level. We dragged the attributes from the dimension tables and the measures from the left panel to generate the reports. Following are some of the reports which we have developed.