Skip to main content

Defining Cube-Cube Relationships

This page describes how to define relationships between cubes for use in Business Intelligence.

For background information, see Summary of Model Options.

For information on how cube versions interact with relationships, consult Cube Versions and Relationships.

Also see Accessing the BI Samples.

Overview of Relationships

You can define relationships between cubes as follows:

  • One-to-many relationships. Then you can use levels of either cube in both cubes.

    In one cube (the one side), the relationship behaves much like a list-based level.

  • One-way one-to-many relationships. In one cube, the relationship behaves much like a list-based level. In the other cube, the relationship is not visible.

If you define relationships, you can define a level once rather than multiple times, which minimizes the sizes of fact tables and their indexes.

The Patients sample provides five related cubes, in a folder named RelatedCubes. The following figure summarizes how these cubes are related:

Two-way arrows between the Patients cube and Allergies and Doctors. One-way arrow from Patients and Cityrainfall to Cities.

The one-way arrows represent one-way relationships; the cube that has the arrow pointing away can see the levels of the other cube. The two-way arrows represent two-way relationships.

A Look at a One-Way Relationship

In this section, we examine a working one-way relationship.

The RelatedCubes/Patients cube has a one-way relationship to the RCities cube. If you open the RelatedCubes/Patients cube in the Analyzer, you see the following cube contents:

The Patients cube has relationships to the Allergies, Cities and Doctors cubes. These are shown in italic, colored text.

The Cities folder is a relationship to the RelatedCubes/Cities cube. If you expand it, you see the levels defined in that cube:

In the Patients cube, expanding the Cities folder shows that the Cities cube has a relationship to the Doctors cube.

When you work with the RelatedCubes/Patients cube, you can use all these levels in the same way that you use the levels that are defined directly in this cube. For example, you could drag and drop ZIP to Rows.

The Doctors folder within Cities is a relationship from the RelatedCubes/Cities cube to the RelatedCubes/Doctors cube; it is not recommended that you use relationships of relationships, because the result quickly becomes confusing.

A Look at a Two-Way Relationship

There is a two-way relationship between the RelatedCubes/Cities cube and the RelatedCubes/Doctors cube. If you open the RelatedCubes/Cities cube, you see the following cube contents:

The Cities cube has a relationship to Doctors. Opening the Doctors folder shows it has a relationship to Cities.

Similarly, if you open the RelatedCubes/Doctors cube, you see the following cube contents:

The Doctors cube has a relationship to Cities. Opening the Cities folder shows it has a relationship to Doctors.

Considerations for Defining Cube Relationships

Prior to defining any new relationships for cubes, you must take into account whether or not one or both of the affected cubes are already members of a group that is registered in the Cube Manager.

Attempting to add relationships to cubes in registered groups may result in an error and suspended Cube Manager tasks until the error is resolved. In order to prevent this behavior, you must unregister from the Cube Manager any groups which the affected cubes belong to prior to adding the relationships. Once the relationships have been added, reregister the groups and recreate the settings that were previously in place for them.

InterSystems recommends that all such changes to relationships be made on development systems, that the Cube Registry be verified for proper setup, and that the cube definitions and Cube Registry class then be imported onto the production system together.

Defining a One-Way Relationship

To define a one-way relationship from one cube to another cube, you define a single relationship in the first cube. To do so, make the following changes in the first cube:

  1. Select Add Element.

    The system displays a dialog box.

  2. For Enter New Item Name, type a relationship name. This determines the logical name of the relationship. It is convenient for this to be the same as the logical name of the other cube.

  3. Select Relationship.

  4. Select OK.

  5. In the Details Area on the right, specify the following values:

    Attribute Purpose
    Display Name Display name for this relationship. It is useful for this to be the same as the display name of the other cube.
    Property Specify one of these. The value must be the ID of a record in the base class used by the other cube.
    Expression
    Cardinality one
    Related cube Logical name of the other cube.
    Null replacement string (Optional) Specifies the string (for example, None) to use as the member name if the source data for a relationship is null.

    There is no default null replacement for relationships.

    Do not specify Inverse.

  6. Select the cube definition in the Architect to select it. Then edit the Depends On option in the Details Area on the right. This option specifies the class or classes that must be runnable before this class can be compiled.

    By default, the system sets the DependsOn keyword equal to the name of the source class for the cube when a new cube is created.

    To specify this option, specify a comma-separated list of classes and specify the full package and class name for each class in the list. Your list should include the source class for the cube and the cube class on which this cube depends. For example:

    [ DependsOn = (MyApp.CubeBaseClass, MyApp.OtherCubeClass)]
    
  7. Optionally edit the cube class in Studio to define a dependency of this relationship to some other relationship defined in the same cube.

    In some cases, there is a virtual dependency between two relationships. For example, you might have a cube with a Country relationship and a Product relationship. These relationships are logically independent from each other; theoretically any product could be sold in any country. But if specific products are sold only in specific countries, there is a virtual dependency between these relationships. When a user selects a country, it is desirable to show only the products appropriate for that country.

    In such a case, you can add a dependency between the relationships. To do so, specify the Depends on option as described in Defining Dependencies Between Levels in Different Hierarchies. For the value, specify the logical name of another relationship defined in the same cube. (Or, if the relationship depends upon a level, specify the MDX identifier of that level.)

    Note that the Depends on attribute is completely unrelated to the DependsOn compiler keyword.

