Defining Cubes
This chapter describes the basics of defining cubes. It includes the following topics:
DeepSee 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 DeepSee can escape the property names. For a list of reserved words, see “Reserved Words” in the Caché SQL Reference. For information on enabling support for delimited identifiers, see “Identifiers” in Using Caché SQL.
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 chapter.
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 the appendix “Reference Information for Cube Classes.”
Generating the Cube Class
The class %DeepSee.WizardUtilsOpens in a new tab provides a 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 Browse next to Source Class, the system displays a dialog box like the following:
![generated description: arch source class finder](images/d2model_arch_source_class_finder.png)
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 external non-Caché data, but you can also use it to specify an SQL query against Caché, including an SQL query on a view.
For a cube that is based on a data connector, all listings in that cube must also be based on data connectors. Also, all 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 the DeepSee Implementation Guide.
-
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 the chapter “Performing Localization” in the DeepSee Implementation Guide.
-
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 Caché resource that secures the cube.
For information on how this is used, see “Setting Up Security” in the DeepSee Implementation Guide.
-
Owner — (Optional) Specify the owner of the cube. Specify a Caché 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 DeepSee Modeling Guide.
-
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 the DeepSee Implementation Guide.
-
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 the Advanced DeepSee Modeling Guide.
-
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, this option is blank, and DeepSee 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 DeepSee Modeling Guide.
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 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 | “Defining Dimensions, Hierarchies, and Levels” |
Time Dimension | |
Age Dimension | |
Hierarchy | |
Level | |
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.
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.
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 Caché 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 DeepSee 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.
DeepSee 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 DeepSee, 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 DeepSee 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 indices.
-
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")
-
In Studio, right-click the cube class and select Delete.
Or, if you decide not to delete the cube, recompile and rebuild it.
-