Design of data warehouse to support organizational analyses

Assignment Help Database Management System

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

Reference no: EM132773429

Questions Cloud

Prepare journal entries required for capital project fund : The township entered into a contract for construction of the fountain at estimated cost of $7,275,000. Prepare journal entries required for Capital Project Fund

Create statement of cost of goods manufactured for the year : The beginning inventory of finished goods is 25% of the cost of goods sold. Create a statement of cost of goods manufactured for the year

What selling price would the company establish : Using absorption cost-plus pricing and assuming a planned sales volume of 20,000 units, what selling price would the company establish for its new product

How communication has shaped your self-concept : Define and describe self-concept and apply your definition to an example of how communication has shaped your self-concept

Design of data warehouse to support organizational analyses : Develop dimensional models that depict the design of a data warehouse to support organizational analyses and decision-making

Network administrator for organization : You are the network administrator for your organization. Which PowerShell cmdlet would you use?

Develop scenario where you are hacker : Develop a scenario where you are a hacker. What method of social engineering would you use and what information or value would you seek?

Strategic management decision making in information : Refer any dissertation - Qualitative (Strategic management decision making in Information Technology) -

What you proposed in your ethical application : What you proposed in your ethical application form in your final project report unless you have subsequently agreed a slightly different focus with your tutor

User Account

All Pages