building a data warehouse for an online brokerage company. The company makes money by charging commissions...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
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 (i.e. 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 1, 2, 3, 4 or 5, with 5 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 2003 on trades of IBM stock by customers with a profitability score of 4 or 5? There is a total of 100,000 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 1: Data Warehouse Design Design a data warehouse for the above mention scenario. Implement your data warehouse with SQL Developer. Prepare report of your data warehouse design and demonstrate your design to your tutor. Task 2: 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 2003 on trades of IBM stock by customers with a profitability score of 4 or 5? 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 (i.e. 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 1, 2, 3, 4 or 5, with 5 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 2003 on trades of IBM stock by customers with a profitability score of 4 or 5? There is a total of 100,000 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 1: Data Warehouse Design Design a data warehouse for the above mention scenario. Implement your data warehouse with SQL Developer. Prepare report of your data warehouse design and demonstrate your design to your tutor. Task 2: 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 2003 on trades of IBM stock by customers with a profitability score of 4 or 5?
Expert Answer:
Answer rating: 100% (QA)
Solution Task 1 Data Warehouse Design To design a data warehouse for the online brokerage company well create a star schema with a fact table and dime... View the full answer
Related Book For
Auditing A Practical Approach with Data Analytics
ISBN: 978-1119401742
1st edition
Authors: Raymond N. Johnson, Laura Davis Wiley, Robyn Moroney, Fiona Campbell, Jane Hamilton
Posted Date:
Students also viewed these databases questions
-
Jon Batiste stocks and sells Colbert model airplanes that have a weekly demand of 50 airplanes. The standard deviation for weekly demand is 2 airplanes. The lead-time for receiving the airplanes from...
-
Planning is one of the most important management functions in any business. A front office managers first step in planning should involve determine the departments goals. Planning also includes...
-
The Crazy Eddie fraud may appear smaller and gentler than the massive billion-dollar frauds exposed in recent times, such as Bernie Madoffs Ponzi scheme, frauds in the subprime mortgage market, the...
-
When something burns, a. it combines with phlogiston b. it gives off phlogiston c. it combines with oxygen d. it gives off oxygen
-
An air compressor takes in air at 100 kPa, 17C and delivers it at 1 MPa, 600 K to a constant-pressure cooler, which it exits at 300 K. Find the specific compressor work and the specific heat...
-
A new unit of energy, sol, is defined as the average amount of solar energy received by earth in a given second. The average rate at which the energy is received per unit area, known as the solar...
-
Consider an infinite Bernoulli process with \(p=0.5\), that is, an infinite sequence of random variables \(\left\{Y_{i}, i \in \mathbb{Z} ight\}\) with \(\mathbf{P}\left(Y_{i}=0...
-
Comparative statement of financial position accounts of Jensen Limited, which follows IFRS, appear below: Data from Jensen's 2017 income statement follow: Additional information: 1. Equipment that...
-
Select a metallic ship or boat component that is manufactured starting from a billet using at least two combinations of forging, rolling, extrusion or wire drawing (ex. Hot extrusion then forging). ...
-
A 2.5-lb collar is attached to a spring and slides without friction along a circular rod in a vertical plane. The spring has an undeformed length of 4 in. and a constant k. The collar is at rest at C...
-
Affirming the necessity of Assigning Costs in improving decision making such as product valuation, product pricing... give your examples and supporting arguments related to reasonable distribution...
-
Billy Bob is at risk for \(\$ 10,000\) in Partnership A and \(\$ 22,000\) in Partnership B on January 1, 2018. Both partnerships are passive activities to Billy Bob. Billy Bob's share of net income...
-
Richard and Sally Murphy were divorced in 2016. By the terms of their decree Richard pays Sally \(\$ 1,000\) a month for alimony and child support. When Jane, their 14-year-old daughter, reaches age...
-
Roger signed for a ten-year lease to rent office space from Doug. In the first year, Roger paid Doug $ 5 , 000 $ 5 , 000 for the first year's rent and $ 5 , 000 $ 5 , 000 as rent for the last year of...
-
What role does the Maximum Chance Criterion play in discriminant analysis?
-
Greg Grove pays \(\$ 50,000\) interest in 2018 on his mortgage on his principal residence. It has an interest rate of 4 percent and balance of \(\$ 1,250,000\) during the year. He took out the...
-
Span of control describes the number of subordinates who reportdirectly to each manager. A wide span of control means that onemanager has many subordinates. A narrow span of control means thatone...
-
Q:1 Take any product or service offered in Pakistan and apply all determinents of customer Perceived value ?
-
Parsons & Co, LLC, an audit firm, has audited Cascade Motors, a manufacturer of auto parts, for the last two years. Dereck Miller, a first-year auditor, has noted that the IT auditor has evaluated IT...
-
Billys Burger House, a private company, is a chain of fast-food restaurants that has experienced fast growth in the last decade. The restaurant chain has been successful because it focuses on the...
-
a. Conclusions: Based on the new information and assuming management cooperates with your recommended course of action, how would you date your updated report and would your updated report be an...
-
Describe the significance of the term filious nullius.
-
Calculate the isentropic compressibility for air at a pressure of \(0.5 \mathrm{~atm}\). Compare the result with that for the isothermal compressibility obtained in Example 1.3. Data From Example...
-
Describe the nature and significance of the biology plus approach to establishing parental rights for unwed fathers.
Study smarter with the SolutionInn App