Reference no: EM132503519 , Length: 800 words

Design and Implement a Data Warehouse for Successful Olympic Performance consultant agency (SOPCA).

A data warehouse is an extremely useful tool that can be used for both analysis and analytics. You have been asked to design and implement a data warehouse for a company that assists coaches and Olympic teams worldwide. They have contracts with a number of national Olympic teams and individual athletes and they work hand in hand with the international Olympic committee.

Overview
SOPCA has commissioned you to design and implement a Data warehouse. The aim is to produce an example of a suitable data warehouse design that can be used to demonstrate to the agency the benefits to themselves and to the countries' Olympic teams. The demonstrations of the data warehouse will include some visualisation too.

You have been granted access to a large dataset of their historical data. The data are accessible in Moodle under coursework heading. The data may need some cleaning before they can be used. There may be other tables and attributes that you consider are required. In this case, you are encouraged to add them as you see fit. Attribute types and sizes; it is up to you to decide on appropriate values.

Therefore your tasks are:

Problem 1:
Define a Constellation schema. Your schema should cover two subjects (i.e. two fact tables). You must present your design as one constellation diagram (which is simply a collection of multiple fact tables which share dimension tables).

You are expected to create and populate further tables with dummy data, which will enhance the experience and rationale of the data warehouse. You can generate your own dummy data any way you see fit,

Problem 2:
Create and populate the tables on your VMs using PostgreSQL or on SQL Developer Web using PL/SQL.

Problem 3:
Write and run 4 SQL queries. Your queries must be meaningful and demonstrate the strength of DW in supporting decision makers. Also, your queries must cover all subjects in your DW. You should provide a short description of each query.

Your queries and your table creation and population should be on the same SQL version, for example if you use Oracle PL/SQL to create and populate the data warehouse, then you should use Oracle PL/SQL for your SQL queries.