Design dimensional data models based on business reporting

Reference no: EM132429910 , Length: word count:1800

Data Modelling and OLAP Techniques for Data Analytics

The aim of this coursework is to design, implement, and test a data warehouse based on a given business case scenario. It consists of 2 parts, Assessment A needs to be completed as ateam work and Assessment B is anindividual coursework.

Coursework Specification:

CASE STUDY SCENARIO: Blue Sky Online Consumer Electronics Retailer

Assessment A: Task 1 below will be completed working as an agile development team and the product will be a design on Conceptual Level; the architecture and the components of the designed architecture needs to be explained clearly and justification needs to be given.

Tasks:

1. Design dimensional data models based on the business reporting and data analysis requirements of the Blue Sky Consumer Electronics retailer.

1.1 Identify and define the grain (detail level) of a fact table. Explain the hierarchies, if there are any?

1.2 Identify measures of the fact table, and attributes of each dimension table. Justify why they have been chosen.

1.3 Identify dimensions of the fact table.

1.4 Choose an appropriate schema to define the structure that shows how the fact table is related to its dimensions. Present your schema using Entity Relationship Diagram.

1.5 Reflection: Needs to be written individually about the team work and the following needs to be answered briefly:

What has been gained by working in an agile team, and what was your individual addition to the team?

Assessment B: From the above Conceptual Design, a physical design needs to be implemented as individual work.

2. Implement a data warehouse based on the dimensional data models designed in the previous task using SQL Server Management Studio.

2.1 Create a database.

2.2 Create dimension tables

2.3 Create fact tables.

2.4 Add appropriate primary keys and foreign keys constraints.

3. Populate the implemented data warehouse based on the provided data sources using SQL Server Management Studio.

1.1 Implement ETL (extract, transform, and load) processes to populate dimension tables.

1.2 Implement ETL (extract, transform, and load) processes to populate a fact table.

1.3 Write SQL statements to test whether the dimensions and fact tables are populated correctly.

4. Written Report

The report, which you will submit, should be well written, structured and well-presented and it must include:

• An introduction section that summarise the objectives of the course work and business case scenario.
• The Analysis and design of a dimension data model.
• The implementation, populating and testing of the data warehouse (include screen shots to show SQL commands and their results).
• Provide a personal reflective conclusion of what you have learnt from your overall coursework.

Attachment:- Data Modelling and OLAP Techniques for Data Analytics.rar

Reference no: EM132429910

Questions Cloud

Transitions to market people in democratic societies : Transitions to Market People in democratic societies would like to think the process would/could/should work everywhere.

What is deviant varies : Sociologists stress that the definition of what is deviant varies from time to time and from place to place. Some people feel that our society is moving toward

Human resource plan for organizational strategy : For this scholarly activity, develop a supporting human resource (HR) plan for an organizational strategy.

Contrast the differing views regarding abortion : Compare and contrast the differing views regarding abortion. 200-300 words, refernces and citiation, no abstract or cover sheet.

Design dimensional data models based on business reporting : Design dimensional data models based on the business reporting and data analysis requirements of the Blue Sky Consumer Electronics retailer

Weber six-point ideal-type model : Select a bureaucracy with which you are familiar and analyze the extent to which it is accurately described by Weber's six-point ideal-type model.

Understanding of cash flow management-break-even analysis : The purpose of this assignment is to allow the student an opportunity to apply their understanding of cash flow management, break-even analysis,

What are social factors that are greatest threat to health : Define the relationship between social behavior and health. Justify your answer using examples and reasoning.What are social factors that are greatest threat

What is interview bias and when can occur : What are different methods for administering surveys? When would you use each method?What is interview bias and when can occur?

User Account

All Pages