<level>
Details
The <level> element has the following contents:
Attribute | Purpose |
---|---|
name, displayName, description, disabled | See Common Attributes in a Cube. |
sourceProperty, sourceExpression | Specify one of these attributes. The syntax is almost the same as you would use for Property or Expression in the Architect; see Defining the Source Values for a Dimension or Level. Notes:
For the requirements for levels within a time or age dimension, see Defining a Time Level and Defining an Age Level. For levels in an NLP dimension, sourceProperty and sourceExpression are ignored, because these levels use a different mechanism to specify the source values. See Using Text Analytics in Cubes. |
timeFunction | Only for levels within a time or age dimension. See Defining a Time Level and Defining an Age Level. |
timeOffset | (Optional) Only for levels within a time dimension. See Specifying a Date Offset. |
timeFormat | (Optional) Only for levels within a time dimension. See Specifying a Time Format in Details of Defining Levels. |
list | (Optional) If this attribute is "true" then the source value is expected to be a list, and each item in the list becomes a member of this level.
The default is "false". By default, the list is expected to be in $LIST format. To use a string consisting of a character-delimited list, specify listDelimiter.A list-based level cannot have parent levels or child levels. |
listDelimiter | (Optional) Specifies the delimiter used to separate items in the list that is used as the source data for the level. Use this if the list is a character-separated list. |
nullReplacement | (Optional) Specifies the string to use as the member name if the source data for this level is null. For example, specify nullReplacement="No City". |
rangeExpression | (Optional) For numeric data, this specifies how to assign numeric values to bins (each bin is a member of this level). For other data, this specifies replacement values. This attribute also controls the default order of the members of this level. See the subsections Defining a Basic Range Expression and Defining a Compact Multi-Range Expression. |
useDisplayValue | (Optional) For properties that have values for the DISPLAYLIST and VALUELIST parameters, this attribute specifies which value to include in the index. If this attribute is "false" (the default), the system uses the value given by VALUELIST; if this attribute is "true", the system uses the value given by DISPLAYLIST.
This option is ignored if you specify linkClass and linkProperty. |
sort | (Optional) Specifies how to sort members of this level by default. For a level in a time dimension, specify "asc" or "desc"
For a level in a data dimension, specify "asc", "asc numeric", "desc", or "desc numeric" |
linkClass, linkProperty | (Optional) See Linking to Another Table. |
factName | (Optional) Name, in the generated fact table, for the column that corresponds to this level. If this attribute is null, the system generates a name. This option does not apply to time levels or NLP levels.
See the comments for factName for <measure>. |
factNumber | Internal ID assigned to this level. Required if namedFactNums is "true" for the cube. |
dependsOn | (Optional) Specifies the level (or relationship) on which this level has a dependency. Specify the full MDX identifier of the level (or relationship) . Or specify a comma-separated list of MDX level (or relationship) identifiers. See Defining Dependencies Between Levels in Different Hierarchies. This attribute is completely unrelated to the DependsOn compiler keyword. |
useAsFilter | (Optional) Specifies whether the level can be used as a filter control in the dashboard. If this attribute is "true" (the default), users can select this level when adding a filter control; if this attribute is "false", the level is not listed as an option. The purpose of this option is only to reduce the number of choices seen in the Dashboard Editor, and there is no effect on the Analyzer or on the engine. |
factSelectivity | (Optional, not used by the system) Value to override the generated selectivity of the property in the generated level table. Business Intelligence queries do not use this parameter. This option is intended for cases where SQL is used directly against the level table and the generated selectivity needs to be overridden.
Specify a positive value less than or equal to 1. For details, see SetFieldSelectivity() in %SYSTEM.SQLOpens in a new tab. |
hidden | (Optional) If hidden="true" then the level is defined and can be used in queries, but the Analyzer does not list the level as available for use. The default is "false". |
<member> | (Optional) You can include any number of <member> elements, each of which defines a member of the level. |
<property> | (Optional) You can include any number of <property> elements, each of which defines a property of the level. |
Examples
For example:
<level name="ZIP" sourceProperty="HomeCity.PostalCode" />
For another example, the following level definition also defines a property for that level:
<level name="City" sourceProperty="HomeCity.Name">
<property name="Population" sourceProperty="HomeCity.Population" />
Linking to Another Table
Within a <level>, <measure>, or <property> element, the linkClass and linkProperty attributes enable you to use a property in another class, a class that you cannot access via dot syntax. To use the link feature, you must be able to look up a record in the other class by the ID of the record.
The feature works as follows:
-
Specify sourceProperty or sourceExpression as a value that gives the ID of the desired record in the other class.
-
Specify linkClass as the complete package and class name for the other class.
-
Specify linkProperty as the property in the other class on which you want to base the level that you are defining.
For example, you could add the following to the Hole Foods sample:
<level name="Product" sourceProperty ="Product" linkClass="HoleFoods.Product" linkProperty="Name" />
If the external class linkClass and a property linkProperty are defined, the system runs a query against the external class to fetch the value of the given property from that class, for the record whose ID equals the given sourceProperty or sourceExpression.
Specifying a Date Offset
In some cases, you may need a time level to match a corporate financial calendar that does not start on 1 January. For example, in many companies, the financial year starts 1 Oct. Consider the following pivot table:
In this case, the member FY 2005 consists of records of sales between 1 October 2004 and 30 September 2005, inclusive.
To create a level like this, specify the timeOffset and timeFormat attributes of the level (which must be within a time-type dimension). This section discusses the timeOffset attribute; the next section discusses timeFormat.
The timeOffset attribute specifies an amount of time to add to the source values used in this level; this amount of time can be negative or positive. The system uses this at cube build time.
For timeOffset, specify an amount of time by specifying a string of the following form:
#y#m#d
Where # is a number, #y represents an amount of time in years, #m represents an amount of time in months, and #d represents an amount of time in days. If you omit an element, the system uses zero in its place. For example, the string 3m15d represents three months and 15 days.
The most common value for timeOffset is -3m, which you use if the fiscal year starts in 1 October of the previous year. If timeOffset="-3m", the system subtracts three months to each time value used in this level. For example, for this level, the date 1 Jan 2010 is converted to 1 Oct 2009.
Other levels are unaffected, even within the same dimension. This means that you can also define more granular levels that display the actual dates. For an example, see Handling a Calendar That Has a Date Offset.
If there are two levels that use the same timeFunction attribute but different timeOffset attributes, Architect recognizes that these are two distinct values for the same fact. They are automatically mapped to two different columns in the fact table and each is assigned a separate factNumber.
Defining a Basic Range Expression
For rangeExpression, the basic syntax is as follows:
value_or_range:new_value;value_or_range:new_value; ... ;
Here new_value is a single value, which the system treats as a string and uses as the name of a level member. Also, value_or_range is any of the following:
-
A single value such as 5 or Louisiana
-
A range in the form (value1,value2)
-
A range in the form [value1,value2]
-
A range in the form (value1,value2]
-
A range in the form [value1,value2)
In these expressions, value1 and value2 are numeric values or null (that is, omitted from the expression). The left and right parentheses indicate that the range is not inclusive at the given endpoint. The left and right square brackets indicate that the range is inclusive at the given endpoint. For example, (45,49] represents all values greater than 45 and less than or equal to 49.
Defining a Compact Multi-Range Expression
In some cases, it is necessary to define many ranges for a given level, which can be a tedious process. Also the resulting range expression (when viewed in an IDE) is difficult to read. In such cases, you may want to use the following alternative syntax for the value of rangeExpression:
rangeExpression="[start:increment:end]:replacement;"
Or use the appropriate parenthesis in place of either square bracket (details below).
This syntax generates a series of ranges.
Here, start is the numeric start value of the first range, end is the numeric end value of the last range, and increment is the numeric value by which the ranges are defined. The first range extends from start to start plus increment, and so on.
Also, replacement is an expression that is used as the replacement value. Within replacement, you can use the following elements:
-
%1 — is replaced with the start value of the range
-
%2 — is replaced with the end value of the range
-
$$$eval(expression) — causes the system to evaluate the contained expression. For example: $$$eval(%2-1) returns the end value of the range minus one
The square brackets or parentheses affect how the start and end values are treated, in the generated start and end ranges:
-
A square bracket causes the value to be included in the range.
-
A parenthesis causes the value to be excluded from the range.
At the intermediate range boundaries, the boundary value is always assigned to the upper range, rather than the lower range. That is, for intermediate ranges, the opening bracket is always [ and the closing bracket is always ).
Consider the following range expression:
rangeExpression="[0:30:90]:%1 to $$$eval(%2-1);"
This generates the same members as the following longer form:
rangeExpression="[0,30):0 to 29;[30,60):30 to 59;[60,90]:60 to 90;"
If the value end - start is not an integer multiple of increment , the last range extends beyond end. For example, consider the following range expression:
rangeExpression="[0:30:100]:%1 to $$$eval(%2-1);"
This generates the same members as the following longer form:
rangeExpression="[0,30):0 to 29;[30,60):30 to 59;[60,90):60 to 90;[90,119]:90 to 119;"