Skip Headers
Oracle® OLAP DML Reference
11g Release 2 (11.2)

Part Number E17122-07
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

INFO

The INFO function obtains information that has been produced by a FORECAST, PARSE, or REGRESS statement or that has been produced for a model in your analytic workspace.

Because the syntax of the INFO function is different depending on the type of information being obtained, four separate entries are provided:


INFO (FORECAST)

The INFO (FORECAST) function obtains information produced by a FORECAST statement and stored internally by Oracle OLAP. Through the use of keywords, INFO lets you extract specific pieces of information about the forecast you have calculated.

Note:

Before using INFO, familiarize yourself with FORECAST.REPORT that is a standard report of its results, which may give you all the information you need. INFO is useful primarily for creating customized reports or for performing further analysis on the results.

When you try to extract information without having calculated a forecast, INFO produces an error. You can use the keyword AVAILABLE to determine whether any results are currently available.

Return Value

The return value depends on the keyword you use, as described in the tables in this entry. INFO returns NA when you use an index that is out of range or for any choice that does not apply to the forecasting method last used. For example, when your forecast formula has two coefficients and you request the twelfth one, INFO returns NA.

Syntax

INFO(FORECAST choice [index])

Parameters

FORECAST

Indicates that you want to obtain information produced by a FORECAST statement.

choice

The specific information you want. The choices available for FORECAST are listed in Table 7-12, "Choices for All Methods", Table 7-13, "Choices for TREND and EXPONENTIAL Forecasts", and Table 7-14, "Choices for WINTERS Forecasts". Choices marked as indexed require the index argument.

index

An INTEGER expression that specifies which result you want for a choice that can have several different results. For example, a trend equation might have several coefficients. You would use index to specify which coefficient you want information about. When you omit index for a choice that requires it, an error occurs.

Table 7-12 Choices for All Methods

Keyword Type Indexed? Meaning

AVAILABLE

BOOL

No

Is there a computed forecast for which to obtain information?

DEPENDENT

TEXT

No

The variable or expression being forecast.

METHOD

TEXT

No

The forecast method.

MAPE

DEC

No

The mean absolute percent error (a measure of goodness of fit).

LENGTH

INT

No

The number of forecast periods calculated.

TIME

TEXT

No

The dimension along which forecasting is performed.

FCNAME

TEXT

No

The name of the variable that contains the fitted and forecasted values (NA when no forecasts were saved).


Table 7-13 Choices for TREND and EXPONENTIAL Forecasts

Keyword Type Indexed? Meaning

FORMULA

TEXT

No

The text of the forecasting equation.

NUMCOEFS

INT

No

The number of coefficients.

COEFFICIENT

DEC

Yes

The specified coefficient in the forecasting equation; index specifies which one you want.


Table 7-14 Choices for WINTERS Forecasts

Keyword Type Indexed? Meaning

PERIODICITY

INT

No

The number of periods in a seasonal cycle.

ALPHA

DEC

No

The smoothing constant for the smoothed data series.

BETA

DEC

No

The smoothing constant for the seasonal index series.

GAMMA

DEC

No

The smoothing constant for the trend series.

STSMOOTHED

DEC

No

The starting value of the smoothed data series.

STSEASONAL

DEC

Yes

The starting values for the seasonal index series; index specifies which one you want.

STTREND

DEC

No

The starting value for the trend series.

FCSMOOTHED

TEXT

No

The variable that holds the smoothed data series.

FCSEASONAL

TEXT

No

The variable that holds the seasonal index series.

FCTREND

TEXT

No

The variable that holds the trend series.


Examples

Example 7-111 Getting Forecast Information

In this example, suppose you forecasted sales.

The following statements limit the dimensions of the sales variable, then obtain the formula for your forecast.

LIMIT product TO 'Sportswear'
LIMIT district TO 'Chicago'
LIMIT month TO 'Jan95' TO 'Dec96'
FORECAST LENGTH 12 METHOD EXPONENTIAL FCNAME fcst time -
month sales
SHOW INFO(FORECAST FORMULA)

