Learn how to identify the fact table and surrogate key when designing a star schema data model for Power BI. Understand the role of each table and key in the schema.
Table of Contents
Question
You are designing the data model for a Power BI semantic model.
You have the following tables in the star schema.
| Name | Description |
|---|---|
| Date | Contains one row for each day from the last five years: Each row contains attributes for the year, quarter, month, week of the year, and day of the week. Date is the unique identifier of a row. |
| Patient | Contains one row per patient: Each row contains attributes for the patient key, patient key, patient source ID, first name, last name, date of birth, gender, address, city, state, and country. Patient key is the unique identifier of a row. |
| Test | Contains one row per test: Each row contains attributes for the test key, test source ID, type, and name. The test key is the unique identifier of a row. |
| Test Result | Contains one row per administered test: Each row contains attributes for the date the test was adminisered, test key, patient key, result value, and comments. |
Which table is the fact table of the star schema, and which column in the Patient table is the surrogate key of the star schema? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Fact table:
A. Date
B. Patient
C. Test
D. Test Result
Surrogate key:
A. Date of birth
B. Last name
C. Patient key
D. Patient source ID
Answer
Fact table: Test Result
Surrogate key: Patient key
Explanation
The fact table in the given star schema is the Test Result table. A fact table contains the measurements, metrics or facts of a business process. It is the central table in a star schema that stores the numeric values which are the facts.
In this schema, the Test Result table fits the criteria of a fact table because it contains a row for each administered test with the result value. The other tables – Date, Patient, and Test – are dimension tables that provide context and details about the facts in the Test Result table.
The surrogate key in the Patient dimension table is the Patient key column. A surrogate key is a unique identifier for each row in a dimension table that is not derived from the data itself. It is artificially generated, usually as an auto-incrementing integer.
In contrast, the Patient source ID is a natural key because it comes directly from the source data. The Patient key serves as the surrogate key to uniquely identify each patient regardless of the source system and links the Patient dimension to the facts in the Test Result table.
In summary, the Test Result table is the fact table and the Patient key is the surrogate key in the Patient dimension table for this star schema data model in Power BI.
Microsoft PL-300 certification exam assessment practice question and answer (Q&A) dump including multiple choice questions (MCQ) and objective type questions, with detail explanation and reference available free, helpful to pass the Microsoft PL-300 exam and earn Microsoft PL-300 certification.