2 OLAP Functions

Return Value

NUMBER

Syntax

AVERAGE_RANK ( ) OVER (rank_clause)

rank_clause::=

{ DIMENSION dimension_id | HIERARCHY hierarchy_id }
  ORDER BY order_by_clause [, order_by_clause]...
  [ WITHIN { LEVEL 
           | PARENT 
           | ANCESTOR AT { DIMENSION LEVEL dim_level_id
                         | HIERARCHY LEVEL hier_level_id
                         }
           }
  ]

order_by_clause::=

expression [ASC | DESC] [NULLS {FIRST | LAST}] 

Arguments

dimension_id

The dimension over which the values are calculated using the default hierarchy.

hierarchy_id

The hierarchy over which the values are calculated. If dimension_id is used instead, the default hierarchy is used.

ORDER BY

Provides the basis for the ranking. You can provide additional ORDER BY clauses to break any ties in the order.

expression

Provides the values to use as the basis for the rankings.

ASC | DESC

Sorts the ranking from smallest to largest (ascending) or from largest to smallest (descending).

NULLS {FIRST | LAST}

Determines whether members with null values are listed first or last.

WITHIN

Selects a set of related dimension members to be ranked.

LEVEL ranks all members at the same level.

PARENT ranks members at the same level with the same parent.

ANCESTOR ranks all members at the same level and with the same ancestor at a specified level.

dim_level_id

The name of a level of dimension_id.

hier_level_id

The name of a level of hierarchy_id.

Example

This example ranks time periods within a calendar year by Unit Cost. Notice that no month is ranked 7, because two months (JAN-02 and JUL-02) have the same value and the same rank (6.5).

AVERAGE_RANK() OVER (HIERARCHY TIME.CALENDAR ORDER BY PRICE_CUBE.UNIT_COST DESC NULLS LAST WITHIN ANCESTOR AT DIMENSION LEVEL TIME.CALENDAR_YEAR)

Related Topics

DENSE_RANK, RANK, ROW_NUMBER

Return Value

NUMBER

Syntax

AVG (value_expr) OVER (window_clause)

window_clause::=

[ { DIMENSION dimension_id | HIERARCHY hierarchy_id } ]
    BETWEEN preceding_boundary | following_boundary 
     [WITHIN { LEVEL
             | PARENT
             | GREGORIAN {YEAR | QUARTER | MONTH | WEEK}
             | ANCESTOR AT { DIMENSION LEVEL dim_level_id
                           | HIERARCHY LEVEL hier_level_id 
                           }
             }
     ]

preceding_boundary::=

{UNBOUNDED PRECEDING | expression PRECEDING} AND 
   { CURRENT MEMBER 
   | expression {PRECEDING | FOLLOWING}
   | UNBOUNDED FOLLOWING
   }

following_boundary::=

{CURRENT MEMBER | expression FOLLOWING} AND
  { expression FOLLOWING 
  | UNBOUNDED FOLLOWING
  }

Arguments

value_expr

A dimensional expression whose values you want to calculate.

dimension_id

The Time dimension over which the values are calculated using the default hierarchy.

hierarchy_id

The hierarchy over which the values are calculated. If dimension_id is used instead, the default hierarchy is used.

dim_level_id

The name of a level of dimension_id.

hier_level_id

The name of a level of hierarchy_id.

boundaries

The preceding_boundary and following_boundary identify a range of time periods within the group identified by the dimension or hierarchy.

UNBOUNDED starts with the first period or ends with the last period of the group.

CURRENT MEMBER starts or ends the calculation at the current time period.

expression

A numeric value identifying a period at a particular distance from the current time period that starts or ends the range.

WITHIN subclause

Identifies the range of time periods used in the calculation. Following are descriptions of the keywords.

Example

This example calculates a cumulative average within each parent. The selection of data shows the cumulative averages for quarters within the 2005 and 2006 calendar years.

AVG(GLOBAL.UNITS_CUBE.UNITS) OVER (HIERARCHY GLOBAL.TIME.CALENDAR BETWEEN UNBOUNDED PRECEDING AND CURRENT MEMBER WITHIN PARENT)

Related Topics

COUNT, MAX, MIN, SUM

Return Value

NUMBER

Syntax

COUNT (value_expr) OVER (window_clause)

window_clause::=

{ DIMENSION dimension_id | HIERARCHY hierarchy_id }
  BETWEEN preceding_boundary AND following_boundary 
   [WITHIN { LEVEL
           | PARENT
           | GREGORIAN {YEAR | QUARTER | MONTH | WEEK}
           | ANCESTOR AT { DIMENSION LEVEL dim_level_id
                         | HIERARCHY LEVEL hier_level_id 
                         }
           }
   ]

preceding_boundary::=

