DP-500 V1

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 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.
A . Set Use datasets across workspaces to Enabled
B. Publish the financial data to the web.
C. Grant the Build permission for the financial data to each customer.
D. Configure the FinData workspace to use a Power Bl Premium capacity.

View Answer

Answer: A,D

Explanation:

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 BI workspace to create their own reports. Each workspace will use the new dataset in the FinData workspace

Reference: https://docs.microsoft.com/en-us/power-bi/connect-data/service-datasets-admin-across-workspaces

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.

View Answer

Answer:

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. In the dataset. change the fact table from an import table to a hybrid table.
C. Enable the large dataset storage format for workspace.
D. Create a dataset parameter to reduce the fact table row count in the development and test pipelines.

View Answer

Answer: B

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

You have a Power BI Premium capacity.

You need to increase the number of virtual cores associated to the capacity.

Which role do you need?
A . Power Bl workspace admin
B. capacity admin
C. Power Platform admin
D. Power Bl admin

View Answer

Answer: D

Explanation:

Change capacity size

Power BI admins and global administrators can change Power BI Premium capacity. Capacity admins who are not a Power BI admin or global administrator don’t have this option.

Reference: https://docs.microsoft.com/en-us/power-bi/enterprise/service-admin-premium-manage

What should you configure in the deployment pipeline?
A . a backward deployment
B. a selective deployment
C. auto-binding
D. a data source rule

View Answer

Answer: D

Explanation:

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 BI content must be deployed to test and production by using deployment pipelines.

Create deployment rules

When working in a deployment pipeline, different stages may have different configurations. For example, each stage can have different databases or different query parameters. The development stage might query sample data from the database, while the test and production stages query the entire database.

When you deploy content between pipeline stages, configuring deployment rules enables you to allow changes to content, while keeping some settings intact. For example, if you want a dataset in a production stage to point to a production database, you can define a rule for this. The rule is defined in the production stage, under the appropriate dataset. Once the rule is defined, content deployed from test to production, will inherit the value as defined in the deployment rule, and will always apply as long as the rule is unchanged and valid.

You can configure data source rules and parameter rules.

Incorrect:

Not B: if you already have a steady production environment, you can deploy it backward (to Test or Dev, based on your need) and set up the pipeline. The feature is not limited to any sequential orders.

Reference: https://docs.microsoft.com/en-us/power-bi/create-reports/deployment-pipelines-get-started#step-4—create-deployment-rules

You are using GitHub as a source control solution for an Azure Synapse Studio workspace.

You need to modify the source control solution to use an Azure DevOps Git repository.

What should you do first?
A . Disconnect from the GitHub repository.
B. Create a new pull request.
C. Change the workspace to live mode.
D. Change the active branch.

View Answer

Answer: A

Explanation:

By default, Synapse Studio authors directly against the Synapse service. If you have a

need for collaboration using Git for source control, Synapse Studio allows you to associate your workspace with a Git repository, Azure DevOps, or GitHub.

Prerequisites

Users must have the Azure Contributor (Azure RBAC) or higher role on the Synapse workspace to configure, edit settings and disconnect a Git repository with Synapse.

Reference: https://docs.microsoft.com/en-us/azure/synapse-analytics/cicd/source-control

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.
A . Synapse Studio
B. DAX Studio
C. Azure Data Studio
D. Performance analyzer in Power Bl Desktop

View Answer

Answer: B,D

Explanation:

Some users indicate that the visuals in Power BI reports are slow to render when making filter selections.

B: You can investigate a slow query in a Power BI report using DAX Studio, looking at the query plan and the server timings.

D: Use Power BI Desktop Performance Analyzer to optimize the report or model.

Reference:

https://www.sqlbi.com/tv/analyzing-a-slow-report-query-in-dax-studio/

https://docs.microsoft.com/en-us/power-bi/guidance/report-performance-troubleshoot

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.

HOTSPOT

You need to populate the CustomersWithProductScore 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.

View Answer

Answer:

Explanation:

Box 1: FLOAT

Identify which customers should receive promotional emails based on their likelihood of purchasing promoted products.

FLOT is used in the last statement of the code: WITH (score FLOAT) as p;

From syntax: MODEL

The MODEL parameter is used to specify the model used for scoring or prediction. The model is specified as a variable or a literal or a scalar expression.

Box 2: dbo.CustomerWithProductScore

Identify which customers should receive promotional emails based on their likelihood of purchasing promoted products.

Only table CustomerWithProductScore has the required filed score.

From the syntax:

DATA

The DATA parameter is used to specify the data used for scoring or prediction. Data is specified in the form of a table source in the query. Table source can be a table, table alias, CTE alias, view, or table-valued function.

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 . rollup
B. cov
C. explain
D. describe

View Answer

Answer: D

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

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?
A . Azure Purview
B. Site-to-Site VPN
C. an XMLA endpoint
D. the on-premises data gateway

View Answer

Answer: D

Explanation:

Refresh data from an on-premises SQL Server database

The SQL Server database must be accessed by Power BI through an on-premises data gateway.

You can install an on-premises data gateway on the same local computer as SQL Server (in production, it would typically be a different computer).

Reference: https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-sql-tutorial