This page describes how to define properties in a Business Intelligence cube.
Time and age dimensions cannot contain properties.
Also see Accessing the Samples Shown in This Book.
Adding a Property
To add a property to an existing level:
Click either the level or an property within that level, in the Model Viewer.
This action indicates where the property is to be added:
If you click the level, the new property will be added after all the other properties in this level.
If you click a property, the new property will be added immediately before that property.
Click Add Element.
The system displays a dialog box.
For Enter New Item Name, type a property name.
Note that there are reserved names for properties, unlike for other kinds of cube elements. For details, see Names for Model Elements.
Select the property in the Model Viewer.
In the Details Area, specify a value for Property or Expression. See Defining the Source Values for a Dimension or Level and Details for Source Expressions.
Alternatively, drag a class property from the Class Viewer and drop it onto a level in the Model Viewer. Then make changes if needed in the Details Area.
Also see Other Common Options.
Defining a Property for a List-Based Level
If the associated level is list-based, you must define the property as follows:
Specify a value for Expression.
In the expression, refer to the %value variable. This variable contains the value of the associated list element.
For example, consider the Allergy Severities level in the Patients cube. This level has a property that controls the sort order of the members of this level. This property (SeveritySort) is defined by the following expression:
This executes the GetSeveritySort() method in the cube class and the allergy severity (a string) as a argument. The method returns an integer.
Specifying a Format String
In Studio, you can specify a format string, which enables you to specify the display format for the property. You can override this formatting in the Analyzer (or in manually written MDX queries). For details, see <property>.
Getting a Property Value at Runtime
In some cases, you can define a property so that its value is retrieved at runtime from the appropriate source table. The requirements are as follows:
For the property, select the Get value at run time option.
For the parent level (the level that contains the property), the source property or source expression of that level must evaluate to an ID.
The system assumes that (at least for this level), the source data is normalized. That is, for the level, the data is in a different table and the source table contains a link to that table.
This requirement means that, by default, the member names for this level are IDs, which is not generally suitable for your users. In this case, also configure a property of this level for use as display names for the members. This can be the same property or a different property.
See Using Property Values as the Member Names.
Changing the Order of Properties in a Level
To change the order of properties in a level:
To move a property up in the level, click the up arrow in the row for that level.
To move a property down in the level, click the down arrow in the row for that level.
The order of properties in a level can affect the sort order of the members of the level. See Specifying the Sort Order for Members of a Data Level.
Specifying the Column Names in the Dimension Tables
When you compile a cube class, the system generates a fact table class and related classes for the dimensions. When you build a cube, the system populates these tables, which are described in Details for the Fact and Dimension Tables. The properties for a level are stored in the corresponding dimension table.
By default, the system generates the names of the columns in the fact table, but you can specify the column names to use instead. To do so, specify a value for the Field name in level table option for each property. Take care to use unique names.
For Field name in fact table, be sure not to use an SQL reserved word. For a list of the SQL reserved words, see Reserved Words. The name must start with either a letter or a percent sign (%). If the first character is %, the second character must be Z or z. For more details on restrictions, see Class Members. Also, do not use fact or listing, in any combination of lowercase and uppercase characters.
Special Uses for Properties
By default, properties have no effect on the members of the level to which they belong. You can, however, use properties to modify the members in the following ways (which you can combine):
You can use the property values as the member names, overriding the default member names defined by the level definition.
To do this, use the Use as member names option and (optionally) Get value at run time options; see Using Property Values as the Member Names.
Note that if you select Use as member names, and if the source value is null, the system instead uses the appropriate null replacement string; this is either the null replacement string for the level (if defined) or for the cube (if defined). See Specifying the Null Replacement String for a Level and Specifying Cube Options.
You can use the property values to specify the default sort order of the members within the level.
To do this, use the Sort members by property value option; see Specifying the Sort Order for Members of a Data Level.
The Sort members by property value option has no effect if the level uses a range expression.
You can use the property values as tooltips for the members. This is useful when you want to use short member names, for reasons of space, but also need to provide a fuller description of each member. To use a property this way, specify the isDescription attribute as "true". See <property> in Reference Information for Cube Classes.
Within a level, member names are not required to be unique, as noted in the section Defining Member Keys and Names. In a well-defined cube, however, each member of a given level does have a unique key. When a user creates a query in the Analyzer, the system automatically uses the member keys instead of the names. The user can expand the list of members and separately drag and drop different members that have the same names. For convenience of the users, InterSystems suggests that you also add a level property whose value is the key. For such a property, simply base the property on the same source property or source expression that the level uses. The users can then display this property in order to determine the unique identifier that the system uses for the member.