{UNBOUNDED PRECEDING | expression PRECEDING} AND 
   { CURRENT MEMBER 
   | expression {PRECEDING | FOLLOWING}
   | UNBOUNDED FOLLOWING
   }

following_boundary::=

{CURRENT MEMBER | expression FOLLOWING} AND
  { expression FOLLOWING 
  | UNBOUNDED FOLLOWING
  }

Arguments

value_expr

A dimensional expression whose values you want to calculate.

dimension_id

The Time dimension over which the values are calculated using the default hierarchy.

hierarchy_id

The hierarchy over which the values are calculated. If dimension_id is used instead, the default hierarchy is used.

dim_level_id

The name of a level of dimension_id.

hier_level_id

The name of a level of hierarchy_id.

boundaries

The preceding_boundary and following_boundary identify a range of time periods within the group identified by the dimension or hierarchy.

UNBOUNDED starts with the first period or ends with the last period of the group.

CURRENT MEMBER starts or ends the calculation at the current time period.

expression

A numeric value identifying a period at a particular distance from the current time period that starts or ends the range.

WITHIN subclause

Identifies the range of time periods used in the calculation. Following are descriptions of the keywords.

Example

This example tallies the number of time periods at the same level and the same year up to and including the current time period. The selected data displays the number of each month in the year.

COUNT(GLOBAL.UNITS_CUBE.UNITS) OVER (HIERARCHY GLOBAL.TIME.CALENDAR BETWEEN UNBOUNDED PRECEDING AND CURRENT MEMBER WITHIN ANCESTOR AT DIMENSION LEVEL GLOBAL.TIME.CALENDAR_YEAR)

Related Topics

AVG, MAX, MIN, SUM

Return Value

NUMBER

Syntax

DENSE_RANK ( ) OVER (rank_clause)

rank_clause::=

{ DIMENSION dimension_id | HIERARCHY hierarchy_id }
  ORDER BY order_by_clause [, order_by_clause]...
  [ WITHIN { LEVEL 
           | PARENT 
           | ANCESTOR AT { DIMENSION LEVEL dim_level_id
                         | HIERARCHY LEVEL hier_level_id
                         }
           }
  ]

order_by_clause::=

expression [ASC | DESC] [NULLS {FIRST | LAST}] 

Arguments

dimension_id

The dimension over which the values are calculated using the default hierarchy.

hierarchy_id

The hierarchy over which the values are calculated. If dimension_id is used instead, the default hierarchy is used.

ORDER BY

Provides the basis for the ranking. You can provide additional ORDER BY clauses to break any ties in the order.

expression

Provides the values to use as the basis for the rankings.

ASC | DESC

Sorts the ranking from smallest to largest (ascending) or from largest to smallest (descending).

NULLS {FIRST | LAST}

Determines whether members with null values are listed first or last.

WITHIN

Selects a set of related dimension members to be ranked.

LEVEL ranks all members at the same level.

PARENT ranks members at the same level with the same parent.

ANCESTOR ranks all members at the same level and with the same ancestor at a specified level.

dim_level_id

The name of a level of dimension_id.

hier_level_id

The name of a level of hierarchy_id.

Example

This example ranks time periods within a calendar year by Unit Cost, using the default Calendar hierarchy. Notice that although two months (JAN-02 and JUL-02) have the same value and the same rank (6), the ranking continues at 7 for JUN-02.

DENSE_RANK() OVER (DIMENSION "TIME" ORDER BY PRICE_CUBE.UNIT_COST DESC NULLS LAST WITHIN ANCESTOR AT DIMENSION LEVEL TIME.CALENDAR_YEAR)

Related Topics

AVERAGE_RANK, RANK, ROW_NUMBER

Return Value

VARCHAR2

Syntax

HIER_ANCESTOR( 
  [member_expression] [WITHIN]
  {DIMENSION dimension_id | HIERARCHY hierarchy_id}
  {DIMENSION LEVEL dim_level_id | HIERARCHY LEVEL hier_level_id} ) 

Arguments

member_expression

Identifies a dimension member within the hierarchy whose ancestor is returned. If this optional argument is specified, then the result does not vary across dimension members.

dimension_id

The dimension over which the values are calculated using the default hierarchy.

hierarchy_id

The hierarchy over which the values are calculated. If dimension_id is used instead, the default hierarchy is used.

dim_level_id

The level of the ancestor in dimension_id.

hier_level_id

The level of the ancestor in hierarchy_id.

Example

This example returns the ancestor at the Calendar Quarter level for members of the default Calendar hierarchy of the Time dimension.

HIER_ANCESTOR(DIMENSION "TIME" DIMENSION LEVEL TIME.CALENDAR_QUARTER)

The next example returns GOV as the ancestor of the US Department of Labor at the Customer Market Segment level in the Market hierarchy of the Customer dimension.

