Previous section   Next section

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 DeepSee Models.

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:
images/d2modadv_compound_cube_pivot_example.png
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.
    DeepSee 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:
    images/d2modadv_compound_cube_pivot_example_with_nonshared_dim.png
    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, DeepSee 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, DeepSee 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 Studio, find the applicable
    <dimension>
    element, and add child
    <hierarchy>
    and
    <level>
    elements as needed; see “Reference Information for Cube Classes” in Defining DeepSee Models. 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 DeepSee 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:
  • 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 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
images/d2modadv_checkmark.png   images/d2modadv_checkmark.png
DocD
images/d2modadv_checkmark.png images/d2modadv_checkmark.png  
DocTypeD
images/d2modadv_checkmark.png images/d2modadv_checkmark.png  
HomeD
images/d2modadv_checkmark.png images/d2modadv_checkmark.png images/d2modadv_checkmark.png
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:
images/d2modadv_compound_cube_ex1.png
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:
images/d2modadv_compound_cube_ex2.png
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:
images/d2modadv_compound_cube_ex3.png
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).
Previous section   Next section