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

Reviews

Write a Review

Database Management System Questions & Answers

  Knowledge and data warehousing

Design a dimensional model for analysing Purchases for Adventure Works Cycles and implement it as cubes using SQL Server Analysis Services. The AdventureWorks OLTP sample database is the data source for you BI analysis.

  Design a database schema

Design a Database schema

  Entity-relationship diagram

Create an entity-relationship diagram and design accompanying table layout using sound relational modeling practices and concepts.

  Implement a database of courses and students for a school

Implement a database of courses and students for a school.

  Prepare the e-r diagram for the movie database

Energy in the home, personal energy use and home energy efficiency and Efficient use of ‘waste' heat and renewable heat sources

  Design relation schemas for the entire database

Design relation schemas for the entire database.

  Prepare the relational schema for database

Prepare the relational schema for database

  Data modeling and normalization

Data Modeling and Normalization

  Use cases perform a requirements analysis for the case study

Use Cases Perform a requirements analysis for the Case Study

  Knowledge and data warehousing

Knowledge and Data Warehousing

  Stack and queue data structure

Identify and explain the differences between a stack and a queue data structure

  Practice on topic of normalization

Practice on topic of Normalization

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd