Answered step by step
Verified Expert Solution
Question
1 Approved Answer
In this assignment, we are building a data warehouse for an online brokerage company. The company makes money by charging commissions when customers buy and
In this assignment, we are building a data warehouse for an online brokerage company. The company makes money by charging commissions when customers buy and sell stocks. We are planning to have a Trades fact table with the grain of one row per stock trade. We will use the following dimensions: Date, Customer, Account, Security ie which stock was traded and TradeType. The company's data analysts have told us that they have developed two customer scoring techniques that are used extensively in their analyses. Each customer is placed into one of nine Customer Activity Segments based on their frequency of transactions, average transaction size, and regency of transactions. Each customer is assigned a Customer Profitability Score based on the profit earned as a result of that customer's trades. The score can be either or with being the most profitable. These two scores are frequently used as filters or grouping attributes in queries. For example: How many trades were placed in July by customers in each customer activity segment? What was the total commission earned in each quarter of on trades of IBM stock by customers with a profitability score of or There is a total of customers, and scores are recalculated every three months. The activity level or profitability level of some customers' changes over time, and users are very interested in understanding how and why this occurs. Task : Data Warehouse Design Design a data warehouse for the above mention scenario. Implement your data warehouse with SQL Developer. Prepare a report of your data warehouse design and demonstrate your design to your tutor. Task : OLAP Queries Based on your design, write down the queries for the following problem: How many trades were placed in July by customer "John Alan"? What was the total commission earned in each quarter of on trades of IBM stock by customers with a profitability score of or
In this assignment, we are building a data warehouse for an online brokerage company. The company
makes money by charging commissions when customers buy and sell stocks. We are planning to have a
Trades fact table with the grain of one row per stock trade. We will use the following dimensions: Date,
Customer, Account, Security ie which stock was traded and TradeType.
The company's data analysts have told us that they have developed two customer scoring techniques
that are used extensively in their analyses.
Each customer is placed into one of nine Customer Activity Segments based on their frequency of
transactions, average transaction size, and regency of transactions.
Each customer is assigned a Customer Profitability Score based on the profit earned as a result of that
customer's trades. The score can be either or with being the most profitable.
These two scores are frequently used as filters or grouping attributes in queries. For example:
How many trades were placed in July by customers in each customer activity segment?
What was the total commission earned in each quarter of on trades of IBM stock by customers
with a profitability score of or There is a total of customers, and scores are recalculated
every three months. The activity level or profitability level of some customers' changes over time, and
users are very interested in understanding how and why this occurs.
Task : Data Warehouse Design
Design a data warehouse for the above mention scenario. Implement your data warehouse with SQL
Developer.
Prepare a report of your data warehouse design and demonstrate your design to your tutor.
Task : OLAP Queries
Based on your design, write down the queries for the following problem:
How many trades were placed in July by customer "John Alan"?
What was the total commission earned in each quarter of on trades of IBM stock by customers
with a profitability score of or
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started