Non-SQL functions in scripts
--<<NON-SQL>>
--<<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
orFalse
, 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.
Syntax
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 OLAP object definition tables of the
Staging database to develop a model, use PublishCustomModelToOLAP
to
publish the custom model to OLAP. Object definition tables are
primarily used by OLAP Modeling.
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);
PublishCustomCubeToOLAP
When you have used the OLAP object definition tables of the
Staging database to develop a model, use PublishCustomCubeToOLAP
to publish
a custom cube to OLAP. Object definition tables are primarily used
by OLAP Modeling.
Syntax
--<<NON-SQL>>
PublishCustomCubeToOLAP(string applicationName, string cubeName, bool includeDimensions);
By default, all dimensions of a cube are published. Set the
includeDimensions
parameter to False to publish
only these parts:
- Properties
- Rules
- Rule Groups
- Facts, if a fact table is assigned to the cube
Currently, you can run the PublishCustomCubeToOLAP
function only for cubes
of the Data type.
PublishCustomDimensionToOLAP
When you have used the OLAP object definition tables of the
Staging database to develop a model, use PublishCustomDimensionToOLAP
to
publish a custom dimension to OLAP. Object definition tables are
primarily used by OLAP Modeling.
Syntax
--<<NON-SQL>>
PublishCustomDimensionToOLAP(string applicationName, string dimensionName);
With the PublishCustomDimensionToOLAP
function, these parts are
published:
- Properties
- Attributes
- Hierarchies
- Subsets
- DAC Table, if applicable
- Data Roles, if applicable