Question
You have a Fabric tenant that contains a semantic model named Model1. Model1 uses Import mode. Model1 contains a table named Orders. Orders has 100 million rows and the following fields.
| Name | Data type | Description |
|---|---|---|
| OrderID | Integer | Column imported from the source |
| OrderDateTime | Date/Time | Column imported from the source |
| Quantity | Integer | Column imported from the source |
| Price | Decimal | Column imported from the source |
| TotalSalesAmount | Decimal | Calculated column that multiplies Quantity and Price |
| TotalQuantity | Integer | Measure |
You need to reduce the memory used by Model1 and the time it takes to refresh the model.
Which two actions should you perform? Each correct answer presents part of the solution.
NOTE: Each correct answer is worth one point.
- A. Split OrderDateTime into separate date and time columns. Correct
- B. Replace TotalQuantity with a calculated column.
- C. Convert Quantity into the Text data type.
- D. Replace TotalSalesAmount with a measure. Correct
Explanation
A Date/Time column with 100 million rows has very high cardinality; splitting it into separate date and time columns produces two low-cardinality columns that compress far better in VertiPaq. TotalSalesAmount is a calculated column, so it is computed during every refresh and stored for all 100 million rows; replacing it with a measure (for example SUMX over Quantity times Price) removes that storage and refresh work. Turning the TotalQuantity measure into a calculated column would add memory, and converting Quantity to Text would increase size and break aggregation.