HIER_ANCESTOR('US DPT LBR' WITHIN HIERARCHY CUSTOMER.MARKET DIMENSION LEVEL CUSTOMER.MARKET_SEGMENT)

Return Value

NUMBER

Syntax

HIER_CHILD_COUNT ( 
   [member_expression] [WITHIN]
   {DIMENSION dimension_id | HIERARCHY hierarchy_id} ) 

Arguments

member_expression

Identifies a single dimension member within the hierarchy used for the calculation. If this optional argument is specified, then the result does not vary across dimension members.

dimension_id

The dimension over which the values are calculated using the default hierarchy.

hierarchy_id

The hierarchy over which the values are calculated. If dimension_id is used instead, the default hierarchy is used.

Example

This example returns the number of children for each member of the default hierarchy of the Time dimension.

HIER_CHILD_COUNT(DIMENSION "TIME")

The next example returns 8 as the number of children for Government within the Market hierarchy of the Customer dimension.

HIER_CHILD_COUNT('GOV' WITHIN HIERARCHY CUSTOMER.MARKET)

Return Value

NUMBER

Syntax

HIER_DEPTH ( 
   [member_expression] [WITHIN]
   {DIMENSION dimension_id | HIERARCHY hierarchy_id} ) 

Arguments

member_expression

Identifies a single dimension member within the hierarchy used for the calculation. If this optional argument is specified, then the result does not vary across dimension members.

dimension_id

The dimension over which the values are calculated using the default hierarchy.

hierarchy_id

The hierarchy over which the values are calculated. If dimension_id is used instead, the default hierarchy is used.

Example

This example returns the depth of each member in the default hierarchy of the Time dimension.

HIER_DEPTH(DIMENSION "TIME")

The next example returns 2 as the depth of Italy in the default Customer hierarchy.

HIER_DEPTH('ITA' WITHIN DIMENSION CUSTOMER)

Return Value

VARCHAR2

Syntax

HIER_LEVEL ( 
   [member_expression] [WITHIN]
   {DIMENSION dimension_id | HIERARCHY hierarchy_id} ) 

Arguments

member_expression

Identifies a single dimension member within the hierarchy used for the calculation. If this optional argument is specified, then the result does not vary across dimension members.

dimension_id

The dimension over which the values are calculated using the default hierarchy.

hierarchy_id

The hierarchy over which the values are calculated. If dimension_id is used instead, the default hierarchy is used.

Example

This example returns the level of each member of the default hierarchy of the Time dimension.

HIER_LEVEL(DIMENSION "TIME")

The next example returns ACCOUNT as the level of Business World in the Market hierarchy of the Customer dimension.

HIER_LEVEL('BUSN WRLD' WITHIN HIERARCHY CUSTOMER.MARKET)

Return Value

NUMBER

Syntax

HIER_ORDER ( 
   [member_expression] [WITHIN]
   {DIMENSION dimension_id | HIERARCHY hierarchy_id} ) 

Arguments

member_expression

Identifies a single dimension member within the hierarchy used for the calculation. If this optional argument is specified, then the result does not vary across dimension members.

dimension_id

The dimension over which the values are calculated using the default hierarchy.

hierarchy_id

The hierarchy over which the values are calculated. If dimension_id is used instead, the default hierarchy is used.

Example

This example orders the values of the Time dimension:

HIER_ORDER(DIMENSION "TIME")

The next example returns 78 as the order number of Business World in the Market hierarchy of the Customer dimension.

HIER_ORDER('BUSN WRLD' WITHIN HIERARCHY CUSTOMER.MARKET)

Return Value

VARCHAR2

Syntax

HIER_PARENT ( 
   [member_expression] [WITHIN]
   {DIMENSION dimension_id | HIERARCHY hierarchy_id} ) 

Arguments

member_expression

Identifies a single dimension member within the hierarchy used for the calculation. If this optional argument is specified, then the result does not vary across dimension members.

dimension_id

The dimension over which the values are calculated using the default hierarchy.

hierarchy_id

The hierarchy over which the values are calculated. If dimension_id is used instead, the default hierarchy is used.

Example

This example returns the parents of all members of the default hierarchy of the Time dimension.

HIER_PARENT(DIMENSION GLOBAL.TIME)

The next example returns EMEA as the parent of Italy within the default hierarchy of the Customer dimension.

HIER_PARENT('ITA' WITHIN DIMENSION CUSTOMER)

Return Value

VARCHAR2

Syntax

HIER_TOP ( 
   [member_expression] [WITHIN]
   {DIMENSION dimension_id | HIERARCHY hierarchy_id} ) 

Arguments

member_expression

Identifies a single dimension member within the hierarchy used for the calculation. If this optional argument is specified, then the result does not vary across dimension members.

