Consider the business scenario for an online brokerage company with Trades fact table.
computer science
Description
Design the DWH for the OLTP system
Consider the business scenario for an online
brokerage company with Trades fact table. The company makes money by charging
commissions when customers buy and sell stocks. The company’s data analysts
have developed two customer scoring techniques that are used extensively in
their analyses.
o Each customer is placed into one of nine
Customer Activity Segments based on their frequency of transactions, average
transaction size, and regency of transactions.
o Each customeris assigned a Customer
Profitability Score based on the profits 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.
Consider date as one of the dimensions.
The other dimensions are :
1. Customer with attributes Id, Name, City,
Segment, Score
2. Trade with attributes Id and Type
3. Security with attributes Id, company,
sector and country
4. Account with attributes
AccountType(retail/corporate), status(open/closed/hold)
The online brokerage site is a global
brokerage, works only in the working days. TradeAmount is calculated with (No
of Shear X PricePerShear). The commission fee is 5% of the trade amount.
Perform
the following tasks:
(a) Design a dimensional fact model (DFM)
for the data warehouse.
(b) Convert the DFM you constructed in (a)
to an appropriate Entity relationship model. Clearly indicate primary and
foreign keys in your tables.
(c ) Construct an attribute tree for the
DFM
(d) Perform Pruning and Grating, construct
attribute tree before and after pruning and grafting
