NIT6160 Data Warehousing and Mining Assignment

Assignment Help Database Management System

Reference no: EM132415994

NIT6160 Data Warehousing and Mining Assignment - Victoria University, Australia

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.

Task 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.

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?

Reference no: EM132415994

Questions Cloud

What volume does the gas occupy : After opening the bottle, the evaporating carbon dioxide is trapped in a cylinder at 1.00 atm and 17 degrees celcius. What volume does the gas occupy.

Gravitational field strength of the moon : The satellite is moved to a new circular orbit that is 2R from the center of the moon. What is the gravitational field strength of the moon at this new distance

How many radioactive nuclei are initially present in sample : A radioactive isotope has a half-life of 82.0 min. A sample is prepared that has an initial activity of 1.70×1011 Bq

Sketch a before and after picture of the situation : Question 1: Sketch a before and after picture of the situation. Question 2: Is momentum conserved in this process? Explain why or why not.

NIT6160 Data Warehousing and Mining Assignment : NIT6160 Data Warehousing and Mining Assignment Help and Solution, Victoria University, Australia. Task 1: Data Warehouse Design

Emission of blackbody radiation : The primary mechanism for cooling the shuttle is emission of blackbody radiation. The shuttle has a heat shield on its underside

What is metabolic rate during the ascent : Report in both Joules and Calories (kcal). What is her metabolic rate during the ascent? Report in Watts.

What is the rms current in circuit in amperes : An AC voltage source is connected to a resistor R = 3.17 x102 O. The output from an AC voltage source is given by the expression V = (7.66x 102 V) sin (2pft).

Find the thrust of the jet engine : A jet aircraft is traveling at 284 m/s in horizontal flight. The engine takes in air at a rate of 54.3 kg/s and burns fuel at a rate of 3.08 kg/s.

User Account

All Pages