nerdexam
Microsoft

DP-500 · Question #21

You have a Power BI dataset named Dataset1 that uses DirectQuery against an Azure SQL database named DB1. DB1 is a transactional database in the third normal form. You need to recommend a solution…

The correct answer is D. Denormalize the tables in DB1. To minimize DirectQuery execution time from Power BI against a transactional Azure SQL database in third normal form while maintaining functionality, denormalizing the tables in DB1 is recommended.

Implement and manage data models

Question

You have a Power BI dataset named Dataset1 that uses DirectQuery against an Azure SQL database named DB1. DB1 is a transactional database in the third normal form. You need to recommend a solution to minimize how long it takes to execute the query. The solution must maintain the current functionality. What should you include in the recommendation?

Options

  • ACreate calculated columns in Dataset1.
  • BRemove the relationships from Dataset1.
  • CNormalize the tables in DB1.
  • DDenormalize the tables in DB1.

How the community answered

(64 responses)
  • A
    3% (2)
  • B
    8% (5)
  • C
    16% (10)
  • D
    73% (47)

Why each option

To minimize DirectQuery execution time from Power BI against a transactional Azure SQL database in third normal form while maintaining functionality, denormalizing the tables in DB1 is recommended.

ACreate calculated columns in Dataset1.

Creating calculated columns in Dataset1 (Power BI) shifts computation to the Power BI service, but it doesn't directly reduce the time it takes for the underlying DirectQuery to execute against the source database.

BRemove the relationships from Dataset1.

Removing relationships from Dataset1 would break the data model and functionality, preventing correct analysis and violating the requirement to maintain current functionality.

CNormalize the tables in DB1.

Normalizing the tables in DB1 further would increase the number of tables and joins, which would likely increase query execution time for analytical queries, directly contradicting the goal of minimizing query time.

DDenormalize the tables in DB1.Correct

Denormalizing tables in DB1 means introducing redundancy and combining data from multiple related tables into fewer tables, typically creating wider tables optimized for reading. This reduces the number of joins required for analytical queries originating from Power BI DirectQuery, thereby minimizing query execution time against the transactional database.

Concept tested: DirectQuery performance optimization for Power BI

Source: https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-directquery-about#performance

Topics

#DirectQuery optimization#Database design for analytics#Denormalization#Performance tuning

Community Discussion

No community discussion yet for this question.

Sign up to join the discussion
Full DP-500 Practice