A data modeler is optimizing a Power BI model. The 'Sales' table has millions of rows and includes columns like 'OrderID', 'ProductID', 'CustomerID', and 'SalesAmount'. The 'ProductID' column is an integer. The modeler notices that many visuals use 'ProductID' directly as a filter or in row/column headers, even though a 'Products' dimension table exists with 'ProductID' and 'ProductName'. The 'Products' table has a one-to-many relationship to the 'Sales' table. Which optimization technique should the modeler apply to improve performance and user experience?
- AChange the data type of 'ProductID' in the 'Sales' table to text.
- BCreate a calculated column in the 'Sales' table combining 'ProductID' and 'ProductName'.
- CEstablish a bidirectional relationship between 'Products' and 'Sales' tables.
- DHide the 'ProductID' column in the 'Sales' table from report view.
Show answer & explanationAnswer & explanation
Correct answer: D. Hide the 'ProductID' column in the 'Sales' table from report view.
Hiding the 'ProductID' column in the 'Sales' table (the fact table) from the report view encourages users to use the 'ProductID' or 'ProductName' from the 'Products' dimension table instead. This is a best practice in star schema design, as filtering through a dimension table is more efficient and provides better user experience by allowing filters on attributes like 'ProductName' directly, while the underlying relationship with the numerical 'ProductID' handles the filtering of the fact table.
Why the other options are wrong
- A. Changing 'ProductID' to text would increase model size and potentially slow down processing, as integers are more efficiently stored and processed than text.
- B. Creating a calculated column combining 'ProductID' and 'ProductName' in the 'Sales' table would increase model size and redundancy. The 'ProductName' should come from the 'Products' dimension table.
- C. Establishing a bidirectional relationship is generally discouraged unless specifically required for complex scenarios, as it can lead to ambiguous filter paths and performance issues. A one-to-many relationship from 'Products' to 'Sales' is standard and efficient.
Star Schema Best Practices
Star schema is a data modeling approach that organizes data into fact tables (containing measures) and dimension tables (containing descriptive attributes). Best practices include hiding foreign keys in fact tables and using dimension tables for filtering and slicing.
- Central fact table, surrounding dimension tables.
- One-to-many relationships from dimensions to fact.
- Hide foreign keys in fact tables.
- Use dimension attributes for filtering and visuals.
- Optimizes query performance and user experience.
Memory trick: Hide the keys, use the dimensions, keep the star shining bright.