NHS Data Warehouse

Question

I have got coursework, which I do not understand, I tried emailing my tutor but he did not respond and I have been waiting for about 2 months now... I am supposed to create a Star/Snowflake Schema focusing on 2 fact tables.

The project must focus on the NHS, we are free to define the scope so I decided to focus on COVID-19. I have created a star schema for 1 fact table, which is called "Deaths", my idea is the data warehouse to show which areas have the highest death rate so that the NHS knows which areas are in demand in order to manage the situation accordingly.

I was thinking, the second Fact table to be Infection/Infected, which is supposed to see which areas have the highest infection rates. I think that it would not work because the dimension for "Infected" should be different than the ones for deaths( I am not sure if they have to be the same)?

Could you share with me your thoughts and recommendation? Here is the assignment brief and below the brief is my star schema design(Which I think is wrong).

Answer 1

I don't see the need of having two facts one for recovered and one for death cases. You can have an only one FactDiagnosticAnalysis gathering :

• TreatmenCenterSK
• PatientSK
• TreatmenSK
• StaffSK
• DiagnosticSK
• DateSK
• Result
• InsertedDate : a technical column to capture when the record was insterted

The Result column will have the values : Infected,Not Infected, Recovered,Dead at a specific date since :

• a patient will have many analysis until his recovery
• a patient can be not infected when he arrives after doing the analysis
• a patient will be recovered after many analysis
• a patient can die after many analysis

Your model can be like below :

Actually, in this case your fact is a factless fact. A factless fact table captures the many-to-many relationships between dimensions, but contains no numeric or textual facts.

You ceate the measures in your reports/dashboards as views (if you are using SQL):

• Area having the highest death rate
• The number medical centers reaching their maximum capacities