Microsoft Certified: Power BI Data Analyst AssociateModel the dataEasy
A data modeler is designing a Power BI model for a retail company to analyze sales performance. The model includes a 'Sales' fact table and a 'Products' dimension table. The 'Products' table has a column called 'ProductCategory' and another called 'ProductSubCategory'. The modeler wants to create a hierarchy that allows users to drill down from category to subcategory. Which of the following is the most efficient way to implement this hierarchy in Power BI?
- AEstablish a one-to-many relationship between 'ProductCategory' and 'ProductSubCategory' tables.
- BDrag 'ProductCategory' and then 'ProductSubCategory' into the same hierarchy in the 'Products' table.
- CCreate a calculated column in the 'Sales' table concatenating 'ProductCategory' and 'ProductSubCategory'.
- DCreate two separate hierarchies, one for 'ProductCategory' and one for 'ProductSubCategory'.
Show answer & explanationAnswer & explanation
Correct answer: B. Drag 'ProductCategory' and then 'ProductSubCategory' into the same hierarchy in the 'Products' table.
Creating a hierarchy directly within the 'Products' dimension table by dragging the relevant columns is the standard and most efficient approach in Power BI. This leverages the existing data structure and Power BI's built-in hierarchy functionality for drill-down capabilities.
Why the other options are wrong
- A. Establishing a relationship between columns within the same dimension table is not how hierarchies are built; it would require separate tables or be redundant.
- C. Concatenating columns in the fact table is inefficient and doesn't create a drill-down hierarchy.
- D. Creating separate hierarchies prevents the drill-down functionality between category and subcategory.
Dimension Hierarchy
A structured arrangement of columns within a dimension table that represents different levels of granularity, allowing users to navigate data from general to specific.
- Enables drill-down and drill-up functionality in reports.
- Typically built within dimension tables.
- Improves user experience and data exploration.
Memory trick: Hierarchies branch out, making data digestible.