Which data modeling design should you use?

You have sales data in a star schema that contains four tables named Sales, Customer, Date, and Product. The Sales table contains purchase and ship dates.

Most often, you will use the purchase date to analyze the data, but you will analyze the data by both dates independently and together.

You need to design an imported dataset to support the analysis. The solution must minimize the model size and the number of queries against the data source.

Which data modeling design should you use?

A. Use the Auto Date/Time functionality in Microsoft Power Bl and do NOT import the Date table.

B. Duplicate the Date query in Power Query and create active relationships between Sales and both Date tables in the modeling view.

C. On the Date table, use a reference query in Power Query and create active relationships between Sales and both Date tables in the modeling view.

D. Import the Date table twice in Power Query and create active relationships between Sales and both Date tables in the modeling view.

View Answer

Answer: D

Explanation:

Microsoft recommends defining active relationships whenever possible. They widen the scope and potential of how your model can be used by report authors, and users working

with Q&A.

Refactoring methodology (example): Here’s a methodology to refactor a model from a single role-playing dimension-type table, to a design with one table per role.

✑ Remove any inactive relationships.

✑ Consider renaming the role-playing dimension-type table to better describe its role. In the example, the Airport table is related to the ArrivalAirport column of the Flight table, so it’s renamed as Arrival Airport.

✑ Create a copy of the role-playing table, providing it with a name that reflects its role. If it’s an Import table, we recommend defining a calculated table. If it’s a DirectQuery table, you can duplicate the Power Query query.

Only one relationship can be active.

Note: If you query two or more tables at the same time, when the data is loaded, Power BI Desktop attempts to find and create relationships for you. The relationship options Cardinality, Cross filter direction, and Make this relationship active are automatically set.

Reference:

https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-create-and-manage-relationships

https://docs.microsoft.com/en-us/power-bi/guidance/relationships-active-inactive