1 Basic Elements

This chapter describes the basic building blocks of the OLAP expression syntax. It contains these topics:

1.1 Dimensional Object Names

The naming conventions for dimensional objects follow standard Oracle naming rules. All names are case-insensitive.

1.1.1 Syntax

owner.{ cube | dimension | table }.{ measure | column | attribute } 

Table 1-1 Naming Conventions for Dimensional Objects

Convention Quoted ID Unquoted ID

Initial Character

Any character.

Alphabetic character from the database character set.

Other Characters

All characters, punctuation marks, and spaces are permitted.

Double quotation marks and nulls (\0) are not permitted.

Alphanumeric characters from the database character set and underscore (_) are permitted.

The dollar sign ($) and pound sign (#) are permitted but not recommended.

Double quotation marks and nulls (\0) are not permitted.

Reserved Words

Permitted but not recommended.

Not permitted.

1.1.2 Examples

GLOBAL.UNITS_CUBE.SALES identifies the SALES measure in the Units Cube.

TIME.DIM_KEY and TIME.LEVEL_NAME identify columns in the Time view.

TIME.CALENDAR identifies the CALENDAR hierarchy in the Time dimension.

TIME.CALENDAR.CALENDAR_YEAR identifies the CALENDAR_YEAR level of the CALENDAR hierarchy in the Time dimension.

GLOBAL.UNITS_FACT.MONTH_ID identifies a foreign key column in the UNITS_FACT table.

TIME_DIM.CALENDAR_YEAR_DSC identifies a column in the TIME_DIM table.

1.2 Dimensional Data Types

Table 1-2 describes the data types that can be used for cubes and measures.

Table 1-2 Dimensional Data Types

Data Type Description

BINARY_DOUBLE

A 64-bit floating number. A BINARY_DOUBLE value requires 9 bytes.

BINARY_FLOAT

A 32-bit floating number. A BINARY_FLOAT value requires 5 bytes.

CHAR (size [BYTE|CHAR])

A fixed length character string with a length of size characters or bytes. The size can range from 1 to 2000.

DATE

A valid date in the range from January 1, 4712 BC to December 31, 9999 CE. It contains the datetime fields YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND. It does not have fractional seconds or a time zone. The default format is determined explicitly by the NLS_DATE_FORMAT parameter and implicitly by the NLS_TERRITORY parameter. A DATE value requires 7 bytes.

DECIMAL (p,s)

A decimal number with precision p and scale s represented as a NUMBER data type.

FLOAT [(p)]

A subtype of NUMBER with precision p. A FLOAT is represented internally as NUMBER. The precision can range from 1 to 126 binary digits. A FLOAT value requires from 1 to 22 bytes.

INTEGER

A whole number represented as a NUMBER data type with a scale of 0.

INTERVAL DAY[(day_precision)] TO SECOND[(second_precision)]

A period of time in days, hours, minutes, and seconds. The day precision is the maximum number of digits in the DAY datetime field. The default is 2. The second precision is the number of digits in the fractional part of the SECOND field. The default value is 6. Both day and second precision can have a value from 0 to 9. An INTERVAL DAY TO SECOND value requires 11 bytes.

INTERVAL YEAR[(precision)] TO MONTH

A period of time in years and months. The precision is the number of digits in the YEAR datetime field, which can have a value of 0 to 9. The default precision is 2 digits. An INTERVAL YEAR TO MONTH value requires 5 bytes.

NCHAR[(size)]

A fixed length character string with a length of size characters. The size can range from 1 character to 2000 bytes. The maximum number of characters depends on the national character set, which can require up to four bytes per character.

NUMBER [(p[,s])]

A decimal number with precision p and scale s. The precision can range from 1 to 38. The scale can range from -84 to 127. A NUMBER value requires from 1 to 22 bytes.

NVARCHAR2(size)

A variable length Unicode character string with a maximum length of size characters. The size can range from 1 character to 32,767 bytes. The maximum number of characters depends on the national character set, which can require up to four bytes per character.

TIMESTAMP[(precision)]

A valid date that contains the datetime fields YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND. It does not have a time zone. The precision is the number of digits in the fractional part of the SECOND field, which can have a value of 0 to 9. The default precision is 6 digits. The default format is determined explicitly by the NLS_DATE_FORMAT parameter and implicitly by the NLS_TERRITORY parameter. A TIMESTAMP value requires from 7 to 11 bytes depending on the precision.

TIMESTAMP [(precision)] WITH LOCAL TIME ZONE

A valid date with the same description as TIMESTAMP WITH TIME ZONE with these exceptions:

  • The data is stored in the database with the database time zone.

  • The data is converted to the session time zone when it is retrieved.

  • A TIMESTAMP WITH LOCAL TIME ZONE value requires from 7 to 11 bytes depending on the precision.

TIMESTAMP[(precision)] WITH TIME ZONE

A valid date that contains the datetime fields YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, TIMEZONE_HOUR, and TIMEZONE_MINUTE. The precision is the number of digits in the fractional part of the SECOND field, which can have a value of 0 to 9. The default precision is 6 digits. The default format is determined explicitly by the NLS_DATE_FORMAT parameter and implicitly by the NLS_TERRITORY parameter. A TIMESTAMP WITH TIMEZONE value requires 13 bytes.

VARCHAR2(size [BYTE|CHAR])

A variable length character string with a maximum length of size characters or bytes. The size can range from 1 to 32,767 bytes.

1.3 Operators

An operator manipulates data items and returns a result. Operators manipulate individual data items called operands or arguments. They are represented by special characters or by keywords. Syntactically, an operator appears before an operand, after an operand, or between two operands.

The OLAP Expression Syntax has these types of operators:

For conditional operators, go to "Conditions".

1.3.1 Unary Arithmetic Operators

A unary operator operates on only one operand.

Table 1-3 Unary Operators

Operator Description

+

Positive value

-

Negative value

1.3.1.1 Syntax
operator operand
1.3.1.2 Example

-5 is a negative number.

1.3.2 Binary Arithmetic Operators

A binary operator operates on two operands.

Table 1-4 Binary Operators

Operator Description

+

Add

-

Subtract

*

Multiply

/

Divide

1.3.2.1 Syntax
operand operator operand
1.3.2.2 Examples

Here are two simple examples using numeric literals for the operands.

7 * 2 is 14.

(8/2) + 1 is 5.

This example multiplies the values of the Sales measure by a numeric literal to create a calculated measure named Sales Budget.

UNITS_CUBE.SALES * 1.06

Table 1-5 Multiplication Operator Example

Product Level Sales Sales Budget

Hardware

CLASS

124191336

131642816

Desktop PCs

FAMILY

74556528

79029919

Monitors

FAMILY

3972142

4210470

Memory

FAMILY

5619219

5956372

Modems/Fax

FAMILY

5575726

5910269

CD/DVD

FAMILY

16129497

17097267

Portable PCs

FAMILY

18338225

19438518

The next example creates a calculated measure named Profit by subtracting Cost from Sales.

UNITS_CUBE.SALES - UNITS_CUBE.COST

Product Level Sales Cost Profit

Hardware

CLASS

124191336

116058248

8133088

Desktop PCs

FAMILY

74556528

71937312

2619215

Monitors

FAMILY

3972142

3546195

425947

Memory

FAMILY

5619219

4962527

656692

Modems/Fax

FAMILY

5575726

5162879

412847

CD/DVD

FAMILY

16129497

12510832

3618664

Portable PCs

FAMILY

18338225

17938502

399723

1.3.3 Concatenation Operator

The concatenation operator (||) combines text expressions.

1.3.3.1 Syntax
operand || operand
1.3.3.2 Example

'The date today is: ' || sysdate generates a text string such as The date today is: 23-AUG-06.

The next example concatenates the level name and dimension keys of the Product dimension to create an identifier.

PRODUCT.LEVEL_NAME || ' ' || PRODUCT.DIM_KEY

Level Dim Key Identifier

CLASS

HRD

CLASS HRD

FAMILY

DTPC

FAMILY DTPC

FAMILY

MON

FAMILY MON

FAMILY

MEM

FAMILY MEM

FAMILY

MOD

FAMILY MOD

FAMILY

DISK

FAMILY DISK

FAMILY

LTPC

FAMILY LTPC

1.4 Conditions

A condition specifies a combination of one or more expressions and logical (Boolean) operators. The OLAP Expression Syntax has these types of conditions:

Return Value

NUMBER (0=FALSE, 1=TRUE)

1.4.1 Simple Comparison Conditions

Comparison conditions compare one expression with another.

You can use these comparison operators:

Table 1-6 Simple Comparison Operators

Operator Description

>

Greater than

>=

Greater than or equal to

<

Less than

<=

Less than or equal to

=

Equal to

!= ˆ= <>

Not equal to

1.4.1.1 Syntax
expr { > | >= | < | <= | = | != | ˆ= | <> } expr 
1.4.1.2 Arguments

expr can be any expression.

1.4.1.3 Examples

5 > 3 is true, 4 != 5 is true, 6 >= 9 is false.

1.4.2 Group Comparison Conditions

A group comparison condition specifies a comparison with any or all members in a list or subquery.

You can use these comparison operators:

Table 1-7 Group Comparison Operators

Operator Description

>

Greater than

>=

Greater than or equal to

<

Less than

<=

Less than or equal to

=

Equal to

!= ˆ= <>

Not equal to

ANY SOME

Returns true if one or more values in the list match, or false if no values match.

ALL

Returns true if all values in the list match, or false if one or more values do not match.

1.4.2.1 Syntax
expr
     { = | != | ˆ= | <> | > | < | >= | <= }
     { ANY | SOME | ALL }
     ({ expression_list | subquery })
1.4.2.2 Examples

5 <= ALL (5, 10, 15) is true, 5 <> ANY (5, 10, 15) is true.

1.4.3 Hierarchical Relation Conditions

Hierarchical relation conditions specify the comparison of the relationship of a hierarchy member to itself or to another member of the hierarchy.

1.4.3.1 Syntax
expr1 IS [ NOT ] 
 { PARENT 
 | CHILD 
 | ANCESTOR
 | ROOT_ANCESTOR
 | DESCENDANT
 | LEAF_DESCENDANT
 | RELATIVE
 }
 [ OR SELF ] OF expr2 WITHIN hierarchy
1.4.3.2 Arguments

expr1 is any expression, including a literal or a column, that resolves to a dimension member.

PARENT compares expr1 as the parent of expr2.

CHILD compares expr1 as a child of expr2.

ANCESTOR compares expr1 as an ancestor of expr2.

ROOT_ANCESTOR compares expr1 as the highest-level ancestor of expr2.

DESCENDANT compares expr1 as a descendant of expr2.

LEAF_DESCENDANT compares expr1 as a descendant that has no children of expr2.

RELATIVE compares expr1 as a dimension member that has a parent in common with expr2.

OR SELF compares expr1 as the same dimension member as expr2.

expr2 is any expression, including a literal or a column, that resolves to a dimension member.

hierarchy is the hierarchy to consider when determining the relationship between expr1 and expr2, expressed in the form dimension_name.hierarchy_name, as in PRODUCT.PRIMARY, or owner.dimension_name.hierarchy_name, as in GLOBAL.PRODUCT.PRIMARY.

1.4.3.3 Examples
TIME.DIM_KEY IS CHILD OR SELF OF 'FY2008' WITHIN TIME.FISCAL
'FY2008' IS ROOT_ANCESTOR OR SELF OF TIME.DIM_KEY WITHIN TIME.FISCAL
'MEMORY' IS NOT ANCESTOR OF PRODUCT.DIM_KEY WITHIN GLOBAL.PRODUCT.PRIMARY

1.4.4 Range Conditions

The BETWEEN operator tests whether a value is in a specific range of values. It returns true if the value being tested is greater than or equal to a low value and less than or equal to a high value.

1.4.4.1 Syntax
expr [ NOT ] BETWEEN expr AND expr
1.4.4.2 Example

7 NOT BETWEEN 10 AND 15 is true.

1.4.5 Multiple Conditions

Conjunctions compare a single expression with two conditions.

Table 1-8 Conjunctions

Operator Description

AND

Returns true if both component conditions are true. Returns false if either is false.

OR

Returns true if either component condition is true. Returns false if both are false.

1.4.5.1 Syntax
expr operator condition1 { AND | OR } condition2 
1.4.5.2 Example

5 < 7 AND 5 > 3 is true; 5 < 3 OR 10 < 15 is true.

1.4.6 Negation Conditions

The NOT operator reverses the meaning of a condition. It returns true if the condition is false. It returns false if the condition is true.

1.4.6.1 Syntax
NOT {BETWEEN | IN | LIKE | NULL }
1.4.6.2 Example

5 IS NOT NULL is true; 5 NOT IN (5, 10, 15) is false.

1.4.7 Special Conditions

The IS operator tests for special conditions, such as nulls, infinity and values that are not numbers.

Table 1-9 Special Conditions Operators

Operator Description

IS INFINITE

Returns true if the expression is infinite, or false otherwise. For mappings only.

IS NAN

Returns true if the expression is not a number, or false otherwise. For mappings only.

IS NULL

Returns true if the expression is null, or false otherwise.

1.4.7.1 Syntax
expr IS [ NOT ] NULL
1.4.7.2 Example

13 IS NOT NULL is true.

1.4.8 Pattern-Matching Conditions

The pattern-matching conditions compare character data.

1.4.8.1 LIKE Operators

The LIKE operators specify a test involving pattern matching. Whereas the equality operator (=) exactly matches one character value to another, the LIKE operators can match patterns defined by special pattern-matching ("wildcard") characters.

You can choose from these LIKE operators:

Table 1-10 LIKE Pattern-Matching Operators

Operator Description

LIKE

Uses characters in the input character set.

LIKEC

Uses Unicode complete characters. It treats a Unicode supplementary character as two characters.

LIKE2

Uses UCS2 code points. It treats a Unicode supplementary character as one character.

LIKE4

Uses UCS4 code points. It treats a composite character as one character.

1.4.8.2 Syntax
char1 [ NOT ] ( LIKE | LIKEC | LIKE2 | LIKE4 )
  char2 [ ESCAPE esc_char ]
1.4.8.3 Arguments

char1 is a text expression for the search value.

char2 is a text expression for the pattern. The pattern can contain these wildcard characters:

  • An underscore (_) matches exactly one character (as opposed to one byte in a multibyte character set) in the value.

  • A percent sign (%) can match zero or more characters (as opposed to bytes in a multibyte character set) in the value. A '%' cannot match a null.

esc_char is a text expression, usually a literal, that is one character long. This escape character identifies an underscore or a percent sign in the pattern as literal characters instead of wildcard characters. You can also search for the escape character itself by repeating it. For example, if @ is the escape character, then you can use @% to search for % and @@ to search for @.

1.4.8.4 Examples

'Ducks' LIKE 'Duck_' and 'Ducky' LIKE 'Duck_' are true.

'Duckling' LIKE 'Duck_' is false.

'Duckling' LIKE 'Duck%' is true.

1.5 Literal Expressions

The OLAP Expression Syntax has three types of literal expressions: strings, numbers, and null. Other data types must be created using conversion functions such as TO_DATE.

The terms text literal, character literal, and string are used interchangeably. They are always enclosed in single quotes to distinguish them from object names.

1.5.1 Examples

'A Literal Text String'

'A Literal Text String with ''Quotes '''

'A Literal Text String
That Crosses Into a Second Line'

2

2.4

+1

-1

NULL

1.6 CASE Expressions

CASE expressions let you use IF... THEN... ELSE logic in expressions.

In a simple case expression, CASE searches for the first WHEN... THEN pair for which expr equals comparison_expr, then it returns return_expr. If none of the WHEN... THEN pairs meet this condition, and an ELSE clause exists, then CASE returns else_expr. Otherwise, CASE returns NULL.

In a searched CASE expression, CASE searches from left to right until it finds an occurrence of condition that is true, and then returns return_expr. If no condition is found to be true, and an ELSE clause exists, CASE returns else_expr. Otherwise, CASE returns NULL.

1.6.1 Return Value

Same as the else_expression argument

1.6.2 Syntax

CASE { simple_case_expression
     | searched_case_expression
     }
     [ ELSE else_expression ]
     END

simple_case_expression::=
     expr WHEN comparison_expr
          THEN return_expr
          [ WHEN comparison_expr
            THEN return_expr ]...

searched_case_expression::=
     WHEN condition THEN return_expr
     [ WHEN condition THEN return_expr ]...

1.6.3 Arguments

expr is the base expression being tested.

comparison_expr is the expression against which expr is being tested. It must be the same basic data type (numeric or text) as expr.

condition is a conditional expression.

return_expr is the value returned when a match is found or the condition is true.

1.6.4 Examples

This statement returns Single Item or Value Pack depending on whether the PACKAGE attribute of the PRODUCT dimension is null or has a value:

CASE PRODUCT.PACKAGE WHEN NULL THEN 'Single Item'  ELSE 'Value Pack'END
Product Package Category

1.44MB External 3.5" Diskette

Executive

Value Pack

1GB USB Drive

--

Single Item

512MB USB Drive

--

Single Item

56Kbps V.90 Type II Modem

Executive

Value Pack

56Kbps V.92 Type II Fax/Modem

Laptop Value Pack

Value Pack

Deluxe Mouse

Executive

Value Pack

Envoy Ambassador

--

Single Item

Envoy Executive

Executive

Value Pack

Envoy External Keyboard

Executive

Value Pack

Envoy Standard Laptop

Value Pack

Value Pack

External - DVD-RW - 8X

Executive

Value Pack

External 101-key keyboard

Multimedia

Value Pack

External 48X CD-ROM

--

Single Item

Internal - DVD-RW - 6X

Multimedia

Value Pack

The next statement increases the unit price by 20%, truncated to the nearest dollar, if the difference between price and cost is less than 10%. Otherwise, it returns the current unit price.

CASE   
  WHEN PRICE_CUBE.UNIT_PRICE < PRICE_CUBE.UNIT_COST * 1.1 
  THEN TRUNC(PRICE_CUBE.UNIT_COST * 1.2)  ELSE PRICE_CUBE.UNIT_PRICE
END
Product Cost Price

1GB USB Drive

483.55

546.83

512MB USB Drive

234.69

275.91

56Kbps V.90 Type II Modem

135.72

158.58

56Kbps V.92 Type II Fax/Modem

95.01

111.08

Envoy Ambassador

2686.01

2850.88

Envoy Executive

2799.80

2943.96

Envoy Standard

1933.82

1921.62

External - DVD-RW - 8X

263.83

300.34

External 48X CD-ROM

223.11

254.15

Internal - DVD-RW - 6X

134.46

160.18

Internal 48X CD-ROM

108.32

127.54

Internal 48X CD-ROM USB

46.00

68.54

Monitor- 17"Super VGA

228.53

269.70

Monitor- 19"Super VGA

445.04

504.84

Sentinel Financial

1685.72

1764.14

Sentinel Multimedia

1849.17

1932.54

Sentinel Standard

1572.98

1610.53

The next example creates a Sales Budget calculated measure by multiplying Sales from the previous year by 1.06 for a 6% increase. The detail levels of all dimensions are excluded from the calculation. The Budget is projected only using data from 2006 or later.

CASE 
   WHEN TIME.END_DATE >= TO_DATE('01-JAN-2006') 
      AND TIME.LEVEL_NAME IN ('CALENDAR_YEAR', 'CALENDAR_QUARTER') 
      AND PRODUCT.LEVEL_NAME != 'ITEM' 
      AND CUSTOMER.LEVEL_NAME IN ('TOTAL', 'REGION', 'WAREHOUSE')
   THEN TRUNC(LAG(UNITS_CUBE.SALES, 1) OVER HIERARCHY 
      (TIME.CALENDAR BY ANCESTOR AT LEVEL TIME.CALENDAR.CALENDAR_YEAR 
      POSITION FROM BEGINNING) * 1.06)
   ELSE NULL
END 
Product Time Sales

Hardware

Q1.05

28172590

Hardware

Q2.05

34520379

Hardware

Q3.05

29466573

Hardware

Q4.05

32031795

Hardware

Q1.06

32711891

Hardware

Q2.06

33637473

Hardware

Q3.06

29227635

Hardware

Q4.06

31319881

Hardware

Q1.07

--

Hardware

Q2.07

--

Hardware

Q3.07

--

Hardware

Q4.07

--

1.7 Qualified Data References (QDRs)

Qualified data references (QDRs) limit a dimensional object to a single member in one or more dimensions for the duration of a query.

1.7.1 Syntax

expression [ qualifier [ , qualifier]... ]

qualifier::=

dimension_id = member_expression

Note: The outside square brackets shown in bold are part of the syntax. In this case, they do not indicate an optional argument.

1.7.2 Arguments

expression is a dimensional expression, typically the name of a measure.

dimension_id is a cube dimension of expression.

member_expression resolves to a single member of dimension_id.

1.7.3 Examples

global.sales[global.time = 'CY2007'] returns Sales values for the year 2007.

sales[customer = 'US', time = 'CY2007'] returns Sales values only for the United States in calendar year 2007.