Mastering Microsoft DP-500 Exam: The Ultimate DP-500 Dumps to Power BI Skills

1. 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.

HOTSPOT

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.

2. You need to configure the Sales Analytics workspace to meet the ad hoc reporting requirements.

What should you do?

3. What should you configure in the deployment pipeline?

4. How should you configure the Power BI dataset refresh for the dbo.SalesTransactions table?

5. You need to recommend a solution to ensure that sensitivity labels are applied. The solution must minimize administrative effort.

Which three actions should you include in the recommendation? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point.

6. DRAG DROP

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.

7. DRAG DROP

You need to create the customized Power Bl usage reporting. The Usage Metrics Report dataset has already been created. The solution must minimize development and administrative effort.

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.

8. Topic 2, Contoso, Ltd

Overview

Contoso, Ltd. is a company that sells enriched financial data to a variety of external customers.

Contoso has a main office in Los Angeles and two branch offices in New York and Seattle.

Data Infrastructure

Contoso has a 50-TB data warehouse that uses an instance of SQL Server on Azure Virtual Machines.

The data warehouse populates an Azure Synapse Analytics workspace that is accessed by the external customers. Currently, the customers can access alt the data.

Contoso has one Power Bl workspace named FinData that contains a single dataset. The dataset

contains financial data from around the world. The workspace is used by 10 internal users and one external customer. The dataset has the following two data sources: the data warehouse and the Synapse Analytics serverless SQL pool.

Users frequently query the Synapse Analytics workspace by using Transact-SQL.

User Problems

Contoso identifies the following user issues:

• Some users indicate that the visuals in Power Bl reports are slow to render when making filter selections.

• Users indicate that queries against the serverless SQL pool fail occasionally because the size of tempdb has been exceeded.

• Users indicate that the data in Power Bl reports is stale. You discover that the refresh process of the Power Bl model occasionally times out

Planned Changes

Contoso plans to implement the following changes:

• Into the existing Power Bl dataset, integrate an external data source that is accessible by using the REST API.

• Build a new dataset in the FinData workspace by using data from the Synapse Analytics dedicated SQL pool.

• Provide all the customers with their own Power Bl workspace to create their own reports. Each workspace will use the new dataset in the FinData workspace.

• Implement subscription levels for the customers. Each subscription level will provide access to specific rows of financial data.

• Deploy prebuilt datasets to Power Bl to simplify the query experience of the customers.

• Provide internal users with the ability to incorporate machine learning models loaded to the dedicated SQL pool.

You need to recommend a solution for the customer workspaces to support the planned

changes.

Which two configurations should you include in the recommendation? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point.

9. DRAG DROP

You need to integrate the external data source to support the planned changes.

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.

10. You need to recommend a solution to resolve the query issue of the serverless SQL pool.

The solution must minimize impact on the users.

What should you in the recommendation?

11. DRAG DROP

You need to create Power BI reports that will display data based on the customers’ subscription level.

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.

12. You need to recommend a solution to add new fields to the financial data Power Bl dataset with data from the Microsoft SQL Server data warehouse.

What should you include in the recommendation?

13. Which two possible tools can you use to identify what causes the report to render slowly? Each correct answer presents a complete solution. NOTE: Each correct selection is worth one point.

14. HOTSPOT

You need to build a Transact-SQL query to implement the planned changes for the internal

users.

How should you complete the Transact-SQL query? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.

15. You need to identify the root cause of the data refresh issue.

What should you use?

16. Topic 3, Misc. Questions

DRAG DROP

You have a Power Bl dataset. The dataset contains data that is updated frequently.

You need to improve the performance of the dataset by using incremental refreshes.

Which four actions should you perform in sequence to enable the incremental refreshes? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.

17. HOTSPOT

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.

18. HOTSPOT

You have an Azure Data Lake Storage Gen 2 container that stores more than 300,000 files representing hourly telemetry data. The data is organized in folders by the year, month, and day according to when the telemetry was captured.

You have the following query in Power Query Editor.

For each of the following statements, select Yes if the statement is true. Otherwise, select No. NOTE: Each correct selection is worth one point

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

20. You open a Power Bl Desktop report that contains an imported data model and a single report page.

You open Performance analyzer, start recording, and refresh the visuals on the page.

The recording produces the results shown in the following exhibit

What can you identify from the results?

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

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

23. You plan to modify a Power Bl dataset.

You open the Impact analysis panel for the dataset and select Notify contacts.

Which contacts will be notified when you use the Notify contacts feature?

24. You have a Power Bl workspace that contains one dataset and four reports that connect to the dataset.

The dataset uses Import storage mode and contains the following data sources:

• A CSV file in an Azure Storage account

• An Azure Database for PostgreSQL database

You plan to use deployment pipelines to promote the content from development to test to production. There will be different data source locations for each stage.

What should you include in the deployment pipeline to ensure that the appropriate data source locations are used during each stage?

25. You plan to generate a line chart to visualize and compare the last six months of sales data for two departments. You need to increase the accessibility of the visual.

What should you do?

26. You have a file named File1.txt that has the following characteristics:

• A header row

• Tab delimited values

• UNIX-style line endings

You need to read File1.txt by using an Azure Synapse Analytics serverless SQL pool.

Which query should you execute?

27. HOTSPOT

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.

28. You are using a Python notebook in an Apache Spark pool in Azure Synapse Analytics. You need to present the data distribution statistics from a DataFrame in a tabular view.

Which method should you invoke on the DataFrame?

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

30. You have a Power Bl dataset that contains the following measure.

You need to improve the performance of the measure without affecting the logic or the results.

What should you do?

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

32. You have a Power Bl dataset that uses DirectQuery against an Azure SQL database.

Multiple reports use the dataset.

A database administrator reports that too many queries are being sent from Power Bl to the database.

You need to reduce the number of queries sent to the database.

The solution must meet the following requirements:

• DirectQuery must continue to be used.

• Visual interactions in all the reports must remain as they are configured currently.

• Consumers of the reports must only be allowed to apply filters from the Filter pane.

Which two settings should you select? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point.

33. 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 define the collation for businessName and survey Name as Latim_Generai_100_BiN2_UTF8.

Does this meet the goal?

34. You have a Power Bi workspace named Workspacel in a Premium capacity. Workspacel contains a dataset.

During a scheduled refresh, you receive the following error message: "Unable to save the changes since the new dataset size of 11,354 MB exceeds the limit of 10,240 MB."

You need to ensure that you can refresh the dataset.

What should you do?

35. You are using a Python notebook in an Apache Spark pool in Azure Synapse Analytics.

You need to present the data distribution statistics from a DataFrame in a tabular view.

Which method should you invoke on the DataFrame?

36. DRAG DROP

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.

37. You are using GitHub as a source control solution for an Azure Synapse Studio workspace.

You need to modify the source control solution to use an Azure DevOps Git repository.

What should you do first?

38. You are creating a Python visual in Power Bl Desktop.

You need to retrieve the value of a column named Unit Price from a DataFrame.

How should you reference the Unit Price column in the Python code?

39. You are attempting to configure certification for a Power BI dataset and discover that the certification setting for the dataset is unavailable.

What are two possible causes of the issue? Each correct answer presents a complete solution. NOTE: Each correct selection is worth one point.

40. You have an Azure Synapse Analytics dedicated SQL pool.

You need to ensure that the SQL pool is scanned by Azure Purview.

What should you do first?