Microsoft Implementing Analytics Solutions Using Microsoft Fabric (DP-600日本語版) - DP-600日本語 Exam Practice Test

Explanation:

In the ROW_NUMBER() function, choose OVER (PARTITION BY CustomerID ORDER BY LastUpdated DESC) .
In the WHERE clause, choose WHERE X = 1 .
To select the most recent row for each customer ID, you use the ROW_NUMBER() window function partitioned by CustomerID and ordered by LastUpdated in descending order. This will assign a row number of 1 to the most recent update for each customer. By selecting rows where the row number ( X ) is 1, you get the latest update per customer.
References =
Use the OVER clause to aggregate data per partition
Use window functions

Explanation:

For the semantic model design required for the customer satisfaction report, the choices for data source authentication method and mode should be made based 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 authentication 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 needs 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 data. 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
Topic 2, Contoso, ltd.
Overview
Contoso, ltd. is a US-based health supplements company, Contoso has two divisions named Sales and Research. The Sales division contains two departments named Online Sales and Retail Sales. The Research division assigns internally developed product lines to individual teams of researchers and analysts.
Identity Environment
Contoso has a Microsoft Entra tenant named contoso.com. The tenant contains two groups named ResearchReviewersGroupi and ReseachReviewefsGfoup2.
Data Environment
Contoso has the following data environment
* The Sales division uses a Microsoft Power B1 Premium capacity.
* The semantic model of the Online Sales department includes a fact table named Orders that uses import mode. In the system of origin, the OrderlD value represents the sequence in which orders are created.
* The Research department uses an on-premises. third-party data warehousing product.
* Fabric is enabled for contoso.com.
* An Azure Data Lake Storage Gen2 storage account named storage1 contains Research division data for a product line named Producthne1. The data is in the delta format.
* A Data Lake Storage Gen2 storage account named storage2 contains Research division data for a product line named Productline2. The data is in the CSV format.
Planned Changes
Contoso plans to make the following changes:
* Enable support for Fabric in the Power Bl Premium capacity used by the Sales division.
* Make all the data for the Sales division and the Research division available in Fabric.
* For the Research division, create two Fabric workspaces named Producttmelws and Productline2ws.
* in Productlinelws. create a lakehouse named LakehouseV
* In Lakehouse1. create a shortcut to storage1 named ResearchProduct.
Data Analytics Requirements
Contoso identifies the following data analytics requirements:
* All the workspaces for the Sales division and the Research division must support all Fabric experiences.
* The Research division workspaces must use a dedicated, on-demand capacity that has per-minute billing.
* The Research division workspaces must be grouped together logically to support OneLake data hub filtering based on the department name.
* For the Research division workspaces, the members of ResearchRevtewersGroupl must be able to read lakehouse and warehouse data and shortcuts by using SQL endpoints.
* For the Research division workspaces, the members of ResearchReviewersGroup2 must be able to read lakehouse data by using Lakehouse explorer.
* All the semantic models and reports for the Research division must use version control that supports branching Data Preparation Requirements Contoso identifies the following data preparation requirements:
* The Research division data for Producthne2 must be retrieved from Lakehouset by using Fabric notebooks.
* All the Research division data in the lakehouses must be presented as managed tables in Lakehouse explorer.
Semantic Model Requirements
Contoso identifies the following requirements for implementing and managing semantic models;
* The number of rows added to the Orders table during refreshes must be minimized.
* The semantic models in the Research division workspaces must use Direct Lake mode.
General Requirements
Contoso identifies the following high-level requirements that must be considered for all solutions:
* Follow the principle of least privilege when applicable
* Minimize implementation and maintenance effort when possible.

