PL-300 Question #71: Real Exam Question with Answer & Explanation

Question

Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is the same in each question in this series. You have a Microsoft SQL Server database that contains the following tables. The following columns contain date information: - Date[Month] in the mmyyyy format - Date[Date_ID] in the ddmmyyyy format - Date[Date_name] in the mm/dd/yyyy format - Monthly_returns[Month_ID] in the mmyyyy format The Order table contains more than one million rows. The Store table has a relationship to the Monthly_returns table on the Store_ID column. This is the only relationship between the tables. You plan to use Power BI Desktop to create an analytics solution for the data. You need to create a chart that displays a sum of Order[Order_amount] by month for the Order_ship_date column and the Order_date column. How should you model the data?

Options

• ACreate a one-to-many relationship from Date[Date_ID] to Order[Order_date] and another
• BAdd a second Date table named Ship_date to the model. Create a many-to-many relationship
• CAdd a second Date table named Ship_date to the model. Create a one-to-many relationship from
• DCreate a one-to-many relationship from Date[Date_ID] to Order[Order_date] and another

Explanation

To enable analysis by both Order_date and Ship_date from an Order table using a common Date dimension, create a second role-playing Date table for the additional date attribute.

Common mistakes.

• A. Creating two relationships from the same Date table to the Order table is problematic because Power BI allows only one active relationship between two tables, limiting simultaneous analysis by both date types without complex DAX.
• B. A many-to-many relationship from a Date table to a fact table's date key is generally an incorrect cardinality; date dimensions should typically have a one-to-many relationship to the fact table.
• D. Similar to A, using two relationships from the same Date table, even with an inactive one, requires explicit DAX functions like USERELATIONHIP for the inactive relation, which is less straightforward than having two active role-playing dimensions.

Concept tested. Role-playing dimensions and active/inactive relationships

Reference. https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-create-and-manage-relationships#multiple-relationships

No community discussion yet for this question.