| Oracle® OLAP DML Reference 10g Release 1 (10.1) Part Number B10339-02 |
|
|
View PDF |
| Oracle® OLAP DML Reference 10g Release 1 (10.1) Part Number B10339-02 |
|
|
View PDF |
The INFO function obtains information that has been produced by the FORECAST, PARSE, or REGRESS command 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:
The INFO (FORECAST) function obtains information produced by the FORECAST command 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.REPORTthat 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. |
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])
Arguments
Indicates that you want to obtain information produced by the FORECAST command.
The specific information you want. The choices available for FORECAST are listed in Table 15-2, "Choices for All Methods", Table 15-3, "Choices for TREND and EXPONENTIAL Forecasts", and Table 15-4, "Choices for WINTERS Forecasts". Choices marked as indexed require the index argument.
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 15-2 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 15-3 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 15-4 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.
|
Notes
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.
Examples
Example 15-3 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
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. |
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:
Arguments
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).
A keyword that specifies the information you want. The choices available for models are listed in the following tables that represent different informational categories:
Table 15-5, "INFO (MODEL) Choices to Retrieve General Information About the Model"
Table 15-6, "INFO (MODEL) Choices to Retrieve Information about the Structure of the Model"
Table 15-7, "INFO (MODEL) Choices to Retrieve Information about Target, Sources, and Dependencies". These choices provide information about statements that are equations. Equations have the form assignment target = expression. The expression can refer to one or more data sources. Assignment targets and data sources can be either variables or dimension values, and they can have qualifiers that affect their dimensionality.
Table 15-8, "INFO (MODEL) Choices to Retreive Information About Execution Status". All of these choices (except XEQSTATUS) are relevant only after running a model with a simultaneous block. When the current model has not been compiled, Oracle OLAP returns an error when you use any choice except AVAILABLE or NAME.
Each table consists of three columns that provide the following information: keyword, data type of returned value; index argument associated with the keyword.
Table 15-5 INFO (MODEL) Choices to Retrieve General Information About the Model
| Keyword(s) | Data Type | Index Argument(s); 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 15-6 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 as well as 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 15-7 INFO (MODEL) Choices to Retrieve Information about Target, Sources, and Dependencies
| Keyword(s) | Data Type | Index Argument(s); 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]
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 15-8 INFO (MODEL) Choices to Retreive Information About Execution Status
| Keyword(s) | Data Type | Index Argument(s); 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. |
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.
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.
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.
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.
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.
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.
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.
Notes
You can use 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.
Examples
Example 15-4 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 15-5 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 15-6 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
The INFO (PARSE) function obtains information produced by the PARSE command 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 15-9. When you try to extract unavailable information or use an 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 will return NA.
Syntax
INFO(PARSE choice [index])
Arguments
Indicates that you want to obtain information produced by the PARSE command.
The specific information you want. The choices available for PARSE are listed in Table 15-9, "INFO PARSE Keywords". Choices marked as indexed can take the optional index argument.
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 15-9 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 15-7 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, as well as the name of a data variable. You cannot process expression arguments with the ARGS command, 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
The INFO (REGRESS) function obtains information produced by the REGRESS command 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 15-10, "INFO REGRESS Keywords".
Syntax
INFO(REGRESS choice [index])
Arguments
Indicates that you want to obtain information produced by the REGRESS command.
The specific information you want. The choices available for REGRESS are listed in Table 15-10, "INFO REGRESS Keywords". Choices marked as indexed require the index argument.
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 more than one independent variable. 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 15-10 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 will 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 |
Notes
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 will remain true until you use RESET to change the AVAILABLE state back to its original value of NO.
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
|
 Copyright © 2003 Oracle Corporation All Rights Reserved. |
|