Microsoft DP-500 Certification All-in-One Exam Guide Mar-2024
Get Real DP-500 Exam Dumps [Mar-2024] Practice Tests
Microsoft DP-500 exam is designed to test the knowledge and skills of IT professionals who are interested 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 a great way to demonstrate your proficiency in the latest technologies and tools used in the field of data analytics.
Microsoft DP-500 Exam is an industry-recognized certification that showcases one's ability to design and implement enterprise-scale analytics solutions using Microsoft Azure and Microsoft Power BI. DP-500 exam is designed for professionals who wish to validate their skills and knowledge in data analytics, and those who want to demonstrate their proficiency in using Microsoft's cloud-based services to drive business insights and decision-making.
NEW QUESTION # 20
You manage a dataset that contains the two data sources as shown in the following table.
When you attempt to refresh the dataset in powerbi.com, you receive the following error message: "[Unable to combine data] Add Columns is accessing data sources that have privacy levels which cannot be used together.
Please rebuild this data combination."
You discover that the dataset contains queries that fold data from the SharePoint folder to the Azure SQL database.
You need to resolve the error. The solution must provide the highest privacy possible.
Which privacy level should you select for each data source? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Answer:
Explanation:
Explanation
Box 1: Private
This Formula.Firewall error is the result of Power Query's Data Privacy Firewall (aka the Firewall) Note: Folding is a term that refers to converting expressions in M (such as filters, renames, joins, and so on) into operations against a raw data source (such as SQL, OData, and so on).
Box 2: Organizational
Organizational Limits the visibility of a data source to a trusted group of people. It is isolated from all Public data sources, but is visible to other Organizational data sources. A common example is a Microsoft Word document on an intranet SharePoint site with permissions enabled for a trusted group.
Reference:
https://support.microsoft.com/en-us/office/set-privacy-levels-power-query-cc3ede4d-359e-4b28-bc72-9bee7900b
NEW QUESTION # 21
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. Option C
- B. Option A
- C. Option B
- D. Option D
Answer: B
Explanation:
Use FIELDTERMINATOR ='\t' for tab.
Use ROWTERMINATOR ='\0x0A ' for UNIX-style line endings
Use FIRSTROW= 2 for a header row
Note: Using Row Terminators
The row terminator can be the same character as the terminator for the last field. Generally, however, a distinct row terminator is useful. For example, to produce tabular output, terminate the last field in each row with the newline character (\n) and all other fields with the tab character (\t).
If you want to output a line feed character only (LF) as the row terminator - as is typical on Unix and Linux computers - use hexadecimal notation to specify the LF row terminator. For example:
bcp -r '0x0A'
FIRSTROW
FIRSTROW =first_row Specifies the number of the first row to load. The default is 1. This indicates the first row in the specified data file. The row numbers are determined by counting the row terminators. FIRSTROW is 1-based.
Reference:
https://docs.microsoft.com/en-us/sql/t-sql/functions/openrowset-transact-sql
NEW QUESTION # 22
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. one Embedded
- C. 70 Pro
- D. one Premium
- E. 35 Premium per user
- F. 35 Pro
Answer: D,E,F
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 # 23
You have the following Python code in an Apache Spark notebook.
Which type of chart will the code produce?
- A. an area chart
- B. a bar chart
- C. a pie chart
- D. a stacked bar chart
Answer: A
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 # 24
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 surveyName as Latim_Generai_100_BiN2_UTF8.
Does this meet the goal?
- A. No
- B. Yes
Answer: B
Explanation:
Explanation
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
NEW QUESTION # 25
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.
- A. The sensitivity level for the dataset is set to Highly Confidential.
- B. The workspace is in shared capacity.
- C. Row-level security (RLS) is missing from the dataset.
- D. Dataset certification is disabled for the Power Bl tenant.
- E. You have insufficient permissions.
Answer: D,E
Explanation:
Reference:
https://docs.microsoft.com/en-us/power-bi/collaborate-share/service-endorse-content
NEW QUESTION # 26
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.
Answer:
Explanation:
Explanation
Box 1: SELECTEDMEASURENAME()
SELECTEDMEASURENAME is used by expressions for calculation items to determine the measure that is in context by name.
Syntax: SELECTEDMEASURENAME()
No parameters.
Example:
The following calculation item expression checks if the current measure is Expense Ratio and conditionally applies calculation logic. Since the check is based on a string comparison, it is not subject to formula fixup and will not benefit from object renaming being automatically reflected. For a similar comparison that would benefit from formula fixup, please see the ISSLECTEDMEASURE function instead.
IF (
SELECTEDMEASURENAME = "Expense Ratio",
SELECTEDMEASURE (),
DIVIDE ( SELECTEDMEASURE (), COUNTROWS ( DimDate ) )
)
Box 2: SELECTEDVALUE()
SELECTEDVALUE returns the value when the context for columnName has been filtered down to one distinct value only. Otherwise returns alternateResult.
Syntax:
SELECTEDVALUE(<columnName>[, <alternateResult>])
M1, M2, ... - A list of measures.
Reference: https://docs.microsoft.com/en-us/dax/selectedmeasurename-function-dax
https://docs.microsoft.com/en-us/dax/selectedvalue-function
NEW QUESTION # 27
You discover a poorly performing measure in a Power Bl data model.
You need to review the query plan to analyze the amount of time spent in the storage engine and the formula engine.
What should you use?
- A. Performance analyzer in Power Bl Desktop
- B. DAX Studio
- C. Tabular Editor
- D. Vertipaq Analyzer
Answer: A
Explanation:
Explanation
Monitor report performance in Power BI Desktop using the Performance Analyzer. Monitoring will help you learn where the bottlenecks are, and how you can improve report performance.
Monitoring performance is relevant in the following situations:
Your Import data model refresh is slow.
Your DirectQuery or Live Connection reports are slow.
Your model calculations are slow.
Slow queries or report visuals should be a focal point of continued optimization.
Reference: https://docs.microsoft.com/en-us/power-bi/guidance/monitor-report-performance
NEW QUESTION # 28
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?
- A. Turn off auto refresh when publishing the dataset changes to the Power Bl service.
- B. Create a dataset parameter to reduce the fact table row count in the development and test pipelines.
- C. In the dataset. change the fact table from an import table to a hybrid table.
- D. Enable the large dataset storage format for workspace.
Answer: C
Explanation:
Explanation
Hybrid tables
Hybrid tables are tables with incremental refresh that can have both import and direct query partitions. During a clean deployment, both the refresh policy and the hybrid table partitions are copied. When deploying to a pipeline stage that already has hybrid table partitions, only the refresh policy is copied. To update the partitions, refresh the table.
Refreshes are faster - Only the most recent data that has changed needs to be refreshed.
Reference: https://docs.microsoft.com/en-us/power-bi/create-reports/deployment-pipelines-best-practices
NEW QUESTION # 29
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?
- A. Matplotlib and Seaborn
- B. Matplotlib only
- C. TensorFlow only
- D. Seaborn only
- E. Matplotlib and TensorFlow
- F. Seaborn and TensorFlow
Answer: C
NEW QUESTION # 30
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. No
- B. Yes
Answer: B
Explanation:
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.
Reference: https://www.mssqltips.com/sqlservertutorial/3211/denormalize-for-performance/
NEW QUESTION # 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?
- A. Yes
- B. No
Answer: B
NEW QUESTION # 32
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 # 33
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?
- A. Configure a distinct color for each series.
- B. Replace long text with abbreviations and acronyms.
- C. Configure a unique marker for each series.
- D. Move important information to a tooltip.
Answer: A
Explanation:
Themes, contrast and colorblind-friendly colors.
You should ensure that your reports have enough contrast between text and any background colors.
Certain color combinations are particularly difficult for users with color vision deficiencies to distinguish. These include the following combinations:
green and red
green and brown
blue and purple
green and blue
light green and yellow
blue and grey
green and grey
green and black
Avoid using these colors together in a chart, or on the same report page.
NEW QUESTION # 34
You are building a Power Bl dataset that contains a table named Calendar. Calendar contains the following calculated column.
pfflag = IF('Calendar'[Date] < TOOAYQ, "Past", "Future")
You need to create a measure that will perform a fiscal prior year-to-date calculation that meets the following requirements:
* Returns the fiscal prior year-to-date value for [sales Amount]
* Uses a fiscal year end of June 30
* Produces no result for dates in the future
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:
Explanation
Box 1: CALCULATETABLE
CALCULATETABLE evaluates a table expression in a modified filter context.
Syntax: CALCULATETABLE(<expression>[, <filter1> [, <filter2> [, ...]]]) Incorrect:
* SUMMARIZECOLUMNS
SUMMARIZECOLUMNS returns a summary table over a set of groups.
Syntax: SUMMARIZECOLUMNS( <groupBy_columnName> [, < groupBy_columnName >]...,
[<filterTable>]...[, <name>, <expression>]...)
* CROSSJOIN returns a table that contains the Cartesian product of all rows from all tables in the arguments.
The columns in the new table are all the columns in all the argument tables.
Syntax: CROSSJOIN(<table>, <table>[, <table>]...)
* UNION creates a union (join) table from a pair of tables.
Syntax: UNION(<table_expression1>, <table_expression2> [,<table_expression>]...) Box 2: SAMEPERIODLASTYEAR SAMEPERIODLASTYEAR returns a table that contains a column of dates shifted one year back in time from the dates in the specified dates column, in the current context.
Syntax: SAMEPERIODLASTYEAR(<dates>)
The dates returned are the same as the dates returned by this equivalent formula: DATEADD(dates, -1, year) Example:
The following sample formula creates a measure that calculates the previous year sales of Reseller sales.
= CALCULATE(SUM(ResellerSales_USD[SalesAmount_USD]),
SAMEPERIODLASTYEAR(DateTime[DateKey))
Box 3: TODAY()
TODAY() returns the current date.
The TODAY function is useful when you need to have the current date displayed on a worksheet, regardless of when you open the workbook. It is also useful for calculating intervals.
Example:
The following sample formula creates a measure that calculates the 'Running Total' for Internet sales.
= CALCULATE(SUM(InternetSales_USD[SalesAmount_USD]), DATESYTD(DateTime[DateKey])) Reference: https://docs.microsoft.com/en-us/dax/calculatetable-function-dax
https://docs.microsoft.com/en-us/dax/sameperiodlastyear-function-dax
https://docs.microsoft.com/en-us/dax/datesytd-function-dax
NEW QUESTION # 35
You use Vertipaq Analyzer to analyze a model.
The Relationships tab contains the results 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 # 36
You have a Power Bl tenant that is linked to an Azure AD tenant. The tenant contains a group named Group1.
You create a paginated report that does NOT use row-level security (RLS).
You need to send a copy of the report to Group1 every Monday at 9 AM UTC Which two actions should you include in the solution? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.
- A. Create a data alert for Group1.
- B. Create a subscription for Group1
- C. Publish the paginated report to a Pro workspace.
- D. Publish the paginated report to a Premium Per User workspace.
Answer: B,D
NEW QUESTION # 37
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. freqlcems
- B. rollup
- C. cov
- D. summary
Answer: C
Explanation:
Explanation
pandas.DataFrame.corr computes pairwise correlation of columns, excluding NA/null values.
Incorrect:
* freqItems
pyspark.sql.DataFrame.freqItems
Finding frequent items for columns, possibly with false positives. Using the frequent element count algorithm described in https://doi.org/10.1145/762471.762473, proposed by Karp, Schenker, and Papadimitriou.'
* summary is used for index.
* There is no panda method for rollup. Rollup would not be correct anyway.
Reference: https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.corr.html
NEW QUESTION # 38
You have a Power Bl tenant
You invite an external consultant to work in the tenant.
You need to grant the consultant access to the tenant. The solution must meet the following requirements;
* The consultant must be able to consume, create, and update reports and datasets.
* The consultant must access content by using Azure AD B2B.
Which settings should you enable in the Power Bl Admin portal? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.
- A. Allow Azure Active Directory guest users to access Power Bl
- B. Invite external users to your organization
- C. External sharing
- D. Allow Azure Active Directory guest users to edit and manage content in the organization
Answer: A,D
NEW QUESTION # 39
......
Last DP-500 practice test reviews: Practice Test Microsoft dumps: https://www.testpdf.com/DP-500-exam-braindumps.html
Try DP-500 Free Now! Real Exam Question Answers: https://drive.google.com/open?id=1DvPEjZboN07MXqLQFgbdhrB-0NS8USbL
