Reference no: EM132500732
NIT6160 Data Warehousing and Mining - Victoria University
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 (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.
Problem 1: 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.
Problem 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?
Attachment:- Data Warehousing and Mining.rar
|
Write an explanation of why you selected each activity
: Provide an explanation of why you selected each activity and how it can contribute to the prenatal, postpartum, and future development of each woman's child.
|
|
Compound average annual return
: What was its compound average annual return? Answer in percent, rounded to two decimal places (e.g., 4.32% = 4.32).
|
|
Total nonoperating assets and total nonoperating liabilities
: Using the following financial statement data, calculate the difference between total nonoperating assets and total nonoperating liabilities.
|
|
What is the company pretax cost of debt
: What is the company's pretax cost of debt? (Do not round intermediate calculations and enter your answer as a percent rounded to 2 decimal places, e.g., 32.16.)
|
|
NIT6160 Data Warehousing and Mining Assignment
: NIT6160 Data Warehousing and Mining Assignment Help and Solution, Victoria University - Assessment Writing Service - Design a data warehouse
|
|
What will operating cash flow for this project be during 3
: The truck will have no effect on revenues, but it is expected to save the firm $20,000 per year in before-tax operating costs, mainly labor.
|
|
What will the year 1 operating cash flow for this project be
: Depreciation is computed using MACRS over a five-year life, and the cost of capital is 10 percent. Assume a 21 percent tax rate.
|
|
Prepare the necessary general journal entry for june
: Prepare the necessary general journal entry for June 16, purchased merchandise having a price of $6,000 from the Shelby Manufacturing Co
|
|
What is the expected value of the cash flows
: You are given the following probability distribution. What is the expected value of the cash flows?
|