Tools → Formula Validation Tool

About the Formula Validation Tool

The Formula Validation Tool identifies issues with the formula expression syntax in dashboards, business schemas, and schemas. In order to validate the formula expression syntax, the tool requires a tenant export file. You can create tenant export using the Cluster Management Console (CMC) or the Tenant Management Tool (TMT).

For example, the Formula Validation Tool identifies issues with formula columns and runtime security filters in a schema table. One such issue is with aggregation expressions and missing commas between input values of the type integers, longs, and doubles. In Release 5.1, commas must separate input parameter values for built-in functions. In previous builds of Incorta, the Formula Builder accepted spaces between input values. The Formula Validation Tool identifies this and other issues with formula expression syntax.

Important

The Formula Validation Tool does not identify issues with load filters or session variables.
In order to identify issues with formulas that depend on presentation variables, you must supply a username and password for a user that belongs to the SuperRole role. This Super User must have View access permissions for all dashboards as this access allows the Formula Validation Tool to read and parse all presentation variables.

In its initial execution, the Formula Validation Tool creates a timestamp directory with the following output files:

  • extractedFormulas.tsv
  • failedFormulas.tsv
  • succeededFormulas.html
  • succeededFormulas.tsv

Each file captures specific details about the formulas in a given tenant: extracted formulas, formulas that successfully passed validation, and also formulas that failed validation. For example, the failedFormulas.tsv file contains the following columns:

  • Formula Type
  • Context Formula Field
  • Formula Label
  • Report Name
  • Report GUID
  • Link
  • Error Message
  • Formula Text
  • Valid Syntax
Note

If the user does not have View access permission to all dashboards, the Link column for some formula rows may contain UNAUTHORIZED ACCESS.

Using the Error Message field, resolve all issues in the failedFormulas.tsv file. In many cases, resolving one instance of an issue will resolve issues in dependent objects.

To determine if you successfully resolved all the issues with formulas that failed validation, you must again export the given tenant and run the Formula Validation Tool. For the second execution, you can optionally identify the previous directory as a reference folder which in turn instructs the tool to generate an additional file that compares previous and current failedFormulas.tsv files. The comparison file is failedFormulasDifference.tsv. Depending on the number of schemas, business schemas, and dashboards in a given tenant, it may take several iterations to successfully resolve all formula validation issues.

Resources required to use the Formula Validation Tool

Here are the resources required to run the Formula Validation Tool and identify outstanding issues with formula expressions:

  • A CMC Administrator to export tenants in a cluster or a System Administrator who can export tenants using the Tenant Management Tool (TMT)
  • A System Administrator with root access to the host running an Incorta Node will need to run the Formula Validation Tool.
  • A Super User that can access each tenant in the Incorta environment.
  • An Incorta Developer to resolve identified issues with formula expressions.

Formula Validation Tool Releases

There are two releases of this Formula Validation Tool.

ReleaseSupports Incorta VersionNotes
FormulaValidationTool_48.jar<= 4.8.1
  ●  Download from Google Drive
  ●  Standalone Jar
incorta.formula.extractor.jar>= 4.8.2
  ●  Included in the installer
  ●  Run shell script

FormulaValidationTool_48.jar is the standalone JAR. The JAR is for Incorta Releases 4.8.1 and earlier.

For Incorta Releases 4.8.2 and higher, you can find the Formula Validation Tool in in ~/IncortaAnalytics/IncortaNode/formulaParsingTool/. Within the packaged formulaParsingTool directory are three files: incorta.formula.extractor.bat, incorta.formula.extractor.jar, and incorta.formula.extractor.sh.

Both the standalone JAR and the packaged directory JAR support working with either a Tenant_Export.zip file or an uncompressed directory of the tenant export that includes the tenant.xml file.

Upon execution, both JARs requires the following details:

Input ParameterExampleDescription
Tenant NamemyTenantThe tenant name for the tenant export
UsernameadminThe username for a user that belongs to the SuperRole role
PasswordIncorta#1Password for username
Host URLhttp://incorta.mycompany.com:8080/incortaThe Incorta Analytics Service host for
the tenant
Incorta Version Number4.8.2The Incorta release version number.
This becomes part of the export
folder name for the validation results
Tenant file (.zip) or
folder (containing tenant.xml)
absolute path
/tmp/2020-07-29-16-08-50/ebs_gold.zipA path to the .zip
or the folder that contains the tenant.xml file
Output Folder absolute
path
/tmp/A path to an existing directory
for the formula validation tool results
Do you have a
reference to compare to?
YY is Yes. N is No.
Y requires specifying an existing
validation result for comparison
Reference folder path/tmp/Rel4.8.2_myTenant_1595989474708A path to a previously generated
formula validation output;
required for comparison usage

Formula validation with the standalone JAR for Incorta 4.8.1 or below

Follow these steps to download and run the standalone JAR file:

  • Download the Formula Validation Tool JAR.
  • Copy FormulaValidationTool_48.jar to the same parent directory of the Tenant_Export.zip file or the directory of the uncompressed tenant export.
  • From the terminal, enter the following command:
java -jar FormulaValidationTool_48.jar
  • Enter the required values for...
  • Tenant Name
  • Username
  • Password
  • Host URL
  • Incorta Version Number
  • Tenant_Export.zip or folder (containing tenant.xml) absolute path
  • Output Folder absolute path
  • Do you have a reference to compare to?
    • If Yes, specify the Reference folder path
  • View the results in the Output Folder.
  • Sign in to the Incorta Direct Data Platform and resolve all issues in failedFormulas.tsv.
  • Repeat this process to confirm validation success.

Formula validation with the packaged directory for Incorta 4.8.2 and above

A System Administrator with root access to the host running an Incorta Node is able to run the Formula Validation Tool in Release 4.8.2 and greater. an Incorta Developer with access to the tenant may be able to resolve issues

To run formula validation from the packaged directory, follow these steps:

  • From the terminal, navigate to: <INCORTA_NODE_INSTALLATION_PATH>/IncortaAnalytics/IncortaNode/formulaParsingTool/.
  • Enter one of the following commands:
java -jar incorta.formula.extractor.jar
./incorta.formula.extractor.sh
  • Enter the required values for...
    • Tenant Name
    • Username
    • Password
    • Host URL
    • Incorta Version Number
    • Tenant_Export.zip or folder (containing tenant.xml) absolute path
    • Output Folder absolute path
    • Do you have a reference to compare to?
      • If Yes, specify the Reference folder path
  • View the results in the Output Folder.
  • Sign in to the Incorta Direct Data Platform and resolve all issues in failedFormulas.tsv.
  • Repeat this process to confirm validation success.