Skip to main content
Skip table of contents

CONFIG_LOAD_TRANSFORMATIONS

CONFIG_LOAD_TRANSFORMATIONS is a configuration package that is used to configure custom load transformations, such as defining custom transformation logic for source entity attributes.


See also:


Usage

  1. Open the configuration package from Designer package list.

  2. Click Export package in the Actions tab.

  3. Click Download.

  4. Edit JSON file.

  5. Import edited JSON back to Designer with Import Package.

After a successful import, changes to the configuration package have been applied to Designer only. It is recommended to deploy the same changes to Runtime environments as well.


Contents

ATTR transformation type configuration

Attribute transformation type configurations are managed inside the JSON array block named attrTransformationTypes.

Key

Value type

Example

Description

transformationType

String

HASH

The name/type of the transformation.

isShownInDesigner

Boolean

true

Toggles the visibility of the transformation type in Designer.

ordinalPositionInDesigner

Integer

1

Toggles the order in which the transformation types are shown in Designer.

You can add a new transformation type or update the contents of an existing transformation type.

Be careful when editing existing transformation types!

Note that existing transformation types are utilised in Agile Data Engine load and view generation logic. Therefore, it is recommended to create new transformation types for specific use cases instead of editing existing ones to avoid unwanted misbehavior.

Default transformation types

Transformation type

Description

CUSTOM

User defined transformation for the attribute in DBMS specific SQL Dialect. Can contain environment variables.

HASH

Data Vault HASH transformation.

CONCAT

Concatenation of the mapped attributes.

CURRENT_TS

Current Timestamp

DV_STATUS

Status satellite activity indicator (1 or 0)

DV_BUSINESS_KEY

Data Vault Business Key transformation

ST_ASTEXT

GEOGRAPHY/GEOMETRY to WKT format

ST_ASBINARY

GEOGRAPHY/GEOMETRY to WKB format

ST_GEOGFROMTEXT

WKT formatted spatial information to GEOGRAPHY

ST_GEOGFROMWKB

WKB formatted spatial information to GEOGRAPHY

ST_GEOGFROMTEXT_FLAT

WKT format spatial information to GEOMETRY

ST_GEOGFROMWKB_FLAT

WKB formatted spatial information to GEOMETRY

RUN_ID

Unique RUN_ID for each transaction

DV_RUNNING_ID

Data Vault DV_RUNNING_ID transformation.

HASH_LIST

Data Vault HASH list transformation.

Example: Transformation Type Configuration

JSON
"attrTransformationTypes": [
  . . .
  {
    "transformationType": "HASH",
    "isShownInDesigner": true,
    "ordinalPositionInDesigner": 2
  }
  . . .
]

Hide unneeded transformations

If a transformation formula is not needed it can be hidden from the Designer by setting the isShownInDesigner as false.

Transformation types can also be removed completely by removing them from the configuration package, but this is not recommended.


DBMS transformation formula configuration

DBMS transformation formula configurations are managed inside the JSON array block named dbmsTransformationFormulas. The transformation formulas are DBMS product specific.

Key

Value type

Example

Description

transformationType

String

CUSTOM

The transformation type. For more information, refer to the Transformation Type Configuration section above.

dbmsProduct

String

REDSHIFT

The DBMS product.

transformationFormula

String

Optional. The transformation formula itself needs to be in Freemarker syntax and it has currently access to sourceAttributes list information (so access to datatype and name information). The end result of the formula will be compressed into a single line format and due to this, please do not use single line sql comments in your solution.

Variables available for the transformation formula:

Variable

Description

sourceAttributes

Contains attributeName and datatype for the source attribute linked to this formula

sourceEntity

Access to source entity owner and entityName details)

loadOptDefined

Function to test whether certain load option is set for the load

loadOpt

Provides the requested load option

batchLoading

Indicates whether batch loading has been enabled in this load

keyAttributeList

Contains the sourceAttributes of the HASH KEY field in the load (used in the multi-active satellite)

prefix

