Virtual cubes

In IBM® Cognos® Dynamic Cubes, a virtual cube consists of two merged cubes. You can merge cubes by using the following combinations:

  • Merge two source cubes.
  • Merge two virtual cubes.
  • Merge one source cube with one virtual cube.

By combining two virtual cubes, or one source cube with a virtual cube, you can merge more than two cubes into a single virtual cube.

Some advantages of using virtual cubes include the following points:
  • Virtual cubes use less memory than physical cubes.
  • There is reduced cube refresh latency.
  • You can add volatile information to a lookup cube.
  • You can join cubes to present consolidated data and provide more sophisticated calculations.
  • Each source cube can be derived from a separate data source.

A virtual cube must contain the following objects:

  • A virtual measure dimension that contains one or more virtual measures.
  • At least one virtual dimension that contains one or more virtual hierarchies.

It can also contain virtual calculated measures, and virtual calculated members.

When you create a virtual cube, the following objects are added, if they exist in at least one source cube:
  • Dimensions
  • Hierarchies
  • Measures
  • Levels
  • Members

Virtual dimensions and hierarchies

Any dimensions and hierarchies with identical names in the source cubes are known as conformed dimensions and conformed hierarchies. These objects are added to the virtual cube as merged virtual dimensions and virtual hierarchies.

For example, two source cubes with a Time dimension are merged into a virtual dimension also named Time.

Any dimensions and hierarchies that do not have identical names, or that exist in only one of the source cubes, are known as non-conformed dimensions and non-conformed hierarchies. These objects are added to the virtual cube as new virtual dimensions and virtual hierarchies.

For example, if source cube 1 contains a Sales Q3 hierarchy, and source cube 2 contains a Sales Q4 hierarchy, the dimensions are not merged because the names do not match. Instead, two virtual hierarchies, Sales Q3 and Sales Q4, are added to the virtual cube.

If a virtual cube contains a non-conformed hierarchy, the virtual cube queries both source cubes to retrieve data only if one of the following conditions is met:

  • The non-conformed hierarchy is deleted from the virtual cube.
  • The virtual hierarchy includes an All member and the query includes this member.

    This can occur if the All member is referenced explicitly in the query or if the All member is the default member.

If neither of these conditions is met, the virtual cube queries only the source cube with the non-conformed hierarchy, and never the second source cube.

Virtual measures

Any measures with identical names in the source cubes are added to the virtual cube as merged virtual measures. Any measures that do not have identical names, or that exist in only one of the source cubes, are added to the virtual cube as new virtual measures.

Important: It is possible to merge measures only when the regular aggregate is one of the following: Sum, Maximum, Minimum, or Count. It is not possible to merge non-distributive measures or a distributive measure with an aggregation rule applied.

When merging measures from two source cubes, if there is a conflict between the data format of each measure, the data format of the merged virtual measure is set to * or unknown. For example, if a measure in source cube 1 has a US currency data format, and a measure in source cube 2 has a UK currency data format, the data format cannot be merged.

Virtual levels

Source cubes containing identical levels in a hierarchy (same number of levels and identical names), are merged as virtual levels. If levels in the source cubes are not identical, level names from the first source cube are used as the names of the virtual levels. If one source cube contains more hierarchy levels than the second source cube, the extra levels are added as the lowest levels of the virtual hierarchy.

For example, source cube 1 contains a Time hierarchy with Year, Quarter, and Month levels. Source cube 2 also has a Time hierarchy with Year, Month, Day, and Time levels. When they are merged, a Time virtual hierarchy is created with Year, Quarter, and Month, and Time virtual levels with the following members:

  • The Quarter virtual level contains Quarter members from source cube 1 and Month members from source cube 2.
  • The Month virtual level contains Month members from source cube 1 and Day members of source cube 2.
  • The Time virtual level contains Time members from source cube 2.

Virtual members

For a virtual hierarchy that is merged from two conformed dimensions, all hierarchy members from the source cubes are available as virtual members. If the level key for each source member is identical, members are added to the virtual cube as merged virtual members. Any members that do not have matching level keys are added to the virtual cube as new virtual members.

Tip: To browse virtual members, ensure that each source cube is deployed as data source to the content store and started.

Calculated measures and calculated members

Calculated measures and calculated members from source cubes are not added to a virtual cube. To use calculated measures or members from source cubes, you must manually define them in the virtual cube.

For more information, see Calculated members.

In-database aggregates

In-database aggregates are unavailable in a virtual cube because a virtual cube can retrieve data only from source cubes, not by querying a data source.

Support for multiple locales

If source cubes include support for multiple locales, a virtual cube also has multiple locale support.

A virtual cube automatically supports all locales defined in the source cubes. For example, in source cube 1, English and French are defined as supported locales. In source cube 2, English and Japanese are defined as supported locales. In the virtual cube, English, French and Japanese are included as supported locales.

A virtual cube also supports the use of multilingual names and captions for a virtual cube, virtual dimensions, virtual hierarchies, virtual levels, and virtual measures. However, with the exception of the All member caption, multilingual names and captions from source cubes are not automatically added to a virtual cube. To use multilingual names and captions from source cubes, you must manually define them in the virtual cube.

Manual merging of source objects

It is possible to manually merge objects in a virtual cube that could not be merged automatically. For example, source cube 1 contains a Time dimension and source cube 2 contains a Fiscal Time dimension. They are not merged, so two virtual dimensions Time and Fiscal Time are added to the virtual cube. If both dimensions contain the same structure and data, you could manually merge them into one virtual dimension named Time. You could then delete the redundant Fiscal Time virtual dimension.

You cannot reference a source object more than once in a virtual cube. For example, if the Time source hierarchy is used in the Time virtual hierarchy, it cannot also be used in the Fiscal Time virtual dimension.