Skip to main content

Defining Dimensions, Hierarchies, and Levels

This page describes how to define dimensions and hierarchies in a Business Intelligence cube, and describes the basics of defining levels.

Business Intelligence provides many options that affect levels; also see Details of Defining Levels.

Also see Accessing the Business Intelligence Samples.

Overview

In the Architect, you can create model elements in either of two ways:

  • By drag and drop. If you can drag a property from the Class Viewer to an appropriate target in the Model Viewer, the Architect creates a model element; as described in the table after this list. Then you can edit the definition in the Details Area, if needed.

    This technique makes it easy to define elements directly based on source properties. It is also useful as a starting point when you need to create elements based on source expressions.

  • By using the Add Element link.

For reference, the following table describes the result of various drag-and-drop actions:

If you drop the property XYZ here... The Architect creates this...
Measures heading or any existing measure A measure named XYZ, XYZ1 or so on, as needed for uniqueness. The measure is based on the source property XYZ and is aggregated with SUM. See Defining Measures.
Dimensions heading A new data dimension named XYZ, XYZ1, or so on, as needed for uniqueness. This dimension contains the hierarchy H1, which contains the level XYZ. The level is based on the source property XYZ.
Existing dimension A new hierarchy (H2, for example), which contains the level XYZ. The level is based on the source property XYZ.
Existing hierarchy A new level named XYZ, XYZ1, or so on, as needed for uniqueness within this hierarchy. The level is based on the source property XYZ.
Existing level A new level property named XYZ, XYZ1, or so on, as needed for uniqueness within this level. The level property is based on the source property XYZ. See Defining Properties.

Creating a New Dimension, Hierarchy, and Level

To define a usable level, you must define the following, at a minimum:

  • A dimension

  • A hierarchy in that dimension

  • A level in that hierarchy

In the Architect, you can use drag-and-drop actions as described in Overview. Or, you can do the following:

  1. Add a dimension:

    1. Click Add Element.

      The system displays a dialog box.

    2. For Enter New Item Name, type a dimension name.

      See Names for Model Elements.

    3. Click one of the following choices, depending on the type of dimension you want to create:

      • Data Dimension — Click this for most dimensions.

      • Time Dimension — Click this to create a dimension that groups data by a date or time value. For details, see Defining a Time Level, later in this page.

      • Age Dimension — Click this to create a dimension that groups data by age, based on a date value. Note that age dimensions are not generally recommended. For details, see Defining an Age Level, later in this page.

      For information on the iKnow Dimension option, see Using Text Analytics in Cubes.

    4. Click OK.

      The system creates the dimension and displays it in the Model Viewer. For example:

      Model Viewer showing a data dimension called PatGrpD, which contains a hierarchy called H1 with a level called New_Level1.

      Note that the system has also created a hierarchy within this dimension and a level within that hierarchy.

  2. Modify the automatically created level to use a more suitable name:

    1. Click the level in the Model Viewer.

      The system displays details in the Details Area.

    2. Make the following changes:

      • Name — Change this to the level name you want.

        See Names for Model Elements.

      • Display Name — Specifies the localized display name for this level. Either clear this (so that the system uses the value given for Name instead) or specify a display name.

      See also Other Common Options.

  3. Specify the source values for this level, as described in Defining the Source Values for a Dimension or Level.

  4. Click Save.

  5. When prompted, click OK.

Note:

By default, the Analyzer does not display hierarchy names shown unless a dimension contains multiple hierarchies. Alternatively, a dimension can be defined so that its hierarchy names are always shown or never shown. See the reference for showHierarchies in <dimension> in Reference Information for Cube Classes.

Adding a Hierarchy and Level

In the Architect, to add a hierarchy and a level to an existing dimension, you can use drag-and-drop actions as described in Overview. Or, you can do the following:

  1. Click the dimension in the Model Viewer.

  2. Click Add Element.

    The system displays a dialog box.

  3. For Enter New Item Name, type a hierarchy name.

    See Names for Model Elements.

  4. Click Hierarchy.

  5. Click OK.

    The system creates the hierarchy and displays it in the Model Viewer. It also creates one level within that hierarchy.

  6. Optionally select the hierarchy in the Model Viewer and edit the details in the Details Area.

  7. Select the level in the Model Viewer and edit the details in the Details Area.

Adding a Level

