7BUIS010W Data Warehousing and OLAP Assignment

Assignment Help Data Structure & Algorithms

Reference no: EM133099396

7BUIS010W Data Warehousing and OLAP - University of Westminster

Learning Outcome 1: critically evaluate different data warehousing and analysis approaches and their relevant benefits to business intelligence, data mining and analytics;

Learning Outcome 2: critically analyse the relevant merits of a data integration and data warehousing architecture;

Learning Outcome 3: conceptualise data modelling requirements in data warehouses for the needs of subject oriented analysis;

Task

Task Description

Problem specification:
Design a data mart solutionfor theEuropean Centre for Disease Prevention and Control (ECDC)to monitor the daily number of new reported COVID-19 cases and deaths, Intensive care unit cases and vaccination progress across the EU/EEA area.

Information regarding of new reported COVID-19 cases and deaths, Intensive care unit cases and vaccination progress is logically described with the of a UML class diagram shown in Figure-1.Primay keys are denoted as {PK} and the foreign keysare followed with {FK}.

1666_Data mart.jpg

Figure-1. Relational database schema derived from ECDC

The European health authorities would liketo build a strategic data martto answer some frequent queries the managers would like to answer are the following.

1. For each region and month report the COVID-19 cases and deaths, Intensive care unit cases as reported by different sources and vaccination rates for each vaccine per target group.

2. For eachcountry and quarter report the COVID-19 cases and deaths, Intensive care unit cases and vaccination rates per target group

3. For each country and year report the COVID-19 cases and deaths, Intensive care unit cases and vaccination rates for the plus 60+ group

Design

Considering the designed data mart and its cardinality, decide whether and which materialized views are convenient to improve response time of the frequent queries (consider all the frequent queries 1-3).
Please also consider attached information provided by European Centre for Disease Prevention and Control (ECDC) regarding
• Variable_Dictionary_VaccineTracker
• Variable_Dictionary_and_Disclaimer_hosp_icu_all_data
• Variable_Dictionary_and_Disclaimer_variant-data

Task Deliverables

1. Based on the integrated relational schema,in Figure-1,design a data warehouse model (DFM); in particular, the designed data mart must promptly answer to all the frequent queries ‘1-3'.

i. Build the Attribute Tree from the integrated relational schema

ii. Build the Fact Schema from Attribute Tree

Question 2. Map the DFM model to a logical model (i.e. relational). Clearly display the main fact table(s) and dimensions.

Question 3. Implement the above logical as a working data warehouse schema, under MySQL/R, or any other suitable DBMS. Provide the DDL statements to create the proposed data-warehouse schema.

Question 4. Considering the designed data warehouse and its cardinalities, decide whether and which materialized views are convenient to improve response time of the frequent queries (consider all the frequent queries).Explain the reasons for your choices

Question 5. Provide and implement a materialised view(s) to answer the directors frequent queries ‘1-3'

1. For each region and month report the COVID-19 cases and deaths, Intensive care unit cases as reported by different sources and vaccination rates for each vaccine per target group.

2. For each country and quarter report the COVID-19 cases and deaths, Intensive care unit cases and vaccination rates per target group.

3. For each country and year report the COVID-19 cases and deaths, Intensive care unit cases and vaccination rates for the plus 60+ group

Attachment:- Data Warehousing and OLAP.rar

Reference no: EM133099396

Questions Cloud

Real-world example of a going private transaction : Explain in full detail the Real-World Example of a Going Private Transaction?

Analyze chain and nature of communication : Analyze the chain and nature of the communication among leaders and subordinate personnel.

What was the firm economic value added : Elike Stores reported $253,130 of sales and $137,698 of operating costs (including depreciation). What was the firm Economic Value Added

Identify opportunities for individuals : How can you identify opportunities for individuals and the team to improve? Mention any three (3) ways.

7BUIS010W Data Warehousing and OLAP Assignment : 7BUIS010W Data Warehousing and OLAP Assignment Help and Solution, University of Westminster - Assessment Writing Service

Culturally and linguistically diverse backgrounds : If you are from an English-speaking background, what communication strategies can you adopt when working with people who are from culturally and linguistically

Compute the break-even point in dollars and in units : Mandeep's Beauty Salon gave 2,100 haircuts, shampoos and permanents at an average price of $32. Compute the break-even point in dollars and in units

Organization and draw organization structure : Select an organization and draw its organization structure. Do they follow a functional, divisionalized, or matrix structure? Analyze the six key elements of st

Performance indicators for pa tools : Keng Finance Company is a lending firm with 38 locations throughout the Philippines. It is a highly centralized organization with its headquarters in Cebu City.

User Account

All Pages