Microsoft Microsoft Certifications DP-500 Questions & Answers

• Question 51:

  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?

  A. Turn off auto refresh when publishing the dataset changes to the Power Bl service.

  B. In the dataset. change the fact table from an import table to a hybrid table.

  C. Enable the large dataset storage format for workspace.

  D. Create a dataset parameter to reduce the fact table row count in the development and test pipelines.

  Correct Answer: B

  Hybrid tables Hybrid tables are tables with incremental refresh that can have both import and direct query partitions. During a clean deployment, both the refresh policy and the hybrid table partitions are copied. When deploying to a pipeline stage that already has hybrid table partitions, only the refresh policy is copied. To update the partitions, refresh the table.

  Refreshes are faster - Only the most recent data that has changed needs to be refreshed.

  Reference: https://docs.microsoft.com/en-us/power-bi/create-reports/deployment-pipelines- best-practices

• Question 52:

  You have a group of data scientists who must create machine learning models and run periodic experiments on a large dataset.

  You need to recommend an Azure Synapse Analytics pool for the data scientists. The solution must minimize costs.

  Which type of pool should you recommend?

  A. a Data Explorer pool

  B. an Apache Spark pool

  C. a dedicated SQL pool

  D. a serverless SQL pool

  Correct Answer: B

  In Azure Synapse, training machine learning models can be performed on the Apache Spark Pools with tools like PySpark/Python, Scala, or .NET. Reference: https://docs.microsoft.com/en-us/azure/synapse-analytics/machine- learning/what-is-machine- learning

• Question 53:

  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?

  A. From Capacity settings in the Power Bl 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 Bl Admin portal, increase the Max Intermediate Row Set Count setting.

  Correct Answer: B

  A composite model in Power BI means part of your model can be a DirectQuery connection to a data source (for example, SQL Server database), and another part as Import Data (for example, an Excel file). Previously, when you used DirectQuery, you couldn't even add another data source into the model. DirectQuery and Import Data have different advantages. Now the Composite Model combines the good things of both Import and DirectQuery into one model. Using the Composite Model, you can work with big data tables using DirectQuery, and still import smaller tables using Import Data.

  Reference: https://radacad.com/composite-model-directquery-and-import-data-combined- evolution- begins-in-power-bi

  https://powerbi.microsoft.com/en-us/blog/five-new-power-bi-premium-capacity-settings-is- available-on-the- portal-preloaded-with-default-values-admin-can-review-and-override-the- defaults-with-their-preference-to- better-fence-theircapacity/

• Question 54:

  Your company is migrating its current, custom-built reporting solution to Power BI.

  The Power BI tenant must support the following scenarios:

  40 reports that will be embedded in external websites. The websites control their own security. The reports will be consumed by 50 users monthly. Forty-five users that require access to the workspaces and apps in the Power BI Admin portal.

  Ten of the users must publish and consume datasets that are larger than 1 GB. Ten developers that require Text Analytics transformations and paginated reports for datasets. An additional 15 users will consume the reports.

  You need to recommend a licensing solution for the company. The solution must minimize costs. Which two Power BI license options should you include in the recommendation? Each correct answer presents part of the solution.

  NOTE: Each correct selection is worth one point.

  A. 70 Premium per user

  B. one Premium

  C. 70 Pro

  D. one Embedded

  E. 35 Pro

  F. 35 Premium per user

  Correct Answer: BE

  Since we need 45 users to have access to the workspaces and apps, 35 Premium Per user would not work because for the Premium Per User workspace content to be accessed we need that all users have Premium Per User. So even if we go for Premium Per User, then we need to purchase it for 70 users.

  Also, since we want to publish reports to web and not embed to the app, I would not consider Embedded capacity.

  Another important thing is to minimize the cost. We can use Premium capacity for publishing the reports to web, supporting Text Analytics transformations and paginated reports and having dataset more than 1GB. For the users who will be actively busy with development we can purchase 35 pro licenses (IMO 20 pro account is enough).

