Business performance planning cubes

  • 10 minutes

A cube is a collection of dimension and fact data. It's made up of fact data that you can use for modeling and analytical purposes. The dimensions in a cube are descriptors that define the facts. Typically, they’re how you want to slice and view 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. You can use these columns to analyze the transactions. Additionally, you can use them to create a hierarchical structure that lets you drill down from year to month to date.

The fact data in a cube is made up of numeric values that you can aggregate and analyze. Facts are the fundamental reason for defining a cube. Examples of fact data include sales invoices, production costs, or salaries and wages.

Create and use cubes

To use a cube in Microsoft Power BI for planning purposes, follow these steps.

  1. Assemble the cube.

    1. Give the cube a name.
    2. Select the dimensions for the cube.

  2. Load fact data into the cube.

    1. Select the ___location of the fact data.
    2. Map the selected dimensions to columns that contain fact data.

  3. Schedule a refresh (available if you're using dataflows to load cube data).

Create a cube

To create a cube, follow these steps.

  1. On the navigation pane, select Cube > New.

  2. In the Create cube wizard, name the cube. You can use numbers and special characters for the cube name. Note the cube name because you use it to load your data into Power BI later.

    If you're using dataflows, the system uses the cube name to load fact data, which shows as msdyn_xpna_CUBENAME.

Select dimensions

When you create a cube, consider which dimensions you should include. You need to select a minimum of two dimensions to create a cube. Then, the dimensions that you select are available in Power BI, and you can use them to filter your data.

You can select as many dimensions as you want to include in the cube. However, for the data to be filtered by the dimensions in Power BI, the fact data must have a relationship with the dimension.

Important

After you define the dimensions and finalize the cube structure, you can't add or remove dimensions. If you want to use different dimensions, you need to create a new cube and load the fact data into it.

Load fact data

To load fact data, go to the Cube list and select Load fact data.

Two ways to load fact data in the Business performance planning application are:

  • Load data from an Excel workbook.
  • Create a dataflow and link it to an existing cube.

If you're loading production data, we recommend that you use dataflows. Dataflows provide better support for typical production volume and complexity. They also provide a transformation experience, detailed status results when data is loaded, and the option to schedule refreshes of the data. For more information, see Load data into Business performance planning using dataflows.

Load fact data from Excel

To load fact data from Excel, on the Cube details page, select Load fact data.

Note

The amount of data that you can load from Excel at one time is limited. It’s best that you use Excel with demo data or sample data for testing purposes.

Select a data source

To load data from Excel, go to and select the workbook that contains the fact data.

Column mapping

After you select the data source, select the Amount column for your fact data. The values in the Amount column must be numeric. They can't contain non-numeric values. Typically, fact data is general ledger (GL) account balances, product prices, sales amounts, or employee wages.

The Amount column includes a list of the dimensions that you selected when creating the cube. Each dimension has a Map type value of Link to or Fixed value.

  • When you select Link to, you must map the dimension to its corresponding data column in the fact data.
  • When you select Fixed value, you must enter a value in the Table column field. The system uses this value as the dimension value for all records in the cube.

For example, if you select Department as a dimension, and you set the Map type field to Link to, the fact data must have a Department column. If you set the Map type field to Fixed value and set the value to Human Resources, the system sets the Department value to Human Resources for all rows in the cube. In this case, Human Resources is the only available department where you filter and group in Power BI.

Note

All dimension values in a column of fact data must exist in the corresponding dimension. For example, the fact data for the Contoso Company contains the Human Resources, Finance, IT, and Marketing departments. The dimension values in the Department dimension contain Human Resources, Finance, IT, and Sales/Marketing. Because the dimension value Marketing in the fact data and the Sales/Marketing dimension value don't match, you receive an error when you try to create the cube.

You must map all dimensions that you select when creating the cube to a column in the fact data before you can load the data into the cube.

After you map all values, select Next. Note the cube name because you need it later when you connect to Power BI to load the data for the visuals.