Defining a Two-Way Relationship

To define a two-way relationship between cubes, you define two complementary <relationship> elements, one in each cube. One of these cubes is the dependent cube and the other is the independent cube.

Let us consider cube A (which based on the records of source class A) and cube B (which is based on the records of source class B). To define a two-way relationship between these cubes, use the following procedure:

  1. Evaluate the relationship between the source classes. Use this to determine which cube is the dependent cube, as follows:

    • Does one record in class A correspond to multiple records in class B?

      If so, cube B is the dependent cube.

    • Does one record in class B correspond to multiple records in class A?

      If so, cube A is the dependent cube.

    • Is there a one-to-one relationship between the classes?

      If so, either cube can be the dependent cube.

  2. In the Architect, make the following changes to the dependent cube:

    1. Select Add Element.

      The system displays a dialog box.

    2. For Enter New Item Name, type a relationship name. This determines the logical name of the relationship. It is convenient for this to be the same as the logical name of the other cube (IndependentCubeName, for example).

    3. Select Relationship.

    4. Select OK.

    5. In the Details Area on the right, specify the following values:

      Attribute Purpose Example
      Display Name Display name for this relationship. It is useful for this to be the same as the display name of the other cube. Independent Cube Display Name
      Property Specify one of these. The value must be the ID of a record in the base class used by the other cube.  
      Expression
      Cardinality one  
      Inverse Value of the inverse relationship in the other cube. It is useful for the name of a relationship to be the same as the logical name of the cube to which it points, so use the logical name of the cube. DependentCubeName
      Related cube Logical name of the other cube. IndependentCubeName
      Null replacement string (Optional) Specifies the string to use as the member name if the source data for a relationship is null.

      There is no default null replacement for relationships.

      None
  3. Select the cube definition in the Architect; that is, select the top line in the middle area. Then edit the Depends On option in the Details Area on the right. This option specifies the class or classes that must be runnable before this class can be compiled.

    By default, the system sets the DependsOn keyword equal to the name of the source class for the cube when a new cube is created.

    To specify this option, specify a comma-separated list of classes and specify the full package and class name for each class in the list. Your list should include the source class for the cube and the cube class on which this cube depends. For example:

    [ DependsOn = (MyApp.CubeBaseClass, MyApp.OtherCubeClass)]
    
  4. In the Architect, make the following changes to the independent cube::

    1. Select Add Element.

      The system displays a dialog box.

    2. For Enter New Item Name, type a relationship name. This determines the logical name of the relationship. It is convenient for this to be the same as the logical name of the other cube (DependentCubeName, for example).

    3. Select Relationship.

    4. Select OK.

    5. In the Details Area on the right, specify the following values:

      Attribute Purpose Example
      Display Name Display name for this relationship. It is useful for this to be the same as the display name of the other cube. Dependent Cube Display Name
      Cardinality many  
      Inverse Value of the inverse relationship in the other cube. It is useful for the name of a relationship to be the same as the logical name of the cube to which it points, so use the logical name of the cube. IndependentCubeName
      Related cube Logical name of the other cube. DependentCubeName
    6. Optionally edit the cube class in Studio to define a dependency of this relationship to some other relationship defined in the same cube.

      In some cases, there is a virtual dependency between two relationships. For example, you might have a cube with a Country relationship and a Product relationship. These relationships are logically independent from each other; theoretically any product could be sold in any country. But if specific products are sold only in specific countries, there is a virtual dependency between these relationships. When a user selects a country, it is desirable to show only the products appropriate for that country.

      In such a case, you can add a dependency between the relationships. To do so, specify the Depends on option as described in Defining Dimensions, Hierarchies, and Levels. For the value, specify the logical name of another relationship defined in the same cube. (Or, if the relationship depends upon a level, specify the MDX identifier of that level.)

      Note that the Depends on option is completely unrelated to the DependsOn compiler keyword.