These statements produce the following output.

87718.0009541865 * (1.005533834579 ** MONTH)

The next statement obtains the mean absolute percent error for your forecast.

SHOW INFO(FORECAST MAPE)

This statement produces the following output.

.17

INFO (MODEL)

The INFO (MODEL) function obtains information that is produced for the models in your analytic workspace and stored internally by Oracle OLAP. Through the use of keywords, INFO lets you extract specific pieces of information about the structure of a compiled model or the status of a model that you have run in your current session.

Note:

Before using INFO, familiarize yourself with the reports created by MODEL.COMPRPT, MODEL.DEPRT, and MODEL.XEQRPT that might give you all the information you need.

Use INFO with the keyword AVAILABLE to determine whether any model results are currently available. When you try to extract any other information without having considered or defined a model in your current session, INFO produces an error.

Return Value

The return value depends on the keyword you use, as described in the tables in this entry. INFO returns NA when you use an index that is out of range or when you request information that is not relevant. For example, if the model contains 5 statements and you request information about statement 6, INFO returns NA; or if you specify the DIMENSION REFERENCE choice when the assignment target is actually a variable, INFO returns NA.

Syntax

INFO(MODEL choice [index1 [index2 [index3]]])

where index is an argument specifies the result you want for a choice that can have several different results. Depending on the keyword choice, you can supply one or more of the following index arguments:

block-num
dimension-num
element-num
model-num
qualifier-num
source-num
stmnt-num

Parameters

MODEL

Indicates that you want to obtain information about a model in your analytic workspace. INFO returns information about the model that you have most recently defined or considered in the current session (see the DEFINE MODEL and CONSIDER commands).

choice

A keyword that specifies the information you want. The choices available for models are listed in the following tables that represent different informational categories:

Each table consists of four columns that provide the following information: keyword, data type of returned value; index argument associated with the keyword; and meaning.

Table 7-15 INFO (MODEL) Choices to Retrieve General Information About the Model

Keywords Data Type Index Arguments Meaning

AVAILABLE

BOOL

(No arguments)

Is there a model for which information is available?

NAME

TEXT

[MODEL model-num]

Without model-num (or with model-num equal to 0), the name of the current model. With model-num greater than 0, the name of the included model that is the specified model-num within the current model.

COUNT STATEMENTS

INT

(No arguments)

The number of statements in the current model. The count includes comments, equations, and DIMENSION and INCLUDE commands (if any), it but does not include the statements in an included model.

STATEMENT

TEXT

stmnt-num

The text of statement stmnt-num.

SIMULTANEOUS

BOOL

(No arguments)

Does the current model contain a simultaneous block? 


Table 7-16 INFO (MODEL) Choices to Retrieve Information about the Structure of the Model

Keyword(s) Data Type Index Argument(s) Meaning

COUNT ELEMENTS

INT

[BLOCK block-num]

Without block-num, the number of blocks in the current model. With block-num, the total number of statements and nested blocks within block block-num in the current model.

When you request further information about a particular element (for example, with the TYPE ELEMENT choice), you always specify the block number to which the element belongs and the number of the element within that block.

TYPE ELEMENT

TEXT

element-num BLOCK block-num

Returns BLOCK or STATEMENT, depending on whether element element-num of block block-num is a nested block or a statement.

NUMBER BLOCK

INT

element-num BLOCK block-num

The block number of the nested block that is element element-num of block block-num.

TYPE BLOCK

TEXT

block-num

Returns SIMPLE, STEP-FORWARD, STEP-BACKWARD, or SIMULTANEOUS, depending on the execution type of block block-num.

COUNT DIMS

INT

[BLOCK block-num]

Without block-num, the number of model dimensions of the current model. With block-num, the number of step-forward, step-backward, or simultaneous dimensions of block block-num within the current model.

DIMENSION

TEXT

dimension-num [BLOCK block-num]

Without block-num, the name of model dimension dimension-num of the current model. With block-num, the name of the specified step-forward, step-backward, or simultaneous dimension of block block-num.

