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">
Copy code to clipboardAfter the greater than sign (>), add one or more <expression> elements. For example:
<expression name="patDetails" sourceExpression='%cube.GetPatientDetails(%source.PatientID)' />
Copy code to clipboardAt 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
Copy code to clipboardSave 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)' />
Copy code to clipboardThis 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 clipboardThis 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)'
Copy code to clipboardRedefine the Profession level to use the following sourceExpression:
sourceExpression='$LI(%expression.patDetails,2)'
Copy code to clipboardRedefine the Favorite Color level to use the following sourceExpression:
sourceExpression='$LI(%expression.patDetails,3)'
Copy code to clipboard
Save and recompile the class.
Rebuild the cube.
Defining Intermediate Expressions in Architect
To define an expression in the Architect:
Open a cube definition in the Architect.
Select Add Element.
The system displays a dialog box.
For Enter New Item Name, type a name.
Select Expression.
Select OK.
In the Details tab on the right, specify values for each of the fields.