Skip to main content

Defining Shared Dimensions and Compound Cubes

This page describes how to define shared dimensions and compound cubes for use in Business Intelligence.

You can define compound cubes in the Architect, but for shared dimensions, you must use an IDE.

For background information, see Summary of Model Options.

Also see Accessing the BI Samples.

Overview

This section provides an overview of shared dimensions and compound cubes.

Shared Dimensions

A shared dimension is a dimension that can be used in more than one cube. A shared dimension enables you to do the following:

  • Create a dashboard that includes pivot tables from both cubes.

  • On the dashboard, include a filter that uses the shared dimension.

    This filter affects pivot tables from both dimensions, if the tables are configured as the target of the filter.

  • Create a pivot table that uses both cubes (only if you also define a compound cube, as discussed in the next section).

Typically, dimensions based on location and time (see the note below) can be shared, even for unrelated cubes.

It might be possible to share other dimensions. For example, suppose that one cube represents transactions and another represents the customers who own the transactions. These two cubes might have common dimensions such as customer class, broker, and so on.

You can share dimensions in either of the following ways:

  • You can share a dimension formally. In this case, the dimension is defined in one cube and is referenced in the other (or others).

    In this case, you can also define a compound cube, which combines two or more cubes, which should have formally shared dimensions. This enables you to combine elements from different cubes in a single pivot table.

  • You can share a dimension informally. In this case, each cube has its own definition of the dimension, and the cubes are independent of each other.

    In this case, you cannot use the cubes together in a compound cube. As noted above, however, you can create a dashboard that includes pivot tables from both cubes and applies filters to both of them.

Compound Cubes

A compound cube is a subject area that combines multiple cubes (typically two). For these cubes, any dimensions that have the same name must be formally shared dimensions. This enables you to create pivot tables that contain elements from multiple cubes.

The following shows a pivot table created from a compound cube:

A pivot table with ZIP Codes in the rows and columns for Doctor Count, Patients/Week, Avg Allergy Count and Avg Test Score.

In this pivot table:

  • The Doctor Count measure and the Patients Per Week measure come from the Doctors cube.

    The Patients Per Week measure is the number of patients seen per week by the given set of doctors.

  • The Avg Patient Allergy Count measure and the Avg Patient Test Score measure come from the Patients cube.

    The CompoundCube subject area defines overrides so that these measures have different names than in that cube.

  • The ZIP level is in a shared dimension used by both of these cubes.

In a compound cube, the available dimensions are the dimensions from the first listed cube and all formally shared dimensions. The available measures include all the measures from all the cubes. The following rules apply:

  • For any measure that has the same name in all cubes used in the compound cubes, that measure becomes a common measure. For this measure, values are aggregated across all the cubes. For example, suppose that one cube is Employees and another cube is Patients. If both cubes have a Count measure, those counts are aggregated together.

    The system provides an option for renaming the Count measure so that you can prevent this from occurring when it is not appropriate.

  • For any measure that exists only in one cube, it is treated as usual.

  • For any level that is formally shared, you can use members of that level with any of the cubes to select subsets of their records. In the example shown previously, the 32006 member corresponds to all doctors that have this ZIP code and all patients that have this ZIP code.

    This fact means that the measures of all the cubes can potentially have different values for members of such a level. For example, the measure Patients Per Week (which is specific to doctors) and the measure Avg Patient Allergy Count (which is specific to patients) have different values for each ZIP code.

  • For any level that is not formally shared, a member of that level selects a subset of the records from the cube that owns it, but selects all records from the other cube.

    This fact means that measures from the cube that define this level can potentially have different values for members of such a level, but measures from the other cubes always have the same value. In the following example, the Doctor Type dimension is not shared:

    A pivot table with Doctor Type in the rows and columns for Doctor Count, Patients/Week, Patient Count and Patient Avg Age.

    Note that the Doctor Count and Avg Patients Per Week measures (both of which are specific to doctors) have different values for each doctor type.

    The other measures are specific to patients. They have the same value for each doctor type; this is the value aggregated across all patients.

Defining a Formally Shared Dimension