Prefix required for the source attributes given by the calling load [

You can add a new transformation formula or update the contents of an existing transformation formula.

Example: DBMS Transformation Formula Configuration

JSON
"dbmsTransformationFormulas": [
  . . .
  {
    "transformationType": "MODIFIED_HASH_FOR_RS",
    "dbmsProduct": "REDSHIFT",
    "transformationFormula": "MD5(UPPER(<#list sourceAttributes as attribute>\n<#if attribute.datatype?? && attribute.datatype?lower_case?contains(\"char\")>\nNVL(${attribute.attributeName}, '-1')\n<#elseif attribute.datatype?? && attribute.datatype?lower_case?contains(\"bool\")>\nNVL(CAST(CAST(${attribute.attributeName} AS INT) AS VARCHAR), '-1')\n<#else>\nNVL(CAST(${attribute.attributeName} AS VARCHAR), '-1')\n</#if>\n<#if attribute_has_next> || '#' || </#if></#list>))"
  }
  . . .
]

If an existing customized transformation formula needs to be deleted from the system, simply remove it from the set of values before importing the configuration package. Once this is done, Agile Data Engine will revert back to the default behavior of the transformation type.

If the transformation formula requires repetitive steps, the definition can provide macros in the beginning of the formula definition (example: Customized hash list function for Azure Sql Database).


Examples

Customized transformation formula for Data Vault Hub entities

In this example, we will define a custom transformation formula for Hub hash keys following the these guidelines:

  • MD5 hash in 16 byte BINARY

  • UPPER function for all source attributes

  • Leading and trailing whitespace trimmed using TRIM function

  • If composite key, add ~ between key attributes

  • If null value, use '00' value in BINARY

The transformation formula would be the following:

JSON
  "attrTransformationTypes": [
      {
      "transformationType": "CUSTOM_HUB",
      "shortDescription": "Transformation for Hub",
      "description": "Transformation formula to be used for Hub-entities. Target field should be BINARY(16).",
      "isShownInDesigner": true,
      "ordinalPositionInDesigner": 14
    }
  ],
  "dbmsTransformationFormulas": [
    {
      "transformationType": "CUSTOM_HUB",
      "dbmsProduct": "SNOWFLAKE",
      "transformationFormula": "NVL(MD5_BINARY(UPPER(<#list sourceAttributes as attribute>TRIM(${attribute.attributeName}::STRING)<#if attribute_has_next> || '~' ||</#if></#list>)), '00'::BINARY)"
    }
  ]

Customized hash list function for Azure SQL Database

In this example, we will define a custom transformation formula for the multi-active satellite hash list key in Azure SQL Database following this guideline:

  • Using SHA2_256 instead of MD5

The transformation formula would be the following:

JSON
  "attrTransformationTypes": [
      {
      "transformationType": "HASH_LIST_SHA2",
      "shortDescription": "Hash list with SHA2",
      "description": "Transformation formula with SHA2 hashing",
      "isShownInDesigner": true,
      "ordinalPositionInDesigner": 17
    }
  ],
  "dbmsTransformationFormulas": [
    {
      "transformationType": "HASH_LIST_SHA2",
      "dbmsProduct": "MS_SQL",
      "transformationFormula": "<#macro castClouse attribute prefix=\"\">    <#if attribute.datatype?? && ( attribute.datatype?lower_case?contains(\"nvarchar\") || attribute.datatype?lower_case?contains(\"nchar\") )>      TRIM(COALESCE(CAST(${prefix}[${attribute.attributeName}] AS VARCHAR(MAX)), '-1'))    <#elseif attribute.datatype?? && attribute.datatype?lower_case?contains(\"char\")>     TRIM(COALESCE(${prefix}[${attribute.attributeName}], '-1'))    <#elseif attribute.datatype?? && attribute.datatype?lower_case?contains(\"geo\")>      COALESCE(CAST(${prefix}[${attribute.attributeName}].STAsText() AS VARCHAR(MAX)), '-1')    <#elseif attribute.datatype?? && attribute.datatype?lower_case?contains(\"bool\")>      COALESCE(CAST(CAST(${prefix}[${attribute.attributeName}] AS INT) AS VARCHAR), '-1')    <#else>      COALESCE(CAST(${prefix}[${attribute.attributeName}] AS VARCHAR), '-1')    </#if></#macro><#macro sourceAttributeList sourceAttributes prefix=\"\">    <#if sourceAttributes?size == 0 >        null    <#else>        <#list sourceAttributes as attribute><@castClouse attribute prefix/><#if attribute_has_next> + '~' + </#if></#list>    </#if></#macro> ( SELECT CONVERT(VARCHAR(32),HASHBYTES('SHA2_256',   STRING_AGG(<@sourceAttributeList sourceAttributes/>)  WITHIN GROUP (    ORDER BY <@sourceAttributeList sourceAttributes/>   ) ),2) FROM [${sourceEntity.owner}].[${sourceEntity.entityName}] value_list WHERE<#if useBatchLoading>    <#if sourceEntity.containsRunIdAttribute>        value_list.[${sourceEntity.runIdAttributeName}] IN (<loadablerunids>)    <#else>        value_list.<sourcerunidattr> IN (<loadablerunids>)     </#if></#if><#if loadOptDefined(\"OPT_WHERE\")><#if useBatchLoading>AND </#if>${loadOpt(\"OPT_WHERE\")}</#if><#list keyAttributeList as keyAttribute>    <#if useBatchLoading || loadOptDefined(\"OPT_WHERE\")>AND </#if>     <@castClouse keyAttribute \"value_list.\"/>=<@castClouse keyAttribute \"src_entity.\"/></#list>)"
    }
  ]
JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.