Microsoft DP-600 - Implementing Analytics Solutions Using Microsoft Fabric

To enable scale-out for a semantic model, you should first set Large dataset storage format to On (C) at the semantic model level. This configuration is necessary to handle larger datasets effectively in a scaled-out environment. Referenc es = Guidance on configuring large dataset storage formats for scale-out is available in the Power BI documentation.

To implement row-level security (RLS) in a Fabric Warehouse (like in Azure Synapse or SQL Server):

You must define a p redicate function (usually an inline table-valued function) that filters rows for each user → Function .

Then you bind that function to the table using a Security Policy → Security Policy .

A Database Role is used for group-based access control but not specifically for implementing RLS.

Stored procedures and constraints are not used for RLS.

Correct answers: A and D

We are designing a star schema for sales transactions. In a star schema:

Dimension tables contain descriptive attributes (e.g., Customer, Product, Date).

Fact tables contain measurable, quantitative data (e.g., SalesTransactionID, Quantity, UnitPrice).

Factless fact tables capture events without numeric measures.

Junk dimensions store miscellaneous low-cardinality attributes.

The requirement says dimension tables will use Type 2 Slowly Changing Dimensions (SCD) → so Customer and Date attributes belong to Dimension tables .

Field-by-field classification

CustomerCode

A business key (natural key) for the customer dimension.

Belongs in the Customer Dimension table.

Answer: Dimension .

CustomerName

A descriptive attribute of the customer.

Belongs in the Customer Dimension table.

Answer: Dimension .

SaleDate

A time attribute used for analysis.

Belongs in the Date Dimension table.

Answer: Dimension .

Final Answer:

CustomerCode → Dimension

CustomerName → Dimension

SaleDate → Dimension

Step 1: Requirement

We need to calculate total sales amou nt by OrderDate .

Total Sales Amount = Quantity × UnitPrice per row.

Then SUM over all rows per OrderDate.

Only include rows where the OrderDate matches a public holiday in Australia .

Step 2: Calculation Logic

If we select s.Quantity * s.UnitPrice , it only computes row-level values.

To calculate the total sales amount per OrderDate , we need:

SUM (s.Quantity * s.UnitPrice) AS TotalSalesAmt

Thus, the correct calculation: SUM(s.Quantity * s.UnitPrice)

Step 3: Join Type

We want to return only those sales that occurred on public holidays in Australia .

If we used a LEFT JOIN , we’d include all sales regardless of holiday.

A FULL OUTER JOIN would include non-matching rows, not required.

A CROSS JOIN would duplicate results unnecessarily.

A RIGHT OUTER JOIN would include holidays with no sales, which is not needed.

The correct choice is an INNER JOIN on matching dates.

Step 4: Final Query

SELECT

s.OrderDate,

SUM (s.Quantity * s.UnitPrice) AS TotalSalesAmt

FROM dbo.sales AS s

INNER JOIN dbo.publicholidays AS ph

ON s.OrderDate = ph.Date

WHERE ph.countryOrRegion = ' Australia '

GROUP BY s.OrderDate;

Why This is Correct

SUM(s.Quantity * s.UnitPrice) → ensures sales are aggregated correctly.

INNER JOIN → ensures only rows where OrderDate matches a holiday are included.

WHERE ph.countryOrRegion = ' Australia ' → filters holidays to Australia only.

References

SUM (Transact-SQL)

JOIN operations in T-SQL

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 pr ocessing step that summarizes data.

References =

Introduction to DataFrames - Spark SQL

Power BI and Azure Databricks

The PoC is planned to be completed using a Fabric trial capacity, which implies that users involved in the PoC should be able to try paid features. However, this should be limited to specific security groups involved in the PoC to prevent the entire organization from accessing the se features before the trial is proven successful (A). The ability for users to create Fabric items should also be enabled for specific security groups to ensure that only the relevant team members participating in the PoC can create items in the Fabric en vironment (E).

For the semantic model design required for the customer satisfaction report, the choices for data source authentication method and mode should be made b ased on security and performance considerations as per the case study provided.

Authentication method: The data should be accessed securely, and given that row-level security (RLS) is required for users executing T-SQL queries, you should use an authentica tion method that supports RLS. Service principal authentication is suitable for automated and secure access to the data, especially when the access needs to be controlled programmatically and is not tied to a specific user ' s credentials.

Mode: The report n eeds to show data as soon as it is updated in the data store, and it should only contain data from the current and previous year. DirectQuery mode allows for real-time reporting without importing data into the model, thus meeting the need for up-to-date da ta. It also allows for RLS to be implemented and enforced at the data source level, providing the necessary security measures.

Based on these considerations, the selections should be:

Authentication method: Service principal authentication

Mode: DirectQuery

Comprehensive Detailed Explanation

When assigning permissions to roles in a Fabric workspace for analytics workloads, you must align permissions with the responsibilities and tools typically used by each persona:

1. Data Engineers

Their primary tasks include building and transforming data pipelines, as well as making datasets usable by others.

The appropriate permission is Build Reports on the default dataset , because this allows them to create and manage reports using the curated semantic model while preparing data for analysts and scientists.

2. Data Analysts

Analysts work heavily with Apache Spark for exploratory analysis, cleansing, and shaping data.

The correct permission is Read All Apache Spark , as this grants them the ability to query and analyze Spark-based data directly without elevated permissions that are unnecessary for their role.

3. Data Scientists

Data scientists often use SQL analytics endpoints to run queries, train models, and integrate data into machine learning workflows.

The correct permission is Read All SQL analytics endpoint data , since it enables direct programmatic access to the SQL endpoint, which is needed for advanced modeling and experimentation.

Summary of Se lections:

DataEngineers → Build Reports on the default dataset

DataAnalysts → Read All Apache Spark

DataScientists → Read All SQL analytics endpoint data

References (Microsoft Fabric – DP-600 exam scope):

Workspace roles and permissions in Microsoft Fabric

SQL analytics endpoint in Fabric

Apache Spark in Microsoft Fabric

The measure should use the AVERAGE function to calculate the average value.

It should reference the Response Value column from the ' Survey ' table.

The ' Number of months ' should be used to define the period for the average calculation.

To calculate the average overall satisfaction score using DAX, yo u would need to use the AVERAGE function on the response values related to satisfaction questions. The DATESINPERIOD function will help in calculating the rolling average over the last 12 months.