Numeric Data Types

The numeric data types described in Table 2-2, "OLAP DML Numeric Data Types" are supported.

Table 2-2 OLAP DML Numeric Data Types

Data Type	Data Value
`INTEGER`	A whole number in the range of (-231) to (231)-1.
`SHORTINTEGER`	A whole number in the range of (-215) to (215)-1.
`LONGINTEGER`	A whole number in the range of (-263) to (263)-1.
`DECIMAL`	A decimal number with up to 15 significant digits in the range of -(10308) to +(10308).
`SHORTDECIMAL`	A decimal number with up to 7 significant digits in the range of -(1038) to +(1038).
`NUMBER`	A decimal number with up to 38 significant digits in the range of -(10125) to +(10125).

For data entry, a value for any of these data types can begin with a plus (+) or minus (-) sign; it cannot contain commas. Note, however, that a comma is required before a negative number that follows another numeric expression, or the minus sign is interpreted as a subtraction operator. Additionally, a decimal value can contain a decimal point. For data display, thousands and decimal markers are controlled by the NLS_NUMERIC_CHARACTERS option as described in "NLS Options".

Using LONGINTEGER Values

Most of the numerical data types return NA when a value is outside its range. However, the LONGINTEGER data type does not have overflow protection and will return an incorrect value when, for example, a calculation produces a number that exceeds its range. Use the NUMBER data type instead of LONGINTEGER when this is likely to be a problem.

Using NUMBER Values

When you define a NUMBER variable, you can specify its precision (p) and scale (s) so that it is sufficiently, but not unnecessarily, large. Precision is the number of significant digits. Scale can be positive or negative. Positive scale identifies the number of digits to the right of the decimal point; negative scale identifies the number of digits to the left of the decimal point that can be rounded up or down.

The NUMBER data type is supported by Oracle Database standard libraries and operates the same way as it does in SQL. It is used for dimensions and surrogates when a text or INTEGER data type is not appropriate. It is typically assigned to variables that are not used for calculations (like forecasts and aggregations), and it is used for variables that must match the rounding behavior of the database or require a high degree of precision. When deciding whether to assign the NUMBER data type to a variable, keep the following facts in mind in order to maximize performance:

Analytic workspace calculations on NUMBER variables is slower than other numerical data types because NUMBER values are calculated in software (for accuracy) rather than in hardware (for speed).
When data is fetched from an analytic workspace to a relational column that has the NUMBER data type, performance is best when the data already has the NUMBER data type in the analytic workspace because a conversion step is not required.