In the Architect, to add a level to an existing hierarchy, you can use drag-and-drop actions as described in Overview. Or, you can do the following:

  1. Click either the hierarchy or an existing level within that hierarchy, in the Model Viewer.

    This action indicates where the level is to be added:

    • If you click the hierarchy, the new level will be added after all the other levels in this hierarchy.

    • If you click a level, the new level will be added immediately before that level.

  2. Click Add Element.

    The system displays a dialog box.

  3. For Enter New Item Name, type a level name.

  4. Click Level.

  5. Click OK.

    The system creates the level and displays it in the Model Viewer.

  6. Optionally select the level in the Model Viewer and edit the details shown in the Details Area.

Alternatively, drag a class property from the Class Viewer and drop it onto a hierarchy. The system adds a level based on this property; this level is added after any other levels in this hierarchy.

Important:

The order of the levels in a hierarchy determines the structure of the hierarchy, as noted in Defining Hierarchies Appropriately. If you want to reorder the levels after defining them, see Changing the Order of Levels in a Hierarchy.

Defining the Source Values for a Dimension or Level

Each level must have a specified source value. You can specify the source values within the dimension or within the level. Typically:

  • For data dimensions, you specify the source values within the level.

  • For time and age dimensions, you specify the source values within the dimension.

  • For NLP dimensions, see Using Text Analytics in Cubes. NLP dimensions do not use the mechanism described here.

To specify a source value in the Architect:

  1. Select the dimension or the level in the Model Viewer.

  2. In the Details Area, specify a value for one of the following:

    • Property — Specify the property name relative to the base class used by the cube; you can use dot syntax to refer to properties of properties. For example:

      Age
      

      For another example:

      HomeCity.PostalCode
      

      The property must have a projection to SQL.

      Also, you can refer to an object-valued property. When you do so, the numeric ID of the object is used as the source value.

      You cannot directly use a stream property (to use such a property, create an expression that returns the contents of the stream or a selected part of the contents).

    • Expression — Specify an ObjectScript expression on which the level is based. For example:

      ##class(Cubes.StudyPatients).GetFavoriteColor(%source.PatientID)
      

      This expression is evaluated when the cube is built. For details, see the next section.

You can enter values into the Property and Expression fields in any of the following ways:

  • By dragging a class property from the Class Viewer and dropping it into the field.

    The property that you drag and drop replaces any existing contents of the field.

  • By clicking the Search button .

    • For Property, the system then displays a dialog box that shows the properties of this class. Click a property and then click OK.

    • For Expression, the system then displays a dialog box with a larger field you can type into. Type an expression and then click OK.

  • By directly editing the value in the field.

The information on this section and in the next section also applies to level properties and to measures, which also require source values of some form.

Specifying a Source Value when Using a Data Connector

If the cube is based on a data connector, note the following restrictions:

  • Specify the Property option but not the Expression option.

  • You cannot use dot syntax, because none of the properties of the data connector are object references.

For information on creating data connectors, see Defining Data Connectors.

Details for Source Expressions

As noted in the previous section, you can specify either a source value or a source expression to use as the basis of a dimension or level (or property or measure). You can create source expressions as follows:

  • You can refer to a property in the source class. To do so, use the syntax %source.PropertyName, where PropertyName is the name of the property. When it builds the cube, the system parses this expression and looks up the SqlFieldName of the given property.

  • You can use dot syntax to refer to properties of properties.

  • You can refer to an object-valued property. When you do so, the numeric ID of the object is used as the source value.

  • You can use the variable %cube to refer to the cube class; this is useful if you have defined utility methods in the cube class that you want to use within source expressions.

  • You can refer to the value of an <expression> element. To do so, use the following syntax:

    %expression.expressionName
    

    For information on expressions, see Advanced Modeling for InterSystems Business Intelligence

  • You can use the utility methods ToUpper(), ToLower(), and Log() as in the following example:

    ..ToUpper(%source.HomeCity.Name)
    

    The system can use these methods in this way because the fact table class inherits from %DeepSee.CubeFunctionSetOpens in a new tab, which defines them. For details on these methods, see the class reference for that class.

  • You can use the %Lookup() method of %DeepSee.CubeDefinitionOpens in a new tab to invoke a term list. See the subsection Using a Term List.

  • You can use the %Rule() method of %DeepSee.CubeDefinitionOpens in a new tab to invoke a production business rule. See the subsection Using a Production Business Rule.

