InterSystems IRIS Data Platform 2019.2  /  Advanced Modeling for InterSystems IRIS Business Intelligence

Advanced Modeling for InterSystems IRIS Business Intelligence
Defining Intermediate Expressions
Previous section           Next section
InterSystems: The power behind what matters   

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.
Also see “Accessing the Samples Shown in This Book,” in the preface.
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"   
        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 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 Models for InterSystems Business Intelligence.
  4. Within the definition of a measure, dimension, level, or another <expression>, use the following syntax to refer to an expression:
  5. Save and recompile the class.
  6. Rebuild the cube.
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:
The GetFavoriteColor() method contains embedded SQL as follows:
ClassMethod GetFavoriteColor(patientID As %String) As %String
    &sql(SELECT FavoriteColor INTO :ReturnValue 
    FROM BI_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 an editor, open the Patients cube class, BI.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 BI_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.

Previous section           Next section
Send us comments on this page
View this book as PDF   |  Download all PDFs
Copyright © 1997-2019 InterSystems Corporation, Cambridge, MA
Content Date/Time: 2019-09-20 05:36:42