To share a dimension formally:

  1. Define the dimension as usual in one cube definition.

    When that cube is built, the system determines the initial members of all levels of that dimension, in the usual way. When the source class receives additional data and the cube is updated, the system adds additional members for any levels, in the usual way.

  2. Open the other cube definition in the Architect and add a shared dimension as follows:

    1. Select Add Element.

    2. Select Shared Dimension.

    3. In the first drop-down list, select the cube that defines the dimension.

    4. In the second drop-down list, select the dimension.

    5. Select OK.

    For all levels in this dimension, the fact table for this cube points to the dimension tables of the other cube.

  3. Optionally, in the second cube, override the source data definition for the levels of the shared dimension.

    By default, the shared dimension uses the same source properties or source expressions that are used in the Dependent Cube. To override these, edit the class in an IDE, find the applicable <dimension> element, and add child <hierarchy> and <level> elements as needed; see Reference Information for Cube Classes. In this case, the dimension name, hierarchy names, and level names must be the same as in the other cube.

The following restrictions apply:

  • The cube that owns the dimension must be built first. This process creates the tables for the dimensions defined in that cube. When you build the other cube, as the system processes records, it adds records to the dimension tables of any shared dimensions.

  • Unless you override the source data definitions in the second cube, the same level definitions must be appropriate for both cubes. That is, the identical source property or source expression must be applicable in both cubes. For example, if the cube that owns the definition uses the source expression %source.Item.Category, that source expression must also be appropriate for the other cube.

  • For any cubes that share that dimension: the source values (member keys) must be the same for the levels in the shared dimension.

    For example, consider two cubes, each based on a table that includes a city name. For these cubes to share a level that is based on city name, the city names must be identical, including case, in both of the source tables. (Otherwise, you will end up with multiple, similar members such as Jonesville and JONESVILLE.)

    There is no requirement for both source tables to have the same set of values, however. For example, one source table could list a city that is not in the other one. The dimension tables contain the entire superset of values.

Also, for any filters that use these levels, the list of members includes all the members, from all cubes that share the dimension. So, for example, in a given dashboard, a user might see an unfamiliar city name in a filter drop-down, a city name that does not appear in the data used on that dashboard. The user can select it, but no matching data will be found.

The HoleFoodsBudget and CompoundCube/Doctors cubes both contain examples of shared dimensions. These examples are not related to each other.

Defining an Informally Shared Dimension

To define an informally shared dimension, ensure that the logical dimension name, its hierarchy names, its level names, and its member keys are the same in all relevant cubes. (The underlying details of the source expressions, transformation options, and so on do not matter. All that matters is that the logical names match and the member keys match.)

When you do this, you can define pivot tables in each of these cubes and then place those pivot tables on the same dashboard. If you include a filter widget that uses one of the shared dimensions, it can affect all the pivot tables.

Examples

The Patients cube (in the class BI.Model.PatientsCube) includes the HomeD dimension. This dimension includes an H1 hierarchy, which includes the ZIP and City levels.

The CityRainfall cube (in the class BI.Model.RainfallCube) also contains the HomeD dimension, which differs from the one in the Patients cube only as follows:

  • The HomeD dimension has the display name CityD (rather than being the same as the internal name).

  • The HomeD dimension has an All member.

  • The City level uses the City.Name source property (rather than HomeCity.Name).

  • The ZIP level uses the City.PostalCode source property (rather than HomeCity.PostalCode).

These definitions mean that you can use these cubes in different pivot tables on the same dashboard, and have them respond in the same way to any filters that use the HomeD dimension. The dashboard Dashboards/Demo Two Subject Areas Together demonstrates this. It has a pivot table that uses the Patients cube and another pivot table that uses the CityRainfall cube. The dashboard includes filter controls that affect both pivot tables.

Similarly, the Cities cube (in the class BI.Model.CityCube) contains a dimension named HomeD, which includes an H1 hierarchy, which includes the ZIP and City levels. The display name for HomeD is CityD, so that the dimension appears to have a different name in this cube. As before, the source properties used by the levels are different in the Cities cube than in the Patients cube.

Defining Compound Cubes

To create compound cubes, you must use an IDE. To create a compound cube, do all the following:

  • Create a subject area with the Base cube option equal to a comma-separated list of cubes. For example, for the sample subject area CompoundCube/CompoundCube, Base cube is as follows:

    CompoundCube/Patients,CompoundCube/Doctors,CompoundCube/CityRainfall

    Also edit the Depends On option in the Details Area on the right. For the value, specify the full package and class name of all the cube classes.

    Any subject area class should always be compiled after the cube class or classes on which it is based. The Depends On setting helps control this.

  • In the cubes that the compound cube uses, optionally redefine the Count measure. To do so, specify the countMeasureName and (optionally) countMeasureCaption attributes in the definitions of the cubes. For example:

    <cube xmlns="http://www.intersystems.com/deepsee" 
