Defining Models for InterSystems Business Intelligence
This chapter describes the basics of defining cubes.
The system uses SQL to access data while building the cube, and also when executing detail listings. If your model refers to any class properties that are SQL reserved words, you must enable support for delimited identifiers so that InterSystems IRIS Business Intelligence can escape the property names. For a list of reserved words, see “Reserved Words” in the InterSystems SQL Reference. For information on enabling support for delimited identifiers, see “Identifiers” in Using InterSystems SQL.
Also see “Accessing the Samples Shown in This Book,” in the first chapter.
Defining a Cube
To define a cube:
In the Architect, click New.
The system displays a dialog box where you can enter details for the new cube.
Enter the following information at a minimum:
Cube Name — Logical name of the cube to use in queries.
Class Name for the Cube — Complete package and class name for the cube class.
Source Class — Complete package and class name of the base class for this cube. See the subsection “Possible Source Classes.”
You can either type the class name or click Browse and select the class.
The other options are discussed later in this chapter.
Apart from the class name of the cube class, you can edit all cube options after creating the cube.
Optionally save the cube. To do so:
The system creates the class.
Another option is to use a utility to generate the cube class, as discussed in the following subsection.
Or manually create the class as described in the appendix “Reference Information for Cube Classes.”
Generating the Cube Class
classmethod %GenerateCubeDefinition(pSourceClass As %Library.String(MAXLEN="")="", pCubeName As %Library.String(MAXLEN=""), pCubeClass As %Library.String(MAXLEN="")="", pAutoDelete As %Library.Integer = 0)
pSourceClass is the full name of the source class for the cube.
pCubeName is the logical name of the cube.
pCubeClass is the full name of the cube class.
pAutoDelete controls whether the cube class is deleted, if it already exists. If this argument is nonzero, the class is deleted; otherwise it is not.
This method generates a cube definition as follows:
It has one measure for each numeric property in the source class.
It has one date dimension for each date property in the source class. This dimension contains one hierarchy with three levels. The levels are year, year and month, and date.
It has one data dimension for each other property in the source class. This dimension contains one hierarchy with one level.
It has one listing that uses all properties in the source class.
The method ignores transient and multidimensional properties.
Changing the Base Class for a Cube
On rare occasions, you might need to change the base class for a cube. To do so, you can do either of the following in the Architect:
Edit the Source Class option for the cube in the Details Area.
Click the Change link next to Source Values at the top of the Class Viewer.
If you do so, the system displays a dialog box where you can choose a source class; this is the same dialog box as is shown in “Possible Source Classes.”
After you do this, be sure to modify the source property or source expression appropriately for all parts of the model.
Possible Source Classes for a Cube
In the Architect, if you click New and then Browse next to Source Class, the system displays a dialog box like the following:
Here you can select any class that can be used as the source of a cube in this namespace. There are three types of classes you can use this way:
Persistent classes — Classes that extend %Library.PersistentOpens in a new window.
Data connector classes — Classes that extend %DeepSee.DataConnectorOpens in a new window. A data connector maps the results of an arbitrary SQL query into an object that can be used as the source of a cube. Typically, a data connector accesses data in a non-InterSystems database, but you can also use it to specify an SQL query against an InterSystems database, including an SQL query on a view.
If you have a cube based on a data connector and listings in that cube that are also based on data connectors, all of these data connectors must have the same property marked as idkey="true", because the underlying mechanism uses the same ID values in all cases.
See “Defining and Using Data Connectors” in Implementing InterSystems Business Intelligence.
Other Cube Options
In the Architect, you can specify the following options for a cube:
Cube name — Logical name of the cube to use in queries.
Display name — Localizable name of the cube. If you do not specify this, the user interface instead displays the logical name.
Description — (Optional) Comments to add to the cube class definition. Each line is saved as a separate comment line at the start of the class definition.
Caption— (Optional) Specify the caption to display in the Analyzer and other utilities when working with this cube.
Domain— (Optional) Specify the name of the domain to contain the localized strings of this cube. You might find it convenient to use a single domain for all your cubes; in other cases, it might be appropriate to have a separate domain for each cube. See the chapter “Performing Localization” in Implementing InterSystems Business Intelligence.
Source class — Complete package and class name of the base class for this cube.
Null replacement string — (Optional) Specifies the string (for example, None) to use as the member name if the source data for a level is null.
This option can overridden for levels, by a level option of the same name.
Default listing — (Optional) Logical name of the default listing for this cube. This listing must be defined in the cube.
Resource — (Optional) Specify the resource that secures the cube.
For information on how this is used, see “Setting Up Security” in Implementing InterSystems Business Intelligence.
Owner — (Optional) Specify the owner of the cube. Specify an InterSystems IRIS® username.
Count measure name — (Optional) Specify an alternative name for the Count measure. The default is %COUNT. It is useful to rename the Count measure if you create a compound cube; see the Advanced Modeling for InterSystems Business Intelligence.
Count measure caption — (Optional) Specify an alternative caption for the Count measure. The default is COUNT.
Initial build order — (Optional) Specifies an optional ORDER BY clause for use when building the entire cube; does not affect cube synchronization or incremental updates. Specify a comma-separated list of fields in the source table. You can use the SQL keywords ASC and DESC. For example: Age DESC,Gender
For the implications of this option, see the section “Controlling the Fact Order” in Implementing InterSystems Business Intelligence.
Build restriction — (Optional) Specifies an optional WHERE clause to use when building or updating the cube; this causes the cube to use a subset of the records. Specify an SQL comparison expression that uses fields in the source table. For example: Gender='F'
This option has no effect if the cube is based on a data connector.
For an alternative option, see “Restricting the Records Used in the Cube” in the chapter “Using Advanced Features of Cubes and Subject Areas” of Advanced Modeling for InterSystems Business Intelligence.
Depends On — (Optional) Specifies the class or classes that must be runnable before this class can be compiled. This option controls how the Architect sets the DependsOn compiler keyword.
By default, when a cube is created, the system automatically sets the DependsOn keyword equal to the name of the source class for the cube. In some cases (for example with cube relationships), you might need to specify an additional class.
If you need to specify this option, specify a comma-separated list of classes and specify the full package and class name for each class in the list. Your list should include the source class for the cube.
For information on relationships between cubes, see the Advanced Modeling for InterSystems Business Intelligence.
Allow SQL Restrict — (Optional) Selecting this check box enables you to use the %SQLRESTRICT dimension for a cube. This option enables you to define an SQL restriction in the slicer of an MDX query by adding a SQL SELECT statement or WHERE clause. Selecting this option also enables the SQL Restriction field in the Pivot Options menu of the Analyzer. For more information on using the %SQLRESTRICT dimension, see the %FILTER Clause section of the InterSystems MDX Reference.
Adding Items to a Cube
In the Architect, there are two general ways to add items to a cube:
By using the Add Element link, as follows:
Click Add Element at the top of the Model Viewer.
The system displays a dialog box where you can choose the type of item to add.
Enter the item name.
Click the item type.
By using a drag-and-drop action, as follows:
The following table indicates the parts of this book that discuss the different types of cube items. These sections also provide specific information on where you can drag and drop property names.
|For information on ...||See the chapter ...|
|Data Dimension, Time Dimension, Age Dimension, Hierarchy, or Level||“Defining Dimensions, Hierarchies, and Levels”|
|Listing Field||“Defining Listing Fields”|
|Calculated Member||“Defining Calculated Members”|
|Named Set||“Defining Named Sets”|
Names for Model Elements
When you define a model element, you specify a logical name for it (the Name field in the Architect). This name is used in MDX queries and also is the default display name for that element. This section discusses requirements and suggestions for these names. As of IRIS 2020.4, if you attempt to define a non-compliant name, the system will notify you of the error and prompt you to enter a different name.
The logical names must follow these rules:
The first character must be either a letter (in the Latin-1 character set), a number, or the underscore character (_).
The other characters must be either letters, numbers, spaces or underscore characters. IRIS versions 2020.1.1 and later also support periods and colons in element names.
Note that if you use spaces in a name, you must enclose the name within square brackets when writing MDX queries.
The name must not be an MDX reserved keyword. Reserved keywords are not case-sensitive in MDX.
The logical names must also follow these additional rules:
Within a given InterSystems IRIS namespace, each cube name must be unique.
Within a given cube, each dimension name must be unique.
Within a given dimension, each hierarchy name must be unique.
Within a given hierarchy, each level name must be unique.Note:
Level names do not have to be unique within a cube. If you do have multiple levels with the same name within a cube, however, you must specify the Field name in fact table option and ensure that each level has a unique name in the fact table. See “Specifying Field Names in the Fact Table,” later in this book.
Within a given level, each property name must be unique.
Names of intrinsic properties are case-insensitive reserved keywords and cannot be used as names of your properties, with one exception. The exception is that you can create a property named Name (in any case), if that property also has the option Use as member names enabled.
For a list of intrinsic properties, see “Intrinsic Properties” in the InterSystems MDX Reference.
Within a given cube, each measure name must be unique.
Other Common Options
In the Architect, when you define a model element, you can also specify the following options for it:
Display name — (Optional) Localized name of this element for use in user interfaces. If you do not specify this, the user interface instead displays the logical name.
Description — (Optional) Description of this element.
Disabled — (Optional) If you select this check box, the element is disabled (not seen by the compiler). When you recompile the cube, this element is ignored.
Additional Description — (Optional) Additional notes about this element, for display only within the Architect and Studio.
Compiling and Building a Cube
In the Architect, as you develop your cubes, you will probably recompile and rebuild them multiple times. Briefly:
To compile a cube, click Compile.
The system starts to compile the class and displays a dialog box that shows progress.
If you have made changes that you have not yet saved, the system saves them.
Then click Done.
To build a cube, click Build. The system displays a dialog box. Click Build.
The system starts to build the cube and displays progress as it does so. Then click OK.
The cube is then available for use in the Analyzer.
For more information, see the chapter “Compiling and Building Cubes,” later in this book.
Opening a Cube in the Analyzer
As you develop your cubes, you should periodically use the Analyzer and examine the results. To open a cube in the Analyzer:
Click Analytics, click Analyzer, and then click Go.Tip:
If the Analyzer is already open, just click the Analyzer link at the top of the page.
If the left area is not currently displaying the cube you are validating, click Open and choose the cube.
For specific tips on validating levels, see “Validating Your Levels,” later in this book. For general information on using the Analyzer, see Using the Analyzer.
Deleting a Cube
To delete a cube, do the following:
In the Terminal, execute the following command:
do ##class(%DeepSee.Utils).%KillCube(cubeName)Copy code to clipboard
Where cubeName is the logical name of the cube to remove. This command removes the cube cache and indices.
Also in the Terminal, delete the cube metadata, as follows:
kill ^DeepSee.Cubes("cubes",cubeName)Copy code to clipboard
Where cubeName is the logical name of the cube to remove.
Delete the cube class (and its generated classes and their data) in either of the following ways:
In the Terminal, execute the following command:
do $system.OBJ.Delete(classname)Copy code to clipboard
Where classname is the full package and class name of the cube class. For example:
do $system.OBJ.Delete("Mypackage.Myclass")Copy code to clipboard
In Studio, right-click the cube class and select Delete.
Or, if you decide not to delete the cube, recompile and rebuild it.