Project 6 Dimensional modeling
Individual Project
Instructions
Review the data set of the project. It includes a sample report (rows 1 to 4) a set of business rules (five) and six business questions.
Design a dimensional model that support the generation of the report and observe the business rules. In addition, the model should be able to be used to answer the business questions.
>Sheet 2 0
1 CA 1 2018 Jones 10
1
Date
Quarter
Year
Trans Type
Trans Amount
Acct Type
Teller
Branch
Branch City
Branch State
Branch Supervisor
Customer
6/1/17
2017
Deposit
$
10
Savings
Jones
456
Fullerton
CA
Smith
Carter
1/3/18
2018
Withdrawal
$12
Checking
Lee
500
Brea
Garcia
Reagan
2/2/18
Loan pmt
$1,010
Car Loan
Phoenix
AZ
Pick
Ford
Business rules:
1. Tellers may work in different branches
2. Branch supervisors are assigned to only one branch
3. Account types are: Savings, Checking, Car Loan, Mortgage Loan, Home Equity Line of Credit, Certificate of Deposit
4. Transanction types are: Deposit, Withdrawal, Loan Pmt, Loan Origination, Open Account, Close Account
5. Transaction Dates span 2015-2018
Business questions:
1. How many transactions take place on weekends?
2. Which day is the busiest?
3. Which day is the busiest for deposits?
4. Which tellers work more?
5. Which customers are depleting their money? (withdrawals exceed deposits)
6. Which branches are growing their business?