dimension_id

The dimension over which the values are calculated using the default hierarchy.

hierarchy_id

The hierarchy over which the values are calculated. If dimension_id is used instead, the default hierarchy is used.

Example

This example returns the top member of the default hierarchy of the Time dimension.

HIER_TOP(DIMENSION "TIME")

The next example returns TOTAL, which is the top member for Europe in the default hierarchy of the Customer dimension.

HIER_TOP('EMEA' WITHIN DIMENSION CUSTOMER)

Return Value

The same data type as the value expression

Syntax

LAG (lag_args) OVER (lag_clause)

lag_args::=

expression, offset [, {default_expression | CLOSEST} ]

lag_clause::=

[ {DIMENSION dimension_id | HIERARCHY hierarchy_id} ]
[ [BY] { LEVEL 
       | PARENT 
       | GREGORIAN {YEAR | QUARTER | MONTH | WEEK | DAY}
       | ANCESTOR AT { DIMENSION LEVEL dim_level_id
                     | HIERARCHY LEVEL hier_level_id
                     }
       }
  [POSITION FROM {BEGINNING | END}] 
] 

Arguments

expression

A dimensional expression whose values you want to calculate.

offset

A numeric expression for the number of periods to count back from the current time period.

default_expression

The value returned when offset does not identify a valid period. This clause is either an expression of any data type or the CLOSEST keyword for the closest match. The closest match is the first member when counting back.

dimension_id

The Time dimension over which the lag is calculated.

hierarchy_id

The hierarchy over which the lag is calculated. Otherwise, the default hierarchy for dimension_id is used.

dim_level_id

The name of a level of dimension_id.

hier_level_id

The name of a level of hierarchy_id.

BY subclause

The BY subclause identifies the range of time periods used when counting the offset. Following are descriptions of the keywords:

Example

This example returns the value from the prior year for each period.

LAG(UNITS_CUBE.UNITS, 1) OVER (HIERARCHY "TIME".CALENDAR ANCESTOR AT DIMENSION LEVEL "TIME".CALENDAR_YEAR)

Related Topics

LAG_VARIANCE, LAG_VARIANCE_PERCENT, LEAD

Return Value

The same data type as the value expression

Syntax

LAG_VARIANCE (lag_args) OVER (lag_clause)

lag_args::=

expression, offset [, {default_expression | CLOSEST} ]

lag_clause::=

[ {DIMENSION dimension_id | HIERARCHY hierarchy_id} ]
[ [BY] { LEVEL 
       | PARENT 
       | GREGORIAN {YEAR | QUARTER | MONTH | WEEK | DAY}
       | ANCESTOR AT { DIMENSION LEVEL dim_level_id
                     | HIERARCHY LEVEL hier_level_id
                     }
       }
  [POSITION FROM {BEGINNING | END}] 
] 

Arguments

expression

A dimensional expression whose values you want to calculate.

offset

A numeric expression for the number of periods to count back from the current time period.

default_expression

The value returned when offset does not identify a valid period. This clause is either an expression of any data type or the CLOSEST keyword for the closest match. The closest match is the first member when counting back.

dimension_id

The Time dimension over which the lag is calculated.

hierarchy_id

The hierarchy over which the lag is calculated. Otherwise, the default hierarchy for dimension_id is used.

dim_level_id

The name of a level of dimension_id.

hier_level_id

The name of a level of hierarchy_id.

BY subclause

The BY subclause identifies the range of time periods used when counting the offset. Following are descriptions of the keywords:

Examples

This example returns the difference in values between the current period and the equivalent period in the prior year.

LAG_VARIANCE (GLOBAL.UNITS_CUBE.UNITS, 1) OVER (HIERARCHY GLOBAL.TIME.CALENDAR ANCESTOR AT DIMENSION LEVEL GLOBAL.TIME.CALENDAR_YEAR)

Related Topics

LAG, LAG_VARIANCE_PERCENT, LEAD

Return Value

NUMBER

Syntax

LAG_VARIANCE_PERCENT (lag_args) OVER (lag_clause)

lag_args::=

expression, offset [, {default_expression | CLOSEST} ]

lag_clause::=

[ {DIMENSION dimension_id | HIERARCHY hierarchy_id} ]
[ [BY] { LEVEL 
       | PARENT 
       | GREGORIAN {YEAR | QUARTER | MONTH | WEEK | DAY}
       | ANCESTOR AT { DIMENSION LEVEL dim_level_id
                     | HIERARCHY LEVEL hier_level_id
                     }
       }
  [POSITION FROM {BEGINNING | END}] 
] 

Arguments

expression

A dimensional expression whose values you want to calculate.

offset

A numeric expression for the number of periods to count back from the current time period.

