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


Related Questions in computer science category


Disclaimer
The ready solutions purchased from Library are already used solutions. Please do not submit them directly as it may lead to plagiarism. Once paid, the solution file download link will be sent to your provided email. Please either use them for learning purpose or re-write them in your own language. In case if you haven't get the email, do let us know via chat support.