• Question 55:

  You have two Power BI reports named Report1 and Report2.

  Report1 connects to a shared dataset named Dataset1.

  Report2 connects to a local dataset that has the same structure as Dataset1. Report2 contains several calculated tables and parameters.

  You need to prepare Report2 to use Dataset1.

  Which two actions should you perform? Each correct answer presents part of the solution.

  NOTE: Each correct selection is worth one point.

  A. Remove the data source permissions.

  B. Delete all the Power Query Editor objects.

  C. Modify the source of each query.

  D. Update all the parameter values.

  E. Delete all the calculated tables.

  Correct Answer: CD

  C: Power BI Desktop also comes with Power Query Editor. Use Power Query Editor to connect to one or many data sources, shape and transform the data to meet your needs, then load that model into Power BI Desktop.

  D: Common uses for parameters

  Here are some of the most common ways to use parameters.

  Control paginated report data

  *

  Filter paginated report data at the data source by writing dataset queries that contain variables.

  *

  Etc. Reference: https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-query- overview https://docs.microsoft.com/en-us/learn/modules/dax-power-bi-add-calculated-tables/1- introduction

• Question 56:

  You have an Azure subscription that contains an Azure Synapse Analytics workspace. You create an Azure Data Lake Storage Gen2 account and upload a CSV file named Filel.csv. You need to use Synapse Studio to query the data in Filel.csv by using a serverless SQL pool. Which Transact-SQL operator should you include in the query?

  A. STRIMO_SPLIT

  B. OPENOUERY

  C. OPCNROWSET

  D. OPEMDATASOURCE

  Correct Answer: C

• Question 57:

  You have a Power BI Premium capacity.

  You need to increase the number of virtual cores associated to the capacity.

  Which role do you need?

  A. Power Bl workspace admin

  B. capacity admin

  C. Power Platform admin

  D. Power Bl admin

  Correct Answer: D

  Change capacity size

  Power BI admins and global administrators can change Power BI Premium capacity. Capacity admins who are not a Power BI admin or global administrator don't have this option.

  Reference: https://docs.microsoft.com/en-us/power-bi/enterprise/service-admin-premium- manage

• Question 58:

  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?

  A. Request the authors of the deployment pipeline datasets to reduce the number of datasets republished during development.

  B. In the dataset, delete the fact table.

  C. Configure the dataset to use a composite model that has a DirectQuery connection to the fact table.

  D. From Capacity settings in the Power Bi Admin portal, reduce the Max Intermediate Row Set Count setting.

  Correct Answer: C

  Previously in Power BI Desktop, when you used a DirectQuery in a report, no other data connections, whether DirectQuery or import, were allowed for that report. With composite models, that restriction is removed. A report can seamlessly include data connections from more than one DirectQuery or import data connection, in any combination you choose.

  The composite models capability in Power BI Desktop consists of three related features:

  *

  Composite models: Allows a report to have two or more data connections from different source groups, such as one or more DirectQuery connections and an import connection, two or more DirectQuery connections, or any combination thereof.

  *

  Etc.

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

• Question 59:

  You have a Power Bl dataset named Dataset1.

  In Dataset1, you currently have 50 measures that use the same time intelligence logic.

  You need to reduce the number of measures, while maintaining the current functionality.

  Solution: From Tabular Editor, you create a calculation group.

  Does this meet the goal?

  A. Yes

  B. No

  Correct Answer: A

• Question 60:

  You use the Vertipaq Analyzer to analyze tables in a dataset as shown in the Tables exhibit. (Click the Tables tab.)

  

  The table relationships for the dataset are shown in the Relationships exhibit. (Click the Relationships tab.)

  

  You need to reduce the model size by eliminating invalid relationships. Which column should you remove?

  A. Sales[Sales Amount]

  B. Sales[RowlD]

  C. Sales[Sales ID]

  D. Plan[RowlD]

  Correct Answer: B

  Sales[Row ID] has 858,786 missing keys and 858,789 Max From Cardinality. Note: The Max From Cardinality column defines the cost of the relationship which is the amount of time DAX needs to transfer the filters from the dimensions table to the fact table.

  Reference: https://blog.enterprisedna.co/vertipaq-analyzer-tutorial-relationships-referential- integrity/