default_expression

The value returned when offset does not identify a valid period. This clause is either an expression of any data type or the CLOSEST keyword for the closest match. The closest match is the first member when counting back.

dimension_id

The Time dimension over which the lag is calculated.

hierarchy_id

The hierarchy over which the lag is calculated. Otherwise, the default hierarchy for dimension_id is used.

dim_level_id

The name of a level of dimension_id.

hier_level_id

The name of a level of hierarchy_id.

BY subclause

The BY subclause identifies the range of time periods used when counting the offset. Following are descriptions of the keywords:

Examples

This example returns the percent difference in value between the current period and the equivalent period in the prior year.

LAG_VARIANCE_PERCENT (GLOBAL.UNITS_CUBE.UNITS, 1) OVER (HIERARCHY GLOBAL.TIME.CALENDAR ANCESTOR AT DIMENSION LEVEL GLOBAL.TIME.CALENDAR_YEAR)

Related Topics

LAG, LAG_VARIANCE, LEAD

Return Value

The same data type as the value expression

Syntax

LEAD (lead_args) OVER (lead_clause)

lead_args::=

expression, offset [, {default_expression | CLOSEST} ]

lead_clause::=

[ {DIMENSION dimension_id | HIERARCHY hierarchy_id} ]
[ [BY] { LEVEL 
       | PARENT 
       | GREGORIAN {YEAR | QUARTER | MONTH | WEEK | DAY}
       | ANCESTOR AT { DIMENSION LEVEL dim_level_id
                     | HIERARCHY LEVEL hier_level_id
                     }
       }
  [POSITION FROM {BEGINNING | END}] 
] 

Arguments

expression

A dimensional expression whose values you want to calculate.

offset

A numeric expression for the number of periods to count forward from the current time period.

default_expression

The value returned when offset does not identify a valid period. This clause is either an expression of any data type or the CLOSEST keyword for the closest match. The closest match is the first member when counting forward.

dimension_id

The Time dimension over which the lead is calculated.

hierarchy_id

The hierarchy over which the lead is calculated. Otherwise, the default hierarchy for dimension_id is used.

dim_level_id

The name of a level of dimension_id.

hier_level_id

The name of a level of hierarchy_id.

BY subclause

The BY subclause identifies the range of time periods used when counting the offset. Following are descriptions of the keywords:

Examples

This calculation returns the value of the next time period based on calendar quarter. The sample output from this calculation appears in the Next Qtr column.

LEAD (GLOBAL.UNITS_CUBE.UNITS, 1, CLOSEST) OVER (DIMENSION GLOBAL.TIME BY ANCESTOR AT DIMENSION LEVEL GLOBAL.TIME.CALENDAR_QUARTER)

Related Topics

LAG, LEAD_VARIANCE, LEAD_VARIANCE_PERCENT

Return Value

The same data type as the value expression

Syntax

LEAD_VARIANCE (lead_args) OVER (lead_clause)

lead_args::=

expression, offset [, {default_expression | CLOSEST} ]

lead_clause::=

[ {DIMENSION dimension_id | HIERARCHY hierarchy_id} ]
[ [BY] { LEVEL 
       | PARENT 
       | GREGORIAN {YEAR | QUARTER | MONTH | WEEK | DAY}
       | ANCESTOR AT { DIMENSION LEVEL dim_level_id
                     | HIERARCHY LEVEL hier_level_id
                     }
       }
  [POSITION FROM {BEGINNING | END}] 
] 

Arguments

expression

A dimensional expression whose values you want to calculate.

offset

A numeric expression for the number of periods to count forward from the current time period.

default_expression

The value returned when offset does not identify a valid period. This clause is either an expression of any data type or the CLOSEST keyword for the closest match. The closest match is the first member when counting forward.

dimension_id

The Time dimension over which the lead is calculated.

hierarchy_id

The hierarchy over which the lead is calculated. Otherwise, the default hierarchy for dimension_id is used.

dim_level_id

The name of a level of dimension_id.

hier_level_id

The name of a level of hierarchy_id.

BY subclause

The BY subclause identifies the range of time periods used when counting the offset. Following are descriptions of the keywords:

Examples

This calculation returns the difference between the current value and the value of the next time period based on calendar quarter. The sample output from this calculation appears in the Difference column.

LEAD_VARIANCE (GLOBAL.UNITS_CUBE.UNITS, 1, CLOSEST) OVER (DIMENSION GLOBAL.TIME BY ANCESTOR AT DIMENSION LEVEL GLOBAL.TIME.CALENDAR_QUARTER)

Related Topics

LAG, LEAD, LEAD_VARIANCE_PERCENT

Return Value

NUMBER

Syntax

LEAD_VARIANCE_PERCENT (lead_args) OVER (lead_clause)

