Virtual Dimensions

Virtual Dimensions let you use an attribute in the same way you would use a dimension. You can use attributes to pivot data, consolidate elements, and write back to the database. All updates and structural changes to the Virtual Dimension are managed automatically, based on changes of the attribute values in the original dimension. Access rights and visibility behaviors that exist in the source dimensions/cubes are preserved in Virtual Dimensions.

For example, in a dimension "Products", you could flag its attributes (e.g. color, size, vendor, etc.) as Virtual Dimensions. You could then create a Jedox View with the Products dimension in rows and a Virtual Dimension, such as "color" (which is actually an attribute of Products) in columns. The In-Memory DB would then treat this attribute as a dimension for returning data. All base elements from the original dimension that share the same attribute value are consolidated as child elements below a parent element, matching this attribute value. In the example, all products which have the values "red" for the attribute "color" would become child elements of an element called "red" in the virtual dimension.

Several possibly use scenarios include:

  • Pivoting data by an attribute of a dimension
  • Consolidating by an attribute
  • Writing back via an attribute

All updates and structural changes to the Virtual Dimension are managed by the Jedox automatically, based on changes of the attribute values in the original dimension.

Considerations when using Virtual Dimensions

  • Virtual Dimensions are system-generated and cannot be edited manually. This means you cannot create a customer hierarchy in a Virtual Dimension. If it is required to do so, create a normal dimension for this functionality.

  • Virtual Dimensions are only built from attributes of type 'string'. Numeric attributes cannot be used for Virtual Dimensions.

  • Virtual Dimensions can only be built upon the base level elements. Consolidated element attributes cannot be used in a Virtual Dimension.

  • Virtual Dimensions are also supported in DynaRanges.

  • For optimization and performance improvement, Virtual Dimensions use multicore aggregation in virtual cubes.

  • If an attribute value is empty for a specific base level element, it is grouped under "No [attribute name]".

  • Currently combining Virtual Dimension data and semiadditive measures (Average, First, and Last) is not supported. When viewing data on an element in a Virtual Dimension, it will always aggregate the sum of the children, even if the cube cell coordinates also include an aggregated element from the regular dimension which is set to calculate using semiadditive.

  • Virtual Dimensions cannot be used in a rule explicitly, that is, they cannot be part of the rule definition. However, when rules are defined in a cube where Virtual Dimensions are active, they behave as every dimension would do, if it is not explicitly defined in a rule.

  • Rule-calculated attributes are not resolved when used in a Virtual Dimension. Only physically stored attribute values are used.

  • Aggregation functions (Sum, Minimum, Maximum, or Average) for multiple selected elements in the Paste View Dialog are currently not supported for Virtual Dimensions.

Naming conventions for Virtual Dimensions

When using an attribute as a Virtual Dimension, all attribute values should follow the same naming restrictions as dimensions and elements.

Any invalid attribute value, e.g., system reserved names like "Total <attribute>" and "No <attribute>", or attribute value with leading and trailing spaces, becomes a part of the "No <attribute>" node in a Virtual Dimension.

Note that the name of a Virtual Dimension cannot be the same as the name of a base element in the dimension itself. Enabling the Virtual Dimension means (virtual) elements are created from the values for that attribute. If some attribute value is exactly equal to the element name of a base-level element in the actual dimension, you would have two elements with the same name, and this is not possible.

"Invalid <attribute>" in Virtual Dimensions

Attribute values that go against general element naming rules (for example, starting or ending with a space character) or are matching reserved element names ("Total <attribute>", "No <attribute>") are categorized under the "Invalid <attribute>" node in the Virtual Dimensions. This helps you find the affected elements more quickly and take the required actions, e.g., changing the attribute value.

Tagging an attribute for Virtual Dimension

To build a Virtual Dimension, first, you need to tag an attribute:

  1. Navigate to the Modeler and choose the database for which you want to make a Virtual Dimension.

  2. Choose a Cube (e.g., Sales), and select a Dimension (e.g., Products).

  3. Click on Products dimension properties. In the Attributes panel, under the Virtual column, you can choose attributes to build Virtual Dimensions.

  4. To disable a Virtual Dimension, uncheck the attribute in the Virtual column in the dimension properties.

