Non-SQL functions in scripts

In non-SQL scripts, you can run script functions, but not SQL statements. To mark a script as non-SQL, you add this macro to the first line of the script: --<<NON-SQL>>

For example:

--<<NON-SQL>>
ExecuteLoadQuery(string application unique name, string table name, string load query name,bool ClearData);

Syntax

You can combine several functions in one script, to automate a series of data manipulations.

You can use a script to run another SQL or non-SQL script.

These rules apply:

All scripts containing non-SQL functions must start with --<<NON-SQL>>.
You cannot mix SQL statements and script functions.
All statements within a script must end with a semi colon (;).
String parameters must be enclosed in double quotes ("").
Boolean parameters are True or False, without quotes.
Numeric values must not be formatted. For example, they must not be formatted as dates. Only a period (.) can be used as the decimal place indicator.
Use // for single line comments.
Use"/*" at the beginning of multiline comments, and "*/" at their end.

ExecuteScript

Use ExecuteScript to run a script within the current application.

Note: We recommend that you do not run one non-SQL script from another non-SQL script.

Syntax

This runs a script of the current application:

ExecuteScript(string ScriptName);

This runs a script of a specified application:

--<<NON-SQL>>
ExecuteScript(string ApplicationUniqueName, string ScriptName);

This runs a script of a specified application, with parameters:

--<<NON-SQL>>
ExecuteScript(string ApplicationUniqueName, string ScriptName),
{parameter1:<value or @parameter>,<parameter2:<value or @parameter>|});

Example

--<<NON-SQL>>
ExecuteScript("My_Script");
//This is a one-line comment

Example with parameters

--<<NON-SQL>>
ExecuteScript("demo","DeleteWaterSales",
{pWater: "Springwater", pBrand: @pBrand}

);

ExecuteLoadQuery

Use ExecuteLoadQuery to load data to a table within the current application.

Syntax

--<<NON-SQL>>
ExecuteLoadQuery(string application unique name, string table name, string load query name, bool ClearData);

Specify False as the value of ClearData to retain existing data. Specify True to clear existing data before loading new data.

This runs a load query of a specified application, with parameters:

--<<NON-SQL>>
ExecuteLoadQuery(string application unique name, string table name, string load query name, bool ClearData,
{parameter of called load query:<value or @parameter of this non-sql script>, parameter2: <value or @parameter>|});

Example with parameters

--<<NON-SQL>>
ExecuteLoadQuery("demo","WaterSales","LoadWaterSales", true,
{pProduct: "Springwater", pBrand: @pBrand}

);

ExecuteIntegrationTableLoadQuery

Use ExecuteIntegrationTableLoadQuery to load data to a table in the Integration database.

Syntax

This runs a load query in the current application:

--<<NON-SQL>>
ExecuteIntegrationTableLoadQuery(string application unique name, string table name, string load query name, bool ClearData,
{parameter of called load query: <value or @parameter of this non-sql script>, parameter2 : <value or @parameter>|}

);

Specify False as the value of clearData to retain existing data. Specify True to clear existing data before loading new data.

This runs a load query in a specific application:

--<<NON-SQL>>
ExecuteIntegrationTableLoadQuery(string application unique name, string table name, string load query name, bool ClearData,
{parameter of called load query: <value or @parameter of this non-sql script>, parameter2 : <value or @parameter>|}

);

Example with parameters

--<<NON-SQL>>ExecuteIntegrationTableLoadQuery("demo","Accounts","LoadAccounts", true, {Type: "A"});

ExecuteHierarchyMapping

Use ExecuteHierarchyMapping to perform a hierarchy mapping from a specified application. Hierarchy mappings are created in the Edit Database dashboard and map tables of the staging database to the hierarchies of the application model.

Syntax

--<<NON-SQL>>
ExecuteHierarchyMapping(string ApplicationUniqueName, string MappingName);

Example

--<<NON-SQL>>
ExecuteHierarchyMapping("Samples", "Hier_Accounts");

ExecuteFactMapping

Use ExecuteFactMapping to perform a mapping from a specified application. Cube mappings load fact data from a source table to a cube.

Syntax

--<<NON-SQL>>
ExecuteFactMapping(string application unique name, string mapping name,
{parameter of called mapping: <value or @parameter of this non-sql script>, parameter2 : <value or @parameter>|});

This performs a mapping in the current application:

--<<NON-SQL>>
ExecuteFactMapping(string mapping name,
{parameter of called mapping: <value or @parameter of this non-sql script>, parameter2 : <value or @parameter>|}

);

This performs a mapping in a specific application:

--<<NON-SQL>>
ExecuteFactMapping(string application unique name, string mapping name,
{parameter of called mapping: <value or @parameter of this non-sql script>, parameter2 : <value or @parameter>|}

);

Example

--<<NON-SQL>>
ExecuteFactMapping("Samples", "Cube_Accounts");

PublishCustomModelToOLAP

When you have used the object definition tables to develop a model, use PublishCustomModelToOLAP ("string application unique name"); to publish the model to OLAP.

Syntax

--<<NON-SQL>>
PublishCustomModelToOLAP("ApplicationUniqueName");

ValidateCustomOLAPModel

Use ValidateCustomOLAPModel to test whether an OLAP model that you have created in the Staging database is consistent and suitable for publication if required. The result is written to the application log.

A non-SQL script that uses the validation function fails if the validation detects errors in the OLAP model.

Syntax

Use this syntax to validate the current application's model:

--<<NON-SQL>>
ValidateCustomOLAPModel();

Use this syntax to validate the model of a specific application:

--<<NON-SQL>>
ValidateCustomOLAPModel(string application unique name);