Non-SQL functions in scripts

In non-SQL scripts, you can execute 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("TableName","QueryName",True)

Syntax

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

You can use a script to call 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.

Hierarchy mapping through Application Engine and Application Studio

This is an example of how a mapping script can be run through an Application Engine process that is called from Application Studio. 

--<<NON-SQL>>
void ExecuteHierarchyMapping(string ApplicationUniqueName, string MappingUniqueName)
@Description: "...";
@Category: "...";
@Parameter[ApplicationUniqueName]: "Mandatory - Application Unique Name";
@Parameter[MappingUniqueName]: "Mandatory - Hierarchy Mapping Unique Name";
{ ModelingExecuteHierarchyMapping(ApplicationUniqueName, MappingUniqueName); }

Fact mapping through Application Engine and Application Studio

This is an example of how a mapping script can be run through an Application Engine process that is called from Application Studio. 

--<<NON-SQL>>
void ExecuteFactMapping(string ApplicationUniqueName, string MappingUniqueName)
@Description: "...";
@Category: "...";
@Parameter[ApplicationUniqueName]: "Mandatory - Application Unique Name";
@Parameter[MappingUniqueName]: "Mandatory - Fact Mapping Unique Name";
{ ModelingExecuteFactMapping(ApplicationUniqueName, MappingUniqueName); }

ExecuteScript

Use ExecuteScript to execute a script within the current application.

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

Syntax

ExecuteScript(string scriptName);

Example

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

ExecuteLoadQuery

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

Syntax

--<<NON-SQL>>
ExecuteLoadQuery(string tableName, string queryName, bool clearData);

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

Example

--<<NON-SQL>>
ExecuteLoadQuery("My_Table", "Query1", TRUE);
"/*"This comment occupies
multiple lines"*/"

Example

--<<NON-SQL>>
ExecuteLoadQuery("My_Table", "Query1", TRUE);
ExecuteLoadQuery("My_Table", "Query2", FALSE);

ExecuteIntegrationTableLoadQuery

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

Syntax

ExecuteIntegrationTableLoadQuery (string tableName, string queryName, bool clearData);

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

Example

--<<NON-SQL>>
ExecuteIntegrationTableLoadQuery ("My_Int_Table", "IQuery", TRUE);

ExecuteHierarchyMapping

Use ExecuteHierarchyMapping to execute 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

ExecuteHierarchyMapping(string appName, string mappingName);

Example

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

ExecuteFactMapping

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

Syntax


ExecuteFactMapping("string appName", "string mappingName");

Example

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

PublishCustomModelToOLAP

When you have used the object definition tables to develop a model, use PublishCustomModelToOLAP to publish the model to OLAP.

Syntax


PublishCustomModelToOLAP("appName");