nerdexam
Microsoft

DP-500 · Question #81

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…

The correct answer is B. Configure the dataset to use a composite model that has a DirectQuery connection to the fact table. To reduce the size of queries sent to the database during Power BI dataset publication for a large fact table, configure a composite model with a DirectQuery connection to that table.

Implement and manage data models

Question

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?

Options

  • AFrom Capacity settings in the Power BI Admin portal, reduce the Max Intermediate Row Set Count setting.
  • BConfigure the dataset to use a composite model that has a DirectQuery connection to the fact table.
  • CEnable the large dataset storage format for workspace.
  • DFrom Capacity settings in the Power BI Admin portal, increase the Max Intermediate Row Set Count setting.

How the community answered

(69 responses)
  • A
    12% (8)
  • B
    81% (56)
  • C
    3% (2)
  • D
    4% (3)

Why each option

To reduce the size of queries sent to the database during Power BI dataset publication for a large fact table, configure a composite model with a DirectQuery connection to that table.

AFrom Capacity settings in the Power BI Admin portal, reduce the Max Intermediate Row Set Count setting.

Reducing the Max Intermediate Row Set Count in Capacity settings affects internal query processing limits within Power BI, not the volume of data sent from the source database during refresh.

BConfigure the dataset to use a composite model that has a DirectQuery connection to the fact table.Correct

A composite model allows large fact tables (like one with 500 million rows) to use DirectQuery mode, meaning data is queried directly from the Azure SQL database instead of being imported into the Power BI model. This drastically reduces the data volume transferred and the query size during dataset changes and refreshes, as only the schema and metadata are managed within Power BI.

CEnable the large dataset storage format for workspace.

Enabling the large dataset storage format allows models to exceed 1 GB, but it does not inherently reduce the size of queries sent to the database or the data imported if the model remains in Import mode.

DFrom Capacity settings in the Power BI Admin portal, increase the Max Intermediate Row Set Count setting.

Increasing the Max Intermediate Row Set Count in Capacity settings would potentially allow larger internal query results within Power BI, but it does not reduce the data volume transferred or the query size sent from the source database during refresh operations.

Concept tested: Power BI composite models and DirectQuery

Source: https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-composite-models

Topics

#Composite Models#DirectQuery#Dataset Performance#Data Model Management

Community Discussion

No community discussion yet for this question.

Sign up to join the discussion
Full DP-500 Practice