DP-500 Designing and Implementing Enterprise-Scale Analytics Solutions Using Microsoft Azure and Microsoft Power BI Questions and Answers

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.

Step 1: From powerbi.com, create a new report..

The company wants custom Power BI usage reporting that includes the percent change of users that view reports in the Sales Analytics workspace each month.

Step 2: Add a report measure

Measures are used in some of the most common data analyses. Simple summarizations such as sums, averages, minimum, maximum and counts can be set through the Fields well. The calculated results of measures are always changing in response to your interaction with your reports, allowing for fast and dynamic ad-hoc data exploration.

Step 3: Add visuals to the report

Step 4: Publish the report to the Sales Analytics workspace

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.

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.

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).

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

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.

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.

Users indicate that queries against the serverless SQL pool fail occasionally because the size of tempdb has been exceeded.

In the dedicated SQL pool resource, temporary tables offer a performance benefit because their results are written to local rather than remote storage.

Temporary tables in serverless SQL pool.

Temporary tables in serverless SQL pool are supported but their usage is limited. They can't be used in queries which target files.

For example, you can't join a temporary table with data from files in storage. The number of temporary tables is limited to 100, and their total size is limited to 100 MB.

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