A data modeler is optimizing a Power BI model for a large manufacturing company. The model includes a 'ProductionLog' fact table with billions of rows, containing granular event data. This table has a 'Timestamp' column (datetime) and several high-cardinality text columns used for detailed logging. The model is experiencing slow refresh times and report performance. Which optimization technique specifically targets improving performance related to high-cardinality text columns in a fact table?
- AIncrease the 'Data Cache Maximum Size' setting in Power BI Desktop options.
- BConvert the 'Timestamp' column to a calculated column with only the date part.
- CChange the storage mode of the 'ProductionLog' table to DirectQuery.
- DReplace high-cardinality text columns with integer keys referencing a new dimension table.
Show answer & explanationAnswer & explanation
Correct answer: D. Replace high-cardinality text columns with integer keys referencing a new dimension table.
High-cardinality text columns in a fact table consume significant memory and can severely impact query performance due to inefficient compression and larger data volumes. Replacing these text columns with integer keys that link to a new, smaller dimension table containing the actual text values (a process called 'dimensioning' or 'star schema optimization') is a fundamental and highly effective optimization for fact tables.
Why the other options are wrong
- A. This setting affects client-side caching, not the underlying model's efficiency or storage size for columns.
- B. This might optimize the 'Timestamp' column but doesn't address high-cardinality text columns.
- C. DirectQuery shifts processing to the source database, which might not improve Power BI model performance for high-cardinality columns if the source is also slow.
Fact Table Column Optimization (Dimensioning)
The process of reducing the size and improving the performance of fact tables by replacing high-cardinality or wide columns with narrower, lower-cardinality keys that link to separate dimension tables.
- Primarily targets text or high-cardinality numeric columns.
- Converts the model towards a star schema design.
- Reduces memory footprint and improves compression efficiency.
Memory trick: Optimize facts, shrink the table, boost the speed.