lead_args::=

expression, offset [, {default_expression | CLOSEST} ]

lead_clause::=

[ {DIMENSION dimension_id | HIERARCHY hierarchy_id} ]
[ [BY] { LEVEL 
       | PARENT 
       | GREGORIAN {YEAR | QUARTER | MONTH | WEEK | DAY}
       | ANCESTOR AT { DIMENSION LEVEL dim_level_id
                     | HIERARCHY LEVEL hier_level_id
                     }
       }
  [POSITION FROM {BEGINNING | END}] 
] 

Arguments

expression

A dimensional expression whose values you want to calculate.

offset

A numeric expression for the number of periods to count forward from the current time period.

default_expression

The value returned when offset does not identify a valid period. This clause is either an expression of any data type or the CLOSEST keyword for the closest match. The closest match is the first member when counting forward.

dimension_id

The Time dimension over which the lead is calculated.

hierarchy_id

The hierarchy over which the lead is calculated. Otherwise, the default hierarchy for dimension_id is used.

dim_level_id

The name of a level of dimension_id.

hier_level_id

The name of a level of hierarchy_id.

BY subclause

The BY subclause identifies the range of time periods used when counting the offset. Following are descriptions of the keywords:

Example

This calculation returns the percent difference between the current value and the value of the next time period based on calendar quarter. The sample output from this calculation appears in the Percent column.

LEAD_VARIANCE_PERCENT (GLOBAL.UNITS_CUBE.UNITS, 1, CLOSEST) OVER (DIMENSION GLOBAL.TIME BY ANCESTOR AT DIMENSION LEVEL GLOBAL.TIME.CALENDAR_QUARTER)

Related Topics

LAG, LEAD, LEAD_VARIANCE

Return Value

NUMBER

Syntax

MAX (value_expr) OVER (window_clause)

window_clause::=

[ { DIMENSION dimension_id | HIERARCHY hierarchy_id } ]
    BETWEEN preceding_boundary | following_boundary 
     [WITHIN { PARENT
             | LEVEL
             | GREGORIAN {YEAR | QUARTER | MONTH | WEEK}
             | ANCESTOR AT { DIMENSION LEVEL dim_level_id
                           | HIERARCHY LEVEL hier_level_id 
                           }
             }
     ]

preceding_boundary::=

{UNBOUNDED PRECEDING | expression PRECEDING} AND 
   { CURRENT MEMBER 
   | expression {PRECEDING | FOLLOWING}
   | UNBOUNDED FOLLOWING
   }

following_boundary::=

{CURRENT MEMBER | expression FOLLOWING} AND
  { expression FOLLOWING 
  | UNBOUNDED FOLLOWING
  }

Arguments

value_expr

A dimensional expression whose values you want to calculate.

dimension_id

The Time dimension over which the values are calculated using the default hierarchy.

hierarchy_id

The hierarchy over which the values are calculated. If dimension_id is used instead, the default hierarchy is used.

dim_level_id

The name of a level of dimension_id.

hier_level_id

The name of a level of hierarchy_id.

boundaries

The preceding_boundary and following_boundary identify a range of time periods within the group identified by the dimension or hierarchy.

UNBOUNDED starts with the first period or ends with the last period of the group.

CURRENT MEMBER starts or ends the calculation at the current time period.

expression

A numeric value identifying a period at a particular distance from the current time period that starts or ends the range.

WITHIN subclause

Identifies the range of time periods used in the calculation. Following are descriptions of the keywords.

Example

This example calculates a moving maximum within the calendar year.

MAX(GLOBAL.UNITS_CUBE.UNITS) OVER (DIMENSION GLOBAL.TIME BETWEEN UNBOUNDED PRECEDING AND CURRENT MEMBER WITHIN ANCESTOR AT DIMENSION LEVEL GLOBAL.TIME.CALENDAR_YEAR)

Related Topics

AVG, COUNT, MIN, SUM

Return Value

NUMBER

Syntax

MIN (value_expr) OVER (window_clause)

window_clause::=

[ { DIMENSION dimension_id | HIERARCHY hierarchy_id } ]
    BETWEEN preceding_boundary | following_boundary 
     [WITHIN { LEVEL
             | PARENT
             | GREGORIAN {YEAR | QUARTER | MONTH | WEEK}
             | ANCESTOR AT { DIMENSION LEVEL dim_level_id
                           | HIERARCHY LEVEL hier_level_id 
                           }
             }
     ]

preceding_boundary::=

{UNBOUNDED PRECEDING | expression PRECEDING} AND 
   { CURRENT MEMBER 
   | expression {PRECEDING | FOLLOWING}
   | UNBOUNDED FOLLOWING
   }

following_boundary::=

