Data Design and Populate the Data Warehouse on Olympic Dataset

Assignment Task

Datasets and Problem Domain

Prescribed datasets: the source data to design and populate the data warehouse in this project is based on the Olympic Dataset.

The Olympic Games represent the sole global, multi-disciplinary sports event, celebrated worldwide. Featuring participation from over 200 nations in more than 400 events spanning both the Summer and Winter Games, the Olympics serve as a platform for global competition, inspiration, and unity.

Paris 2024 will host the XXXIII Olympic Summer Games , 26 July to 11 August. 

Data sources

• olympic_hosts.csv and olympic_medals.csv originate from Olympic Summer & Winter Games, 1896-2022
• mental-illness.csv and life-expectancy.csv are sourced from Our World in Data ('DALYs' in the dataset stand for Disability-adjusted life years)
• Global Population.csv is obtained from the International Monetary Fund (IMF)
• Economic data.csv is derived from world-development-indicators
• list-of-countries_areas-by-continent-2024.csv is obtained from the World Population Review.

Data Warehousing Design and Implementation

Following the four steps below of dimensional modelling (i.e. Kimball's four steps), design a data warehouse for the dataset(s)

• Identify the process being modelled.
• Determine the grain at which facts can be stored
• Choose the dimensions
• Identify the numeric measures for the facts.

To realise the four steps, we can start by drawing and refining a StarNet with the above four questions in mind.

• Think about a few business questions that your data warehouse could help answer.
• Draw a StarNet to identify the dimensions and concept hierarchies for each dimension. This should be based on the lowest level of information you have access to.
• Use the StarNet footprints to illustrate how the business queries can be answered with your design. Refine the StarNet if the desired queries cannot be answered, for example, by adding more dimensions or concept hierarchies.
• Once the StarNet diagram is completed, draw it using software such as Microsoft Visio (free to download under Azure Education) or a drawing program/website of your own choice. A Hand-printed StarNet diagram is also welcome! Paste it onto an Atoti/Power BI Dashboard.
• Implement a star or snowflake schema using SQL Server Management Studio (SSMS), or PostgreSQL, or other software. For the fact table and dimension tables, clearly state which ones are measures and dimensions, and indicate the dimension references. Include the database ER diagram in the report.
• Load the data from the CSV files to populate the tables. You may need to create separate data files for your dimension tables.
• Use Atoti to build a multi-dimensional analysis service solution, with a cube designed to answer your business queries. Make sure the concept hierarchies match your StarNet design.
• Use Power BI/Atoti to visualise the data returned from your business queries.

Association Rule Mining

Make sure you complete the relevant lab before attempting this task. The lab content may be helpful for you in completing this part.

We are using this example to show how association mining can be applied to this Olympic Games dataset, there are certainly other more suitable scenarios for association rule mining, in particular, if the market basket analogy fits well.

Do you get some insights from the mining results? If no meaningful rules are found, what could be the reason?

If we treat each discipline_title and the medal_type of each country's records as a transaction. Each transaction contains multiple discipline_title types. For example, Australian athletes attended multiple disciplines, e.g. the Canoe Sprint in Tokyo-2020, Basketball in Tokyo-2020, and Swimming in Tokyo-2020.

Meanwhile, in the submitted PDF, you need to:

• Explain the top k rules (according to confidence or lift) that have the "discipline_title" type (or other suitable columns) on the right-hand side, where k>=1.
• Explain the meaning of the k rules in plain English.

Answer the following question in your report

Some articles argue that the data cube is an outdated technology. For example, in a 2023 article, Albert Wong argued that 'database cubes were popular in the early days of data warehousing, but they have largely been replaced by other technologies.' Do you agree or disagree with this point? If you agree with this point, discuss your reason. If you disagree with this point, discuss your reasons and explain at least one technology that can replace the data cube. (Please include the answer in your PDF. The word limit is 300 to 500 words.)

This Data Warehouse has been solved by our PhD Experts at My Uni Paper.