Designing and Implementing Enterprise-Scale Analytics Using Microsoft Azure and Power BI — Question 103

You have a deployment pipeline for a Power BI workspace. The workspace contains two datasets that use import storage mode.
A database administrator reports a drastic increase in the number of queries sent from the Power BI service to an Azure SQL database since the creation of the deployment pipeline.
An investigation into the issue identifies the following:
One of the datasets is larger than 1 GB and has a fact table that contains more than 500 million rows.
When publishing dataset changes to development, test, or production pipelines, a refresh is triggered against the entire dataset.
You need to recommend a solution to reduce the size of the queries sent to the database when the dataset changes are published to development, test, or production.
What should you recommend?

Answer options

• A. From Capacity settings in the Power BI Admin portal, reduce the Max Intermediate Row Set Count setting.
• B. Configure the dataset to use a composite model that has a DirectQuery connection to the fact table.
• C. Enable the large dataset storage format for workspace.
• D. From Capacity settings in the Power BI Admin portal, increase the Max Intermediate Row Set Count setting.

Correct answer: B

Explanation

The correct answer is B because using a composite model with a DirectQuery connection allows for more efficient querying, reducing the load on the Azure SQL database by not requiring a full dataset refresh. Options A and D are incorrect as adjusting the Max Intermediate Row Set Count does not directly address the issue of large query volumes. Option C, while beneficial for handling large datasets, does not specifically tackle the problem of query size when publishing changes.