Reference no: EM132335614
Data Mart Design
The learning outcomes that are assessed by this coursework are:
1. A comprehensive understanding of the concepts, purposes, architectures, evolution and benefits of DW.
2. A systematic knowledge of how to apply ERD and Star Schema to design databases, DW or data mart.
3. The ability to design appropriate data extraction, transformation and loading strategy and create reasonable queries.
Midlands Theatre (MT) Company
Scenario
Company Overview:
Midlands Theatre (MT) is a chain of small theatres that are found within the suburbs of several cities and towns within the Midlands, including Leicester and Birmingham. It specialises in high quality theatre productions that may be seen to be insufficiently "mainstream" for the general population. The company purchased its first theatre in January 1982, and since then has seen substantial increases in the number of clients that want to visit a local theatre to see a production that is more alternative to those typically found within the city centre theatres and/or large entertainment complexes. In response to the growing client base, MT sometimes offers a matinee (i.e., afternoon) performance of a popular production in addition to the traditional evening performance time.
Your Tasks
You are to develop a prototype of ORACLE data mart for ticket sales as a 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 Extended Entity Relationship (EER) Diagram and corresponding tables for the existing relational database.
Task 1: Analyse the given database design and the requirement for data mart, identify the dimensions and fact for your data mart
Task 2: Design the star schema for the data mart and identify the corresponding PKs and FKs
Task 3: According to given data and requirements, determine the relevant attributes and suitable granularity in your data mart
Task 4: Map your star schema to logical relations
Task 5: Create the corresponding tables in Oracle using SQL
Task 6: Identify your source data from the OLTP data base and design your data extraction rules. You need to give a detailed mapping and transformation list from the source to the destination.
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.
Task 8: Comment on how your data mart satisfies the requirements of MT. Implement the required data analysis requirements for both data mart and the original OLTP database. Compare your queries and comments on the advantages of data mart in analysis operations. Test results from Oracle query should be included.
Deliverables:
The deliverable is a report that summarises your work and justifies your design decisions, it includes the following sections
1. Dimension selection and fact identification
2. Star Schema
3. Logical relations and granularity
4. SQL for table creation and constraints
5. Data Sources Mapping (using diagrams)
6. SQL for ETL
7. SQL for required queries
8. Comparison between Data Mart and relational models
Each part should be associated with a concise explanation, and the execution results from your SQL code should be provided as well. Everything should be put into one MSWord document in your submission.
Attachment:- Data Warehouse Design and OLAP.rar