Design and Implementation of ORACLE Data Mart for Ticket Sales at MT

Task 1: Analysis and Dimension Selection

Having established the online transaction processing database, Ms. Heritage wants more intelligence information from the available data and she is looking for a potential data warehouse for MT. As the first step in this process, she expects to setup a data mart for ticket sales as the first step. The data mart should satisfy the following analysis requirements:

• Yearly total sale for each theatre.

• All clients who visited MT theatres in at least 4 different months in a year.

• List the titles, production directors and play authors of all products with the highest total sale.

Here, by ticket sale value, we refer to the value of money rather than number of tickets. For this data mart, only the data involved in ticket sales are stored, and any data not involved in at least one ticket sale should not be included.  You can access the OLTP database in my database schema: ops$yyang00. For example, you can access the data for Theatres using the following SQL statement:

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 database 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.

• 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.

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.