Note:

You cannot use a source expression if the cube is based on a data connector.

Also, if a level is based on a source expression (rather than a source property) and if the members have purely numeric names, InterSystems recommends that you also modify the cube in an IDE and add castAsNumeric="true" to that level definition. This option causes the system to treat the members as numbers when searching for a replacement for a member that does not exist, when a query that uses an MDX range expression (a specific kind of set expression).

Using a Term List

Term lists provide a way to customize an InterSystems IRIS Business Intelligence model without programming. A term list is a simple (but extendable) list of key and value pairs. (See Defining Term Lists.)

You can invoke a term list within a source expression. To do so, use the %Lookup() method of %DeepSee.CubeDefinitionOpens in a new tab. This method has the following signature:

%Lookup(term_list_name, lookup_value,default,alternative_field)

Where the arguments are strings, and their values are as follows:

  • term_list_name evaluates to the name of a term list.

  • lookup_value evaluates to the string to look up in the term list.

  • default, which is optional, evaluates to the value to return if lookup_value is not found in the term list.

  • alternative_field, which is optional, is the name of the field to return. The default is "value".

    This argument is not case-sensitive.

This function examines the given term list, finds the term whose "key" field equals the string given by lookup_value and then returns the value contained in the field identified by alternative_field.

All term lists have at least two fields: "key" and "value". You can add additional fields. For information, see Defining Term Lists.

Note:

Because your cube definition class inherits from %DeepSee.CubeDefinitionOpens in a new tab, which defines the %Lookup() method, you can use a source expression like the following:

%cube.%Lookup(term_list_name,lookup_value,default,alternative_field)

For example, suppose that you have the following term list, called LocalTeams:

The Term List Manager showing a term list, where terms have a Key that is a city and a Value that is a baseball team name.

You could add a property to the City level in HoleFoods as follows:

<property name="Team" sourceExpression='%cube.%Lookup("LocalTeams",%source.Outlet.City,"No Team")'/>

Using a Production Business Rule

Business rules allow nontechnical users to change the behavior of business processes in productions. You can also use them in source expressions in cubes. (For details on production business rules, see Developing Business Rules.)

To access a production business rule within a source expression, use the %Rule() method of %DeepSee.CubeDefinitionOpens in a new tab. This method has the following signature:

%Rule(rule_name)

Where rule_name is the name of a production business rule.

When this function is evaluated (for a given source record) during a cube build, the system passes to it an instance of the cube source class as the context object. The system uses this object, evaluates the rule, and then accesses the value returned by the rule.

Note:

Because your cube definition class inherits from %DeepSee.CubeDefinitionOpens in a new tab, which defines the %Rule() method, you can use a source expression like the following:

%cube.%Rule(rule_name)

Changing the Order of Dimensions in the Cube

In the Architect, to change the order of dimensions in the cube:

  1. Click Reorder.

    The system displays a dialog box.

  2. Click Dimensions.

  3. Optionally click Alphabetize to alphabetize them.

    This affects the list immediately. You can then reorganize the list further if needed. Also, when you add dimensions, they are not automatically alphabetized.

  4. Click the name of a dimension and then click the up or down arrows as needed.

  5. Repeat as needed for other dimensions.

  6. Click OK.

The order of the dimensions in the cube affects how they are displayed in the Analyzer. It does not have any other effect. Some customers choose to alphabetize their dimensions for convenience; others put more-often used dimensions at the top of the list.

Changing the Order of Levels in a Hierarchy

In the Architect, to change the order of levels in a hierarchy:

  • To move a level up in the hierarchy, click the up arrow in the row for that level.

  • To move a level down in the hierarchy, click the down arrow in the row for that level.

Important:

The order of the levels in a hierarchy determines the structure of the hierarchy, as noted in Defining Hierarchies Appropriately.

Validating Your Levels

After defining levels, you should build those levels, either through a full cube build or via a selective build. Validate that the levels behave appropriately. For each level, use the Analyzer and create a new pivot table that displays the level as rows.

  1. 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.

  2. If the left area is not currently displaying the cube you are validating, click Open and choose the cube.

  3. In the left area, find the dimension that contains the level, and expand that dimension.

  4. Drag and drop the level to the Rows box.

