The aim of this page is to provide a glossary of common OLAP and data warehousing terms and expressions. This list is for all terms beginning with N

$NATRIGGER

The $NATRIGGER property specifies values for Oracle OLAP to substitute for NA values that are in a dimensioned variable, but not in the session cache for the variable (if any). The results of the calculation are either stored in the variable or cached in the session cache for the variable.

You add or delete a $NATRIGGER property to the most recently defined or considered object (see DEFINE and CONSIDER) using a PROPERTY statement:

• To add the property, issue the following statement.
• PROPERTY '$NATRIGGER' value
• To delete the property, issue the following statement.
• PROPERTY DELETE '$NATRIGGER'

The following statements define a dimension with three values and define a variable that is dimensioned by the dimension. They add the $NATRIGGER property to the variable, then put a value in one cell of the variable and leave the other cells empty so their values are NA. Finally, they report the values in the cells of the variable.

DEFINE d1 INTEGER DIMENSION
MAINTAIN d1 ADD 3
DEFINE v1 DECIMAL <d1>
PROPERTY '$NATRIGGER' '500.0'
v1(d1 1) = 333.3
REPORT v1

The preceding statements produce the following output.

D1 V1
--------- ----------
1 333.3
2 500.0
3 500.0

See also NAFill and NASpell

NAFill

The NAFILL function returns the replaces any NA values within the the source expression with the specified fill expression.

Suppose you have NA values in the variable sales and you want to calculate an average that counts those values as zeros. Ordinarily, AVERAGE ignores NA values and does not count them in the number of values being averaged. You can use NAFILL inside the AVERAGE function to temporarily treat those values as zeros so they will count in calculating the average.

REPORT AVERAGE(NAFILL(sales 0.0))

See also NASpell, $NATRIGGER

NASpell

The NASPELL option controls the spelling that is used for NA values in output. This can be useful when returning a resultset where a zero figure needs to be displayed

In an ideal world, when loading data into an OLAP cube there is little value in loading a zero balance, since aggregating a series of zero balances together results in the value of zero. Therefore, it makes sense to not load zero rows but when the data is returned the value NA (equivalent to the value NULL in a relational context) is returned. Oracle OLAP allows you to replace the value NA with another value such as "0" or even a text string "No Balance". This is managed by the option NASPELL. This is a session option and can be over-ridden using the NAFILL() function (see above).

Examples

1) When NASPELL is set to its default value of NA, the OLAP DML statement:
SHOW current.month
produces the following output.
NA
In contrast, the OLAP DML statements
NASPELL = 'None'
SHOW current.month
produces the following output.
None

2) Where a variable, SALES, is completely empty, the following commands will return the value 0 for every cell within the cube SALES:

NASPELL='0'
REPORT SALES

See also NAFill, $NATRIGGER

Non-additive

The OLAP engine supports many different aggregation methods. In some cases it is not possible, or not required, to aggregate data within a specific dimension or across a group of dimensions. The OLAP engine allows the ability to define a non-additive type of aggregation. i.e. do not summarize data during the aggregation process.

Number Data Type

The NUMBER data type provides the same results on all platforms. All calculations are performed as integer arithmetic. Because of this, results based on a NUMBER will match those stored in relational tables. Choose NUMBER when you need a high level of precision, or when you need to match values. An unscaled NUMBER value is 22 bytes.