Building Cubes That Have Relationships

When you build cubes that have relationships, first build the independent cube, which is the one that does not define a source property or source expression for the relationship. More generally, whenever you rebuild the independent cube, you must next rebuild the dependent cube. The suggested best practice is to write a utility method or routine that builds your cubes in the appropriate order.

For a production system, InterSystems recommends that you use the Cube Manager, which creates automated tasks that build or synchronize cubes according to your specifications. For details, see Using the Cube Manager.

Determining the Build Order for Related Cubes

If you are not using the Cube Manager, it is necessary for you to determine the correct build order. To do so, use the rules described previously or use the GetCubeGroups() method of %DeepSee.CubeManager.UtilsOpens in a new tab. The first argument, returned by reference, is an array that indicates the natural grouping of the cubes in the given namespace. For example:

SAMPLES>d ##class(%DeepSee.CubeManager.Utils).GetCubeGroups(.groups)
 
SAMPLES>zw groups
groups=11
groups(1,"CITIES")=1
groups(2,"CITYRAINFALL")=1
groups(3,"COMPOUNDCUBE/CITYRAINFALL")=1
groups(4,"COMPOUNDCUBE/DOCTORS")=1
groups(5,"COMPOUNDCUBE/PATIENTS")=1
groups(6,"CONNECTORCUBE")=1
groups(7,"HOLEFOODS")=1
groups(8,"HOLEFOODSBUDGET")=1
groups(9,"PATIENTS")=1
groups(10,"PATIENTSQUERYCUBE")=1
groups(11,"RELATEDCUBES/ALLERGIES")=5
groups(11,"RELATEDCUBES/CITIES")=1
groups(11,"RELATEDCUBES/CITYRAINFALL")=2
groups(11,"RELATEDCUBES/DOCTORS")=3
groups(11,"RELATEDCUBES/PATIENTS")=4

For this array:

  • The first subscript identifies a unique group of cubes in this namespace. The first one is numbered 1, the second is numbered 2, and so on. The numbers are arbitrary.

  • The second subscript identifies a cube in this namespace. This cube is in the group identified by the first subscript.

  • The value of this node is the build order of the given cube, within this group.

In this example, one group consists of the cubes RELATEDCUBES/ALLERGIES, RELATEDCUBES/CITIES, and RELATEDCUBES/CITYTRAINFALL, RELATEDCUBES/DOCTORS, and RELATEDCUBES/PATIENTS. In this group, RELATEDCUBES/CITIES should be built first, then RELATEDCUBES/CITYTRAINFALL, then RELATEDCUBES/DOCTORS, then RELATEDCUBES/PATIENTS, and last RELATEDCUBES/ALLERGIES//.

Errors If You Build Related Cubes in the Wrong Order

If you build related cubes in the wrong error, %BuildCube() generates an error like the following:

ERROR #5001: Missing relationship reference in RelatedCubes/Patients: source ID 1 missing reference to RxHomeCity 4

For general information on build errors and where you can see them, see Build Errors.

Using Model Browser

The system provides a supplemental tool (the Model Browser) that you can use to view cube definitions. The Model Browser is particularly useful for cubes that have relationships because it enables you to view and navigate the cube relationships.

To access this tool, select Analytics, then Tools, and then select Model Browser.

Once you access the Model Browser, use the drop-down list on the right and select the name of a cube. The Model Browser then displays a diagram like the following:

The label AviationEvents(1) has a dashed line to a yellow circle, which in turn has a solid line to a blue circle.

The yellow circle at the center represents the cube that you selected. The label on the top of the diagram gives the name of this cube (in this case, AviationEvents), followed by the number of related cubes (1) in parentheses. Each of the circles around the central circle represents one cube that is related to the selected cube. For these circles, the label indicates the cube name and the number of related cubes. For example, the circle labeled AviationAircraft represents the AviationAircraft cube. There are two cubes that are related to Referrals.

When you select any circle, the diagram changes so that the center shows the newly selected cube, and the rest of the diagram is refreshed accordingly.

The right side of the page displays details for the selected cube. This area displays the contents of the cube in exactly the same way as the left area of the Analyzer.

Removing Relationships

If you later decide to remove a relationship, do so as follows:

  1. Remove the relationship from both cube definitions.

  2. Recompile both cube definitions.

  3. Rebuild all modified cubes in the correct order.

FeedbackOpens in a new tab