PL/SQL

Assignment B Data Mart Design & Development

The objective of this assignment is to design a dimensional model and develop the ETL code to populate the dimensional model. The assignment will test your ability to understand the data in a schema and your ability to develop an appropriate dimensional model. The assignment will require you to write some simple programs in SQL and PL/SQL.

Part 1 – ������� Initial data extract & load

The first for of the assignment involves the uploading of a dataextract from the production database. You will be provided with a number of files. These will include the DDL for the TABLES and the initial data extract for each of the tables. The initial data extract will consist of scripts that you can run. These will insert the data into the tables (you do not need to write code of this part).

In your Oracle schema you should run these scripts to create the tables and populate them with the initial data extract.

Output from this stage

You need to produce the following:

! Full ER diagram to represent the TABLES extracted from the production schema

! Details of all primary and foreign keys that you can identify

! A data dictionary

Files include:

create_tables.sql – �������- ������� ddl script to create the tables product_data_initial.sql promotions_data_initial.sql channels_data_initial.sql countries_data_initial.sql customers_data_initial.sql

sales_data_initial.sql

These files can be found on the module webpage and should be saved to your U: drive

In SQL Developer run the following commands to run these SQL scripts @u:create_tables.sql
@ u:product_data_initial.sql

@ u:promotions_data_initial.sql

@ u:channels_data_initial.sql

@ u:countries_data_initial.sql

@ u:customers_data_initial.sql

@ u:sales_data_initial.sql

Part 2 – ������� Dimensional Model Design

The second part of your assignment is to design a dimensional model which will be used by the Finance Department for analysing daily Sales, based on the data loaded in Part A.

The Data Mart design should consist of :

����� The Dimensional model for the Data Mart (only one Star Schema is required)

! Discussion of any design decisions made

You should use the steps provided by Ralph Kimaball to assist you in the design of your dimensional model.

Assignment B Data Mart Design & Development

Part 3 – ������� Populate the Dimensional Model

The third part of the assignment is to design and develop the programs that are required to extract the data from the production data (which you have loaded into your schema in part 1) and populate the Sales Dimensional Model that you designed in Part 2. This is part of the overall ETL process. The E (Extract) has already been done for you. What you need to do is to define all the data mappings, translations, transformations, etc. required to Load the data into the Dimensional model. You will need to aggregate the data up to a daily level in addition to the other dimensions you have defined

You are required to produce the following:

! Diagram showing the data flows and processes/programs

! Program specifications

! Explanations of all design decisions, translations, transformations, etc.

! Fully developed programs

! Details of all testing

All programming should be done in SQL and/or PL/SQL.

Part 4 – ������� Data Quality Assessment of ETL Process

Perform a data quality assessment of the ETL process to ensure that the process is working correctly, all the data is being transferred and all data that is generated and aggregated into the dimensional model is as you would expect it. Investigate and analyse the data and write a short report of the steps you have completed and what you can report on these steps.

Part 5 – ������� Second extract from production database & population of dimensional model

The fourth part of the assignment involves the the analysis of a second extract of data from the production system, the identification of the data changes and the program changes necessary to be able to process the changes in the data

Some of the data provided may involve changes/modification to the dataand/or structures of the tables that was previously loaded.

You should design how this changed/modified data should be processed.

You are required to produce the following:

Analysis of the data changes

Design of how you are going to process the modified data structures

Design of how you are going to process the changes to the data

The data files are available on the module webpage and have a similar naming structure to the initial data. For this part of the assignment they are labelled * 2nd_Extract.sql

Assignment B Data Mart Design & Development

Submission Details

The assignment is due by Monday 21st November @23:00.

You should create one document/report containing all the material for each part of the assignment. Convert this document into a PDF.

Marking Scheme

The total marks overall for Assignment A & B is 100% and are divided as follows:

Assignment B – ������� Part 3

25%

– ������� Populate the Dimensional Model

Assignment B – ������� Part 4

15%

– ������� Data Quality Assessment

All supporting data files can be made available .

need to work on part 3 and 4.

 
"Looking for a Similar Assignment? Get Expert Help at an Amazing Discount!"