A. The requirement is to design a Database to support BI for Retail Banks.

Subject: creating an olap system from an oltap system

Question: Creating an OLAP System from an OLTP System Traditional database design emphasises accurate
recording of data but this may not produce a database that is easy to use for ad-hoc reporting.

On the next page is a cut down schema from an OLTP database for a retail banking system.

Customers have one or more accounts at a particular bank branch.

Branches process and record all their customer?s transactions.

When a transaction is processed, the balance in the corresponding account is updated.

Each customer is linked to census data for the area they live in.

(Note this schema is quite a bit simpler than real life but this is only an exercise!)

You must create a data warehouse design for the bank based on this data.

It needs to allow the bank to produce a variety of different reports such as:
 Transaction details, broken down by date (day/month/year), time of day (eg morning, afternoon, evening),
  transaction type, customer details (age, city, suburb), branch details (city, suburb, employees) and census details.
 Average as well as total transaction values and number/value/average of deposits and withdrawals.
 Average balances, broken down by date, customer/branch details and census data.
 Ratio of total transaction value in a month to average balance (eg over several months).

Design a data warehouse schema and data cube based on the OLTP database that will allow the reports above to be easily
produced (ie no complex SQL required).

1. Draw a sketch of an appropriate star/snowflake database design.
   Indicate what data items will be in each of the dimension and fact tables.
   Indicate any necessary keys. Explain/justify your design.

2. Describe any data transformations that will need to be applied to data from
   the OLTP system to use it in the data warehouse.

3. For the data cube, describe how the measures would be defined (including any calculations)
   and what the dimensions would be (including any useful hierarchies).

4. Discuss whether any dimensions would require special processing over time, eg any ?slowly
   changing? dimensions.
   It's not necessary to get the design absolutely perfect ? we will use this as a source of discussion in class.

You are not required to actually create the OLAP DB ? just produce a diagram of it.
However you can download the Access schema and use it to create your diagram.
COMP 631 2 2011 Simplified Schema for a Retail Bank

Notes: In Account Account Type indicates checking, savings, child account,
etc. Current Balance is updated whenever a transaction is processed.

In Transaction: Trans Type indicates if on-line, in branch, ATM, etc.
Credit/Debit tells whether it is a deposit or a withdrawal.

In Branch: Branch Type indicates large, medium, small.


A. The Things of Interest are :-
   A.1  To be determined

B. How are they related ?
   B.1 To be determined



Barry Williams

August 11th. 2011

Principal Consultant

Database Answers