100% Free Azure Enterprise Data Analyst Associate DP-500 Dumps PDF Demo Cert Guide Cover [Q77-Q95]

DP-500 Microsoft

admin -

January 12, 2024

Rate this post

100% Free Azure Enterprise Data Analyst Associate DP-500 Dumps PDF Demo Cert Guide Cover

PDF Exam Material 2024 Realistic DP-500 Dumps Questions

Schedule exam

Languages: English

Retirement date: none

This exam measures your ability to accomplish the following technical tasks: implement and manage a data analytics environment; query and transform data; implement and manage data models; and explore and visualize data.

Microsoft DP-500 is a certification exam that focuses on designing and implementing enterprise-scale analytics solutions using Microsoft Azure and Microsoft Power BI. DP-500 exam is designed for professionals who want to validate their skills in building and managing data solutions on the Azure platform. Successful completion of DP-500 exam demonstrates an individual’s ability to design and implement large-scale data solutions that enable organizations to make informed decisions based on data analysis.

Q77. Note: This question is part of a scries of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You have the Power Bl data model shown in the exhibit. (Click the Exhibit tab.)

Users indicate that when they build reports from the data model, the reports take a long time to load.
You need to recommend a solution to reduce the load times of the reports.
Solution: You recommend creating a perspective that contains the commonly used fields.
Does this meet the goal?

Yes

Instead denormalize For Performance.
Even though it might mean storing a bit of redundant data, schema denormalization can sometimes provide better query performance. The only question then becomes is the extra space used worth the performance benefit.

Q78. You have a 2-GB Power Bl dataset.
You need to ensure that you can redeploy the dataset by using Tabular Editor. The solution must minimize how long it will take to apply changes to the dataset from powerbi.com.
Which two actions should you perform in powerbi.com? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point

Enable service principal authentication for read-only admin APIs.

Turn on Large dataset storage format.

Connect the target workspace to an Azure Data Lake Storage Gen2 account.

Enable XMLA read-write.

Optimize datasets for write operations by enabling large models
When using the XMLA endpoint for dataset management with write operations, it’s recommended you enable the dataset for large models. This reduces the overhead of write operations, which can make them considerably faster. For datasets over 1 GB in size (after compression), the difference can be significant.
Tabular Editor supports Azure Analysis Services and Power BI Premium Datasets through XMLA read/write.
Note: Tabular Editor – An open-source tool for creating, maintaining, and managing tabular models using an intuitive, lightweight editor. A hierarchical view shows all objects in your tabular model. Objects are organized by display folders with support for multi-select property editing and DAX syntax highlighting. XMLA read-only is required for query operations. Read-write is required for metadata operations.
Reference:
https://tabulareditor.github.io/

Q79. You have an Azure Synapse Analytics serverless SQL pool.
You need to catalog the serverless SQL pool by using Azure Purview.
Which three actions should you perform? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.

Create a managed identity in Azure Active Directory (Azure AD).

Assign the Storage Blob Data Reader role to the Azure Purview managed service identity (MSI) for the storage account associated to the Synapse Analytics workspace.

Assign the Owner role to the Azure Purview managed service identity (MSI) for the Azure Purview resource group.

Assign the Reader role to the Azure Purview managed service identity (MSI) for the Synapse Analytics workspace.

Explanation
Authentication for enumerating serverless SQL database resources
There are three places you’ll need to set authentication to allow Microsoft Purview to enumerate your serverless SQL database resources:
The Azure Synapse workspace
The associated storage
The Azure Synapse serverless databases
The steps below will set permissions for all three.
Azure Synapse workspace
In the Azure portal, go to the Azure Synapse workspace resource.
On the left pane, selectAccess Control (IAM).
Select the Add button.
Set the Reader role and enter your Microsoft Purview account name, which represents its managed service identity (MSI).
Select Save to finish assigning the role
Azure Synapse Analytics serverless SQL pool catalog Purview Azure Purview managed service identity Storage account In the Azure portal, go to the Resource group or Subscription that the storage account associated with the Azure Synapse workspace is in.
On the left pane, selectAccess Control (IAM).
Select the Add button.
Set the Storage blob data reader role and enter your Microsoft Purview account name (which represents its MSI) in the Select box.
Select Save to finish assigning the role.
Azure Synapse serverless database
Go to your Azure Synapse workspace and open the Synapse Studio.
Select the Data tab on the left menu.
Select the ellipsis (…) next to one of your databases, and then start a new SQL script.
Add the Microsoft Purview account MSI (represented by the account name) on the serverless SQL databases.
You do so by running the following command in your SQL script:
SQL
CREATE LOGIN [PurviewAccountName] FROM EXTERNAL PROVIDER;
Apply permissions to scan the contents of the workspace
You can set up authentication for an Azure Synapse source in either of two ways. Select your scenario below for steps to apply permissions.
Use a managed identity
Use a service principal
Reference: https://docs.microsoft.com/en-us/azure/purview/register-scan-synapse-workspace?tabs=MI