{CURRENT MEMBER | expression FOLLOWING} AND
  { expression FOLLOWING 
  | UNBOUNDED FOLLOWING
  }

Arguments

value_expr

A dimensional expression whose values you want to calculate.

dimension_id

The Time dimension over which the values are calculated using the default hierarchy.

hierarchy_id

The hierarchy over which the values are calculated. If dimension_id is used instead, the default hierarchy is used.

dim_level_id

The name of a level of dimension_id.

hier_level_id

The name of a level of hierarchy_id.

boundaries

The preceding_boundary and following_boundary identify a range of time periods within the group identified by the dimension or hierarchy.

UNBOUNDED starts with the first period or ends with the last period of the group.

CURRENT MEMBER starts or ends the calculation at the current time period.

expression

A numeric value identifying a period at a particular distance from the current time period that starts or ends the range.

WITHIN subclause

Identifies the range of time periods used in the calculation. Following are descriptions of the keywords.

Example

This example calculates the minimum value between the current member and all subsequent members in the same calendar year. The selection of the data displays the minimum values for the months in 2006.

MIN(GLOBAL.UNITS_CUBE.UNITS) OVER (DIMENSION GLOBAL.TIME BETWEEN CURRENT MEMBER AND UNBOUNDED FOLLOWING WITHIN ANCESTOR AT DIMENSION LEVEL GLOBAL.TIME.CALENDAR_YEAR)

Related Topics

AVG, COUNT, MAX, SUM

Return Value

The data type specified in the syntax

Syntax

OLAP_DML_EXPRESSION (expression, datatype)

Arguments

expression

An expression in the OLAP DML language, such as a call to a function or a program.

datatype

The data type of the return value from expression.

Example

In this example, the OLAP_DML_EXPRESSION function executes the OLAP DML RANDOM function to generate a calculated measure with random numbers between 1.05 and 1.10.

OLAP_DML_EXPRESSION('RANDOM(1.05, 1.10)', NUMBER)

Return Value

NUMBER

Syntax

RANK ( ) OVER (rank_clause)

rank_clause::=

{ DIMENSION dimension_id | HIERARCHY hierarchy_id }
  ORDER BY order_by_clause [, order_by_clause]...
  [ WITHIN { PARENT 
           | LEVEL 
           | ANCESTOR AT { DIMENSION LEVEL dim_lvl_id
                         | HIERARCHY LEVEL hier_level_id
                         }
           }
  ]

order_by_clause::=

expression [ASC | DESC] [NULLS {FIRST | LAST}] 

Arguments

dimension_id

The dimension over which the values are calculated using the default hierarchy.

hierarchy_id

The hierarchy over which the values are calculated. If dimension_id is used instead, then the default hierarchy is used.

ORDER BY

Provides the basis for the ranking. You can provide additional ORDER BY clauses to break any ties in the order.

expression

Provides the values to use as the basis for the rankings.

ASC | DESC

Sorts the ranking from smallest to largest (ascending) or from largest to smallest (descending).

NULLS {FIRST | LAST}

Determines whether members with null values are listed first or last.

WITHIN

Selects a set of related dimension members to be ranked.

PARENT ranks members at the same level with the same parent.

LEVEL ranks all members at the same level.

ANCESTOR ranks all members at the same level and with the same ancestor at a specified level.

dim_level_id

The name of a level of dimension_id.

hier_level_id

The name of a level of hierarchy_id.

Example

This example ranks time periods within a calendar year by Unit Cost. Notice that no month is ranked 7, because two months (JAN-02 and JUL-02) have the same value and the same rank (6).

RANK() OVER (DIMENSION TIME.CALENDAR ORDER BY PRICE_CUBE.UNIT_COST DESC NULLS LAST WITHIN ANCESTOR AT DIMENSION LEVEL TIME.CALENDAR_YEAR)

Related Topics

AVERAGE_RANK, DENSE_RANK, ROW_NUMBER

Return Value

NUMBER

Syntax

ROW_NUMBER ( ) OVER (rank_clause)

rank_clause::=

{ DIMENSION dimension_id | HIERARCHY hierarchy_id }
  ORDER BY order_by_clause [, order_by_clause]...
  [ WITHIN { PARENT 
           | LEVEL 
           | ANCESTOR AT { DIMENSION LEVEL dim_lvl_id
                         | HIERARCHY LEVEL hier_level_id
                         }
           }
  ]

order_by_clause::=

expression [ASC | DESC] [NULLS {FIRST | LAST}] 

Arguments

dimension_id

The dimension over which the values are calculated using the default hierarchy.

hierarchy_id

The hierarchy over which the values are calculated. If dimension_id is used instead, then the default hierarchy is used.

ORDER BY

Provides the basis for the ranking. You can provide additional ORDER BY clauses to break any ties in the order.

expression