NUMBER STATEMENT

INT

element-num BLOCK block-num

The statement number of the statement that is element element-num of block block-num.

The statement number refers to the position of the statement within its own model. To request further information about the statement (for example, with the HIDDEN choice), its model must be the model that you are currently considering.

HIDDEN

BOOL

stmnt-num

Has statement stmnt-num been masked by a subsequent statement?

NUMBER MODEL

INT

element-num BLOCK block-num

 

The number of the included model from which the statement that is element element-num of block block-num is taken.

 


Table 7-17 INFO (MODEL) Choices to Retrieve Information about Target, Sources, and Dependencies

Keyword(s) Data Type Index Argument Meaning

COUNT SOURCES

INT

STATEMENT stmnt-num

The number of data sources in statement stmnt-num within the current model.

TYPE REFERENCE

TEXT

STATEMENT stmnt-num [SOURCE source-num]

Without source-num, the object type of the assignment target of statement stmnt-num. With source-num, the object type of data source source-num in statement stmnt-num. The object type is VARIABLE when the reference is to a variable. The type is DIMENSION when the reference is to the value of a dimension.

VARIABLE REFERENCE

TEXT

STATEMENT stmnt-num [SOURCE source-num]

Without source-num, the name of the variable that is the assignment target of statement stmnt-num. With source-num, the name of the variable that is data source source-num in statement stmnt-num.

VALUE REFERENCE

TEXT

STATEMENT stmnt-num [SOURCE source-num]

Without source-num, the dimension value that is the assignment target of statement stmnt-num. With source-num, the dimension value that is data source source-num in statement stmnt-num.

DIMENSION REFERENCE

TEXT

STATEMENT stmnt-num [SOURCE source-num]

Without source-num, the model dimension of the target dimension value in statement stmnt-num. With source-num, the model dimension of source dimension value source-num in statement stmnt-num.

COUNT QUALIFIERS

INT

STATEMENT stmnt-num [SOURCE source-num]

Without source-num, the number of qualifiers of the assignment target in statement stmnt-num. With source-num, the number of qualifiers of data source source-num in statement stmnt-num.

TYPE QUALIFIER

TEXT

qualifier-num STATEMENT stmnt-num [SOURCE source-num]

Without source-num, the qualifier type of qualifier qualifier-num of the target of statement stmnt-num. With source-num, the qualifier type of qualifier qualifier-num of data source source-num in statement stmnt-num. The qualifier type can indicate dimensional dependence: LAG (previous dimension values only), LEAD (later values only), BOTH (both previous and later values), and VARIABLE (either previous or later values, depending on the value of a variable when the model is run). The qualifier type can also be QDR (qualified data reference).

DIMENSION QUALIFIER

TEXT

qualifier-num STATEMENT stmnt-num [SOURCE source-num]

qualifier-num STATEMENT stmnt-num [SOURCE source-num]

Without source-num, the dimension of qualifier qualifier-num of the assignment target in statement stmnt-num. With source-num, the dimension of qualifier qualifier-num of data source source-num in statement stmnt-num.


Table 7-18 INFO (MODEL) Choices to Retrieve Information About Execution Status

Keyword(s) Data Type Index Argument Meaning

XEQSTATUS

TEXT

[BLOCK block-num]

Without block-num, the execution status of the model as a whole; when the model has not been run, the status is NOT EXECUTED. With block-num, the execution status of block block-num; when the model has not been run, an error is returned. When the model has been run, the status for the model as a whole or for a block can be SOLVED, DIVERGED, or FAILED TO CONVERGE. The status of an outer-level block can be EXECUTION INCOMPLETE when a nested block within it diverged or failed to converge.

COUNT ITERATIONS

INT

BLOCK block-num

The number of iterations that were performed for block block-num before it was solved or it diverged or failed to converge.

DAMP

DEC

(No arguments)

The value of the MODDAMP option when the model was run. (Relevant only when the solution method is GAUSS.)

DIVERGSTMT

INT

BLOCK block-num

The element number of the statement that diverged during the calculations for block block-num.