name="Doctors" 
displayName="Doctors" 
sourceClass="BI.Study.Doctor" 
countMeasureName="DoctorCount"  
countMeasureCaption="Doctor Count">
...

    This change does not require rebuilding these cubes.

  • In the compound cube, optionally change the display names of measures names to be more specific, for use in the compound cube. For example:

    <subjectArea xmlns="http://www.intersystems.com/deepsee/subjectarea" 
name="CompoundCube" displayName="CompoundCube" 
baseCube="Doctors,Patients">

<measure name="Allergy Count" displayName="Patient Allergy Count"/>
<measure name="Avg Allergy Count" displayName="Patient Avg Allergy Count"/>
<measure name="Age" displayName="Patient Age"/>
<measure name="Avg Age" displayName="Patient Avg Age"/>
<measure name="Test Score" displayName="Patient Test Score"/>
<measure name="Avg Test Score" displayName="Patient Avg Test Score"/>
<measure name="Encounter Count" displayName="Patient Encounter Count"/>
<measure name="Avg Enc Count" displayName="Patient Avg Enc Count"/>

</subjectArea>

Recompile any cube definitions that you change. Recompile the compound cube last.

In a compound cube, the available dimensions are the dimensions from the first listed cube and all formally shared dimensions. The available measures include all the measures from all the cubes.

Note:

Any dimensions that have the same name in both cubes must be formally shared. Any measures that have the same name in both cubes are aggregated together.

Detail Listings for Compound Cubes

To define detail listings for a compound cube, define identical detail listings in all the participating cubes. The system generates an SQL UNION of these listings.

Note that the listings must be directly based on SQL; detail listings via data connectors will not work for compound cubes.

Example Compound Cube

To see an example of a compound cube, see the class BI.Model.CompoundCube.CompoundCube. This class is defined as follows:

Class BI.Model.CompoundCube.CompoundCube Extends %DeepSee.SubjectArea 
[ DependsOn = (BI.Model.CompoundCube.Patients, BI.Model.CompoundCube.Doctors, 
BI.Model.CompoundCube.CityRainfall) ]
{

/// This XData definition defines the SubjectArea.
XData SubjectArea [ XMLNamespace = "http://www.intersystems.com/deepsee/subjectarea" ]
{
<subjectArea name="CompoundCube/CompoundCube"  displayName="CompoundCube/CompoundCube"
    baseCube="CompoundCube/Patients,CompoundCube/Doctors,CompoundCube/CityRainfall"  >
</subjectArea>
}

}

The cube CompoundCube/Patients, which is defined in BI.Model.CompoundCube.Patients defines all the dimensions.

The other cubes (CompoundCube/Doctors and CompoundCube/CityRainfall) define dimensions that are shared from the CompoundCube/Patients. Notice that not all the dimensions are defined in all the cubes. The following table shows the dimensions available in each cube:

Dimension CompoundCube/Patients cube CompoundCube/Doctors cube CompoundCube/CityRainfall cube
BirthD checkmark   checkmark
DocD checkmark checkmark  
DocTypeD checkmark checkmark  
HomeD checkmark checkmark checkmark

The HomeD dimension is defined in all three cubes, so this dimension affects the measures of all three cubes. For example, the dashboard Demo Compound Cube includes this pivot table:

A pivot table with ZIP Code/City pairs in the rows and columns for Patient Count, Doctor Count, and Avg Monthly Rainfall.

The Patient Count measure is defined in CompoundCube/Patients, Doctor Count measure is defined in CompoundCube/Doctors, and Avg Monthly Rainfall Inches measure is defined in CompoundCube/CityRainfall. Notice that the values are different for each measure for each city.

The same dashboard also includes a pivot table that use BirthD for rows:

A pivot table with birth decades in the rows and columns for Patient Count, Doctor Count, and Avg Monthly Rainfall.

Because CompoundCube/Doctors does not define the BirthD dimension, the measure Doctor Count cannot be broken out by birth decade. Notice that the Doctor Count column shows the same number in all cells; this is the total doctor count across birth decades for all patients.

Finally the Demo Compound Cube dashboard also includes a pivot table that use DoctTypeD for rows:

A pivot table with Doctor Type in the rows and columns for Patient Count, Doctor Count, and Avg Monthly Rainfall.

Because CompoundCube/CityRainfall does not define the DocTypeD dimension, the measure Avg Monthly Rainfall Inches cannot be broken out by doctor type. This measure is aggregated across all patients (by averaging, as defined in the measure).

Defining Cube-Cube RelationshipsNext section
Previous sectionDefining Computed Dimensions
FeedbackOpens in a new tab