Reference no: EM132773429
Data Warehousing Design Assignment #1 :
As data management professionals, you should be able to develop dimensional models that depict the design of a data warehouse to support organizational analyses and decision-making.
Assignment: Consider the below Business Scenario, Design the DWH for the OLTP system
1. 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 frequency of transactions.
o Each customer is 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