In this pivot table, look for the following items:

  • Make sure that the level has the correct number of members.

    If the level has duplicate members with the same name, see the section Duplicate Member Names.

    If a member is missing, use the Management Portal to find the source records that should have been associated with this member. Make sure that the source property is in the expected form needed by your source expression.

  • Make sure that the member names are in the correct form, as needed by the business users.

  • Make sure that the null member, if any, has a suitable name, as needed by the business users.

  • Make sure that the members are sorted in the desired order.

    If not, see the preceding section.

  • If the level uses ranges, do the following to validate that the level is not dropping any records because of errors with range ends:

    1. Click the Pivot Options button .

    2. In the Row Options area, click Show Totals and then click OK.

    The total line shows the total of the values above it. This total should equal the total number of records in the cube (unless you do not want it to do so). If it does not, examine the ranges carefully to find any gaps. For example, consider the following pivot table (for a cube that contains 10000 patients):

    generated description: range expression defect

    This version of the Age Group level uses the following ranges:

    generated description: range expression defect setup

    Patients who are 29, 30, 59, or 60 are not included in any member in this incorrectly defined level.

Tip:

In the Analyzer, you can access the generated MDX query for a pivot table (to do so, click the Query Text button ). Then you can save that query to a text file and rerun the query later programmatically. This technique enables you to revalidate the model again later, if the data has changed. Sometimes new data contains values that you did not consider when creating the model.

For information on running queries programmatically, see Implementing InterSystems Business Intelligence.

Another useful tool is the %AnalyzeMissing() method of %DeepSee.UtilsOpens in a new tab. This method analyzes every non-computed level, measure, and relationship within the given cube and return the number of facts that have no value for each. For example:

Do ##class(%DeepSee.Utils).%AnalyzeMissing("patients")
 
-----------------------------------------------------------------------------
    Level                                                   #Missing %Missing
Lvl [AgeD].[H1].[Age]                                              0    0.00%
Lvl [AgeD].[H1].[Age Bucket]                                       0    0.00%
Lvl [AgeD].[H1].[Age Group]                                        0    0.00%
Lvl [AllerD].[H1].[Allergies]                                    388   38.80%
Lvl [AllerSevD].[H1].[Allergy Severities]                        388   38.80%
Lvl [BirthD].[H1].[Date]                                           0    0.00%
Lvl [BirthD].[H1].[Decade]                                         0    0.00%
Lvl [BirthD].[H1].[Period]                                         0    0.00%
Lvl [BirthD].[H1].[Quarter Year]                                   0    0.00%
Lvl [BirthD].[H1].[Year]                                           0    0.00%
Lvl [BirthQD].[H1].[Month]                                         0    0.00%
Lvl [BirthQD].[H1].[Quarter]                                       0    0.00%
Lvl [BirthTD].[H1].[Birth Time]                                    0    0.00%
Lvl [ColorD].[H1].[Favorite Color]                               221   22.10%
Lvl [DiagD].[H1].[Diagnoses]                                     831   83.10%
Lvl [DocD].[H1].[Doctor]                                         159   15.90%
Lvl [DocD].[H1].[Doctor Group]                                   271   27.10%
Lvl [GenD].[H1].[Gender]                                           0    0.00%
Lvl [HomeD].[H1].[City]                                            0    0.00%
Lvl [HomeD].[H1].[ZIP]                                             0    0.00%
Msr [Measures].[Age]                                               0    0.00%
Msr [Measures].[Allergy Count]                                   388   38.80%
Msr [Measures].[Avg Age]                                           0    0.00%
Msr [Measures].[Avg Allergy Count]                               388   38.80%
Msr [Measures].[Avg Enc Count]                                     0    0.00%
Msr [Measures].[Avg Test Score]                                  200   20.00%
Msr [Measures].[Encounter Count]                                   0    0.00%
Msr [Measures].[Test Score]                                      200   20.00%
Lvl [PatGrpD].[H1].[Patient Group]                                 0    0.00%
Lvl [PatGrpD].[H1].[Tested]                                        0    0.00%
Lvl [ProfD].[H1].[Industry]                                      564   56.40%
Lvl [ProfD].[H1].[Profession]                                    564   56.40%
 
    TOTAL FACTS                                                1,000
FeedbackOpens in a new tab