GAMMA

INT

(No arguments)

The value of the MODGAMMA option when the model was run.

MAXITERS

INT

(No arguments)

The value of the MODMAXITERS option when the model was run.

OVERFLOW

INT

(No arguments)

The value of the MODOVERFLOW option when the model was run.

SIMULTYPE

TEXT

(No arguments)

The value of the MODSIMULTYPE option when the model was run: AITKENS or GAUSS.

TOLERANCE

INT

(No arguments)

The value of the MODTOLERANCE option when the model was run. 


block-num

An INTEGER expression that specifies the block for which you want information. Block-num corresponds to the block numbers that are identified in the report produced by the MODEL.COMPRPT program.

dimension-num

An INTEGER expression that specifies the model dimension or block dimension for which you want information. For the model as a whole, the first dimension listed for the model is dimension-num 1, and so on. For example, assume that the MODEL.COMPRPT specifies the model dimensions as <line month>. In this case, line is dimension-num 1 and month is dimension-num 2. For a simultaneous block in the current model, the first dimension of the block is dimension-num 1, and so on. A step-forward or step-backward block has a single dimension, so the dimension of the block is always dimension-num 1. To see a list of the dimensions for the model as a whole and for each block of the model, you can run the MODEL.COMPRPT program.

element-num

An INTEGER expression that specifies the element for which you want information. When you request information about an element, you always specify the block number to which the element belongs. An element is either a statement in the specified block, or it is a nested block within the specified block. The element numbers correspond to the order of the statements and blocks in the compiled model. You can run the MODEL.COMPRPT program to see the list of elements in the compiled model.

For example, suppose the current model has the following compiled structure.

block 1
statement a
  block 2
  statement b
  statement c
  END block 2
statement d
END block 1

When you request information about block 1 in the preceding model, statement a is element-num 1; block 2 is element-num 2; and statement d is element-num 3. When you request information about block 2, statement b is element-num 1 and statement c is element-num 2.

model-num

For a hierarchy of included models, an INTEGER expression that specifies the model for which you want information. The model you are currently considering is model-num 0 (zero), the model it includes is model-num 1, and so on. The root model has the highest model number in the hierarchy.

qualifier-num

An INTEGER expression that specifies the qualifier for which you want information. Qualifiers change the dimensionality of a variable or dimension value reference. The reference can be qualified by a function, such as LAG, LEAD, or TOTAL or by a qualified data reference (QDR). To see the qualifiers for a statement, you can run the MODEL.DEPRT program for the model that contains the statement.

For each equation in the model, the MODEL.DEPRT report lists the assignment target and its qualifiers on one line, followed by the data sources. Each data source is listed on a separate line, together with its qualifiers. The MODEL.DEPRTreport also specifies the type of each qualifier: LAG, LEAD, BOTH, VARIABLE, or QDR (see the TYPE QUALIFIER choice in the third group of INFO keyword choices).

For the target and each source, qualifier-num corresponds to the order in which the qualifiers are listed in the MODEL.DEPRT report.

source-num

An INTEGER expression that specifies the data source for which you want information. In a calculation, each reference to a variable or a dimension value is counted as a source of data for the assignment target. A constant value is not counted as a source.

To see the data sources in a statement, you can run the MODEL.DEPRT program for the model that contains the statement. For each equation in the model, the MODEL.DEPRT report lists the assignment target on one line, followed by its data sources. Each data source is listed on a separate line.

stmnt-num

An INTEGER expression that specifies the statement for which you want information. Stmnt-num always refers to a statement from the model you are currently considering. It does not refer to a statement taken from an included model.

To see the statement numbers in the current model, you can run the MODEL.COMPRPT program. To the left of each statement, the report lists the model from which the statement is taken and the statement number within that model.

Examples

Example 7-112 Getting Qualifier Information

Assume that the following statement is statement 3 of a model called income.plan.

budget(line revenue) = LAG(actual(line revenue), 1, month) -
   + plan.factor

You can run the MODEL.DEPRPT program to see the qualifiers of the target and sources in this statement.

