Defining Models for InterSystems Business Intelligence
This page describes the basics of defining Business Intelligence 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. For information on enabling support for delimited identifiers, see Identifiers.
Also see Accessing the Business Intelligence Samples.
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.
-
Click 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 page.
Apart from the class name of the cube class, you can edit all cube options after creating the cube.
-
-
Click OK.
-
Optionally save the cube. To do so:
-
Click Save.
-
Click OK.
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 Reference Information for Cube Classes.
Generating the Cube Class
The class %DeepSee.WizardUtilsOpens in a new tab provides the %GenerateCubeDefinition()Opens in a new tab method that you can use to generate a cube class. The method is as follows:
classmethod %GenerateCubeDefinition(pSourceClass As %Library.String(MAXLEN="")="",
pCubeName As %Library.String(MAXLEN=""),
pCubeClass As %Library.String(MAXLEN="")="",
pAutoDelete As %Library.Integer = 0)
Where:
-
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 tab.
-
Data connector classes — Classes that extend %DeepSee.DataConnectorOpens in a new tab. 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.
-
Collection classes.
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 Performing Localization.
-
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.
-
Owner — (Optional) Specify the owner of the cube. Specify an InterSystems IRIS® data platform 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 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 Controlling the Fact Order.
-
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.
-
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 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 %FILTER Clause.
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.
-
Click OK.
-
-
By using a drag-and-drop action, as follows:
-
Drag a property name from the Class Viewer.
-
Drop the name onto a heading in the Model Viewer; the results depend upon where you drop the name. For example, if you drag and drop a property name onto the Measures heading, the Architect creates a measure.
-
In both cases, the Architect adds the item and displays it in the Model Viewer. You can then make edits in the Details Area.
The following table indicates the articles that discuss the different types of cube items. These articles also provide specific information on where you can drag and drop property names.
For information on ... | See the article ... |
---|---|
Data Dimension, Time Dimension, Age Dimension, Hierarchy, or Level | Defining Dimensions, Hierarchies, and Levels |
Property | Defining Properties |
Measure | Defining Measures |
Listing | Defining Listings |
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. 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. InterSystems 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.
-
To be considered unique, element names cannot differ in case alone. For example, zipcode and zipCode are not considered unique names.
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.
-
Within a given level, each property name must be unique. If the cube class definition manually defines <member> elements for a level, a property cannot have the same name as any <member> element within that level.
-
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.
-
Within a given cube, each measure name must be unique.
-
If an Expression element is defined such that it returns an object rather than a single value, its logical name must not include the full-stop (“.”) character. In these cases, InterSystems recommends using the underscore (“_”) character instead.
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 IDEs.
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 Compiling and Building Cubes.
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. 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)
Where cubeName is the logical name of the cube to remove. This command removes the cube cache and indexes.
-
Also in the Terminal, delete the cube metadata, as follows:
kill ^DeepSee.Cubes("cubes",cubeName)
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)
Where classname is the full package and class name of the cube class. For example:
do $system.OBJ.Delete("Mypackage.Myclass")
-
Delete the cube class from within your IDE.
Or, if you decide not to delete the cube, recompile and rebuild it.
-