[2024] DP-500 by Azure Enterprise Data Analyst Associate Actual Free Exam Practice Test
Free Azure Enterprise Data Analyst Associate DP-500 Exam Question
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.
NEW QUESTION # 89
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.
Answer:
Explanation:
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.
NEW QUESTION # 90
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?
- A. Yes
- B. No
Answer: B
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.
NEW QUESTION # 91
You have the following Python code in an Apache Spark notebook.
Which type of chart will the code produce?
- A. a pie chart
- B. a bar chart
- C. a stacked bar chart
- D. an area chart
Answer: D
Explanation:
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
NEW QUESTION # 92
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.
Answer:
Explanation:
Explanation
Step 1: Create row-level security (RLS) roles
Create roles
Note: Provide all the customers with their own Power BI 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 BI to simplify the query experience of the customers.
Step 2: Create a DAX expression
Consider a model with two roles: The first role, named Workers, restricts access to all Payroll table rows by using the following rule expression:
FALSE()
Note: A rule will return no table rows when its expression evaluates to false.
Yet, a second role, named Managers, allows access to all Payroll table rows by using the following rule expression:
TRUE()
Take care: Should a report user map to both roles, they'll see all Payroll table rows.
Step 3: Add members to row-level security (RLS) roles
Configure role mappings
Once [the model is] published to Power BI, you must map members to dataset roles.
Reference: https://docs.microsoft.com/en-us/power-bi/guidance/rls-guidance
NEW QUESTION # 93
You need to recommend an automated solution to monitor Power Bl user activity. The solution must meet the following requirements:
* Security admins must identify when users export reports from Power Bl within five days of a new sensitivity label being applied to the artifacts in Power Bl.
* Power Bl admins must identify updates or changes to the Power Bl capacity.
* The principle of least privilege must be used.
Which log should you include in the recommendation for each group? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Answer:
Explanation:
Explanation
Box 1: the unified audit log in Microsoft 365
Security admins must identify when users export reports from Power BI within five days of a new sensitivity label being applied to the artifacts in Power BI.
Use the audit log
If your task is to track user activities across Power BI and Microsoft 365, you work with auditing in Microsoft
365 compliance or use PowerShell. Auditing relies on functionality in Exchange Online, which automatically supports Power BI.
You can filter the audit data by date range, user, dashboard, report, dataset, and activity type. You can also download the activities in a csv (comma-separated value) file to analyze offline.
Box 2: Power BI activity log
Power BI admins must identify updates or changes to the Power BI capacity.
Use the activity log
Power BI administrators can analyze usage for all Power BI resources at the tenant level by using custom reports that are based on the Power BI activity log.
Reference: https://docs.microsoft.com/en-us/power-bi/admin/service-admin-auditing
NEW QUESTION # 94
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.
Answer:
Explanation:
NEW QUESTION # 95
You have a Power Bl report hosted on the Power Bl service. The report displays expenses by department for department managers and contains a line chart that shows expenses by month. You need to ensure that users can choose between viewing the report as a line chart or a column chart. The solution must minimize development and maintenance effort. What should you do?
- A. Enable report readers to personalize visuals.
- B. Create a mobile report that contains a column chad.
- C. Add a column chart, a bookmark, and a button for users to choose a visual.
- D. Create a separate report page for users to view a column chart.
Answer: A
NEW QUESTION # 96
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.
- A. Change the report theme to monochromatic.
- B. Replace the product category charts with a bar chart for sales and a hierarchy of Category and Sub Category on the axis.
- C. Replace the single-value cards with a multi-row card.
- D. Replace all the filters on the Filters pane with visual slicers on the report page.
Answer: B,C
NEW QUESTION # 97
You are running a diagnostic against a query as shown in the following exhibit.
What can you identify from the diagnostics query?
- A. Elevated permissions are being used to query records.
- B. All the query steps are folding.
- C. The query is timing out.
- D. Some query steps are folding.
Answer: D
NEW QUESTION # 98
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?
A)
B)
C)
D)
- A. Option B
- B. Option D
- C. Option A
- D. Option C
Answer: C
NEW QUESTION # 99
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.
Answer:
Explanation:
NEW QUESTION # 100
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. a workspace that connects to an Azure Data Lake Storage Gen2 account
- B. a Power Bl Embedded capacity
- C. an on-premises data gateway (personal mode)
- D. an on-premises data gateway (standard mode)
Answer: C
Explanation:
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.
NEW QUESTION # 101
You are implementing a reporting solution that has the following requirements:
* Reports for external customers must support 500 concurrent requests. The data for these reports is
approximately 7 GB and is stored in Azure Synapse Analytics.
* Reports for the security team use data that must have local security rules applied at the database level to
restrict access. The data being reviewed is 2 GB.
Which storage mode provides the best response time for each group of users?
- A. Import for the external customers and import for the security team.
- B. Import for the external customers and DirectQuery for the security team.
- C. DirectQuery for the external customers and import for the security team.
- D. DirectQuery for the external customers and DirectQuery for the security team.
Answer: C
NEW QUESTION # 102
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?
- A. freqltems
- B. explain
- C. rollup
- D. describe
Answer: D
Explanation:
Explanation
The aggregating statistic can be calculated for multiple columns at the same time with the describe function.
Example:
titanic[["Age", "Fare"]].describe()
Out[6]:
Age Fare
count 714.000000 891.000000
mean 29.699118 32.204208
std 14.526497 49.693429
min 0.420000 0.000000
25% 20.125000 7.910400
50% 28.000000 14.454200
75% 38.000000 31.000000
max 80.000000 512.329200
Reference: https://pandas.pydata.org/docs/getting_started/intro_tutorials/06_calculate_statistics.html
NEW QUESTION # 103
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?
- A. When all the visuals refresh simultaneously, the visuals spend most of the time waiting on other processes to finish.
- B. The Actual/Forecast Hours by Type visual takes a long time to render on the report page when the data is cross-filtered.
- C. The Actual/Forecast Billable Hrs YTD visual displays the most data.
- D. Unoptimized DAX queries cause the page to load slowly.
Answer: A
Explanation:
Most time is spent in the category Other - time required by the visual for preparing queries, waiting for other visuals to complete, or performing other background processing.
Note: Each visual's log information includes the time spent (duration) to complete the following categories of tasks:
DAX query - if a DAX query was required, this is the time between the visual sending the query, and for Analysis Services to return the results.
Visual display - time required for the visual to draw on the screen, including time required to retrieve any web images or geocoding.
Other - time required by the visual for preparing queries, waiting for other visuals to complete, or performing other background processing.
NEW QUESTION # 104
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.
Answer:
Explanation:
Explanation
Box 1: Join
Box 2: LeftOuter
Left outer join
One of the join kinds available in the Merge dialog box in Power Query is a left outer join, which keeps all the rows from the left table and brings in any matching rows from the right table.
Diagram, table Description automatically generated
Reference: https://docs.microsoft.com/en-us/power-query/merge-queries-left-outer
NEW QUESTION # 105
You are running a diagnostic against a query as shown in the following exhibit.
What can you identify from the diagnostics query?
- A. Some query steps are folding.
- B. Elevated permissions are being used to query records.
- C. All the query steps are folding.
- D. The query is timing out.
Answer: C
Explanation:
Explanation
Understanding folding with Query Diagnostics
One of the most common reasons to use Query Diagnostics is to have a better understanding of what operations were 'pushed down' by Power Query to be performed by the back-end data source, which is also known as 'folding'. If we want to see what folded, we can look at what is the 'most specific' query, or queries, that get sent to the back-end data source. We can look at this for both ODATA and SQL.
Reference: https://docs.microsoft.com/en-us/power-query/querydiagnosticsfolding
NEW QUESTION # 106
You need to use Power Bl to ingest data from an API. The API requires that an API key be passed in the headers of the request.
Which type of authentication should you use?
- A. Anonymous
- B. Basic
- C. Web API
- D. organizational account
Answer: B
NEW QUESTION # 107
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.
From Power Query Editor, you profile the data shown in the following exhibit.
From Power Query Editor, you profile the data shown in the following exhibit The loT GUIO and loT ID columns are unique to each row in the query.
You need to analyze loT events by the hour and day of the year. The solution must improve dataset performance.
Solution: You remove the loT GUID column and retain the loT ID column.
Does this meet the goal?
- A. Yes
- B. No
Answer: B
NEW QUESTION # 108
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.
Answer:
Explanation:
NEW QUESTION # 109
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.
Answer:
Explanation:
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.
NEW QUESTION # 110
You have the following Python code in an Apache Spark notebook.
Which type of chart will the code produce?
- A. a pie chart
- B. a bar chart
- C. a stacked bar chart
- D. an area chart
Answer: D
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.
NEW QUESTION # 111
......
The DP-500 exam also covers topics related to designing and implementing Power BI solutions, such as creating and designing reports, deploying and configuring Power BI services, and creating and managing datasets. Professionals who pass DP-500 exam demonstrate their expertise in designing and implementing scalable and efficient analytics solutions that can help organizations make informed decisions and drive business growth. Designing and Implementing Enterprise-Scale Analytics Solutions Using Microsoft Azure and Microsoft Power BI certification is ideal for data architects, data engineers, and business intelligence professionals who work with Microsoft Azure and Power BI.
The Microsoft DP-500 exam covers various topics related to designing and implementing enterprise-scale analytics solutions, including designing data storage solutions, designing and developing data processing solutions, designing and implementing data analytics solutions, and designing and implementing data visualization solutions. Additionally, the exam also covers topics related to managing and securing data, as well as optimizing and troubleshooting analytics solutions. Passing the Microsoft DP-500 exam demonstrates a professional's proficiency in designing and implementing enterprise-scale analytics solutions using Microsoft Azure and Microsoft Power BI.
Microsoft DP-500 Actual Questions and Braindumps: https://gocertify.topexamcollection.com/DP-500-vce-collection.html
