Learn how to optimize a semantic model in Microsoft Fabric by reducing the model size and increasing refresh performance in Import mode. Discover tips for handling large columns like TransactionKey and SaleDateTime. Prepare for the DP-600 certification exam with this sample question and explanation.
Table of Contents
Question
You have a Fabric tenant that contains a semantic model named model1. The two largest columns in model1 are shown in the following table.
| Name | Cardinality | Compressed size | Description |
|---|---|---|---|
| TransactionKey | 2,500,350,000 | 160 GB | Contains an incrementing surrogate key for a fact table named SalesTransaction |
| SaleDateTime | 6,850,273 | 120 GB | Contains the datetime (to the nearest second) of when a sale occurred |
You need to optimize model1. The solution must meet the following requirements:
- Reduce the model size.
- Increase refresh performance when using Import mode.
- Ensure that the datetime value for each sales transaction is available in the model.
What should you do on each column? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
TransactionKey:
A. Change the data type of the column.
B. Remove the column.
C. Split the column.
D. Trancate the data.
SaleDateTime:
A. Change the data type of the column.
B. Remove the column.
C. Split the column.
D. Trancate the data.
Answer
TransactionKey: Remove the column.
SaleDateTime: Split the column.
Explanation
To optimize model1 in your Microsoft Fabric tenant, you should do the following for each column:
TransactionKey:
B. Remove the column.
Explanation: The TransactionKey column contains an incrementing surrogate key for the SalesTransaction fact table. While useful for data warehousing, surrogate keys are typically not needed in semantic models and can significantly increase the model size. Removing this column will reduce the overall size of model1 without losing critical information. It’s the best option to meet the requirements of reducing model size and increasing refresh performance.
SaleDateTime:
C. Split the column.
Explanation: The SaleDateTime column contains the date and time of each sales transaction down to the second. To reduce cardinality and model size while still retaining the datetime information, the best approach is to split this column into separate Date and Time columns.
- Create a Date column with just the date portion, which will have much lower cardinality than the full datetime values.
- Create a Time column with an integer representation of the time (e.g. number of seconds after midnight).
This will significantly compress the data size compared to the original SaleDateTime column while preserving the full datetime information in the model. Changing the data type or truncating the data would lose the time portion. Removing the column entirely would not meet the requirement of keeping the sales transaction datetime data in the model.
In summary, removing the TransactionKey surrogate key column and splitting the SaleDateTime into separate Date and Time columns will optimize model1 by reducing the size and increasing refresh performance, while still making the sales transaction datetime values available. This is the best approach to satisfy all the stated requirements.
Microsoft DP-600 certification exam assessment practice question and answer (Q&A) dump including multiple choice questions (MCQ) and objective type questions, with detail explanation and reference available free, helpful to pass the Microsoft DP-600 exam and earn Microsoft DP-600 certification.