Validating the Essbase Outline

Pre-validate the Essbase outline to ensure that your application is compatible with a Hybrid Essbase version.

As part of an effort to upgrade all environments to an Oracle Essbase version that supports Hybrid Block Storage Option (BSO) cubes, Oracle has added a process to pre-validate Essbase outlines to ensure a smooth transition when your environment is upgraded to a Hybrid Essbase version.

Note:

For information about Essbase versions, see About Essbase in Cloud EPM in Getting Started Guide for Administrators.

To ensure that your application is compatible with a Hybrid Essbase version, we've implemented a new utility which verifies the member formulas in your environment and provides a report so you can fix any issues.

The utility can be launched manually and it will run automatically during database refresh:

If errors are found after the utility completes verification of the member formulas, you can view the Outline Pre-validation Report and fix the errors it finds. If no errors are found, no additional action is required.

For a list of sample pre-validation errors and recommended resolutions, see the sections below:

Manually Launching the Pre-Validation Process

To manually launch the pre-validation process, select from the following options under the Essbase Outline Validation menu option in the Actions menu on the application's Overview page:

  • Pre validate Outline: Performs a validation of your application

  • Outline Pre-validation Report: Displays a list of member formulas that need to be fixed to be compatible with Hybrid Essbase version

Note:

  • The Essbase Outline Validation menu option only displays if your environment is on a non-Hybrid Essbase version. If your environment is on a Hybrid Essbase version, then the Essbase Outline Validation menu option is no longer available.

  • Another way to check whether your environment is on a Hybrid or non-Hybrid Essbase version is to view the value of Essbase Version supports Hybrid Block Storage Option in the Activity Report. If the value is Yes, it means that your environment is on a Hybrid Essbase version. If the value is No, then your environment is on a non-Hybrid Essbase version.

To validate the Essbase outline in your environment and to fix any errors found in member formulas:

  1. From the Home page, click Application, and then click Overview.

  2. Click Actions, select Essbase Outline Validation, then select from the following options:

    • To perform the validation, select Pre validate Outline.

    • To view and download a report of errors found during validation, select Outline Pre-validation Report.

After you have fixed the member formulas, you can use the Pre validate Outline option again to make sure that all issues are resolved.

Note:

After fixing the member formulas, ensure that you refresh the database before viewing the pre-validation report again, otherwise the report won't show that all the errors are resolved.

Automatic Pre-Validation During Database Refresh

If the system detects that your environment uses a non-Hybrid Oracle Essbase version and will need to be upgraded to Hybrid Essbase, the validation utility will automatically launch at the end of the Refresh Database process.

If member formula errors are discovered during the validation process, you'll see a warning telling you to review the report and address any errors. Click View Outline Pre-validation Report to view the full report and resolve any errors.

Addressing Validation Errors

Validating the outline may reveal errors like those listed below. Address these issues using the provided recommendations and re-run the validation process until all errors are resolved.

Error Category Recommendations
ELSE or ENDIF without IF The member formula utilizes if, elseif, else, and endif constructs, but there are more endif tokens than if tokens, or the else and if tokens are mismatched. The number of if tokens should correspond with the number of endif or else tokens, ensuring proper grouping. Correct the formula by addressing the syntax issues.
Invalid Data Type or expected type NUMBER found STRING The member formula refers to a non-existing member. Review the formula and remove the non-existing member from it.
Invalid Expression Return Type The member formula refers to a non-existing member. Review the formula and remove the non-existing member from it.
No Matching Quote Found The formula contains mismatched quotation marks. Ensure that the number of opening and closing quotation marks are equal and properly grouped. Correct the formula by resolving syntax errors.
Nothing to do The member formula for the member is commented out using /* and */. Since the member is dynamic, it must have a valid formula. Put a valid member formula for the member or change the member property.
Parenthesis Error The formula has mismatched parentheses. Ensure that the number of opening and closing parentheses are balanced and correctly grouped. Correct the formula by resolving syntax errors.
Substitution Variable doesn't exist The member formula references a non-existent Substitution Variable. Create the Substitution Variable with an appropriate value to ensure successful formula validation.
Syntax Error in MDX Query To fix your syntax error, see MDX Syntax and Grammar Rules.
Missing Semicolon

