Work With Dimensions

Work With Dimensions

When building a star schema, you will have dimension and fact tables. Fact tables contain information about events such as sales orders, shipping dates, resellers, and suppliers. Dimension tables store details about business entities, such as products or time, and are connected back to fact tables through a relationship.

You can use hierarchies as one source to help you find detail in dimension tables. Hierarchies are useful because they allow you to drill down into the specifics of your data instead of only seeing the data at a high level.

Hierarchies
When you are building visuals, Power BI automatically enters values of the date type as a hierarchy (if the table has not been marked as a date table).

In the preceding Date column, the date is shown in increasingly finer detail through year, quarters, months, and days. You can also manually create hierarchies.

For example, consider a situation where you want to create a stacked bar chart of Total Sales by Category and Subcategory. You can accomplish this task by creating a hierarchy in the Product table for categories and subcategories. To create a hierarchy, go to the Fields pane on Power BI and then right-click the column that you want the hierarchy for. Select New hierarchy, as shown in the following figure.

Next, drag and drop the subcategory column into this new hierarchy that you’ve created. This column will be added as a sublevel on the hierarchy.

Now, you can build the visual by selecting a stacked bar chart in the Visualisations pane. Add your Category Name Hierarchy in the Axis field and Total Sales in the Values field.

Parent-Child Hierarchy
In the following example, you have an Employee table within the database that tells you important information about the employees, their managers, and their IDs. When looking at this table, you notice that Roy F has been repeated in the Manager column. As the image shows, multiple employees can have the same manager, which indicates a hierarchy between managers and employees.

The Manager column determines the hierarchy and is therefore the parent, while the “children” are the employees. For this example, you want to be able to see all levels of this hierarchy.