Parameters

AnalyticsCreator provides various parameters that can be modified to customize its functionality. To access the parameter settings page, navigate to Help → Parameters in the toolbar.
Once the parameter settings page is open, use the Search Criteria field to locate specific parameters.

Below is a list of parameters available for modification in AnalyticsCreator:

Scripts

Scripts are a set of SQL commands that will be executed under specific conditions. There are four types of scripts:

1. Pre-Deployment Script: This script is executed prior to DWH synchronization and before deployment.
2. Post-Deployment Script: This script is executed after DWH synchronization and before deployment.
3. Pre-Workflow Script: This script is executed in the workflow package before starting all other packages.
4. Post-Workflow Script: This script is executed in the workflow package after all other packages have finished.

Deployment Script Control

The deployment script can be disabled during synchronization or deployment by using the "Do Not Deploy" and "Do Not Synchronize" flags.

Below is a typical script definition:

Pre and Post-Deployment Scripts for Stored Procedures

Pre and Post-deployment scripts can be used to create stored procedures for use in transformations. In this case, the script should be executed only during Data Warehouse (DWH) synchronization.

Including the CREATE PROCEDURE script during deployment is unnecessary, as the procedure definition is already included in the deployment package.

Macros

A macro is a powerful tool used to simplify transformation definitions in AnalyticsCreator. Every macro has the following components:

• Name: The name of the macro.
• Language: The programming language used in the macro.
• Definition Statement: The logic or functionality defined within the macro.
• Referenced Table (optional): Used for auto-referencing in transformations.

Currently, two languages are supported: T-SQL and SSIS.

• T-SQL Macros: Used in transformations, calculated fields, and database objects.
• SSIS Macros: Used in SSIS statements for import constraints or field logic.

Macro Example

A typical macro definition:

Macro Statement and Parameters

Every macro uses positional parameters like :1, :2, etc. To call a macro, prefix it with @ and supply parameters in parentheses. For example:

@Date2ID(T1.BUDAT)

This will be parsed into:

CONVERT(bigint, ISNULL(DATEDIFF(DD, '20000101', CONVERT(date, T1.BUDAT)) + 1, 0))

Macro Parameters and NULL Replacement

If fewer parameters are passed than defined, the remaining placeholders will be replaced by NULL.

@Date2ID()

Results in:

CONVERT(bigint, ISNULL(DATEDIFF(DD, '20000101', CONVERT(date, NULL)) + 1, 0))

Referenced Table Parameter

The Referenced Table parameter allows automatic creation of a reference between a field and the referenced table, based on the macro logic.

Macro Usage in Transformations

Macros are commonly used in transformation column definitions. For example:

This will be parsed in the transformation VIEW as:

[FK_ModifiedDate] = CASE
    WHEN T1.ModifiedDate < '19800101' THEN 0 
    WHEN T1.ModifiedDate > '20401231' THEN CONVERT(bigint, DATEDIFF(DD, '19800101',  '20401231') + 1)
    ELSE CONVERT(bigint, ISNULL(DATEDIFF(DD, '19800101', CONVERT(date, T1.ModifiedDate)) + 1, 0))

Macro Updates

If a macro definition is changed, all dependent transformations and calculated fields will be recalculated automatically to reflect the change.