or

Unexpected End of Expression
The member formula does not end with a semicolon.

The formula may return a value, however the absence of a terminating semicolon is a syntax error. Review and correct the formula.

Unknown Member Name used in function The member formula references a non-existent member. Review and update the formula to remove any references to members that do not exist.
Environment Variable doesn't exist The formula refers to an environment variable. You can't create an environment variable in the Oracle Fusion Cloud Enterprise Performance Management applications. Create a substitution variable instead with the same name and value of the environment variable, and use that in the formula instead.
Formula Parsing Error The member formula references a member name that is not quoted, or the formula does not end with a semicolon. Additionally, the member name imay nclude keywords or function names such as for, year, and sum which are not permitted in the formula.

Ensure that the member name is properly quoted and that the formula ends with a semicolon. Also, verify that a member with that name exists in the system.

Unknown Member Used in Query For an ASO cube, the member formula refers to a non-existent member in the MDX query. Additionally, ensure that the names of dimensions, members, and attribute dimensions do not contain the / (slash) character.
Duplicate Member Name The member formula refers to a non-existing member. Review the formula and remove the non-existing members from it.
Invalid Assignment Invalid assignment: The value on the left side of the equal sign must be a member or variable.

In the member formula, the left side of the assignment operation using equal to sign (=) should be the name of an existing member. Ensure that the left-hand side of the assignment is a valid member name.

Operator Error The member formula contains improperly nested if, elseif, or endif constructs, resulting in incorrect and unbalanced nesting. Review and correct the formula to address these syntax issues.
Function Error The member formula uses functions, but extra parameters have been passed. If the function involves Planning's substitution variables, ensure these variables are available and have values, which should not start with ampersand character (&). Additionally, review the formula to remove any extra & symbols before substitution variables. Ensure that each substitution variable in the formula has a single & before its name for proper resolution.
Missing Expression Review and fix the formula to remove the expressions related syntax errors.

Sample Validation Errors

In these samples, [MEMBER_NAME] indicates the name of the member that failed the validation.

Formula verification error samples: member name, unknown member, missing semi-colon, end of expression, etc.:

  • Formula verification failed for member [MEMBER_NAME]. Error compiling formula for [] (line 3): unknown member name ["[MEMBER_NAME]"] in function [operator @X]
  • Formula verification failed for member [MEMBER_NAME]. Error parsing member name in formula
  • Formula verification failed for member [MEMBER_NAME]. Error compiling formula for [] (line 1): semicolon missing
  • Formula verification failed for member [MEMBER_NAME]. Error compiling formula for [] (line 19): unexpected end of expression: [...IF(...)]
  • Formula verification failed for member [MEMBER_NAME]. Error compiling formula for [] (line 1): unexpected end of expression: [@ADD]

Formula verification errors sample: substitution variables sample:

Formula verification failed for member [MEMBER_NAME]. Substitution variable [[MEMBER_NAME]] doesn't exist

Formula verification error sample: parenthesis:

Formula verification failed for member [MEMBER_NAME]. Error compiling formula for [] (line 1): [)] without [(]

Formula verification error sample: invalid data type, return type, and assignment:

  • Formula verification failed for member [MEMBER_NAME]. Error compiling formula for [] (line 1): expected type [NUMBER] found [STRING] ([MEMBER_NAME]) in function [@SUB]
  • Formula verification failed for member [MEMBER_NAME]. Error compiling formula for [] (line 12): invalid expression return type
  • Formula verification failed for member [MEMBER_NAME]. Error compiling formula for [] (line 4): invalid assignment