Q80. 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?

Power Bl workspace admin

capacity admin

Power Platform admin

Power Bl admin

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.

Q81. You are configuring an aggregation table as shown in the following exhibit.

The detail table is named FactSales and the aggregation table is named FactSales(Agg).
You need to aggregate SalesAmount for each store.
Which type of summarization should you use for SalesAmount and StoreKey? To answer, select the appropriate options in the answer area, NOTE: Each correct selection is worth one point.

Explanation
Box 1: Sum
The Manage aggregations dialog shows a row for each column in the table, where you can specify the aggregation behavior. In the following example, queries to the Sales detail table are internally redirected to the Sales Agg aggregation table.

Box 2: GroupBy
Reference: https://docs.microsoft.com/en-us/power-bi/transform-model/aggregations-advanced

Q82. Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You have a Power Bl dataset named Datasetl.
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 DAX Studio, you write a query that uses grouping sets.
Does this meet the goal?

Yes

A grouping is a set of discrete values that are used to group measure fields.

Q83. Note: This question is part of a series of questions that present the same scenario. Each question in the series
contains a unique solution that might meet the stated goals. Some question sets might have more than one
correct solution, while others might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it. As a result, these questions
will not appear in the review screen.
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?

Yes

Q84. You have the following Python code in an Apache Spark notebook.

Which type of chart will the code produce?

a stacked bar chart

a pie chart

a bar chart

an area chart

Explanation
The matplotlib.pyplot.fill_between function fills the area between two horizontal curves.
The curves are defined by the points (x, y1) and (x, y2). This creates one or multiple polygons describing the filled area.
Reference: https://matplotlib.org/3.5.0/api/_as_gen/matplotlib.pyplot.fill_between.html

Q85. You have a Power BI report and dataset in Power BI Desktop. The dataset contains a calculation group named CG1 that contains four calculation items. The Current calculation item is shown in the following exhibit.

Explanation

Q86. After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You are using an Azure Synapse Analytics serverless SQL pool to query a collection of Apache Parquet files by using automatic schema inference. The files contain more than 40 million rows of UTF-8-encoded business names, survey names, and participant counts. The database is configured to use the default collation.
The queries use open row set and infer the schema shown in the following table.

You need to recommend changes to the queries to reduce I/O reads and tempdb usage.
Solution: You recommend using openrowset with to explicitly specify the maximum length for businessName and surveyName.
Does this meet the goal?

Yes

Explanation
Instead use Solution: You recommend using OPENROWSET WITH to explicitly define the collation for businessName and surveyName as Latin1_General_100_BIN2_UTF8.
Query Parquet files using serverless SQL pool in Azure Synapse Analytics.
Important
Ensure you are using a UTF-8 database collation (for example Latin1_General_100_BIN2_UTF8) because string values in PARQUET files are encoded using UTF-8 encoding. A mismatch between the text encoding in the PARQUET file and the collation may cause unexpected conversion errors. You can easily change the default collation of the current database using the following T-SQL statement: alter database current collate Latin1_General_100_BIN2_UTF8′.
Note: If you use the Latin1_General_100_BIN2_UTF8 collation you will get an additional performance boost compared to the other collations. The Latin1_General_100_BIN2_UTF8 collation is compatible with parquet string sorting rules. The SQL pool is able to eliminate some parts of the parquet files that will not contain data needed in the queries (file/column-segment pruning). If you use other collations, all data from the parquet files will be loaded into Synapse SQL and the filtering is happening within the SQL process. The Latin1_General_100_BIN2_UTF8 collation has additional performance optimization that works only for parquet and CosmosDB. The downside is that you lose fine-grained comparison rules like case insensitivity.
Reference: https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/query-parquet-files

Q87. 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?

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

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

Enable the large dataset storage format for workspace.

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

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://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-their-capacity/

Q88. You have the following code in an Azure Synapse notebook.

