Reference no: EM133043117

IMAT5167 Data Warehouse Design and OLAP - De Montfort University

Assessment - Data Mart Design

Learning outcome 1: A comprehensive understanding of the concepts, purposes, architectures, evolution and benefits of DW.

Learning outcome 2: A systematic knowledge of how to apply ERD and Star Schema to design databases, DW or data mart.

Learning outcome 3: The ability to design appropriate data extraction, transformation and loading strategy and create reasonable queries.

Case Scenario - Midlands Theatre (MT) Company

Your Tasks

You are to develop a prototype of an ORACLE data mart for ticket sales as part of a potential data warehouse for MT. For this assignment you are required to work individually.

You have been given the MT company scenario, together with an Entity Relationship (ER) Diagram and corresponding tables for the existing relational database.

Problem Task 1: Analyse the given database design and the requirement for a data mart, and identify the dimensions and fact for your data mart. Here for this assignment, only the essential (minimum) dimensions for the required queries need to be included.

Problem Task 2: Design the star schema for the data mart, and identify the corresponding PKs and FKs.

Problem Task 3: According to given data and requirements, determine the relevant attributes and suitable granularity in your data mart.

Problem Task 4: Map your star schema to logical relations.

Problem Task 5: Create the corresponding tables in Oracle using SQL.

Problem Task 6: Identify your source data from the OLTP database and design your data extraction rules. You need to give a detailed mapping and transformation list from the source to the destination.

Problem Task 7: Implement your data extraction, transformation and loading through Oracle SQL. The number of rows extracted into each dimension or fact table in your data mart should be printed from Oracle query.

Problem Task 8: Comment on how your data mart satisfies the requirements of MT. Implement the required data analysis requirements for both the data mart and the original OLTP database. Compare your queries and comments on the advantages of a data mart in analysis operations. Test results from Oracle query should be included.

- Evidence of the successful execution of your queries is required. For large outputs, you need to provide only the last page of outputs with the number of rows in the end.

Attachment:- Data Warehouse Design and OLAP.rar