Notes:

  • If you have explicitly referred to a Virtual Dimension and element in a view or a report, and you uncheck that Virtual Dimension, you may have to adjust your formulas to exclude that Virtual Dimension. If no Virtual Dimension is explicitly referred to, and you have added or removed Virtual Dimensions, the report will work normally.

  • Any Virtual Dimension that is enabled will appear in every cube that the source dimension is used in.

Virtual Dimension in a View

To use the Virtual Dimensions you selected in your reports, navigate to the Designer, and choose either a New View or Edit View for existing reports. The Virtual Dimensions can be seen under the Source panel. Simply drag and drop them into your View.

There are three ways to distinguish a Virtual Dimension from a normal dimension:

  • Virtual Dimension's icon is different from a normal dimension.

  • The name of a Virtual Dimension is made up of the concatenation of the original dimension and the attribute name. For example, if the Virtual Dimension represents the Color attribute on the Products dimension, the Virtual Dimension would appear as "Products - Color".

  • When you hover over a Virtual Dimension, the technical name is shown.

After pasting the View, the technical name of a Virtual Dimension is shown above in a spreadsheet.

By choosing a specific element within the Products dimension, you can keep filtering your report for more detailed information.

Virtual Dimensions are also supported in detached Views.

Change in a Dimension

Virtual Dimensions are dynamically managed by the Jedox database. When you change an element attribute in the original dimension, the change is automatically synced in the Virtual Dimension. To see the change in your existing Views, repaste the View, or if you already have the dimension on the row or column axis, simply collapse and expand the "All <attribute>" hierarchy again. The new aggregations will automatically appear.

Virtual Dimensions in Subset Editor

It is also possible to use Virtual Dimensions in the Subset Editor. Once you open the Subset Editor, you can choose from the list of Virtual Dimensions you have previously defined in the Modeler.

Subsets based on Virtual Dimensions with DFilter activated are also supported. However, by default, usage of Virtual Dimensions in the new Data Filters is disabled, meaning Virtual Dimensions are not included in the DFilter selections. Use the "Enable virtual dimension" option to activate the Virtual Dimensions in the Subset Editor’s Data Filter.

Please note that stored Subsets (both private and global) are not supported on Virtual Dimensions.

Virtual Dimensions in Paste elements

You can also use Virtual Dimensions in the Paste elements. Once you open the Paste elements, you can choose from the list of Virtual Dimensions you have previously defined in the Modeler.

You can distinguish Virtual Dimensions with their names. A Virtual Dimension has the name of the original dimension in front of it. Also hovering your mouse over Virtual Dimension results in a tooltip that shows the technical name of the Virtual Dimension.

Notes:

  • Even though Hold and Undo functionality are still supported on regular slices from the same cube, they are not supported on the "Virtual" cube slices.

  • Stored Subsets and Attributes on Virtual Dimensions are not currently supported.

Virtual Dimensions in Integrator

It is possible to use Virtual Dimensions in your Integrator projects too, and you can extract and filter data from cubes where Virtual Dimensions are used.

You can also create Virtual Dimensions in the Integrator. To do this, open your project, go to Loads > Dimensions, and choose a dimension. In the Virtual Dimension section, you can add, delete, activate, or deactivate Virtual Dimensions.

Note that you can only turn string attributes into Virtual Dimensions, and it is not possible to turn numeric attributes into Virtual Dimensions.

Cube/CubeSlice Extracts

When a virtual dimension is used in a filter or cube output in a Cube/CubeSlice Extract, the “Read rule-based values” option is automatically disabled to prevent producing wrong results.

Virtual Dimensions in Audit

It is possible to track the information about Virtual Dimensions in the Details column of the Audit. The table below shows the different messages you can see in the Details column in different scenarios.

mode=1 {"vd"[["12","color","Desktop Pro"]]}

Audit trail on areas that included Virtual Dimension elements
mode= 1 {"vd":[]} Audit trail on areas where Virtual Dimension was left on source
mode=0 or mode=1 Audit trail on areas where VD was not active

For more information about Audit, read the Audit Information: Tracking Cell Value Changes article.

Updated September 27, 2024