Advanced DeepSee Modeling Guide
Defining Intermediate Expressions
[Home] [Back] [Next]
InterSystems: The power behind what matters   
Class Reference   
Search:    

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">
  3. 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 a 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.
  4. Within the definition of a measure, dimension, level, or another <expression>, use the following syntax to refer to an expression:
    %expression.expressionName
  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)
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:
  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)'  />
    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.
  3. Redefine the levels that use the PatientDetails table as follows:
  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.