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.
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
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.
Release | Supports Incorta Version | Notes |
---|---|---|
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 Parameter | Example | Description |
---|---|---|
Tenant Name | myTenant | The tenant name for the tenant export |
Username | admin | The username for a user that belongs to the SuperRole role |
Password | Incorta#1 | Password for username |
Host URL | http://incorta.mycompany.com:8080/incorta | The Incorta Analytics Service host for the tenant |
Incorta Version Number | 4.8.2 | The 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.zip | A 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? | Y | Y is Yes. N is No.Y requires specifying an existing validation result for comparison |
Reference folder path | /tmp/Rel4.8.2_myTenant_1595989474708 | A 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 theTenant_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 (containingtenant.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 (containingtenant.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.