Microsoft Certified Power Bi Data Analyst Associate · Free Practice Question Hard
Question 15
GenAffair, Inc. Case Study
This is a case study. Case studies are not timed separately. You can use as much test time as you would like to complete each case. However, there may be additional case studies in this test. You must manage your time to ensure that you are able to complete all questions included in this test in the time provided.
To answer the questions included in a case study, you will need to reference information that is provided in the case study. Case studies might contain exhibits and other resources that provide more information about the scenario that is described in the case study. Each question is independent of the other questions in this case study.
A key point to note is each case study question builds on insights from the previous question. You can return to these case study questions and make changes until the test is submitted.
GenAffair, Inc. is an Indian online fashion company that uses Microsoft Power BI for its dashboards and reports. The company plans to leverage data from its operational data stores, flat files, and several other data sources.
GenAffair, Inc. has an online sales Data warehouse with the SQL schema shown in the following exhibit.
A few points to note about the data model:
a. The Date Key / Order Date Key / Ship Date Key fields have the format yyyymmdd.
b. The Month field in the Date table has the format yyyymm. The Month field in the Monthly Sales Target table stores a date (the first day of a month in the format yyyymmdd). For example, 20190301 for March, 20190601 for June, etc.,
c. The Week Key / Week fields have the format yyyyww.
d. Only one Manager is responsible for a State. A Manager can handle more than one state (s).
e. Multiple states are part of a region.
Below are the requirements of various stakeholders using the model:
1. Due to potential performance issues, the engineers at the company want to minimize the use of bidirectional cross-filters in the data model.
2. The company directors would like to analyze orders by the date hierarchy (Year, Quarter, & Month).
3. Ensure no data is lost during any operation. And give preference to an optimal solution.
4. Marketing managers need to analyze the sales report using long dates in the format 01 Tue, January 2019
Q] All orders in a month may not be shipped in the same month. Probably, 90% of the orders might ship within the same month, and the orders placed toward the month’s end may be shipped the subsequent month.
To illustrate this information, you need to build the below report. What changes would you make to the existing data model?
-
A
Create an inactive relationship between Sales[Ship Date Key] and the Date table. Use the USERELATIONSHIP DAX function.
-
B
Extract the Order Month and Ship Month fields from the Order Date Key and Ship Date Key in the Sales table respectively. Use these extracted fields in the report.
-
C
Rename the existing Date dimension table/fields for Order Date. Duplicate a new dimension table Ship Date. Create two many-to-one relationships: (Sales[Order Date Key] -> Order Date[Order Date Key]), (Sales[Ship Date Key] -> Ship Date[Ship Date Key]).
Reveal correct answer
Correct answer: C
Explanation
In case of multiple relationships between two tables, you can use the DAX formula USERELATONSHIP to activate an inactive relationship for use in a calculation. But to build the given report, there isn't any need to do a calculation using the inactive relationship.
We only need to display two fields (Order Month and Ship Month) from the role-playing dimension table, i.e., the Date dimension.
For example, USERELATIONSHIP can be a good candidate if you need to calculate Total Orders and Total Shipments for a month (USERELATIONSHIP can activate the inactive relationship to filter the Ship date Key).
But using the solution in option A means using both the months from the same Date dimension. So, you don’t get the expected result as stated in the question.
Option A is incorrect.
Reference Link: https://learn.microsoft.com/en-us/dax/userelationship-function-dax
Option B produces the output we need (Download the PBIX files).
But this is not an optimal solution. Below are the reasons:
1. The fact table generally contains millions of rows whereas a dimension table (Date) contains far fewer rows. Adding additional columns in the fact table increases the size of the data model very quickly.
2. Furthermore, if there are additional fact tables in the model, a separate dimension table connected to all facts is far better than adding these columns to every fact table.
3. If we indeed use this solution, then it begs the question, why do we even have the Date dimension table?
3. Finally, understand the purpose of a dimension table. A fact table is used to store facts (transactions). It is the purpose of a dimension table to help analyze facts by filtering, grouping, or aggregating data.
Option B is incorrect.
The correct solution will be to duplicate the Date dimension table and rename the two date tables for Ship Date and Order Date. Then create separate many-to-one relationships between the Sales table and these two tables.
This will enable us to independently use Order Month and Ship Month at the same time in the report.
This model produces the correct output and satisfies the requirements.
Reference Link: https://learn.microsoft.com/en-us/power-bi/guidance/relationships-active-inactive#active-relationships
PBIX File Link: GenAffair, Inc. Case Study - 8
Discussion
Think the marked answer is wrong, or have a better explanation? Share it below — comments appear after review.
