Topic: PL-300 topic 2 question 21

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:

Re: PL-300 topic 2 question 21

I'd say:
Teacher's dim
Class dim

  • Ana_L
  • New member
  • Offline

Re: PL-300 topic 2 question 21

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.

  • JudT
  • New member
  • Offline

Re: PL-300 topic 2 question 21

I will go for:
Teacher Dim
Attendance Fact

Re: PL-300 topic 2 question 21

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."

Re: PL-300 topic 2 question 21

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

Re: PL-300 topic 2 question 21

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.

Re: PL-300 topic 2 question 21

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

Re: PL-300 topic 2 question 21

You could even add the teacher id to the class table, I don't see why that's not possible

Re: PL-300 topic 2 question 21

Agree with you

Re: PL-300 topic 2 question 21

Isn't it teacher dim and attendance fact?

Re: PL-300 topic 2 question 21

I agree!

Re: PL-300 topic 2 question 21

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?

Re: PL-300 topic 2 question 21

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.

Re: PL-300 topic 2 question 21

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.

Re: PL-300 topic 2 question 21

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.

Re: PL-300 topic 2 question 21

100% confirmed, it's Teacher dim and Class dim. How come so many of these answers are wrong?

  • 55884
  • New member
  • Offline

Re: PL-300 topic 2 question 21

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!

Re: PL-300 topic 2 question 21

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)

Re: PL-300 topic 2 question 21

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).

Re: PL-300 topic 2 question 21

Teacher can't be a fact. One class has one teacher, one teacher can teach many classes.

  • Usm_9
  • New member
  • Offline

Re: PL-300 topic 2 question 21

WAS ON THE EXAM 02 03 2024

Re: PL-300 topic 2 question 21

ChatGPT: Teacher dim, Class dim

  • Ana_L
  • New member
  • Offline

Re: PL-300 topic 2 question 21

Class dim should include one record per class.  But a given class can multiple periods.  For example, class PL-300 can have period 1 and period 2.  Having the period in the class dimension would brake the grain, unless we use Kimbal's grouping approach, where we could have 1 column in class dimension called period_group and could have class PL-300, period_group 1,2.  But the question does not include this option.  Another way to design it is to treat period as a degerate dimension, which means add period to the fact.

  • Ana_L
  • New member
  • Offline

Re: PL-300 topic 2 question 21

It is not degerate dimension, it is degenerate dimension.  According to Ralph Kimball, 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. All interesting attributes related to period are in the class dimension.