Microsoft Certified: Power BI Data Analyst AssociateModel the dataHard
A data modeler is optimizing a Power BI model for a large enterprise. The 'Sales' fact table has millions of rows and is related to several dimension tables. The model is experiencing slow query performance. Upon inspection, the modeler notices that the 'Sales' table has a 'ProductDescription' column which is a text column with high cardinality and is not used for filtering or grouping in reports. What action should the modeler take to improve model performance?
- AAdd 'ProductDescription' to the 'Products' dimension table.
- BCreate a calculated column for 'ProductDescription' in the 'Sales' table.
- CRemove the 'ProductDescription' column from the 'Sales' fact table.
- DHide the 'ProductDescription' column from report view.
Show answer & explanationAnswer & explanation
Correct answer: C. Remove the 'ProductDescription' column from the 'Sales' fact table.
Removing high cardinality, unused text columns from a fact table significantly reduces model size and improves processing and query performance, as fact tables should ideally only contain foreign keys and measures.
Why the other options are wrong
- A. Adding it to the dimension table is good practice, but removing it from the fact table is the primary performance gain.
- B. Creating a calculated column exacerbates the problem by adding more data and processing overhead.
- D. Hiding the column only affects visibility, not the underlying model size or performance.
Fact Table Optimization
Optimizing a fact table involves minimizing its size and complexity by including only foreign keys and measures, while offloading descriptive attributes to dimension tables.
- Fact tables store quantitative data (measures) and foreign keys.
- Avoid descriptive text columns in fact tables to reduce size.
- High cardinality columns, especially text, in fact tables negatively impact performance.
- Moving descriptive attributes to dimension tables improves query performance and data model efficiency.
Memory trick: Optimize your model like a race car: shed weight and streamline parts.