Skip main navigation

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.

This article is from the free online

Microsoft Power BI: Advanced Data Analysis and Visualisation

Created by
FutureLearn - Learning For Life

Our purpose is to transform access to education.

We offer a diverse selection of courses from leading universities and cultural institutions from around the world. These are delivered one step at a time, and are accessible on mobile, tablet and desktop, so you can fit learning around your life.

We believe learning should be an enjoyable, social experience, so our courses offer the opportunity to discuss what you’re learning with others as you go, helping you make fresh discoveries and form new ideas.
You can unlock new opportunities with unlimited access to hundreds of online short courses for a year by subscribing to our Unlimited package. Build your knowledge with top universities and organisations.

Learn more about how FutureLearn is transforming access to education