MODEL.DEPRPT income.plan

This statement produces the following output.

MODEL INCOME.PLAN
...
3    BUDGET(QDR <LINE>):
       ACTUAL(LAG <MONTH>)(QDR <LINE>)
       PLAN.FACTOR
...

This report shows that the assignment target, budget, has two data sources, actual and plan.factor.

Example 7-113 Checking Qualifier Information

The following statements make INCOME.PLAN the current model and check the number and type of the qualifiers of the assignment target of statement 3.

CONSIDER income.plan
SHOW INFO(MODEL COUNT QUALIFIERS STATEMENT 3)

These statements produce the following output.

1

The OLAP DML statement

SHOW INFO(MODEL TYPE QUALIFIER 1 STATEMENT 3)

produces the following output.

QDR

Example 7-114 Checking Different Data Sources

The following statements check the number and type of the qualifiers of the two data sources in statement 3.

The OLAP DML statement

SHOW INFO(MODEL COUNT QUALIFIERS STATEMENT 3 SOURCE 1)

produces the following output.

2

The OLAP DML statement

SHOW INFO(MODEL TYPE QUALIFIER 1 STATEMENT 3 SOURCE 1)

produces the following output.

LAG

The OLAP DML statement

SHOW INFO(MODEL TYPE QUALIFIER 2 STATEMENT 3 SOURCE 1)

produces the following output.

QDR

The OLAP DML statement

SHOW INFO(MODEL COUNT QUALIFIERS STATEMENT 3 SOURCE 2)

produces the following output.

0

INFO (PARSE)

The INFO (PARSE) function obtains information produced by a PARSE statement and stored internally by Oracle OLAP. Through the use of keywords, INFO lets you extract specific pieces of information about the expression that you have parsed.

Return Value

The return value depends on the keyword you use, as described in Table 7-19. When you try to extract unavailable information or use an index that is out of range, INFO returns NA. For example, if you parse a phrase that contains four expressions and then ask for the twelfth FORMULA, INFO returns NA.

Syntax

INFO(PARSE choice [index])

Parameters

PARSE

Indicates that you want to obtain information produced by a PARSE statement.

choice

The specific information you want. The choices available for PARSE are listed in Table 7-19, "INFO PARSE Keywords". Choices marked as indexed can take the optional index argument.

index

An INTEGER expression that specifies which result you want for a choice that can have several different results. For example, when you parse text that contains three expressions, each expression has its own formula and data type. You would use index to specify which expression you are interested in.

When you omit index, INFO returns all the information as a multiline value.

Table 7-19 INFO PARSE Keywords

Keyword Type Indexed? Meaning

PARSEABLE

BOOL

No

Was Oracle OLAP able to parse the text?

ERRORTEXT

TEXT

No

The text of an error message when the expressions were not parsed.

NUMFORMULAS

INT

No

The number of expressions (formulas) that were parsed.

NUMDIMS

INT

No

The number of dimensions in the union of all the expressions that were parsed.

FORMULA

TEXT

Yes

The text (formula) of the specified expression; index specifies which one you want.

DATA

TEXT

Yes

The data type of the specified expression.

TYPE

TEXT

Yes

The type of object of the specified expression; when the expression is the name of an object, it returns the type; when the expression is a qualified data reference, it returns QDR; when the expression is anything else, it returns EXP.

DIMENSION

TEXT

Yes

The name of the specified dimension in the union of all dimensions of the expressions. 


Examples

Example 7-115 Getting Parsed Information

In a simple report program, you want to allow the user to specify the data to be reported as an argument to the program. You want to allow the user to specify an expression and the name of a data variable. You cannot process expression arguments with an ARGS statement, so you use PARSE and INFO to parse the program arguments and produce the report.

The following statements create a simple report program.

DEFINE report1 PROGRAM
PROGRAM
PUSH month product district DECIMALS
DECIMALS = 0
LIMIT month TO FIRST 2
LIMIT product TO ALL
LIMIT district TO 'Chicago'
PARSE ARGS
REPORT ACROSS month: WIDTH 8 <&INFO(PARSE FORMULA 1) -
        WIDTH 13 &INFO(PARSE FORMULA 2)>
