Prepare for the Actual Azure Enterprise Data Analyst Associate DP-500 Exam Practice Materials Collection
Azure Enterprise Data Analyst Associate Certified Official Practice Test DP-500 - Jan-2024
Microsoft DP-500 certification exam is designed for IT professionals who are interested in validating their skills and knowledge in designing and implementing enterprise-scale analytics solutions using Microsoft Azure and Microsoft Power BI. Designing and Implementing Enterprise-Scale Analytics Solutions Using Microsoft Azure and Microsoft Power BI certification exam is ideal for data engineers, data architects, data scientists, and other professionals who work with data and analytics on a regular basis.
NEW QUESTION # 88
You have a Power Bl data model.
You need to refresh the data from the source every 15 minutes.
What should you do first?
- A. Change the storage mode of the dataset.
- B. Define an incremental refresh policy.
- C. Enable the XMLA endpoint.
- D. Configure a scheduled refresh.
Answer: D
Explanation:
To get to the Scheduled refresh screen:
1. In the navigation pane, under Datasets, select More options (...) next to a dataset listed.
2. Select Schedule refresh.
NEW QUESTION # 89
You are creating a Power Bl report that will contain multiple visuals.
You need to ensure that the report is accessible to users who use a screen reader.
Which two configurations should you perform? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.
- A. consistent fonts for each visual
- B. consistent colors for each visual
- C. alt text for each visual
- D. the tab order for each page
- E. Play Axis for each page
Answer: C,D
NEW QUESTION # 90
You have an Azure Synapse Analytics serverless SQL pool.
You need to return a list of files and the number of rows in each file.
How should you complete the Transact-SQL statement? 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.
Answer:
Explanation:
Reference:
https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/develop-openrowset
NEW QUESTION # 91
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.
- A. Configure the data sources to use DirectQuery.
- B. Enable the XMIA endpoint.
- C. Configure the data sources to use a streaming dataset
- D. Enable Power Bl embedded.
- E. Select Auto page refresh.
- F. Add a Microsoft Power Automate visual to the report page.
Answer: D,E
NEW QUESTION # 92
You have an Azure subscription that contains an Azure Synapse Analytics workspace. You create an Azure Data Lake Storage Gen2 account and upload a CSV file named Filel.csv. You need to use Synapse Studio to query the data in Filel.csv by using a serverless SQL pool. Which Transact-SQL operator should you include in the query?
- A. OPEMDATASOURCE
- B. STRIMO_SPLIT
- C. OPCNROWSET
- D. OPENOUERY
Answer: C
NEW QUESTION # 93
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 BI 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 denormalizing the data model.
Does this meet the goal?
- A. Yes
- B. No
Answer: A
Explanation:
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.
NEW QUESTION # 94
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 # 95
You have an Azure Synapse workspace named Workspace1.
You need to use PySpark in a notebook to read data from a SQL pool as an Apache Spark DataFrame and display the top five How should you complete the code? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Answer:
Explanation:
Explanation
Graphical user interface, text, application Description automatically generated
Box 1: sqlanalytics
Read from a SQL Pool table with Spark
// Read the table we just created in the sql pool as a Spark dataframe
val spark_read = spark.read.
sqlanalytics(s"$sql_pool_name.dbo.PublicHoliday")
spark_read.show(5, truncate = false)
Box 2: spark_read.show
Sample output:
Graphical user interface, text, application, email Description automatically generated
Reference:
https://github.com/Azure-Samples/Synapse/blob/main/Notebooks/Scala/03%20Read%20and%20write%20from%
NEW QUESTION # 96
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.
Answer:
Explanation:
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.
NEW QUESTION # 97
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 # 98
You have new security and governance protocols for Power Bl reports and datasets. The new protocols must meet the following requirements.
* New reports can be embedded only in locations that require authentication.
* Live connections are permitted only for workspaces that use Premium capacity datasets.
Which three actions should you recommend performing in the Power Bl Admin portal? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point.
- A. From the Premium per user settings, set XMLA Endpoint to Off.
- B. From Capacity settings, set XMLA Endpoint to Read Write.
- C. From Tenant settings, disable Allow XMLA endpoints and Analyze in Excel with on-premises datasets.
- D. From Tenant settings, set Publish to web to Disable.
- E. From Embed Codes, delete all the codes.
Answer: B,C,D
Explanation:
Reference:
https://powerbi.microsoft.com/en-us/blog/power-bi-february-service-update
NEW QUESTION # 99
You have an Azure Synapse Analytics dataset that contains data about jet engine performance. You need to
score the dataset to identify the likelihood of an engine failure. Which function should you use in the query?
- A. PREDICT
- B. PIVOT
- C. GROUPING
- D. CAST
Answer: B
NEW QUESTION # 100
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.
Answer:
Explanation:
Explanation
NEW QUESTION # 101
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. describe
- B. sample
- C. corr
- D. freqItems
Answer: A
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 # 102
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: A
NEW QUESTION # 103
You have a shared dataset in Power Bl named Dataset1.
You have an on-premises Microsoft SQL Server database named DB1.
You need to ensure that Dataset1 refreshes data from DB1.
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: Install the on-premises data gateway (standard mode)
The personal mode is only for a single user, not to be used for a shared dataset.
Step 2: From powerbi.com, add a data source to the gateway clusters
After you install the on-premises data gateway, you can add data sources that can be used with the gateway.
Add a data source
Under Data Source Type, select SQL Server.
After you fill in everything, select Create. You can now use this data source for scheduled refresh or DirectQuery against a SQL Server that's on-premises. You see Created New data source if it succeeded.
Step 3: From powerbi.com, configure Dataset1 to use a data gateway.
Connect a dataset to a SQL Server database
In Power BI Desktop, you connected directly to your on-premises SQL Server database, but the Power BI service requires a data gateway to act as a bridge between the cloud and your on-premises network. Follow these steps to add your on-premises SQL Server database as a data source to a gateway and then connect your dataset to this data source.
Sign in to Power BI. In the upper-right corner, select the settings gear icon and then select Settings.
On the Datasets tab, select the dataset AdventureWorksProducts, so you can connect to your on-premises SQL Server database through a data gateway.
Expand Gateway connection and verify that at least one gateway is listed.
Under Actions, expand the toggle button to view the data sources and select the Add to gateway link.
On the Gateways management page, on the Data Source Settings tab, enter and verify the following information, and select Add.
On the Datasets tab, expand the Gateway connection section again. Select the data gateway you configured, which shows a Status of running on the machine where you installed it, and select Apply.
Reference: https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-personal-mode
https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-sql-tutorial
https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-enterprise-manage-sql
NEW QUESTION # 104
You have a Power Bl report that contains a bar chart. The bar chart displays sales by country.
You need to ensure that a summary of the data shown on the bar chart is accessible by using a screen reader.
What should you configure on the bar chart?
- A. the tab order
- B. conditional formatting
- C. alt text
- D. the layer order
Answer: C
NEW QUESTION # 105
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 # 106
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?
- A. Remove the alternative result of blank( ) from the divide function.
- B. Replace both calculate functions by using a variable that contains the calculate function.
- C. Create a variable and replace the values for [sales Amount].
- D. Remove "calendar'[Flag] = "YTD" from the code.
Answer: B
NEW QUESTION # 107
You have an Azure Synapse Analytics workspace that contains an Apache Spark pool. You create a notebook and configure a cell that runs the following SparkSQL query.
SELECT ProductID, ProductName, Category From products.
You need to create a column chart by using the built-in charting capability. The solution must visualize the distribution of product IDs across product categories.
Answer:
Explanation:
NEW QUESTION # 108
You plan to 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?
- A. any users that accessed a report that uses the dataset within the last 30 days
- B. the Power Bl admins
- C. all the workspace members of any workspace that uses the dataset
- D. the workspace admins of any workspace that uses the dataset
Answer: C
Explanation:
Explanation
Notify contacts
If you've made a change to a dataset or are thinking about making a change, you might want to contact the relevant users to tell them about it. When you notify contacts, an email is sent to the contact lists of all the impacted workspaces. Your name appears on the email so the contacts can find you and reply back in a new email thread.
Reference: https://docs.microsoft.com/en-us/power-bi/collaborate-share/service-dataset-impact-analysis
NEW QUESTION # 109
You plan to create a Power Bl report that will use an OData feed as the data source. You will retrieve all the entities from two different collections by using the same service root The OData feed is still in development. The location of the feed will change once development is complete.
The report will be published before the OData feed development is complete.
You need to minimize development effort to change the data source once the location 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.
Answer:
Explanation:
Explanation
Graphical user interface, text, application Description automatically generated
Step 1: Create a parameter that contains the service root URI
Step 2: Get data from OData feed source and use the parameter to populate the first part of the URL.
The URI is in the first part of the query.
Example: let
Source = OData.Feed
("https://analytics.dev.azure.com/{organization}/{project}/_odata/v3.0-preview/WorkItemSnapshot? "
&"$apply=filter( "
&"WorkItemType eq 'Bug' "
&"and StateCategory ne 'Completed' "
&"and startswith(Area/AreaPath,'{areapath}') "
&"and DateValue ge {startdate} "
&") "
&"/groupby( "
&"(DateValue,State,WorkItemType,Priority,Severity,Area/AreaPath,Iteration/IterationPath,AreaSK), "
&"aggregate($count as Count) "
&") "
,null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4]) in Source Box 3: From Advanced Editor, duplicate the query and change the resource path in the URL.
Choose Get Data, and then Blank Query.
From the Power BI Query editor, choose Advanced Editor.
The Advanced Editor window opens.
Edit the query.
Etc.
Reference: https://docs.microsoft.com/en-us/azure/devops/report/powerbi/odataquery-connect
NEW QUESTION # 110
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.
- A. Add Apply buttons to all basic filters to apply changes when you're ready
- B. Add an Apply button to each slicer to apply changes when you're ready
- C. Disabling cross highlighting/filtering by default
- D. Add a single Apply button to the filter pane to apply changes at once
- E. Ignore the Privacy Levels and potentially improve performance
Answer: B,D
Explanation:
Reduce queries
Reduce the number of queries sent by Power BI using the Query reduction settings. For slicers, select the "Add an Apply button to each slicer to apply changes when you're ready" option. For filters, select "Add a single Apply button to the filter pane to apply changes at once (preview)."
NEW QUESTION # 111
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.
Answer:
Explanation:
Explanation
NEW QUESTION # 112
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
- A. Connect the target workspace to an Azure Data Lake Storage Gen2 account.
- B. Enable service principal authentication for read-only admin APIs.
- C. Enable XMLA read-write.
- D. Turn on Large dataset storage format.
Answer: C,D
Explanation:
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/
NEW QUESTION # 113
......
Ace Microsoft DP-500 Certification with Actual Questions Jan 12, 2024 Updated: https://www.lead1pass.com/Microsoft/DP-500-practice-exam-dumps.html
2024 The Most Effective DP-500 with 157 Questions Answers: https://drive.google.com/open?id=1osiZcclS--GdF2qisonTJONwVje6cbrQ