Skip to main content
Previous sectionNext section

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:

  1. In Studio, open the cube class.

  2. Find the <cube> starting element:

    <cube name="Patients" displayName="Patients"   
       owner="_SYSTEM"
        sourceClass="DeepSee.Study.Patient"
        nullReplacement="None"
        defaultListing="Patient details">
    Copy code to clipboard
  3. After the greater than sign (>), add one or more <expression> elements. For example:

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

    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.

  4. Within the definition of a measure, dimension, level, or another <expression>, use the following syntax to refer to an expression:

    %expression.expressionName
    Copy code to clipboard
  5. Save and recompile the class.

  6. 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)
Copy code to clipboard

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
}
Copy code to clipboard

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:

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

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

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

    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
    }
    Copy code to clipboard

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

  3. Redefine the levels that use the PatientDetails table as follows:

    • Redefine the Industry level to use the following sourceExpression:

      sourceExpression='$LI(%expression.patDetails,1)'
      Copy code to clipboard
    • Redefine the Profession level to use the following sourceExpression:

      sourceExpression='$LI(%expression.patDetails,2)'
      Copy code to clipboard
    • Redefine the Favorite Color level to use the following sourceExpression:

      sourceExpression='$LI(%expression.patDetails,3)'
      Copy code to clipboard
  4. Save and recompile the class.

  5. Rebuild the cube.

Defining Intermediate Expressions in Architect

To define an expression in the Architect:

  1. Open a cube definition in the Architect.

  2. Select Add Element.

    The system displays a dialog box.

  3. For Enter New Item Name, type a name.

  4. Select Expression.

  5. Select OK.

  6. In the Details tab on the right, specify values for each of the fields.