A data modeler is optimizing a Power BI model for an inventory management system. The model has a 'InventoryTransactions' fact table and a 'Products' dimension table. To improve query performance, the modeler wants to reduce the number of distinct values in certain columns within the 'InventoryTransactions' table without losing critical information. For example, a 'TransactionDescription' column has many unique text values but often describes similar types of transactions. What is the most effective data modeling technique to address this specific issue?
- AConvert the 'TransactionDescription' column to a calculated column that uses a shorter text string.
- BCreate a new dimension table for 'Transaction Descriptions' and replace the original column with a foreign key.
- CRemove the 'TransactionDescription' column entirely from the fact table.
- DChange the data type of 'TransactionDescription' to a fixed-length text type.
Show answer & explanationAnswer & explanation
Correct answer: B. Create a new dimension table for 'Transaction Descriptions' and replace the original column with a foreign key.
This scenario describes creating a new dimension table, often called a 'junk dimension' or simply a new dimension. By extracting the 'TransactionDescription' into its own dimension table and linking it back to the 'InventoryTransactions' fact table with a foreign key, you replace a high-cardinality text column in the fact table with a low-cardinality integer key, significantly reducing the fact table's size and improving compression and query performance.
Why the other options are wrong
- A. Converting to a calculated column with a shorter string might reduce size but still leaves a text column in the fact table, which is less efficient than an integer key.
- C. Removing the column entirely would lose critical information, which is not the goal; the goal is to optimize its storage while retaining the data.
- D. Changing the data type to fixed-length text won't reduce cardinality and might even waste space if descriptions are variable length.
Fact Table Column Optimization (Dimensioning)
The process of moving descriptive, often high-cardinality text columns from a fact table into a dedicated dimension table, replacing them with a low-cardinality integer foreign key in the fact table.
- Reduces fact table size and improves compression.
- Enhances query performance for descriptive attributes.
- Standard star schema design principle.
- Applies to 'junk dimensions' or new specific dimensions.
Memory trick: Big fact table columns? Dimension them out!