Data Warehousing and Mining: Brokerage Company Data Design and Queries

This document presents a comprehensive solution for a data warehousing and mining assignment, focusing on the design of a data warehouse for an online brokerage company. The solution includes the creation of a staging area, the use of dimensions such as Date, Customer, Account, Security, and Trade Type, and the design of a Trades fact table. The assignment emphasizes data consistency and quality, and utilizes Oracle SQL Developer for data warehouse design. The solution details the implementation of customer scoring techniques based on transaction frequency, size, and recency, as well as customer profitability scores. The document includes the schema design, and SQL queries for OLAP analysis. The assignment covers the creation of dimension and fact tables, and the relationships between them. The solution also provides example OLAP queries to analyze customer trade data based on various criteria. The assignment aims to build a reliable and secure data warehouse environment to support business intelligence reporting.

Data Warehousing and Mining
Student’s Name
Instructor’s Name

2
Task 1: Data Warehouse Design
In order to make money through charging customers during selling and buying, the given online
brokerage company needs to load data warehousing and external data feeds. Here, a data
infrastructure comprised of staging area with focusing on two different customer scoring
techniques. The external data is loaded into staging area from data warehouse on a 24-hour
schedule. In the design of warehousing, different dimensions are used such as Date, customer,
account, security, and trade type. Mainly in job design, the trades fact table is designed that scrap
one row per stock trade. As the data is going to be loaded, data consistency and quality are
focused. The implementation of such robust production application, the data warehouse
environment becomes more reliable, secure, supportable, and a trusted source for all business
intelligence report.
For data warehouse design, Oracle SQL developer are needed. The brokerage company build
trade fact table in customer activity segment warehouse. Total three operations are focuses as
mentioned in instruction: frequency of transactions, average transaction size, and regency of
transaction. The customer dimension represents the customer’s details that needs for customer
profitability scoring to earn profit of customer’s trades.
The fact table consists all surrogate keys and foreign keys of dimension tables. Data warehouse
design also allows to add essential information with clicking on desired attributes from other
dimensional tables into it and then makes maps between fact table and dimension table. This
process helps to load all data from dimension table to fact table. With the clicking on data source
view, the table is opened with consisting all attributes. With choosing the server option, the
project can be deployed.

3
Figure 1: Data warehouse Schema design
Queries
CREATE SCHEMA
IF NOT EXISTS `brokerage` DEFAULT CHARACTER
SET utf8;
USE `brokerage`;
-- -----------------------------------------------------
-- Table `brokerage`.`dim_acc`
-- -----------------------------------------------------
CREATE TABLE
IF NOT EXISTS `brokerage`.`dim_acc`(`acc_id` INT (11) NOT NULL, `acc_type`
VARCHAR(45) NULL DEFAULT NULL, `acc_balance` VARCHAR(45) NULL DEFAULT
NULL, `acc_expiration` VARCHAR(45) NULL DEFAULT NULL, PRIMARY KEY
(`acc_id`)) ENGINE = InnoDB DEFAULT CHARACTER
SET = utf8;
-- -----------------------------------------------------
-- Table `brokerage`.`dim_customer`
-- -----------------------------------------------------
CREATE TABLE

