Advanced Modeling for InterSystems Business Intelligence Defining Shared Dimensions and Compound Cubes

This chapter describes how to define shared dimensions and compound cubes. It discusses the following topics:
You can define compound cubes in the Architect, but for shared dimensions, you must use Studio.
For background information, see the chapter Summary of Model Options in Defining Models for InterSystems Business Intelligence.
Also see Accessing the Samples Shown in This Book,” in the preface.
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.
Note:
Date dimensions are automatically informally shared; that is, a date dimension in one cube automatically can affect other cubes that define a date dimension that has the same name. No work is necessary in order to share date dimensions.
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:
In this pivot table:
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:
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:
2. In the first drop-down list, select the cube that defines the dimension.
3. In the second drop-down list, select the dimension.
4. 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 Studio, find the applicable <dimension> element, and add child <hierarchy> and <level> elements as needed; see Reference Information for Cube Classes in Defining Models for InterSystems Business Intelligence. 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 DeepSee.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 DeepSee.Model.RainfallCube) also contains the HomeD dimension, which differs from the one in the Patients cube only as follows:
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 DeepSee.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 Studio. 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 subject area CompoundCube/CompoundCube in SAMPLES, 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="DeepSee.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 DeepSee.Model.CompoundCube.CompoundCube in the SAMPLES namespace. This class is defined as follows:
```Class DeepSee.Model.CompoundCube.CompoundCube Extends %DeepSee.SubjectArea
[ DependsOn = (DeepSee.Model.CompoundCube.Patients, DeepSee.Model.CompoundCube.Doctors,
DeepSee.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 DeepSee.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
DocD
DocTypeD
HomeD
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:
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:
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:
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).