Pass DP-500 Exam Latest Practice Questions Updated on Oct 10, 2023 [Q32-Q52]

Pass DP-500 Exam Latest Practice Questions Updated on Oct 10, 2023 [Q32-Q52]

October 10, 2023 latestexam 0 Comments

4/5 - (1 vote)

Pass DP-500 Exam Latest Practice Questions Updated on Oct 10, 2023

Microsoft DP-500 Study Guide Archives

The Microsoft DP-500 exam is targeted towards data professionals who have experience working with Azure data services, Power BI, and other Microsoft products and technologies. DP-500 exam is ideal for data architects, data engineers, and business intelligence professionals who are looking to validate their expertise in designing and implementing enterprise-scale analytics solutions using Microsoft technologies. DP-500 exam is also suitable for those who want to expand their knowledge and skills in data analytics and gain a competitive edge in the job market.

NO.32 You have five Power Bl reports that contain R script data sources and R visuals.
You need to publish the reports to the Power Bl service and configure a daily refresh of datasets.
What should you include in the solution?

a Power Bl Embedded capacity

an on-premises data gateway (standard mode)

a workspace that connects to an Azure Data Lake Storage Gen2 account

an on-premises data gateway (personal mode)

To schedule refresh of your R visuals or dataset, enable scheduled refresh and install an on-premises data gateway (personal mode) on the computer containing the workbook and R.

NO.33 You are creating a Power Bl Desktop report.
You add a Python visual to the report page.
You plan to create a scatter chart to visualize the data.
You add Python code to the Python script editor.
You need to create the scatter chart.
How should you complete the Python code? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.

NO.34 You have a dataset that contains a table named UserPermissions. UserPermissions contains the following data.

You plan to create a security role named User Security for the dataset. You need to filter the dataset based on the current users. What should you include in the DAX expression?

[UserPermissions] – USERNAME()

[UserPermissions] – USERPRINCIPALNAME()

[User] = USERPRINCIPALNAME()

[User] = USERNAME()

[User] = USEROBJECTID()

USERNAME() returns the domain name and username from the credentials given to the system at connection time.
It should be compared to column name of User, which in DAX is expressed through [User].

NO.35 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.

Change the report theme to monochromatic.

Replace the single-value cards with a multi-row card.

Replace the product category charts with a bar chart for sales and a hierarchy of Category and Sub Category on the axis.

Replace all the filters on the Filters pane with visual slicers on the report page.

NO.36 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 Power Bl Desktop, you create a hierarchy.
Does this meet the goal?

Yes

NO.37 You are planning a Power Bl solution for a customer.
The customer will have 200 Power Bl users. The customer identifies the following requirements:
* Ensure that all the users can create paginated reports.
* Ensure that the users can create reports containing Al visuals.
* Provide autoscaling of the CPU resources during heavy usage spikes.
You need to recommend a Power Bl solution for the customer. The solution must minimize costs. What should
you recommend?

Power Bl Premium per user

a Power Bl Premium per capacity

Power Bl Pro per user

Power Bl Report Server

NO.38 You need to implement object-level security (OLS) in the Power Bl dataset for the sales associates.
Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.

1 – From Power BI Desktop, create a role for the sales associates.
2 – From Tabular Editor, set Object Level Security to None for the Customer[Email]…
3 – From Power BI Desktop, publish the dataset to the Sales Analytics workspace.

NO.39 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.

Remove the data source permissions.

Delete all the Power Query Editor objects.

Modify the source of each query.

Update all the parameter values.

Delete all the calculated tables.

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/learn/modules/dax-power-bi-add-calculated-tables/1-introduction

NO.40 You manage a Power BI dataset that queries a fact table named SalesDetails. SalesDetails contains three date columns named OrderDate, CreatedOnDate, and ModifiedDate.
You need to implement an incremental refresh of SalesDetails. The solution must ensure that OrderDate starts on or after the beginning of the prior year.
Which four actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.
NOTE: More than one order of answer choices is correct. You will receive credit for any of the correct orders you select.

1 – Create RangeStart and RangeEndDateTime parameters.
2 – Add an applied step that adds a custom date filter OrderDate is Between RangeStart and RangeEnd.
3 – Configure an incremental refresh to archive data that starts two years before the refresh date.
4 – Add an applied step that filters OrderDate to the start of the prior year.

NO.41 You have a Power Bl 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?

Create calculated columns in Dataset1.

Remove the relationships from Dataset1.

Normalize the tables in DB1.

Denormalize the tables in DB1.