4
IF NOT EXISTS `brokerage`.`dim_customer`(`id_customer` INT (11) NOT NULL,
`cust_name` VARCHAR(45) NULL DEFAULT NULL, `cust_contact` VARCHAR(45) NULL
DEFAULT NULL, `cust_address` VARCHAR(45) NULL DEFAULT NULL,
`cust_profit_score` INT (11) NULL DEFAULT NULL, PRIMARY KEY
(`id_customer`)) ENGINE = InnoDB DEFAULT CHARACTER
SET = utf8 COMMENT = ' ';
-- -----------------------------------------------------
-- Table `brokerage`.`dim_security`
-- -----------------------------------------------------
CREATE TABLE
IF NOT EXISTS `brokerage`.`dim_security`(`secuirty_id` INT (11) NOT NULL,
`stock_id` INT (11) NULL DEFAULT NULL, `stock_type` VARCHAR(45) NULL
DEFAULT NULL, `stock_name` VARCHAR(45) NULL DEFAULT NULL, `stock_company`
VARCHAR(45) NULL DEFAULT NULL, PRIMARY KEY (`secuirty_id`)) ENGINE =
InnoDB DEFAULT CHARACTER
SET = utf8;
-- -----------------------------------------------------
-- Table `brokerage`.`dim_trade`
-- -----------------------------------------------------
CREATE TABLE
IF NOT EXISTS `brokerage`.`dim_trade`(`trade_id` INT (11) NOT NULL,
`stock_id` INT (11) NULL DEFAULT NULL, `trade_type` VARCHAR(45) NULL
DEFAULT NULL, PRIMARY KEY (`trade_id`)) ENGINE = InnoDB DEFAULT CHARACTER
SET = utf8;
-- -----------------------------------------------------
-- Table `brokerage`.`dimdate`
-- -----------------------------------------------------
CREATE TABLE
IF NOT EXISTS `brokerage`.`dimdate`(`stock_id` INT (11) NOT NULL, `date`
DATE NULL DEFAULT NULL, `time` TIME NULL DEFAULT NULL, PRIMARY KEY
(`stock_id`)) ENGINE = InnoDB DEFAULT CHARACTER
SET = utf8;
-- -----------------------------------------------------
-- Table `brokerage`.`fact_table_transaction`
-- -----------------------------------------------------
CREATE TABLE
IF NOT EXISTS `brokerage`.`fact_table_transaction`(`transact_id` INT (11)
NOT NULL, `stock_id` VARCHAR(45) NULL DEFAULT NULL, `date` VARCHAR(45)
NULL DEFAULT NULL, `acc_id` VARCHAR(45) NULL DEFAULT NULL, `trade_id`
VARCHAR(45) NULL DEFAULT NULL, `security_id` VARCHAR(45) NULL DEFAULT
NULL, `amount` VARCHAR(45) NULL DEFAULT NULL, `brokerage` VARCHAR(45) NULL
DEFAULT NULL, `cust_id` VARCHAR(45) NULL DEFAULT NULL, PRIMARY KEY
(`transact_id`), INDEX `fk_fact_table_transaction_dim_acc_idx`(`acc_id`
ASC), INDEX `fk_fact_table_transaction_dim_trade1_idx`(`trade_id` ASC),
INDEX `fk_fact_table_transaction_dim_security1_idx`(`security_id` ASC),

5
INDEX `fk_fact_table_transaction_dim_customer1_idx`(`cust_id` ASC), INDEX
`fk_fact_table_transaction_dimdate1_idx`(`stock_id` ASC), CONSTRAINT
`fk_fact_table_transaction_dim_acc` FOREIGN KEY (`acc_id`) REFERENCES
`brokerage`.`dim_acc`(`acc_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_fact_table_transaction_dim_trade1` FOREIGN KEY (`trade_id`)
REFERENCES `brokerage`.`dim_trade`
(`trade_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_fact_table_transaction_dim_security1` FOREIGN KEY
(`security_id`) REFERENCES `brokerage`.`dim_security`(`secuirty_id`) ON
DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT
`fk_fact_table_transaction_dim_customer1` FOREIGN KEY (`cust_id`)
REFERENCES `brokerage`.`dim_customer`(`id_customer`) ON DELETE NO ACTION
ON UPDATE NO ACTION, CONSTRAINT `fk_fact_table_transaction_dimdate1`
FOREIGN KEY (`stock_id`) REFERENCES `brokerage`.`dimdate`(`stock_id`) ON
DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB DEFAULT CHARACTER
SET = utf8;
Task 2: OLAP Queries
--Query 1
SELECT count(trade_id)
FROM brokerage.fact_table_transaction
INNER JOIN dim_customer ON fact_table_transaction.cust_id =
dim_customer.id_customer
WHERE cust_name = 'John Alan';
--Query 2
SELECT sum(amount)
FROM brokerage.fact_table_transaction
INNER JOIN dim_trade ON dim_trade.trade_id = fact_table_transaction.trade_id
INNER JOIN dim_security ON dim_security.stock_id =
fact_table_transaction.stock_id
INNER JOIN dim_customer ON dim_customer.id_customer =
fact_table_transaction.cust_id
WHERE DATE = '2003' & & dim_security.stock_company = 'IBM' & &
dim_customer.cust_profit_score = '4' || dim_customer.cust_profit_score = '5';
References
As this work is done by my itself so there are no references.