DP-500 Microsoft Azure Enterprise Data Analyst Associate Practice Questions

1. What should you configure in the deployment pipeline?

2. HOTSPOT

You need to populate the Customers With Product Score 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.

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

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

What should you do?

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. How should you configure the Power BI dataset refresh for the dbo.SalesTransactions table?

7. HOTSPOT

You need to create a measure to count orders for the market basket analysis.

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

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

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

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

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 identify the root cause of the data refresh issue.

What should you use?

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

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

16. Topic 3, Fabrikam, Inc.



Overview

Fabrikam, Inc. is a software company that develops educational software for higher education.

Fabrikam has the following business units:

• Finance and Accounting

• Sales and Marketing

• Operations

• Product



The Product business unit contains the following groups:

• User experience designers

• Software engineers

• Product managers

• Testers



The Operations business unit contains an information technology (IT) group. The IT group contains an enterprise analytics team and an information security team.

Fabrikam has three Azure Synapse Analytics workspaces named workspace! prod, workspace 1 test, and workspaceldev. Each workspace is connected to an Azure Data Lake Storage account and contains a lake database that is accessed by using the built-in serverless SQL pool. The data in the Data Lake Storage accounts is available in the lake databases for analysts from every business unit to query and analyze by using Power Bl.



The company imports the following files into the Data Lake Storage accounts:

• User experience data stored as JSON files

• Finance data stored as CSV files

• Sales data stored as CSV files



Fabrikam has a Power Bl tenant that contains 30 workspaces in Pro license mode. The data in the workspaces is a mix of Import and DirectQuery datasets. All reports are interactive.



Fabrikam has three frequently used workspaces as shown in the following table.





The Corporate Data Models workspace contains a dataset named Financial Model that is used by reports in the P&L workspace. Financial Model is maintained by the enterprise analytics team. The Corporate Data Models workspace and the User Experience workspace have corresponding development and test workspaces.

Administrators report an increase in the maintenance of Power Bl tenant assets due to analysts in the Finance and Accounting business unit who create new Power Bl datasets when the existing datasets already meet their needs.



Analysts in the Product business unit report the following issues:

• Datasets are published to the User Experience workspace, while the data sources reference workspace! test.

• The parsing of user experience data in Power Query is very slow.



The enterprise analytics team identifies two DAX measures in the Financial Model dataset that are consistently slow to execute. The team must identify all the reports that use the Financial Model dataset and notify the report owners of changes to the measures.

Members of the enterprise analytics team report that creating Power Bl reports and adjusting tables and views in Azure Synapse is tedious because they must switch between the Power Bl workspaces and the Azure Synapse workspaces.

The information security team identifies that the user experience data is being shared externally.



Fabrikam plans to implement the following changes:

• Power Bl will be registered as a data source in Microsoft Purview.

• The analysts in the Product business unit will create a more automated process for deploying Power Bl reports and datasets to the User Experience workspace.



The enterprise analytics team plans to perform the following tasks:

• Update the DAX calculations in the Financial Model dataset.

• Create views in the Azure Synapse workspaces to speed up the parsing of user experience data.

• Create and document the change management process for shared Power Bl datasets.



From Microsoft Purview, analysts in all the business units must be able to see all the assets in the Power Bl tenant and the Azure Synapse workspaces. Power Bl asset information must include lineage to identify the data sources used by each report.

The information security team must identify all the Power Bl reports and datasets that contain Personally Identifiable Information (Pll).

Fabrikam requires a security solution for the Power Bl tenant.



The solution must meet the following requirements:

• Access to the tenant by external users must be approved by a manager and granted by the IT group.

• The external users must be prevented from publishing or modifying content in the tenant

• Users must be prevented from sharing Power Bl reports publicly to the internet.



The new process for deploying Power Bl reports and datasets to the User Experience workspace must ensure that the datasets point to the lake database to which the relevant dataset is deployed. The views in each lake database must present the data in a tabular format.





The enterprise analytics team needs to notify the report owners of the planned changes. The solution must minimize administrative effort.

What should the team do?

17. You need to recommend a solution for the analysts in the Finance and Accounting business unit to mitigate the increase in maintenance of their assets in the Power Bl tenant.

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

18. HOTSPOT

You need to recommend a security solution for the Power Bl tenant to control external data sharing.

The solution must meet the technical requirements.

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

19. HOTSPOT

The enterprise analytics team needs to create a view for the survey response data. The solution must meet the technical requirements.

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

20. DRAG DROP

You need to ensure that the new process for deploying reports and datasets to the User Experience workspace meets the technical requirements.

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.

21. The enterprise analytics team needs to resolve the DAX measure performance issues.

What should the team do first?

22. You need to optimize the workflow for the creation of reports and the adjustment of tables by the enterprise analytics team.

What should you do?

23. You need to meet the technical requirements for deploying reports and datasets to the User Experience workspace.

What should you do?

24. You need to recommend changes to the Power Bl tenant to meet the technical requirements for external data sharing.

Which tenant setting should you recommend disabling?

25. The group registers the Power Bl tenant as a data source1.

You need to ensure that all the analysts can view the assets in the Power Bl tenant The solution must meet the technical requirements for Microsoft Purview and Power BI.

What should you do?

26. Topic 4, Misc. Questions



You develop a solution that uses a Power Bl Premium capacity. The capacity contains a dataset that is expected to consume 50 GB of memory.

Which two actions should you perform to ensure that you can publish the model successfully to the Power Bl service? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point.

27. DRAG DROP

You have a Power Bl dataset that contains the following measures:

• Budget

• Actuals

• Forecast

You create a report that contains 10 visuals.

You need provide users with the ability to use a slicer to switch between the measures in two visuals only.

You create a dedicated measure named cg Measure switch.

How should you complete the DAX expression for the Actuals measure? To answer, drag the appropriate values to the targets. Each value may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content. NOTE: Each correct selection is worth one point.

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

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

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

31. DRAG DROP

You have a Power Bl dataset that contains two tables named Table1 and Table2. The dataset is used by one report.

You need to prevent project managers from accessing the data in two columns in Table1 named Budget and Forecast.

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.

32. You have a Power Bl data model.

You need to refresh the data from the source every 15 minutes.

What should you do first?

33. HOTSPOT

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.

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

35. You are configuring a Power Bl report for accessibility as shown in the following table.





You need to change the default colors of all three visuals to make the report more accessible to users who have color vision deficiency.

Which two settings should you configure in the Customize theme window? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point.

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

37. DRAG DROP

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.

38. HOTSPOT

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.

39. HOTSPOT

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.

40. You have a Power Bl report that contains one visual.

You need to provide users with the ability to change the visual type without affecting the view for other users.

What should you do?

41. You have a Power Bl report that contains the visual shown in the following exhibit.





You need to make the visual more accessible to users who have color vision deficiency.

What should you do?

42. HOTSPOT

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.

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





Which type of chart will the code produce?

44. You use Azure Synapse Analytics and Apache Spark notebooks to You need to use PySpark to gain access to the visual libraries.

Which Python libraries should you use?

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

46. You have a kiosk that displays a Power Bl report page. The report uses a dataset that uses Import storage mode. You need to ensure that the report page updates all the visuals every 30 minutes.

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

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

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



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.