Provides the values to use as the basis for the rankings.

ASC | DESC

Sorts the ranking from smallest to largest (ascending) or from largest to smallest (descending).

NULLS {FIRST | LAST}

Determines whether members with null values are listed first or last.

WITHIN

Selects a set of related dimension members to be ranked.

PARENT ranks members at the same level with the same parent.

LEVEL ranks all members at the same level.

ANCESTOR ranks all members at the same level and with the same ancestor at a specified level.

dim_level_id

The name of a level of dimension_id.

hier_level_id

The name of a level of hierarchy_id.

Example

This example ranks time periods within a calendar year by Unit Cost. Notice even though two months (JAN-02 and JUL-02) have the same value, they are assigned sequential numbers (6 and 7).

ROW_NUMBER() OVER (HIERARCHY TIME.CALENDAR ORDER BY PRICE_CUBE.UNIT_COST DESC NULLS LAST WITHIN ANCESTOR AT DIMENSION LEVEL TIME.CALENDAR_YEAR)

Related Topics

AVERAGE_RANK, DENSE_RANK, RANK

Return Value

NUMBER

Syntax

share_expression::=

SHARE (expression share_clause [share_clause]... )
 

share_clause::=

OF { DIMENSION dimension_id | HIERARCHY hierarchy_id }
   { PARENT 
   | TOP 
   | MEMBER 'member_name'
   | DIMENSION LEVEL dim_level_id 
   | HIERARCHY LEVEL hier_level_id 
   }

Arguments

expression

A dimensional expression whose values you want to calculate.

dimension_id

A dimension of expression. The default hierarchy is used in the calculation. If you want to use a different hierarchy, then use the HIERARCHY argument instead.

hierarchy_id

A level hierarchy of expression.

member_name

A member of the specified dimension or hierarchy.

dim_level_id

The name of a level of dimension_id.

hier_level_id

The name of a level of hierarchy_id.

Share is calculated with these formulas:

Example

This example calculates the percent share of the parent member for each product. The results appear in the Share of Parent column.

(SHARE(UNITS_CUBE.SALES OF HIERARCHY PRODUCT.PRIMARY PARENT))*100

The next example calculates the percent share of Total Product for each product. The results appear in the Share of Top column.

(SHARE(UNITS_CUBE.SALES OF HIERARCHY PRODUCT.PRIMARY TOP))*100

Return Value

NUMBER

Syntax

SUM (value_expr) OVER (window_clause)

window_clause::=

[ { DIMENSION dimension_id | HIERARCHY hierarchy_id } ]
    BETWEEN preceding_boundary | following_boundary 
      [WITHIN { PARENT
              | LEVEL
              | GREGORIAN {YEAR | QUARTER | MONTH | WEEK}
              | ANCESTOR AT { DIMENSION LEVEL dim_level_id
                            | HIERARCHY LEVEL hier_level_id 
                            }
              }
      ]

preceding_boundary::=

{UNBOUNDED PRECEDING | expression PRECEDING} AND 
   { CURRENT MEMBER 
   | expression {PRECEDING | FOLLOWING}
   | UNBOUNDED FOLLOWING
   }

following_boundary::=

{CURRENT MEMBER | expression FOLLOWING} AND
  { expression FOLLOWING 
  | UNBOUNDED FOLLOWING
  }

Arguments

value_expr

A dimensional expression whose values you want to calculate.

dimension_id

The Time dimension over which the values are calculated using the default hierarchy.

hierarchy_id

The hierarchy over which the values are calculated. If dimension_id is used instead, the default hierarchy is used.

dim_level_id

The name of a level of dimension_id.

hier_level_id

The name of a level of hierarchy_id.

boundaries

The preceding_boundary and following_boundary identify a range of time periods within the group identified by the dimension or hierarchy.

UNBOUNDED starts with the first period or ends with the last period of the group.

CURRENT MEMBER starts or ends the calculation at the current time period.

expression

A numeric value identifying a period at a particular distance from the current time period that starts or ends the range.

WITHIN subclause

Identifies the range of time periods used in the calculation. Following are descriptions of the keywords.

Example

This example calculates the sum of two values, for the current and the following time periods, within a level. The results appear in the Sum column.

SUM(UNITS_CUBE.SALES) OVER (DIMENSION "TIME" BETWEEN 1 PRECEDING AND CURRENT MEMBER WITHIN LEVEL)

The next example calculates Year-to-Date Sales.

SUM(UNITS_CUBE.SALES) OVER (HIERARCHY TIME.CALENDAR BETWEEN UNBOUNDED PRECEDING AND CURRENT MEMBER WITHIN ANCESTOR AT DIMENSION LEVEL TIME.CALENDAR_YEAR)

Related Topics

AVG, COUNT, MAX, MIN