Tior Gaming Data Warehouse Report and Visualizations

SQL Queries

Task 1: Individual - Data warehouse

Use the Tior Gaming data warehouse.

Submit a PDF file - report of up to 2000 words in total, answering ALL of the following questions: 

You must include references to where is needed.

Q1. Write and run 5 SQL queries. You must submit a screenshot of the queries running and its results as well as the short description of the rationale in no more than 100 words per query.

?Your queries must be meaningful and demonstrate the strength of DW in supporting decision makers.

?Your queries must use a broad range of fact tables and dimensions of the provided DW. 

?You should provide a short description of each query, to explain the rationale for creating it.

?All 5 queries must be different from each other, using different fact tables and dimensions. 

?All 5 queries should include at least one data warehouse concept. Queries such as select from tablename and repetitive queries, will get marked as zero. 

{Total marks: 20}

Q2. Modify the given schema and suggest at least 2 more dimensions that would provide you with insights that you wish there were there. You must submit the 2 dimensions, the data dictionary for them and the rationale report.

?Provide reasoning and rationale for your choice (also known as: why these dimensions and how can we use them?) in no more than 400 words.

?You need to submit these two dimensions, following the same naming convention that exists in the data warehouse.

?These two dimensions should seamlessly blend together with at least one fact table or dimension of the current data marts.

?You have to include a data dictionary following the same format of the given data dictionary.

{Total marks 16}

Q3. You are given this query from one of your data junior analysts:

Your task is: 

?To describe what the correct version of the query does (no more than 100 words)

?To identify the problem (no more than 100 words)

?To solve this problem the best way possible - keep in mind that the rationale of the query must remain the same. Feel free to modify everything (data, dimensions, fact tables or query or an assortment of them) to achieve the same rationale and discuss it in not more than 400 words. 

You must submit a screenshot of the final query running and its results (including error).

You are not allowed to alter the data, if you decide to make changes on the schema or the data, for this query’s rationale to work, you have to report them in writing.

Modifying Schema for New Dimensions

{Total marks 6}

Q4. A common way of introducing data warehousing is to refer to its fundamental characteristics. Describe three characteristics of data warehousing. Use no more than 250 words in total.

{Total marks 4}

Q5. Sometimes a fact table references a dimension table multiple times as a foreign key. Provide two generic examples that justify the given statement. 

For example, an employee and her manager are related to the same fact table in workforce-related analysis.

Use no more than 150 words in total.

{Total marks 4}

Use the Tior gaming data warehouse. You must use some external data too. 

Create an online 15 min presentation.

Submit a PDF file - with your presentation. 

The DBA was impressed with your work and suggested that you give a presentation with some of your findings to the board of directors of Tior Gaming.

Your presentation must cater people who have little to no technical knowledge and no IT background.

The presentation will be 15 minutes long plus 5 minutes for questions.

You may use any software to create your presentation.

The presentation will be split in 2 parts:

This part of the presentation must include a dashboard with 3 visualizations. 

All the data on these 3 visualizations must come from the Tior Gaming data warehouse and you must decide in your teams which of the queries from your individual work(previous submission), Question 1 you will visualize and why. You must talk about this decision briefly, during the presentation. 

You must use Tableau to create your dashboard.

You can create more than 3 visualizations, but they will not get extra marks.

In every presentation a good story needs to end with either some cheerful note or a prediction for the future. Given the fact that you present in the board of directors, your DBA suggests that you take 5 minutes in the presentation and discuss future gaming trends in the market.

For this part you must create a storyboard with two visualisations - completely different to Part 1!

You must include data from the Tior Gaming data mart and external data. Feel free to use any external sources, which need to be referenced.

Both visualisations must predict something useful and reasonable for the future! You are given full freedom as to what.

Some suggestions may include, but are not limited to: PC vs console gaming sales, gaming sales trends, user experience based on different factors, popularity of a game during covid19 etc.

You must use Tableau to create your visualisations.

You can create more than 2 visualizations, but they will not get extra marks.