banking2 - dw
TRANSCRIPT
8/2/2019 Banking2 - DW
http://slidepdf.com/reader/full/banking2-dw 1/29
BITS PilaniPilani |Dubai |Goa |Hyderabad
Team :
Parikh Dhruv V. (2011H112150P)
Patel Amitkumar (2011H112159P)
Patel Kunal (2011H112161P)
Ashish Kumar Singh (2011H112162P)
Prateek Vijaywargiya (2011H112165P)
Financial ServicesPresented by
Amitkumar Patel (2011H112159P)
8/2/2019 Banking2 - DW
http://slidepdf.com/reader/full/banking2-dw 2/29
Financial services are the economic services provided by the finance
industry, which encompasses a broad range of organizations that manage
money,
including credit unions, banks, credit card companies, insurance
companies, consumer finance companies, stock brokerages,
investment funds and some government sponsored enterprises. Our Chosen topic for the data warehousing project:
Banking Case Study
Bank¶s initial goal is to build the capability to better analyze the
bank¶s accounts.
BITS Pilani, Deemed to be University under Section 3 of UGC Act, 1956
Review
8/2/2019 Banking2 - DW
http://slidepdf.com/reader/full/banking2-dw 3/29
Business users want to«
1) See 5 years of historical monthly snapshot data on every account
2) Group different types of accounts in the same analyses and compare
primary balances
3) Analyze the condition of the account at the end of each month
4) A Demographic attributes change over time, so track the changes also
5) Ability to have geographic(branch wise) reports
6) In a (big) bank, it may approach 10 million members/customers,
maintain details of each
7) An account can have one, two, or more individual account holders, or
customers, associated with it.
8) Have flexibility to redefine the bands at query time with different
boundaries or levels of precision
9) Have flexibility to drill down reports too transaction level sometimes
10) Business users typically require two different perspective. Handle
custom Heterogeneous Product Schemas.BITS Pilani, Deemed to be University under Section 3 of UGC Act, 1956
What is the Need of Data Warehouse?
8/2/2019 Banking2 - DW
http://slidepdf.com/reader/full/banking2-dw 4/29
We can implement the design into following two methods:
Star Schema
Snowflake Schema
Which method we should use in ³Bank (Financial Services)´ ?
BITS Pilani, Deemed to be University under Section 3 of UGC Act, 1956
Which design method??
8/2/2019 Banking2 - DW
http://slidepdf.com/reader/full/banking2-dw 5/29
Ralph Kimball, the data warehousing guru, proposes three cases where
snowflake implementation is not only acceptable but is also the key to a
successful design:
1) Large customer dimensions of heterogeneous data availability
2) Financial product dimensions for banks, brokerage houses, and
insurance companies, because each of the individual products has ahost of special attributes not shared by other products
3) Multi enterprise calendar dimensions because each organization has
idiosyncratic fiscal periods, seasons, and holidays
Ralph Kimball recommends that in most of the other cases, star schemas
are a better solution.
BITS Pilani, Deemed to be University under Section 3 of UGC Act, 1956
Why Snowflake Schema??
Reference :http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/10g/r2/owb/owb10gr2_gs/owb/lesson3/starandsnowflake.htm
8/2/2019 Banking2 - DW
http://slidepdf.com/reader/full/banking2-dw 6/29
Who(Dept ID,
Vendor,
etc)
What(Account,
Fund, etc)
Where(Business Unit,
etc)
When(Acctg. Period,
Fiscal Year, etc.)
Actual
Fact
Summary
Fact
Budget
Fact
High Level Finance Data Model Diagram
Charges Fact
8/2/2019 Banking2 - DW
http://slidepdf.com/reader/full/banking2-dw 7/29
Business users want to«
1) See 5 years of historical monthly snapshot data on every account
Grain of the fact table is one row for each account at the end of
each month.
Tables: Month-Account Snapshot Fact, Month Dimension ,Account
Dimension
2) Group different types of accounts in the same analyses and compare
primary balances
3) Analyze custom dimension attributes and numeric facts
4) A Demographic attributes change over time, so track the changes also
5) Ability to perform value-band reporting on a standard numeric fact,such as the account balance
6) In a (big) bank, it may approach 10 million members/customers,
maintain details of each
7) An account can have one, two, or more individual account holders, or
customers, associated with it.
BITS Pilani, Deemed to be University under Section 3 of UGC Act, 1956
Bank Requirement Analysis
8/2/2019 Banking2 - DW
http://slidepdf.com/reader/full/banking2-dw 8/29
Initial : Design(1)
Fact Table
Month-Account Snapshot Fact
Dimension Table
Month Dimension
Account Dimension
BITS Pilani, Deemed to be University under Section 3 of UGC Act, 1956
Design
Month Account Snapshot Fact
Month End Date Key (FK)
Account Key (FK)
Primary Month Ending Balance
Average Daily BalanceNumber of Transaction
Interes t Paid
Interest charged
Fees Charged
Account Dimension
Account Key
Account Attributes
Product Attributes
Household Attributes
Status Attributes
Branch Attributes
Month Dimension
Month End Date Key
Month Attributes
8/2/2019 Banking2 - DW
http://slidepdf.com/reader/full/banking2-dw 9/29
Business users want to«
1) See 5 years of historical monthly snapshot data on every account
2) Group different types of accounts in the same analyses and compare
primary balances
It indicate that we should have product dimension consists of a
simple product hierarchy
3) Analyze custom dimension attributes and numeric facts
4) A Demographic attributes change over time, so track the changes also
5) Ability to perform value-band reporting on a standard numeric fact,
such as the account balance
6) In a (big) bank, it may approach 10 million members/customers,maintain details of each
7) An account can have one, two, or more individual account holders, or
customers, associated with it.
BITS Pilani, Deemed to be University under Section 3 of UGC Act, 1956
Requirement Analysis
8/2/2019 Banking2 - DW
http://slidepdf.com/reader/full/banking2-dw 10/29
Business users want to«
1) See 5 years of historical monthly snapshot data on every account
2) Group different types of accounts in the same analyses and compare
primary balances
3) Analyze the condition of the account at the end of each month
Account status Dimension is a useful dimension to record the
condition of the account at the end of each month
To avoid merely embedding a cryptic status code/abbreviation
directly in the fact table
4) A Demographic attributes change over time, so track the changes also
5) Ability to perform value-band reporting on a standard numeric fact,such as the account balance
6) In a (big) bank, it may approach 10 million members/customers,
maintain details of each
7) An account can have one, two, or more individual account holders, or
customers, associated with it.
BITS Pilani, Deemed to be University under Section 3 of UGC Act, 1956
Requirement Analysis
8/2/2019 Banking2 - DW
http://slidepdf.com/reader/full/banking2-dw 11/29
8/2/2019 Banking2 - DW
http://slidepdf.com/reader/full/banking2-dw 12/29
Business users want to«
1) See 5 years of historical monthly snapshot data on every account
2) Group different types of accounts in the same analyses and compare
primary balances
3) Analyze the condition of the account at the end of each month
4) A Demographic attributes change over time, so track the changes also
5) Ability to have geographic(branch wise) reports
Many attributes will be there related to branch for filtering the
reports
Solution : Make Branch Dimension Table rather than keeping
branch just as attribute in account dimension.6) In a (big) bank, it may approach 10 million members/customers,
maintain details of each
7) An account can have one, two, or more individual account holders, or
customers, associated with it.
BITS Pilani, Deemed to be University under Section 3 of UGC Act, 1956
Requirement Analysis
8/2/2019 Banking2 - DW
http://slidepdf.com/reader/full/banking2-dw 13/29
BITS Pilani, Deemed to be University under Section 3 of UGC Act, 1956
Information Package Diagram
Month
Dimension
Branch
Dimension
Product
Dimension
Account Status
DimensionAccount Dimension
Household
Dimension
DateBranch Address
Attributes
Product
Description
Account Status
Description
Account Number
(Natural Key)
Head of Household
Name
More Month
Attributes
Branch Rollup
AttributesProduct Type
Account Status
Group
Primary Account
Holder Name
Household Address
Attributes
Branch Format
Description
Product
Category
Secondary Account
Holder NameHousehold Type
Account Address
AttributesHousehold Income
Account Open Date
Household
Homeownership
IndicatorAccount Type
Description
Household Presence
of Children
Account Type
Category
Facts: Primary Month Ending Balance, Average Daily Balance, Number of Transaction, Interest Paid, Interest
charged, Fees Charged
8/2/2019 Banking2 - DW
http://slidepdf.com/reader/full/banking2-dw 14/29
BITS Pilani, Deemed to be University under Section 3 of UGC Act, 1956
Refinement : Design(2)
Month Account Snapshot Fact
Branch Key (FK)
Month End Date Key (FK)
Account Key (FK)
Product Key (FK)
Account Status Key (FK)
Household Key (FK)
Primary Month Ending Balance
Average Daily Balance
Number of Transaction
Interest Paid
Interest charged
Fees Charged
Month Dimension
Month End Date Key
Month Attributes
Product Dimension
Product Key
Product Description
Product Type
Product Category
Household Dimension
Household Key
Head of Household Name
Household Address Attributes
Household Type
Household Income
Household Homeownership Indicator
Household Presence of Children
Branch Dimension
Branch Key
Branch Address Attributes
Branch Rollup Attributes
Branch Format Description
Account Status Dimension
Account Status Key
Account Status Description
Account Status Group
Account Dimension
Account Key
Account Open Date
Account Type Descrip tion
Account Type Category
Account Number
Primary Account Holder Name
Secondary Account Holder Name
Account Address Attributes
8/2/2019 Banking2 - DW
http://slidepdf.com/reader/full/banking2-dw 15/29
Business users want to«
1) See 5 years of historical monthly snapshot data on every account
2) Group different types of accounts in the same analyses and compare
primary balances
3) Analyze the condition of the account at the end of each month
4) A Demographic attributes change over time, so track the changes also
5) Ability to have geographic(branch wise) reports
6) In a (big) bank, it may approach 10 million members/customers,
maintain details of each
Solution : We should have Customer Dimension which can keep
track/detail of individual customer 7) An account can have one, two, or more individual account holders, or
customers, associated with it.
BITS Pilani, Deemed to be University under Section 3 of UGC Act, 1956
Requirement Analysis
8/2/2019 Banking2 - DW
http://slidepdf.com/reader/full/banking2-dw 16/29
Refinement : Design(3)
BITS Pilani, Deemed to be University under Section 3 of UGC Act, 1956
Design
Branch Dimension
Branch Key
Branch Address Attributes
Branch Rollup Attributes
Branch Format Description
« and more
Account Status Dimension
Account Status Key
Account Status Description
Account Status Group
« and more
Month Account Snapshot Fact
Branch Key (FK)
Month End Date Key (FK)
Account Key (FK)
Product Key (FK)
Account Status Key (FK)
Househo ld Key (FK)
Customer Key (FK)
Primary Month Ending Balance
Average Daily Balance
Number of Transaction
Interest Paid
Interest charged
Fees Charged
Account Dimension
Account Key
Customer Key (FK)
Account Open Date
Account Type Description
Account Type Category
Account Number
Primary Account Holder Nam e
Secondary Account Holder Name
Account Address Attributes« and more
Customer Dimension
Customer Key
Customer Name
Customer Date of Birth
« and more
8/2/2019 Banking2 - DW
http://slidepdf.com/reader/full/banking2-dw 17/29
Business users want to«
1) See 5 years of historical monthly snapshot data on every account
2) Group different types of accounts in the same analyses and compare
primary balances
3) Analyze the condition of the account at the end of each month
4) A Demographic attributes change over time, so track the changes also
5) Ability to have geographic(branch wise) reports
6) In a (big) bank, it may approach 10 million members/customers,
maintain details of each
7) An account can have one, two, or more individual account holders, or
customers, associated with it. Solution : to link an individual customer dimension to an account-
grained fact table requires the use of an Account-to-Customer
bridge table
BITS Pilani, Deemed to be University under Section 3 of UGC Act, 1956
Requirement Analysis
8/2/2019 Banking2 - DW
http://slidepdf.com/reader/full/banking2-dw 18/29
BITS Pilani, Deemed to be University under Section 3 of UGC Act, 1956
Refinement : Design(3)
Account Status Dimension
Account Status Key
Account Status Description
Account Status Group
« and more
Month Account Snapshot Fact
Branch Key (FK)
Month End Date Key (FK)
Account Key (FK)
Product Key (FK)
Account Status Key (FK)
Household Key (FK)
Primary Month Ending Balance
Average Daily Balance
Number of Transaction
Interest Paid
Interest charged
Fees Charged
« and more
Branch Dimension
Branch Key
Branch Address Attributes
Branch Rollup Attributes
Branch Format Description
« and more
Account Dimension
Account Key
Account Open Date
Account Type Description
Account Type Category
Account Number
Primary Account Holder Nam e
Secondary Account Holder Nam e
Account Address Attributes« and more
Account to Customer Bridge
Account Key (FK)
Customer Key (FK)
Weighing Factor
« and more
Customer Dimension
Customer Key
Customer Name
Customer Date of Birth
« and more
8/2/2019 Banking2 - DW
http://slidepdf.com/reader/full/banking2-dw 19/29
Business users want to«
8) Have flexibility to redefine the bands at query time with different
boundaries or levels of precision
Solution : Introduce Band Definition Table which allows to do
flexible value band reporting
9) Have flexibility to drill down reports too transaction level sometimes
10) Business users typically require two different perspective. Handle
custom Heterogeneous Product Schemas.
BITS Pilani, Deemed to be University under Section 3 of UGC Act, 1956
Requirement Analysis
8/2/2019 Banking2 - DW
http://slidepdf.com/reader/full/banking2-dw 20/29
Refinement : Design(3)
BITS Pilani, Deemed to be University under Section 3 of UGC Act, 1956
Refinement Design(4)
Month Account Snapshot Fact
Branch Key (FK)
Month End Date Key (FK)
Account Key (FK)
Product Key (FK)
Account Status Key (FK)
Household Key (FK)
Primary Month Ending Balance
Average Daily BalanceNumber of Transaction
Interest Paid
Interest charged
Fees Charged
« and more
Account to Customer Bridge
Account Key (FK)
Customer Key (FK)
Weighing Factor
« and more
Customer Dimension
Customer Key
Customer Name
Customer Date of Birth
« and more
Branch Dimension
Branch Key
Branch Address Attributes
Branch Rollup Attributes
Branch Format Description
« and more
Account Status Dimension
Account Status Key
Account Status Description
Account Status Group
« and more
Account Dimension
Account Key
Account Open Date
Account Type Description
Account Type Category
Account Number
Primary Account Holder Name
Secondary Account Holder Name
Account Address Attributes
« and more
Band Definition Table
Band Group Key
Band Group Sort Order
Band Group Name
Band Range Name
Band Lower Value
Band Upper Value<
>=
8/2/2019 Banking2 - DW
http://slidepdf.com/reader/full/banking2-dw 21/29
Business users want to«
8) Have flexibility to redefine the bands at query time with different
boundaries or levels of precision
9) Have flexibility to drill down reports too transaction level sometimes
Solution: Have flexibility to drill down by keeping another fact table
at different grain Balance Transaction Fact Table [Approximation :
3.65 billion fact rows per year.]
Key to success: Account Dimension as ³Conformed Dimension´
Supporting Dimension : Transaction Date Dimension & Transaction
Type Dimension
10) Business users typically require two different perspective. Handlecustom Heterogeneous Product Schemas.
BITS Pilani, Deemed to be University under Section 3 of UGC Act, 1956
Requirement Analysis
8/2/2019 Banking2 - DW
http://slidepdf.com/reader/full/banking2-dw 22/29
8/2/2019 Banking2 - DW
http://slidepdf.com/reader/full/banking2-dw 23/29
Business users want to«
8) Have flexibility to redefine the bands at query time with different
boundaries or levels of precision
9) Have flexibility to drill down reports too transaction level sometimes
10) Business users typically require two different perspective. Handle
custom Heterogeneous Product Schemas.
2 different views
I. Global view : needed to plan appropriate CRM strategies
II. Specific line-of-business view : to analyse particular
But its difficult to present in a single fact table
Solution : To create a custom schema for the each line of businessthat is limited to just Specific line-of-business
e.g. for checking accounts
both the custom checking fact table and the corresponding
checking product dimension
BITS Pilani, Deemed to be University under Section 3 of UGC Act, 1956
Requirement Analysis
8/2/2019 Banking2 - DW
http://slidepdf.com/reader/full/banking2-dw 24/29
8/2/2019 Banking2 - DW
http://slidepdf.com/reader/full/banking2-dw 25/29
BITS Pilani, Deemed to be University under Section 3 of UGC Act, 1956
Enterprise Bus ArchitectureMatrix
Month DimensionProduct
Dimension
Branch
Dimension
Account
Dimension
Account Status
Dimension
Band Definition
Table
Checking Specific
Product
Dimension
Customer
Dimension
Transaction Type
Dimension
Transaction Date
Dimension
Month Account
Snapshot Fact
Monthly Checking
Snapshot Fact
Balance
Transaction Fact
Table
Account to
Customer Bridge
8/2/2019 Banking2 - DW
http://slidepdf.com/reader/full/banking2-dw 26/29
8/2/2019 Banking2 - DW
http://slidepdf.com/reader/full/banking2-dw 27/29
http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/10g/r2/owb/o
wb10gr2_gs/owb/lesson3/starandsnowflake.htm
http://www-01.ibm.com/support/docview.wss?uid=swg24022595
http://www.kimballgroup.com/ html/booksDWT2.html
http://www.datawarehousetoolkit.com/ralph-kimball/
http://www.kimballgroup.com/ html/designtips.html
BITS Pilani, Deemed to be University under Section 3 of UGC Act, 1956
References
8/2/2019 Banking2 - DW
http://slidepdf.com/reader/full/banking2-dw 28/29
BITS Pilani, Deemed to be University under Section 3 of UGC Act, 1956
8/2/2019 Banking2 - DW
http://slidepdf.com/reader/full/banking2-dw 29/29
BITS Pilani, Deemed to be University under Section 3 of UGC Act, 1956