<level>

Details

The <level> element has the following contents:

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 Studio) 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;"