Denormalize to improve query performance.
Note: Normalization prevents data duplications, preserves disk space, and improves the performance of the disk I/O operations. The downside of the normalization is that the queries based on these normalized tables require more table joins.
Schema denormalization (i.e. consolidation of some dimension tables) for such databases can significantly reduce costs of the analytical queries and improve the performance.

NO.42 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

NO.43 You need to populate the CustomersWithProductScore table.
How should you complete the stored procedure? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.

Topic 1, Litware, Inc. Overview
Litware, Inc. is a retail company that sells outdoor recreational goods and accessories. The company sells
goods both online and at its stores located in six countries.
Azure Resources
Litware has the following Azure resources:
* An Azure Synapse Analytics workspace named synapseworkspace1
* An Azure Data Lake Storage Gen2 account named datalake1 that is associated with synapseworkspace1
* A Synapse Analytics dedicated SQL pool named SQLDW
Dedicated SQL Pool
SQLDW contains a dimensional model that contains the following table.

SQLDW contains the following additional tables.

SQLDW contains a view named dbo.CustomerPurchases that creates a distinct list of values from
dbo.Customer [customeriD], dbo.Customer
[CustomerEmail], dbo.ProductfProductID] and dbo.Product[ProductName].
The sales data in SQLDW is updated every 30 minutes. Records in dbo.SalesTransactions are updated in
SQLDW up to three days after being created. The records do NOT change after three days.
Power BI
Litware has a new Power Bl tenant that contains an empty workspace named Sales Analytics.
All users have Power B1 Premium per user licenses.
IT data analysts are workspace administrators. The IT data analysts will create datasets and reports.
A single imported dataset will be created to support the company’s sales analytics goals. The dataset will be
refreshed every 30 minutes.
Analytics Goals
Litware identifies the following analytics goals:
* Provide historical reporting of sales by product and channel over time.
* Allow sales managers to perform ad hoc sales reporting with minimal effort.
* Perform market basket analysis to understand which products are commonly purchased in the same
transaction.
* Identify which customers should receive promotional emails based on their likelihood of purchasing
promoted products.
Litware plans to monitor the adoption of Power Bl reports over time. The company wants custom Power Bl
usage reporting that includes the percent change of users that view reports in the Sales Analytics workspace
each month.
Security Requirements
Litware identifies the following security requirements for the analytics environment:
* All the users in the sales department and the marketing department must be able to see Power B1 reports that
contain market basket analysis and data about which customers are likely to purchase a product.
* Customer contact data in SQLDW and the Power B1 dataset must be labeled as Sensitive. Records must be
kept of any users that use the sensitive data.
* Sales associates must be prevented from seeing the CustomerEmail column in Power B1 reports.
* Sales managers must be prevented from modifying reports created by other users.
Development Process Requirements
Litware identifies the following development process requirements:
* SQLDW and datalake1 will act as the development environment. Once feature development is complete, all
entities in synapseworkspace1 will be promoted to a test workspace, and then to a production workspace.
* Power Bl content must be deployed to test and production by using deployment pipelines.
* All SQL scripts must be stored in Azure Repos.
The IT data analysts prefer to build Power Bl reports in Synapse Studio.

NO.44 You are optimizing a Power Bl data model by using DAX Studio.
You need to capture the query events generated by a Power Bl Desktop report.
What should you use?

the DMV list

a Query Plan trace

an All Queries trace

a Server Timings trace

NO.45 You are using Azure Synapse Studio to explore a dataset that contains data about taxi trips.
You need to create a chart that will show the total trip distance according to the number of passengers as
shown in the following exhibit.

How should you configure the chart? To answer, select the appropriate options in the answer area. NOTE:
Each correct selection is worth one point.

NO.46 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 defining a data source and view for the Parquet files. You recommend updating the
query to use the view.
Does this meet the goal?

Yes

NO.47 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.

Reference:
https://matplotlib.org/stable/api/markers_api.html

NO.48 You have a Power Bl 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?

Create calculated columns in Dataset1.

Remove the relationships from Dataset1.

Normalize the tables in DB1.

Denormalize the tables in DB1.

NO.49 You have the Power BI workspaces shown in the following exhibit.

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

Reference:
https://docs.microsoft.com/en-us/power-bi/admin/service-admin-portal-workspaces

NO.50 You are using DAX Studio to query an XMLA endpoint.
You need to identify the duplicate values in a column named Email in a table named Subscription.
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.

NO.51 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 Datasetl, 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 Power Bl Desktop, you group the measures in a display folder.
Does this meet the goal?

Yes

NO.52 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.