ExamTopics Logo
Mail Us[email protected]
Menu
Microsoft Discussions
exam questions

Exam PL-300 All Questions

View all questions & answers for the PL-300 exam
Go to Exam

Exam PL-300 topic 2 question 21 discussion

Actual exam question from Microsoft's PL-300
Question #: 21
Topic #: 2
[All PL-300 Questions]

HOTSPOT -
You plan to create Power BI dataset to analyze attendance at a school. Data will come from two separate views named View1 and View2 in an Azure SQL database.
View1 contains the columns shown in the following table.

View2 contains the columns shown in the following table.

The views can be related based on the Class ID column.
Class ID is the unique identifier for the specified class, period, teacher, and school year. For example, the same class can be taught by the same teacher during two different periods, but the class will have a different class ID.
You need to design a star schema data model by using the data in both views. The solution must facilitate the following analysis:
✑ The count of classes that occur by period
✑ The count of students in attendance by period by day
✑ The average number of students attending a class each month
In which table should you include the Teacher First Name and Period Number fields? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:

Show Suggested Answer Hide Answer
Suggested Answer:
by Jukibabu at Sept. 6, 2022, 8:47 p.m.

Comments

Chosen Answer:
This is a voting comment (?). It is better to Upvote an existing comment if you don't have anything to add.
Switch to a voting comment New
Submit Cancel
Jukibabu
Highly Voted 2 years, 8 months ago
I'd say: Teacher's dim Class dim
upvoted 224 times
JudT
1 year, 1 month ago
I will go for: Teacher Dim Attendance Fact
upvoted 35 times
...
Ana_L
1 year, 2 months ago
Adding period number to Class dim will fill the dim with dups, since 1 class can have more than one period. That means that class dim can only be used when period is part of the requirement. In real life, this would not work. Actually, the best option is not even proposed. Class period is its own dimension and would connect to the fact. Dim period includes period number, period start time and period end time. Since this option is no available to us, that makes period number a degenerate dimension and it belongs in the fact table. See below: According to Ralph Kimball,[1] in a data warehouse, a degenerate dimension is a dimension key in the fact table that does not have its own dimension table, because all the interesting attributes have been placed in analytic dimensions. The term "degenerate dimension" was originated by Ralph Kimball.
upvoted 7 times
...
Hoeishetmogelijk
2 years, 5 months ago
I agree completely. Period is an attribute of Class: "Class ID is the unique identifier for the specified class, period, teacher, and school year. For example, the same class can be taught by the same teacher during two different periods, but the class will have a different class ID."
upvoted 14 times
Regina18
1 year, 4 months ago
The mean is an aggregate measure that can be calculated on the fact table. However, the dimension table can contain attributes that help you split or filter the average based on certain criteria. "You need to design a star schema data model using the data in both views. Your solution should facilitate the following analysis: ✑ The count of lessons that occur per period ✑ The count of students attending per period per day ✑ The average number of students attending a class each month" so I believe the model must have a fact table. but I see that your model only has dimension tables and it is not correct
upvoted 2 times
...
Hoeishetmogelijk
2 years, 4 months ago
I changed my mind. If there would be a Teacher Dimension, then this Teacher Dimension should have a relationship with the Class Dimension (not directly with the Attendence Fact). That is possible, but that would make it a Snowflake Schema. And what is asked for is a Star Schema. So both TeacherName and Period should be attributes of the Class Dimension.
upvoted 28 times
Ridderxxl
2 years, 2 months ago
But the teacher dim can just use the class id to link to the fact table. No need to have it go through the class dim
upvoted 6 times
semauni
2 years ago
You could even add the teacher id to the class table, I don't see why that's not possible
upvoted 1 times
...
...
...
...
GPerez73
2 years, 8 months ago
Agree with you
upvoted 6 times
...
Load full discussion...
...
olajor
Highly Voted 2 years, 8 months ago
Isn't it teacher dim and attendance fact?
upvoted 112 times
birsne
2 years, 7 months ago
I agree!
upvoted 7 times
...
Turmalino
2 years, 1 month ago
Isn't here an issue with the requirement: "The count of classes that occur by period"? A class can be available without attendance, or am I wrong?
upvoted 3 times
...
...
b92fc92
Most Recent 1 month, 1 week ago
According to this video is: teacher dimension attendance fact https://www.youtube.com/watch?v=l1EA-7KYAwo
upvoted 3 times
...
Curious236
2 months, 1 week ago
teacher has to be dim to count classes that occur in period. period uses in the attendance fact to calculate the remaining measures, which requires date and month to be computed
upvoted 1 times
...
aa83599
3 months, 2 weeks ago
'Teacher' is a Dimension Table, it doesn't get updated constantly
upvoted 1 times
...
jaume
5 months, 2 weeks ago
I would vote for "Teacher Dimension" and "Attendance Fact" as I would use "Teacher ID" for the fact table (it would be "Attendance fact" with details per period as class Id, teacher Id, Student Id, Period Number...) and I would place "Teacher details" (being Teached First Name and "Teacher Last Name") in a dim table (will label it as "Teacher dimension")
upvoted 2 times
...
TMOTM
7 months, 3 weeks ago
Microsoft's questions often stray into areas where ambiguity is induced and thus multiple answers could work. Without the benefit of a test where you can elaborate on that, being limited to multiple choice, I reckon MSFT needs to tighten their questions and remove any ambiguity. It's pretty poor exam writing.
upvoted 14 times
...
FrobeniusHammer
7 months, 4 weeks ago
Proposed Solution: Class Dim Class Dim Reasoning: The problem particularly states that Class ID is an identifier for the class (name and subject), Period #, Teacher, and School Year. We know that Attendance will represent our fact table and relate to the Class Dimension containing those attributes. Teacher information can be contained in this Class Dimension -- the problem states itself that it is. We cannot create a Teacher dimension since we lack a primary key relating to a fact table (unless we also created a Teacher fact, which just creates additional tables without adding any new information to the model, which is unreasonable when the information can already be represented fully by the class dimension, as stated in the problem). If you tried to create a Teacher dim relating to the Class dim on Teacher ID, then it is no longer Star Schema.
upvoted 2 times
Ayman_Abdul_Kareem_1984
5 months, 2 weeks ago
Why don't we create a Teacher dimension table and connect it to the Fact table using the Teacher ID? The teacher ID dimension can also include the teacher's first name and last name.
upvoted 1 times
...
FrobeniusHammer
7 months, 4 weeks ago
So we can create a model in Star Schema that fulfills all the requirements by creating a Class Dimension containing: Class ID, Class Name, Class Subject, ***Period #***, Teacher ID, ***Teacher First***, Teacher Last, and School Year with this Dimension relating to an Attendance fact table on Class ID as primary key containing: Class ID, Attendance Date, Student ID. Note that this model fulfills the 3 analytic requirements as well: ># Classes by period #: >>Count of classes grouped by Class[PeriodNumber] ># Students by period by day >>Count of rows grouped by AttendanceDate in the Attendance fact table >Avg students by class by month >>Count of rows in Attendance fact grouped by AttDate & ClassID, then averaged over a month period
upvoted 2 times
...
...
rcaliandro
8 months, 3 weeks ago
I'd say teacher dim, class dim
upvoted 1 times
...
greenlever
11 months, 2 weeks ago
Class dim Class dim
upvoted 2 times
...
bugproof
12 months ago
I would say: 1. Class DIM 2. Class DIM The question says the "Class ID" column is a unique identifier for the specified class, PERIOD, TEACHER, and school year. So if I connect Class ID to the same column in an Attendance Fact Table and then use the period column from dimension to look at the count of class id from fact - this would give me a break down of period by attending classes.
upvoted 3 times
...
shazzzy
1 year ago
I was wondering if there should be a dim.period, dim.teacher & dim.class. Then technically you would put Period number in the fact.attendence although I would personally call it Period Id, but there's nothing saying that the star schema uses 'id' and not 'number' as it's key column naming convention. If period is in dim.class it's not normalised properly. SO if that is the correct answer then I'm afraid Microsoft have messed up here.
upvoted 1 times
shazzzy
1 year ago
The only issue is this: The count of classes that occur by period. We can't do this because we don't know the instances of what classes are assigned to what periods.... we would need another table for that and therefore, the answer must be a really SLOPPY Dim.Class for the Period.
upvoted 1 times
...
...
tarekff
1 year ago
i would choose Teacher Dim and Class dim The teacher's first name and period number are dimensional fields and therefore shouldn't reside in the fact table. Instead, they belong in dimension tables. in star schema, we have a fact table containing attendance data linked to other dimensions such as teacher and class. Consequently, both the teacher's first name and the period number should be included in dimension tables like the Teacher dimension and the Class dimension, respectively.
upvoted 1 times
...
Zach0308
1 year ago
100% confirmed, it's Teacher dim and Class dim. How come so many of these answers are wrong?
upvoted 3 times
...
55884
1 year, 1 month ago
I would say it's: Teacher First Name: Teacher Dim Period Name: Attendance Fact In this STAR Schema case, we only need to have 1 fact table. And since the question mentioned our purpose "to analyze ATTENDANCE at school". So, Fact Attendance should be the only fact table in the star schema (not Fact Teacher), since fact table is defined as "stores measures that measure the business". So, in able to analyze ATTENDANCE, we need to collect as much as measures of attendance data, which will be stored in attendance fact!
upvoted 7 times
...
Giuditta
1 year, 1 month ago
i'll say class dim and attendance fact. the question requires a star schema so we cannot have more than one fact and only dimension that relates to fact. our fact is attendance (because is the many side of the raltionship). and we have a class dimension. it will be possible to create a teacher dim, but it will be related to the class dim, and not tthe fact. so it will not be a star schema anymore but a snowflake. so we need to keep the tables as they are in the question. having only a class dimension (first dropdown) and a attendance fact (second one)
upvoted 5 times
AaronBI
6 months, 2 weeks ago
This was my thought as well. Period dim could be created just like a date dim, relating to the attendance fact table. And teacher would be inside class dim, unless we took extra steps to create surrogate keys inside fact table. Just another example of a poorly written question.....
upvoted 1 times
...
...
rajukg
1 year, 2 months ago
Answer is correct. There will be two fact tables : Teacher fact and Attendance Fact in the model. (That is better model than creating the Teacher dimension).
upvoted 2 times
Elektrolite
1 year, 1 month ago
Teacher can't be a fact. One class has one teacher, one teacher can teach many classes.
upvoted 1 times
...
...
Load full discussion...
Community vote distribution
A (35%)
C (25%)
B (20%)
Other
Most Voted
A voting comment increases the vote count for the chosen answer by one.

Upvoting a comment with a selected answer will also increase the vote count towards that answer by one. So if you see a comment that you already agree with, you can upvote it instead of posting a new comment.

SaveCancel
exam
Someone Bought Contributor Access for:
SY0-701
London, 1 minute ago