Key concepts and terms

  • 10 minutes

Key concepts in Business performance planning are:

  • Cube – A collection of dimension and fact data for modeling and analytical purposes.

  • Dimensions – Descriptors that define the facts. Typically, they’re what you want to use to slice and view your fact data. Common dimensions are people, product, place, and time. A dimension consists of one or more columns. For example, a time dimension might contain the date, month, year, and other aggregation details or attributes. Then, you can use these columns in the analysis of transactions to create a hierarchical structure that provides a drill-down path from the year to the month to the date.

  • Facts – Numeric values that you can aggregate and analyze. (Aggregation and analysis are fundamental reasons for defining a cube.) Examples of fact data include sales invoices, production costs, or salaries and wages.

Cube example

Consider the dimensions that you want to create and include when you assemble a cube. The dimensions provide the mechanism for filtering your data in Power BI. When you create the cube, select all dimensions that you want to include in it. However, keep in mind that you can filter data by those dimensions in Power BI only if the fact data has a relationship with them.

For example, Contoso Company has the following sales data.

Sales amount Product Order date Customer Sales territory
1000 Bike 3/1/2022 Oregon Trails West
1000 Bike 3/1/2022 Southern Rides West
1000 Bike 3/1/2022 Longhorn Sales West
10 Water bottle 5/1/2022 Southern Rides South
10 Water bottle 5/1/2022 Route 66 Bikes South
1000 Bike 5/2/2022 Oregon Trails West
10 Water bottle 6/1/2022 Desert Oasis South
10 Water bottle 6/1/2022 Desert Oasis South
1000 Bike 6/15/2022 Blue Ox Trails North
50 Helmet 6/15/2022 Blue Ox Trails North
1000 Bike 7/1/2022 Dakota Bikes North
1000 Bike 7/1/2022 Oregon Trails West
1000 Bike 7/1/2022 Joe's Bikes West
50 Helmet 9/1/2022 Southern Rides South
50 Helmet 9/15/2022 Palm Street Sales South
1000 Bike 9/15/2022 Southwest Campers South

A sales director at Contoso might want answers to the following basic questions:

  • Who is my best customer?
  • Which sales territory has the most sales?

The sales director might also want to do a deeper analysis to understand the trends and relationships between the data. For example, they might want answers to the following questions:

  • What are the top-selling products in each territory?
  • Do any products have a seasonal sales pattern?

By creating a cube that contains Product, Time, Customer, Territory, and Actual sales as dimensions, the sales director can filter and group data based on how they want to view it. For example, they might want to group sales by territory so that they can identify the top-selling products in each territory. This grouping can give them insight into when the peak season for sales is.

The following table lists the data that the cube shows in this example.

Territory Sales amount Product Order date Customer
South
10 Water bottle 05/01/2022 Southern Rides
10 Water bottle 5/01/2022 Route 66 Bikes
10 Water bottle 6/01/2022 Desert Oasis
10 Water bottle 6/01/2022 Desert Oasis
50 Helmet 9/01/22 Southern Rides
50 Helmet 9/15/2022 Palm Street Sales
1000 Bike 9/15/2022 Southwest Campers
North
50 Helmet 6/15/2022 Blue Ox Trails
1000 Bike 6/15/2022 Blue Ox Trails
1000 Bike 7/1/2022 Dakota Bikes
West
1000 Bike 3/1/2022 Oregon Trails
1000 Bike 3/1/2022 Joe's Bikes
1000 Bike 3/1/2022 Longhorn Sales
1000 Bike 5/2/2022 Oregon Trails
1000 Bike 7/1/2022 Oregon Trails
1000 Bike 7/1/2022 Joe's Bikes

The sales director can use the dimensions that they created during planning to slice their data by territory, product, and date in Power BI. In this way, the sales director can understand trends and prepare a plan that considers trends or outliers.

To identify patterns and filter data, a dimension must map to the sales data in the first table in this article. Therefore, the sales director would define the following dimensions for the cube:

  • Territory
  • Product
  • Time
  • Customer

The sales data (fact data) must contain the details for Territory, Product, Time, and Customer.

As part of the planning process, the sales director uses the sales fact data to create a plan for what they think might happen in the upcoming year. During this time, the sales director can copy the sales data into a new scenario that's named Sales plan. Then, the organization has a starting point for creating a sales plan for the upcoming year. For example, by reviewing their actuals, the organization can plan for a spike in sales during the summer and a decline in sales during the winter. By taking advantage of the ability to filter and group the data by dimension, they can build a plan based on the insights that they gain from the data.