Reference no: EM133052381
1. Why a data warehouse (data warehouse or DW) could be the solution to the problem that the Marketing Department of Acme Bank is having?
2. What would be the dimensions and metrics that would be used in the data warehouse for the case studied. It begins by explaining the concepts of dimension and metric, and then identifies possible dimensions and metrics for the case.
3. Identify the sources of information in the data warehouse.
4. What are the various aspects of the data warehouse requirements: What are the use or the importance of the following components for the data warehouse:
- Data source
- Transformation
- Storage
- Delivery
Background of the situation:
The Marketing Department of Acme Bank needs to know the products that each of its clients have and the clients that at some point had a product with the Bank but are no longer clients. They want to make this with the objective of launching specific marketing campaigns for specific clients. For example, if a customer already has a personal loan, the Bank does not want to send him promotions on personal loans. In this case, you would be sending you promotions on other products, such as a credit card, car loan or others.
Available products and their databases:
The following table shows the products that the Bank offers to its customers and where that information is stored:
|
Product
|
Where it is stored
|
Approximate number of records
|
|
Personal Loan
|
SQL Server Database
|
90,000
|
|
Auto Loan
|
Oracle Database
|
105,000
|
|
Mortgage Loan
|
SQL Server Database
|
400,000
|
|
Credit Card
|
Oracle Database
Excel sheets
|
110,000
|
|
Savings account
|
SQL Server Database
|
125,000
|
Basic customer information is in another SQL Server database.
Information in each of the databases:
|
Database
|
Database Information
|
|
Personal Loan
|
Number of loans, application date, approved flag, payment method, payment frequency, client ID, balance due, termination date
|
|
Auto Loan
|
Number of loans, application date, approved flag, payment method, payment frequency, client ID
|
|
Credit Card
|
Card number, customer ID, requested amount, approved amount, interest rate, credit limit, available balance, social security, purchase transaction date, amount used.
|
|
Debit Card
|
Card number, customer ID, balance, purchase transaction date, purchase amount.
|
|
Savings Account
|
Account number, opening date, deposit date, date withdrawal, available balance, deposit amount, retirement
|
|
Clients
|
Customer number, physical address, postal address, date of birth, marital status, gender.
|
Problem:
The Marketing Department management does not have visibility of all customer information and it depends on the information reaching them in different files so that they can analyze it using different excel sheets It can take weeks before management has all the information available to be able to create the analyzes and design your marketing campaigns.
Request:
A solution is requested that allows the Marketing Department staff to have access to all customer information in one place and without depending on other areas to send you the information.