Loading...

Dimensions

Dimensions lets you define hierarchical data structures which can be used in filters and reporting, both within Profitbase InVision, and in 3rd party tools such as Power BI.
Dimension can have any number of levels, and each level has an id, name, and optional translations for different languages.
Dimension can also have properties, which is a way to define “features” of dimension members (for example, weight, color, length, etc.).

Dimensions are always stored as wide tables in the database, but you can edit them as parent-child hierarchies in the dimension editor.

Each level in the dimension is represented by at least two columns in the wide table – an Id and a Name column. The Id column contains the id of the dimension member, and the Name column contains the text displayed to the user. If you enable language support, there will be a Name column for each language.


Dimension


Key Field specifies the name of leaf level column, for example ProductID or AccountID.

The example below shows the columns in a table for a two-level dimension (L1 and ProductID), having “ProductID” as Key Id, translations for English and Norwegian (EN and NO), and a Weight property. The table includes a SortIndex column that specifies how siblings are sorted (not shown in the example below).


Dimension


How to create a dimension

  1. Start the Designer and open a Solution.
  2. In the Solution Explorer, right-click a Folder and choose “Add Item”, then choose “Dimension” from the list.
  3. Provide a name for the Key Field. The Key Field specifies the name of the column at the leaf (lowest) level of the wide table.

Enable support for multiple languages

If you want to enable the dimension to support different languages for displaying members, select one or more languages in the Languages dropdown. The language-specific names for each dimension member are managed in the Dimension Editor.


Dimension


Primary Dimension

Specifying a dimension as a Primary Dimension means that it appears in the list of "primary dimensions" in the Dimension Editor without users first having to select a specific Solution.

That gives Power users a central repository of dimensions they can edit and publish to Work Process Versions or other solutions.

Think of primary dimensions as a central repository of dimensions that can be modified and published into production.


Dimension


Properties

Dimension members can have zero or more properties. For example, products in a Product dimension can have properties such as weight, height, color, etc. Each property is materialized as a column in the dimension table.

Property Id

This is the Id of the property field and the name of the column in the dimension table in the database.

Name

Name specifies the label of the property in the Dimension Editor user interface. You can also provide specific translations for the label using this field.

Data type

Specifies the data type of the property.

Is Lookup

Use this feature if you want users to select from a set of predefined values when setting the value of the property.


Level names

By default, column names for all levels except the leaf level are auto-generated using the format L[level number], for example, L1, L2. If you want to override the naming for each level column, you can specify this in the Level Names list.


Options / Empty member

If you need the dimension to always contain an empty member, enable the Include Empty Member option.

This option is typically used when you are using the dimension as a source for a dropdown in a spreadsheet, and users must be able to select a “null” or “not applicable” value.


Build the dimension and edit dimension data

To build the dimension and edit data, you need to use the Dimension Editor in a Workbook.


Dimension


  1. In the Designer, check the “Primary Dimension” in the toolbar. That will make the dimension appear in the Primary dimensions dropdown in the Workbook Dimension Editor.
  2. Create a new Workbook.
  3. In the Toolbox, choose the Resources tab and drag/drop the Dimension Editor onto a page.
  4. Open the Workbook and choose a dimension from the list of available ones.

Adding members to a dimension using the tree editor

  • To add a new root dimension member, click the “+” button at the top of the dimension tree
  • To add a new member as a child of another member, choose “Add” from the action menu of an existing item (hover the item to make the menu trigger appear).

Adding members to a dimension by importing from clipboard/Excel

To import multiple members at the same time, click the Import button at the top of the dimension tree. This will display a table editor that lets you copy/paste data from Excel (or any other tab-delimited file).

Note that you need to use Ctrl-V to paste into the table. You cannot right-click a cell in the table editor.


Dimension


Organizing dimension members

To organize dimension members in a hierarchical structure, you can do the following:

  • Drag/drop nodes between levels.
  • Use the action menu which appears when you hover a node to add, delete, cut, paste, and clone members.
  • To move multiple items, hold down the Ctrl key while selecting multiple members. Then use the “Cut” and “Paste” options in the action menu to move the items as children to a different member.
  • To sort children of an item, open the action menu and choose one of the sorting options available. The sorting options are only available on items in the tree which have children.

Dimension


Edit data in table mode

You can edit properties and dimension member names (including translations) using Table Edit mode. That enables users to quickly edit data for multiple dimension members using data grid functionality like drag-copy of cells and copy/paste. While in Table Edit mode, you cannot edit the tree in any way, and you cannot switch back to the Properties tab until you have saved any changes.

Note When you are in Table Edit mode, you must remember to save your changes before choosing a different dimension member from the tree, or else all your changes are lost.


Preview dimension in different languages

If you want to preview how the dimension looks in different languages, use the “Globe” icon at the top of the dimension tree to switch between different languages you have enabled for the dimension.

Dimension


Publishing a dimension

The changes you make in the Dimension Editor are not fully applied until you have published the changes. That means that you can edit the data and hierarchy, save it, and continue working until you are satisfied with the result. Before applying those changes to the actual data model used by Data Stores, Filters, Tables, etc.

When you publish a dimension, it will always be published to the Solution which owns it. Additionally, you can optionally choose to publish the dimension to other Solutions and Work Process Versions as well. When you choose Solutions and/or Work Process Versions to publish to, the dimension will be published to dimensions in the target Solutions and Work Process Versions having the same name as the dimension being published. When publishing to target


Dimension


Automating import of data to dimensions

You can import data to dimensions through Dataflows and staging tables.

  1. Create a table containing the columns you want to import into the dimension. The columns in the staging table must match the dimension table columns by name and data type. Note that the staging table does not need to contain all the columns in the dimension. It only needs to contain the columns that you want to import. To view which columns the dimension table contains, press “Browse P/C table” in the dimension editor in the Designer.
  2. Create a Dataflow Item and add the Import dimension members task from the Dimension Tasks in the toolbox. Specify the name of the staging table and the name of the dimension. You can also specify whether to overwrite changes made by the user and whether blank cells should be ignored during the import.

Dimension


  1. Create a Dataflow and run the Dataflow Item from step 2.

Automating publishing of dimensions

You can pull in a dimension definition (including data) from a different solution. You can do that by using dataflows. This means that you can automatically copy the data and configuration of a Primary Dimension into another Solution (usually a versioned Solution).

  1. Create a Dataflow and Dataflow Item in the target solution. The primary dimension is imported into the target.
  2. Add the Import Primary Dimension task from the Dimension Tasks in the toolbox.

Dimension


  1. Specify the name of the primary dimension and optionally the name of the target dimension. If no target dimension is specified, it is assumed to equal the name of the primary dimension.
  2. Run the Dataflow Item in a Dataflow.

See Also


Videos