Defining Intermediate Expressions

In some cases, you might have multiple measures or dimensions that use similar logic, perhaps running the same subroutine or using SQL to refer to another table. To improve performance of cube building, you can define expressions, which contain intermediate values (one value for each fact), and then you can use these expressions within the definitions of other cube elements. You can define expressions using either Studio or the Architect.

Defining and Using Intermediate Expressions in Studio

To define an expression in Studio:

In Studio, open the cube class.

Find the <cube> starting element:

<cube name="Patients" displayName="Patients"   
   owner="_SYSTEM"
    sourceClass="DeepSee.Study.Patient"
    nullReplacement="None"
    defaultListing="Patient details">

After the greater than sign (>), add one or more <expression> elements. For example:

<expression name="patDetails" sourceExpression='%cube.GetPatientDetails(%source.PatientID)'  />

At a minimum, the <expression> element must have the following attributes:

Attribute	Value
name	Name of this expression.
sourceExpression	Optionally specify an ObjectScript expression that returns a single value for any given source record. If you specify this, do not specify sourceProperty. For an <expression> element, it is more likely that you will use sourceExpression (because your cube elements can directly use properties, without the need for the intermediate step provided by <expression>). An <expression> element can refer to another <expression> element.
sourceProperty	Optionally specify the property name relative to the base class used by the cube; you can use dot syntax to refer to properties of properties. If you specify this, do not specify sourceExpression.

For additional options for sourceExpression and sourceProperty, see “Specifying the Source Values for a Dimension or Level” and “Details for Source Expressions,” in Defining DeepSee Models.

Within the definition of a measure, dimension, level, or another <expression>, use the following syntax to refer to an expression:
```
%expression.expressionName
```
Save and recompile the class.
Rebuild the cube.

Example

First, let us consider a scenario where we might want to use an <expression> element. The Patients cube has several levels that are defined by source expressions that access data via SQL queries to the PatientDetails table. For example, the Favorite Color level is defined with the following source expression:

%cube.GetFavoriteColor(%source.PatientID)

The GetFavoriteColor() method contains embedded SQL as follows:

ClassMethod GetFavoriteColor(patientID As %String) As %String
{
    New SQLCODE
    &sql(SELECT FavoriteColor INTO :ReturnValue 
    FROM DeepSee_Study.PatientDetails 
    WHERE PatientID=:patientID)
    If (SQLCODE'=0) {
        Set ReturnValue=""
        }
    Quit ReturnValue
}

The Profession and Industry levels are defined similarly. As a consequence, when the Patients cube is built, the system executes three queries on the PatientDetails table for each row of the source class.

You can redefine the cube so that the system executes only one query on the PatientDetails table for each row of the source class. To do so:

In Studio, open the Patients cube class, DeepSee.Model.PatientsCube/

Within the <cube> element in this class, add the following element:

<expression name="patDetails" sourceExpression='%cube.GetPatientDetails(%source.PatientID)'  />

This expression runs a method in the cube class. The method is defined as follows:

ClassMethod GetPatientDetails(patientID As %String) As %String
{
    New SQLCODE
    &sql(SELECT Profession->Industry,Profession->Profession,FavoriteColor 
    INTO :Industry,:Profession,:FavoriteColor 
    FROM DeepSee_Study.PatientDetails 
    WHERE PatientID=:patientID)
    
    If (SQLCODE'=0) {
        Set Industry="",Profession="",FavoriteColor=""
    }
    Set ReturnValue=$LISTBUILD(Industry,Profession,FavoriteColor)
    Quit ReturnValue
}

This method retrieves several fields for a given patient, builds a list that contains the information, and returns the list.

Redefine the levels that use the PatientDetails table as follows:
- Redefine the Industry level to use the following sourceExpression:
```
sourceExpression='$LI(%expression.patDetails,1)'
```
- Redefine the Profession level to use the following sourceExpression:
```
sourceExpression='$LI(%expression.patDetails,2)'
```
- Redefine the Favorite Color level to use the following sourceExpression:
```
sourceExpression='$LI(%expression.patDetails,3)'
```
Save and recompile the class.
Rebuild the cube.