# Defining Cube-Cube Relationships

This chapter describes how to define relationships between cubes. It discusses the following topics:

For background information, see the chapter “Summary of Model Options” in Defining DeepSee Models.

## 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 indices.

The Patients sample provides five related cubes, in a folder named RelatedCubes. The following figure summarizes how these cubes are related: 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 Cities folder is a relationship to the RelatedCubes/Cities cube. If you expand it, you see the levels defined in that 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: Similarly, if you open the RelatedCubes/Doctors cube, you see the following cube contents: ## 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:

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, this option is blank, and DeepSee automatically sets the DependsOn keyword equal to the name of the source class for the cube.

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 Dimensions, Hierarchies, and Levels” in Defining DeepSee Models. 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:

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, this option is blank, and DeepSee automatically sets the DependsOn keyword equal to the name of the source class for the cube.

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::

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” in Defining DeepSee Models. 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 DeepSee 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 the DeepSee Implementation Guide.

### 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.Utils. 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=14
groups(1,"AVIATIONAIRCRAFT")=2
groups(1,"AVIATIONCREW")=3
groups(1,"AVIATIONEVENTS")=1
groups(2,"CITIES")=1
groups(3,"CITYRAINFALL")=1
...```

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 AVIATIONAIRCRAFT, AVIATIONCREW, and AVIATIONEVENTS. In this group, AVIATIONEVENTS should be built first, followed by AVIATIONAIRCRAFT, followed by AVIATIONCREW.

### 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” in Defining DeepSee Models.

## Using Model Browser

DeepSee 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 DeepSee, 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 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. For example: 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.

Feedback