Practice Azure Enterprise Data Analyst Associate DP-500 exam. Online Exam Practice Tests with detailed explanations! Pass DP-500 with confidence!
DP-500 - Designing and Implementing Enterprise-Scale Analytics Solutions Using Microsoft Azure and Microsoft Power BI Practice Tests 2024 | Prep4sureGuide
NEW QUESTION # 52
You have an Azure Data Lake Storage Gen 2 container that stores more than 300,000 files representing hourly telemetry dat a. 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
Answer:
Explanation:
Reference:
https://docs.microsoft.com/en-us/powerquery-m/table-selectrows
https://docs.microsoft.com/en-us/azure/data-lake-store/data-lake-store-comparison-with-blob-storage
NEW QUESTION # 53
You deploy a tabular model to the Power Bl service. The model is developed and maintained by using Microsoft Visual Studio. Power Bl connects to the model by using the Connect live option.
You need to recommend a source control solution for the model and the report files.
What should you include in the recommendation for each type of file? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Answer:
Explanation:
NEW QUESTION # 54
You have a Power BI tenant.
You plan to register the tenant in an Azure Purview account.
You need to ensure that you can scan the tenant by using Azure Purview.
Which two actions should you perform? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.
- A. From the Power Bl Admin center, set Allow service principals to use read-only Power Bl admin APIs to Enabled.
- B. From the Microsoft 365 admin center, create a Microsoft 365 group.
- C. From the Power Bl Admin center, set Allow live connections to Enabled.
- D. From the Power Bl Admin center, set Share content with external users to Enabled.
- E. From the Azure Active Directory admin center, create a security group.
Answer: A,D
Explanation:
Explanation
Scan same-tenant Power BI using Azure IR and Managed Identity in public network.
Make sure Power BI and Microsoft Purview accounts are in the same tenant.
Make sure Power BI tenant Id is entered correctly during the registration.
From Azure portal, validate if Microsoft Purview account Network is set to public access.
From Power BI tenant Admin Portal, make sure Power BI tenant is configured to allow public network.
(D) In Azure Active Directory tenant, create a security group.
From Azure Active Directory tenant, make sure Microsoft Purview account MSI is member of the new security group.
On the Power BI Tenant Admin portal, validate if Allow service principals to use read-only Power BI admin APIs is enabled for the new security group.
Associate the security group with Power BI tenant
Log into the Power BI admin portal.
Select the Tenant settings page.
(C) Select Admin API settings > Allow service principals to use read-only Power BI admin APIs (Preview).
Select Specific security groups.
Select Admin API settings > Enhance admin APIs responses with detailed metadata > Enable the toggle to allow Microsoft Purview Data Map automatically discover the detailed metadata of Power BI datasets as part of its scans.
Reference: https://docs.microsoft.com/en-us/azure/purview/register-scan-power-bi-tenant
NEW QUESTION # 55
You need to save Power Bl dataflows in an Azure Storage account.
Which two prerequisites are required to support the configuration? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.
- A. The storage account must have hierarchical namespace enabled.
- B. The storage account must be protected by using an Azure Firewall.
- C. Dataflows must exist already for any directly connected Power Bl workspaces.
- D. The storage account must be created in a separate Azure region from the Power Bl tenant and workspaces.
- E. The connection must be created by a user that is assigned the Storage Blob Data Owner role.
Answer: A,E
NEW QUESTION # 56
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?
- A. Power Bl Pro per user
- B. a Power Bl Premium per capacity
- C. Power Bl Report Server
- D. Power Bl Premium per user
Answer: D
NEW QUESTION # 57
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.
Answer:
Explanation:
NEW QUESTION # 58
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.
Answer:
Explanation:
NEW QUESTION # 59
You have a Power Bl dataset that has the query dependencies 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.
Answer:
Explanation:
Explanation
Box 1: 3
Power Query doesn't start at the first query and work down, it starts at the bottom (last) query and works backwards, so 3 tables from 1 will cause it to process that first source table 3 times.
Box 2: Using Table.Buffer in the Orders query
Table.Buffer buffers a table in memory, isolating it from external changes during evaluation. Buffering is shallow. It forces the evaluation of any scalar cell values, but leaves non-scalar values (records, lists, tables, and so on) as-is.
Note that using this function might or might not make your queries run faster. In some cases, it can make your queries run more slowly due to the added cost of reading all the data and storing it in memory, as well as the fact that buffering prevents downstream folding.
Example 1
Load all the rows of a SQL table into memory, so that any downstream operations will no longer be able to query the SQL server.
Usage
let
Source = Sql.Database("SomeSQLServer", "MyDb"),
MyTable = Source{[Item="MyTable"]}[Data],
BufferMyTable = Table.Buffer(dbo_MyTable)
in
BufferMyTable
Output
table
Reference: https://radacad.com/performance-tip-for-power-bi-enable-load-sucks-memory-up
https://docs.microsoft.com/en-us/powerquery-m/table-buffer
NEW QUESTION # 60
You use an Apache Spark notebook in Azure Synapse Analytics to filter and transform data.
You need to review statistics for a DataFrame that includes:
The column name
The column type
The number of distinct values
Whether the column has missing values
Which function should you use?
- A. %%lsmagic
- B. %%configure
- C. displayHTML()
- D. display(df, summary=true)
- E. display(df)
Answer: D
Explanation:
Explanation
display(df) statistic details
You can use display(df, summary = true) to check the statistics summary of a given Apache Spark DataFrame that include the column name, column type, unique values, and missing values for each column. You can also select on specific column to see its minimum value, maximum value, mean value and standard deviation.
Reference: https://docs.microsoft.com/en-us/azure/synapse-analytics/spark/apache-spark-data-visualization
NEW QUESTION # 61
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:
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.
Reference: https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/query-parquet-files
Topic 1, 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 # 62
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.
- A. First-level elements colors
- B. Divergent colors
- C. Sentiment colors
- D. Theme colors
Answer: A
NEW QUESTION # 63
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?
- A. Create a data policy.
- B. Create a data share connection.
- C. Register a data source.
- D. Search the data catalog.
Answer: C
NEW QUESTION # 64
You are using an Azure Synapse Analytics serverless SQL pool to query network traffic logs in the Apache Parquet format. A sample of the data is shown in the following table.
You need to create a Transact-SQL query that will return the source IP address.
Which function should you use in the select statement to retrieve the source IP address?
- A. CONVERT
- B. FOR.JSON
- C. JS0N_VALUE
- D. FIRST VALUE
Answer: C
NEW QUESTION # 65
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 are a. NOTE: Each correct selection is worth one point.
Answer:
Explanation:
NEW QUESTION # 66
Your company is migrating its current, custom-built reporting solution to Power BI.
The Power BI tenant must support the following scenarios:
40 reports that will be embedded in external websites. The websites control their own security. The reports will be consumed by 50 users monthly.
Forty-five users that require access to the workspaces and apps in the Power BI Admin portal. Ten of the users must publish and consume datasets that are larger than 1 GB.
Ten developers that require Text Analytics transformations and paginated reports for datasets. An additional 15 users will consume the reports.
You need to recommend a licensing solution for the company. The solution must minimize costs.
Which two Power BI license options should you include in the recommendation? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.
- A. 70 Premium per user
- B. 35 Premium per user
- C. one Premium
- D. 35 Pro
- E. 70 Pro
- F. one Embedded
Answer: B,C,D
Explanation:
Explanation
B:
Free - 40 reports that will be embedded in external websites. The websites control their own security.
Free - The reports will be consumed by 50 users monthly.
Free + 1 Premium for the Worspace -Forty-five users that require access to the workspaces and apps in the Power BI Admin portal.
F: Ten of the users must publish and consume datasets that are larger than 1 GB.
Ten developers that require Text Analytics transformations and paginated reports for datasets. An additional
15 users will consume the reports.
Power BI Premium per user features and capabilities
* Pixel perfect paginated reports are available for operational reporting capabilities based on SSRS technology. Users can create highly formatted reports in various formats such as PDF and PPT, which are embeddable in applications and are designed to be printed or shared.
Note: There are three kinds of Power BI per-user licenses: Free, Pro, and Premium Per User.
Power BI (free): Access to content in My Workspace
Power BI (free) + Workspace is Premium: Consume content shared with them Power BI Pro: Publish content to other workspaces, share dashboards, subscribe to dashboards and reports, share with users who have a Pro license Power BI Pro + Workspace is Premium: Distribute content to users who have free licenses Power BI Premium Per User: Publish content to other workspaces, share dashboards, subscribe to dashboards and reports, share with users who have a Premium Per User license Power BI Premium Per User + Workspace is Premium: Distribute content to users who have free and Pro licenses Reference: https://docs.microsoft.com/en-us/power-bi/fundamentals/service-features-license-type
NEW QUESTION # 67
You need to identify the root cause of the data refresh issue.
What should you use?
- A. Query Diagnostics in Power Query Editor
- B. the Usage Metrics Report in powerbi.com
- C. Performance analyzer in Power Bl Desktop
Answer: A
Explanation:
Users indicate that the data in Power BI reports is stale. You discover that the refresh process of the Power BI model occasionally times out.
With Query Diagnostics, you can achieve a better understanding of what Power Query is doing at authoring and at refresh time in Power BI Desktop. While we'll be expanding on this feature in the future, including adding the ability to use it during full refreshes, at this time you can use it to understand what sort of queries you're emitting, what slowdowns you might run into during authoring refresh, and what kind of background events are happening.
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.
NEW QUESTION # 68
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:
NEW QUESTION # 69
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.
Answer:
Explanation:
Explanation
Box 1: PREDICT
Provide internal users with the ability to incorporate machine learning models loaded to the dedicated SQL pool.
The example below shows a sample query using prediction function. An additional column with name Score and data type float is created containing the prediction results. All the input data columns as well as output prediction columns are available to display with the select statement.
-- Query for ML predictions
SELECT d.*, p.Score
FROM PREDICT(MODEL = (SELECT Model FROM Models WHERE Id = 1),
DATA = dbo.mytable AS d, RUNTIME = ONNX) WITH (Score float) AS p;
Box 2: WITH
Reference:
https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-predict
NEW QUESTION # 70
You have a Power Bl dataset that has the query dependencies 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.
Answer:
Explanation:
NEW QUESTION # 71
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 # 72
......
Get instant access to DP-500 practice exam questions: https://drive.google.com/open?id=1878YdA0vWpEY48veB1pHG-ywEKX0wTga
The best DP-500 exam study material and preparation tool is here: https://www.prep4sureguide.com/DP-500-prep4sure-exam-guide.html