Use the drop-down menus to select the answer choice that completes each statement based on the information presented in the code.
NOTE: Each correct selection is worth one point.

Explanation
Box 1: three scatterplots
Compare Plots
Example, Draw two plots on the same figure:
import matplotlib.pyplot as plt
import numpy as np
#day one, the age and speed of 13 cars:
x = np.array([5,7,8,7,2,17,2,9,4,11,12,9,6])
y = np.array([99,86,87,88,111,86,103,87,94,78,77,85,86])
plt.scatter(x, y)
#day two, the age and speed of 15 cars:
x = np.array([2,2,8,1,15,8,12,9,7,3,11,4,7,14,12])
y = np.array([100,105,84,105,90,99,90,95,94,100,79,112,91,80,85])
plt.scatter(x, y)
plt.show()
Result:
Chart, scatter chart Description automatically generated

Box 2: three marker symbols
One for each scatterplot. One default, and two defined.
Default is point.
v is triangle down.
^ is triangle up.
Reference: https://www.w3schools.com/python/matplotlib_scatter.asp
https://matplotlib.org/stable/api/markers_api.html

Q89. You need to provide users with a reproducible method to connect to a data source and transform the data by using an Al function. The solution must meet the following requirement
* Minimize development effort.
* Avoid including data in the file.
Which type of file should you create?

PBIDS

PBIX

PBIT

A PBIT file is a template created by Power BI Desktop, a Microsoft application used to create reports and visualizations. It contains queries, visualization settings, data models, reports, and other data added by the user.
A PBIT file acts as a Power BI template. It doesn’t include any data from your source systems.

Q90. You use Advanced Editor in Power Query Editor to edit a query that references two tables named Sales and Commission. A sample of the data in the Sales table is shown in the following table.

A sample of the data in the Commission table is shown in the following table.

You need to merge the tables by using Power Query Editor without losing any rows in the Sales table.
How should you complete the query? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.

Q91. Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You have the Power Bl data model shown in the exhibit. (Click the Exhibit tab.)

Users indicate that when they build reports from the data model, the reports take a long time to load.
You need to recommend a solution to reduce the load times of the reports.
Solution: You recommend moving all the measures to a calculation group.
Does this meet the goal?

Yes

Explanation
Instead denormalize For Performance.
Even though it might mean storing a bit of redundant data, schema denormalization can sometimes provide better query performance. The only question then becomes is the extra space used worth the performance benefit.
Reference: https://www.mssqltips.com/sqlservertutorial/3211/denormalize-for-performance/

Q92. You are using DAX Studio to query an XMLA endpoint.
How should you complete the DAX expression? To answer, drag the appropriate values to the targets. Each value may be used once, more than once. may need to drag the split bar between panes or scroll to view content.
NOTE: Each correct selection is worth one point.

Explanation

Box 1: CALCULATE
Box 2: CURRENTGROUP
CURRENTGROUP returns a set of rows from the table argument of a GROUPBY expression that belong to the current row of the GROUPBY result.
Remarks
This function can only be used within a GROUPBY expression.
This function takes no arguments and is only supported as the first argument to one of the following aggregation functions: AVERAGEX, COUNTAX, COUNTX, GEOMEANX, MAXX, MINX, PRODUCTX, STDEVX.S, STDEVX.P, SUMX, VARX.S, VARX.P.
Note: COUNTX counts the number of rows that contain a non-blank value or an expression that evaluates to a non-blank value, when evaluating an expression over a table.
Reference: https://docs.microsoft.com/en-us/dax/currentgroup-function-dax

Q93. You have the following Python code in an Apache Spark notebook.

Which type of chart will the code produce?

a stacked bar chart

a pie chart

a bar chart

an area chart

Q94. You need to configure a source control solution for Azure Synapse Analytics. The solution must meet the following requirements:
* Code must always be merged to the main branch before being published, and the main branch must be used for publishing resource
* The workspace templates must be stored in the publish branch.
* A branch named dev123 will be created to support the development of a new feature.
What should you do? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.

Q95. You have a sales report as shown in the following exhibit.

The sales report has the following characteristics:
The measures are optimized.
The dataset uses import storage mode.
Data points, hierarchies, and fields cannot be removed or filtered from the report page.
From powerbi.com, users experience slow load times when viewing the report.
You need to reduce how long it takes for the report to load without affecting the data displayed in the report.
Which two actions should you perform? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.