Microsoft DP-600 - Implementing Analytics Solutions Using Microsoft Fabric

Power BI Premium Per User (PPU) already supports XMLA read/write. You don’t need to upgrade to Premium capacity.

The workspace itself does not get a managed identity; rather, App1 (Azure App Service) can be configured with a managed identity.

Once App1 has a managed identity, you grant it access to the Power BI model in the workspace via XMLA endpoint.

Modifying the XMLA endpoint setting is not sufficient; authentication must come from the service (App1).

Correct answer: B.

To meet the requirements, developers should have Admin access to the deployment pipeline (B), Contributor access to the Development and Test workspaces (E), and Viewer access to the Production workspace (D). This setup ensures they can perform necessary actions in development and test environments without having the ability to affect production. References = The Power BI documentation on workspace access levels and deployment pipelines provides guidelines on assigning appropriate permissions.

VACUUM in Delta Lake removes unreferenced files older than a retention period (default 7 days, but configurable, e.g., 30 days).

It ensures that the transaction log remains consistent and ACID properties are maintained.

OPTIMIZE with V-Order or Z-Order improves file organization and query performance, but it does not remove unused files.

Deleting files manually from OneLake would break the transaction log consistency.

Correct answer: C. Run the VACUUM command.

To view column information like the one shown in the exhibit in Power Query, you need to select the options that enable profiling and display quality and distribution details. These are: A. Enable column profile - This option turns on profiling for each column, showing statistics such as distinct and unique values. B. Show column quality details - It displays the column quality bar on top of each column showing the percentage of valid, error, and empty values. E. Show column value distribution - It enables the histogram display of value distribution for each column, which visualizes how often each value occurs.

We are analyzing the JSON for definition.pbir in a Power BI Project:

{

"version": "1.0",

"datasetReference": {

"byPath": {

"path": "../Sales.Dataset"

},

"byConnection": null

}

}

Statement 1:

"definition.pbir is in the PBIR-Legacy format."

No → The legacy PBIR format references datasets by byConnection (pointing to the Power BI service).

Here, it uses byPath, which is the new project format, not legacy.

Statement 2:

"The semantic model referenced by definition.pbir is located in the Power BI service."

No → The JSON shows byPath: "../Sales.Dataset", meaning the semantic model is referenced locally within the project folder (Sales.Dataset), not in the Power BI service.

Statement 3:

"When the related report is opened, Power BI Desktop will open the semantic model in full edit mode."

Yes → Since the semantic model is referenced by path (local PBIP project files), Power BI Desktop will open the model in full edit mode. If it had been byConnection, it would open in live connect mode to the service instead.

Final Answer:

PBIR-Legacy format → No

Semantic model in Power BI service → No

Opens in full edit mode → Yes

Comprehensive Detailed Explanation

Step 1: Requirement

We need to compare the average miles per trip for:

Statutory holidays (when IsHoliday = 1)

Non-statutory holidays (when IsHoliday = 0)

Step 2: Formula for average miles per trip

Average miles per trip = total miles ÷ number of trips

Total miles → SUM(t.tripDistance)

Number of trips → COUNT(t.tripID)

So the calculation is:

(SUM(t.tripDistance) / COUNT(t.tripID)) AS MilesPerTrip

Step 3: Grouping

We need a comparison by holiday status.

So we must group the results by:

GROUP BY d.IsHoliday

This ensures we get two rows: one for IsHoliday = 1 and one for IsHoliday = 0.

Step 4: Final Query

SELECT

d.IsHoliday,

(SUM(t.tripDistance) / COUNT(t.tripID)) AS MilesPerTrip

FROM DimDate d

INNER JOIN Trips t ON d.DateID = t.DateID

GROUP BY d.IsHoliday;

Why This is Correct

The formula ensures average miles per trip.

Grouping ensures comparison between holidays vs non-holidays.

Efficient aggregation, minimal computation.

References

Aggregate functions in T-SQL

GROUP BY clause

Step 1 – Creating the new table from a query

To create a table in a SQL warehouse using a query, the correct syntax is:

CREATE TABLE AS SELECT ...

So we must select:

CREATE TABLE dbo.POSCustomers AS SELECT

Step 2 – Selecting the source table

Since the data source is the Lakehouse1 table Customer, the fully qualified name is:

lakehouse1.dbo.Customer

Final Answer:

CREATE TABLE dbo.POSCustomers AS SELECT

postalcode,

category

FROM lakehouse1.dbo.Customer;

For a Type 1 SCD, you should include actions that update rows when non-key attributes have changed (A), and insert new records when the natural key is a new value in the table (D). A Type 1 SCD does not track historical data, so you always overwrite the old data with the new data for a given key. References = Details on Type 1 slowly changing dimension patterns can be found in data warehousing literature and Microsoft's official documentation.

ISEMPTY(CALCULATETABLE('Order Item')) checks if there are no related rows in 'Order Item' for a customer. Wrapping this in NOT returns true if the customer has at least one order. This avoids calculating row counts and instead just checks existence, which is more efficient for performance. This reduces execution time and meets the goal.

The code embeds an existing Power BI report. - No

The code creates a Power BI report. - Yes

The code displays a summary of the DataFrame. - Yes

The code provided seems to be a snippet from a SQL query or script which is neither creating nor embedding a Power BI report directly. It appears to be setting up a DataFrame for use within a larger context, potentially for visualization in Power BI, but the code itself does not perform the creation or embedding of a report. Instead, it's likely part of a data processing step that summarizes data.

References =

Introduction to DataFrames - Spark SQL

Power BI and Azure Databricks