Explanation: Only visible for TrainingDump members. You can sign-up / login (it's free).

Explanation: Only visible for TrainingDump members. You can sign-up / login (it's free).

Explanation: Only visible for TrainingDump members. You can sign-up / login (it's free).

Explanation:

Comprehensive Detailed Explanation
We need to create a computed column PriceRange based on the UnitPrice column in the dbo.Product table.
Step 1: Requirements
If UnitPrice = 0 # " Not for resale "
If UnitPrice < 50 # " Under $50 "
If UnitPrice > = 50 AND UnitPrice < 250 # " Under $250 "
Otherwise # " $250+ "
This matches a CASE expression in T-SQL.
Step 2: CASE Expression Structure
The syntax is:
CASE
WHEN condition THEN result
WHEN condition THEN result
ELSE result
END
Step 3: Apply to Problem
SELECT
Item,
UnitPrice,
PriceRange = CASE
WHEN UnitPrice = 0 THEN ' Not for resale '
WHEN UnitPrice < 50 THEN ' Under $50 '
WHEN UnitPrice > = 50 AND UnitPrice < 250 THEN ' Under $250 '
ELSE ' $250+ '
END
FROM [Warehouse1].[dbo] .[Product];
Step 4: Why This is Correct
CASE starts the conditional evaluation.
ELSE handles the default branch ( $250+ ).
END closes the expression.
Meets all requirements exactly.
References
CASE expression in T-SQL
Computed columns in T-SQL

Explanation:

Comprehensive Detailed Explanation
We need to query a KQL database table named Readings to generate output that includes both the current reading and the previous reading values and datetime for each row.
Step 1: Understanding Requirements
The output table shows:
City, Area, MeterReading, Datetime
PrevMeterReading, PrevDatetime
The prev() function in KQL is used to access the value of a column from the previous row (based on order).
Step 2: Query Construction
We start with filtering:
Readings
| where City == " Copenhagen "
| sort by Datetime
To add columns that calculate the previous reading and previous datetime , we use extend :
| extend PrevMeterReading = prev(MeterReading), PrevDatetime = prev(Datetime) extend creates new columns.
prev() pulls the previous row's values.
Finally, we only want specific columns in the output. For this, we use project :
| project City, Area, MeterReading, Datetime, PrevMeterReading, PrevDatetime Step 3: Completed Query Readings
| where City == " Copenhagen "
| sort by Datetime
| extend PrevMeterReading = prev(MeterReading), PrevDatetime = prev(Datetime)
| project City, Area, MeterReading, Datetime, PrevMeterReading, PrevDatetime Why This is Correct extend adds calculated columns.
prev() gives access to the previous row.
project selects only the required columns.
This matches exactly the table shown in the question.
References
Kusto Query Language - extend operator
Kusto Query Language - project operator
prev() function in KQL

Explanation: Only visible for TrainingDump members. You can sign-up / login (it's free).

Explanation:
The results will form a hierarchy of folders for each partition key. - Yes The resulting file partitions can be read in parallel across multiple nodes. - Yes The resulting file partitions will use file compression. - No Partitioning data by columns such as year, month, and day, as shown in the DataFrame write operation, organizes the output into a directory hierarchy that reflects the partitioning structure. This organization can improve the performance of read operations, as queries that filter by the partitioned columns can scan only the relevant directories. Moreover, partitioning facilitates parallelism because each partition can be processed independently across different nodes in a distributed system like Spark. However, the code snippet provided does not explicitly specify that file compression should be used, so we cannot assume that the output will be compressed without additional context.
References =
DataFrame write partitionBy
Apache Spark optimization with partitioning

Explanation: Only visible for TrainingDump members. You can sign-up / login (it's free).

Explanation: Only visible for TrainingDump members. You can sign-up / login (it's free).

Explanation:

Explanation:
To execute each set of items in sequential order with minimized development effort and complexity, you should use the following options:
Orchestration pipeline: Use a pipeline with an Invoke pipeline activity. This allows for orchestrating and scheduling the execution of other pipelines, ensuring they run in the correct sequence.
Bronze layer: Implement a pipeline Copy activity. This aligns with the table indicating that the Bronze layer uses pipelines with Copy activities for data integration.
Silver layer: Implement a pipeline Dataflow activity. The table specifies that Dataflows are used for the Silver layer.
Gold layer: Implement a pipeline Stored procedure activity. Stored procedures are specified for the Gold layer according to the table.

Explanation:

To ensure that users assigned the SalesRegionManager role cannot see the Address column in the SalesAddress table, follow these steps in sequence:
Open the model in Tabular Editor.
Select the Address column in SalesAddress.
Set Object Level Security to None for SalesRegionManager.