POP month product district DECIMALS
END

When users run the program, they can supply either the name of a variable (sales) or an expression (sales-expense) or both as arguments.

The following statement

REPORT1 sales sales-expense

produces the following output.

DISTRICT: CHICAGO
             --------------------MONTH--------------------
             --------Jan95--------- --------Feb95---------
PRODUCT       SALES   SALES-EXPENSE  SALES   SALES-EXPENSE
------------ -------- ------------- -------- -------------
Tents          29,099         1,595   29,010         1,505
Canoes         45,278           292   50,596           477
Racquets       54,270         1,400   58,158         1,863
Sportswear     72,123         7,719   80,072         9,333
Footwear       90,288         8,117   96,539        13,847

INFO (REGRESS)

The INFO (REGRESS) function obtains information produced by an REGRESS statement and stored internally by Oracle OLAP. Through the use of keywords, INFO lets you extract specific pieces of information about the regression you have calculated.

Note:

Before using INFO, familiarize yourself with REGRESS.REPORT that produces a standard report of its results, which might give you all the information you need. INFO is useful primarily for creating customized reports or for performing further analysis on the results

Return Value

The return value depends on the keyword you use, as described in Table 7-20, "INFO REGRESS Keywords".

Syntax

INFO(REGRESS choice [index])

Parameters

REGRESS

Indicates that you want to obtain information produced by an REGRESS statement.

choice

The specific information you want. The choices available for REGRESS are listed in Table 7-20, "INFO REGRESS Keywords". Choices marked as indexed require the index argument.

index

An INTEGER expression that specifies which result you want for a choice that can have several different results. For example, in a regression there may be multiple independent variables. You would use index to specify which independent variable you want information about. When you omit index for a choice that requires it, an error occurs.

Table 7-20 INFO REGRESS Keywords

Keyword Type Indexed? Meaning

AVAILABLE

BOOL

No

Is there a computed regression from which to extract information?

DEPENDENT

TEXT

No

The name of the dependent variable in the regression.

NOINTERCEPT

BOOL

No

Was the regression calculated with the intercept suppressed?

WEIGHTED

BOOL

No

Was the last regression weighted?

WEIGHT

TEXT

No

The expression used to weight the last regression.

NUMCOEFS

INT

No

The number of coefficients.

INDEPENDENT

TEXT

Yes

An independent variable; index specifies which one you want (Intercept to be first unless it was suppressed).

COEFFICIENT

DEC

Yes

An estimated coefficient; index specifies which one you want.

STDERROR

DEC

Yes

The standard error of an estimated coefficient; index specifies which one you want.

TRATIO

DEC

Yes

The t-ratio for an estimated coefficient; index specifies which one you want.

NUMOBS

INT

No

The number of observations that were used.

FRATIO

DEC

No

The F-ratio for the regression.

RBSQ

DEC

No

The corrected R-squared for the regression.

FORMULA

TEXT

No

The regression formula.

STDERROREST

DEC

No

The standard error of estimate for the regression

RESET

BOOL

 

Use when you want to reset the original state of AVAILABLE back to NO 


Usage Notes

Determining Regression Results Availability

When you try to extract information without having performed a regression, INFO produces an error. You can use the keyword AVAILABLE to determine whether any results are currently available. Once a successful regression has run, AVAILABLE remains true even when one or more unsuccessful regressions follow, because the results of the previous successful regression are still available. AVAILABLE remains true until you use RESET to change the AVAILABLE state back to its original value of NO.

NA Results Due to Index

INFO returns NA when you use an index that is out of range. For example, when your regression has five independent variables and you request the coefficient of the twelfth one, INFO returns NA.

Examples

Example 7-116 Getting Regression Information

The following statement sends the third coefficient from your most recently calculated regression to the current outfile.

SHOW INFO(REGRESS COEFFICIENT 3)

This statement produces the following result.

7.55