multisource_mode

Introduction

Agile Data Engine normally supports single source entity mappings in automatically generated loads, but as more complex scenarios appear, this limitation has been removed for FTL Templated loads

Activation

Load generation can read all the mapped sources with mapping type SOURCE when following environment variable exists for the environment and it is set to one of the multisource options:

system.sql.multisource_mode

Options

Source attribute prefixes

As the attribute can now be pointing to different source entities, the prefix handling has been given two additional presets:

• PREFIX_MODE_MAPPING_NAME - prefix will be populated with the source mapping name eg. “STAGE_TO_HUB”

• PREFIX_MODE_MAPPING_ORDINAL -prefix will be populated according to the ordinal position of the source entity mapping eg. src1 or src2

This only works with the prefix handler provided with ADE

If you have used your own prefix logic in CONFIG_LOAD_TRANSFORMATIONS,

please replace the prefix handler calls, e.g.

<@prefixHandler prefix/>

with

<@utils.adePrefixHandler prefix attribute/>

where attribute should be referring to the source attribute involved

Example

GENERIC SETTINGS

FTL Template definition in CONFIG_LOAD_TEMPLATES

<#assign target = model.load.target>
<#assign source1 = model.load.sources[0]>
<#assign source1Key = loads.getAttributeForType(source1.attributes, "SDT_STAGE_ID")>
<#assign source2 = model.load.sources[1]>
<#assign source2Key = loads.getAttributeForType(source2.attributes, "SDT_STAGE_ID")>
INSERT INTO ${target.schemaName}.${target.name} (
    <@sql.generateEntityAttributeList attributes=target.attributes/>
)
SELECT
<#list target.attributes as attribute>
    ${attribute?is_first?then('',',')} ${loads.getSingleAttributeTransformation(attribute, "PREFIX_MODE_MAPPING_NAME")}
</#list>
FROM
    ${source1.schemaName}.${source1.name} ${source1.mappingName}
JOIN
    ${source2.schemaName}.${source2.name} ${source2.mappingName}
ON (${source1.mappingName}.${source1Key.name} = ${source2.mappingName}.${source2Key.name})    

Entity definition

TARGET schema.TARGET:

SOURCE schema.SOURCE1:

SOURCE schema.SOURCE2:

Load step definition

We define a new override load step for the load:

<ftltemplate:multi_source_example>

Single-source mapping

In this scenario we want to combine two stage entities into one target entity so that certain target entity attributes are concatenated together, but we choose the default multisource mode, SINGLE.

Environment variable setting in CONFIG_ENVIRONMENT_VARIABLES

We have defined environment variable system.sql.multisource_mode as SINGLE (or it has not been set).

Load source entity mappings

In the load (load_target_from_source_entity_01) we have two mappings:

source1_to_target (source entity SOURCE1, position 1, mapping type SOURCE)

source2_to_target (source entity SOURCE2, position 2, mapping type SOURCE)

Result

This produces following content (syntax might vary between dbms products) as the multisource mode SINGLE stops processing the source mappings after the first one:

INSERT INTO schema.TARGET (
    key
    , combined_data
)
SELECT
     source1_to_target.key
    , (NVL(source1_to_target.data1, '-1') || '~' || NVL(source1_to_target.data2, '-1') || '~' || NVL(source1_to_target.data3, '-1'))
FROM
    schema.SOURCE1 source1_to_target
JOIN
    schema.SOURCE2 source2_to_target
ON (source1_to_target.key = source2_to_target.key);

Multi-source mapping with matching transformation type

In this scenario we want to combine two stage entities into one target entity so that certain target entity attributes are concatenated together.

Environment variable setting in CONFIG_ENVIRONMENT_VARIABLES

We have defined environment variable system.sql.multisource_mode as MULTI_COMBINE_TRANSFORMATION_ON_MATCH

Load source entity mappings

In the load (load_target_from_source_entity_01) we have two mappings:

source1_to_target (source entity SOURCE1, position 1, mapping type SOURCE)

source2_to_target (source entity SOURCE2, position 2, mapping type SOURCE)

Result

This produces following content (syntax might vary between dbms products):

INSERT INTO schema.TARGET (
    key
    , combined_data
)
SELECT
     source1_to_target.key
    , (NVL(source1_to_target.data1, '-1') || '~' || NVL(source1_to_target.data2, '-1') || '~' || NVL(source1_to_target.data3, '-1') || '~' || NVL(source2_to_target.data1, '-1') || '~' || NVL(source2_to_target.data2, '-1') || '~' || NVL(source2_to_target.data3, '-1'))
FROM
    schema.SOURCE1 source1_to_target
JOIN
    schema.SOURCE2 source2_to_target
ON (source1_to_target.key = source2_to_target.key);

Multi-source mapping without matching transformation type

In this scenario we want to combine two stage entities into one target entity so that certain target entity attributes are concatenated together. In this case the mappings have different transformation types set.

Environment variable setting in CONFIG_ENVIRONMENT_VARIABLES

We have defined environment variable system.sql.multisource_mode as MULTI_COMBINE_TRANSFORMATION_ON_MATCH

Load source entity mappings

In the load (load_target_from_source_entity_01) we have two mappings:

source1_to_target (source entity SOURCE1, position 1, mapping type SOURCE)

source2_to_target (source entity SOURCE2, position 2, mapping type SOURCE)

Result

This produces following content (syntax might vary between dbms products) and as the chosen multisource mode was MULTI_COMBINE_TRANSFORMATION_ON_MATCH, only the first mapping appear in the result:

INSERT INTO schema.TARGET (
    key
    , combined_data
)
SELECT
     source1_to_target.key
    , (NVL(source1_to_target.data1, '-1') || '~' || NVL(source1_to_target.data2, '-1') || '~' || NVL(source1_to_target.data3, '-1'))
FROM
    schema.SOURCE1 source1_to_target
JOIN
    schema.SOURCE2 source2_to_target
ON (source1_to_target.key = source2_to_target.key);

Multi-source mapping with first transformation type

In this scenario we want to combine two stage entities into one target entity so that certain target entity attributes are concatenated together. The chosen multisource mode is MULTI_COMBINE_TRANSFORMATION_WITH_FIRST.

Environment variable setting in CONFIG_ENVIRONMENT_VARIABLES

We have defined environment variable system.sql.multisource_mode as MULTI_COMBINE_TRANSFORMATION_WITH_FIRST

Load source entity mappings

In the load (load_target_from_source_entity_01) we have two mappings:

source1_to_target (source entity SOURCE1, position 1, mapping type SOURCE)

source2_to_target (source entity SOURCE2, position 2, mapping type SOURCE)

Result

This produces following content (syntax might vary between dbms products) and as the chosen multisource mode was with MULTI_COMBINE_TRANSFORMATION_WITH_FIRST, both mappings appear in the result even the transformation type differ between the mappings:

INSERT INTO schema.TARGET (
    key
    , combined_data
)
SELECT
     source1_to_target.key
    , (NVL(source1_to_target.data1, '-1') || '~' || NVL(source1_to_target.data2, '-1') || '~' || NVL(source1_to_target.data3, '-1') || '~' || NVL(source2_to_target.data1, '-1') || '~' || NVL(source2_to_target.data2, '-1') || '~' || NVL(source2_to_target.data3, '-1'))
FROM
    schema.SOURCE1 source1_to_target
JOIN
    schema.SOURCE2 source2_to_target
ON (source1_to_target.key = source2_to_target.key);

Multi-source mapping with failure on transformation type mismatch

In this scenario we want to combine two stage entities into one target entity so that certain target entity attributes are concatenated together. The chosen multisource mode is MULTI_COMBINE_TRANSFORMATION_FAIL_ON_MISMATCH.

Environment variable setting in CONFIG_ENVIRONMENT_VARIABLES

We have defined environment variable system.sql.multisource_mode as MULTI_COMBINE_TRANSFORMATION_FAIL_ON_MISMATCH

Load source entity mappings

In the load (load_target_from_source_entity_01) we have two mappings:

source1_to_target (source entity SOURCE1, position 1, mapping type SOURCE)

source2_to_target (source entity SOURCE2, position 2, mapping type SOURCE)

Result

This produces an error as the transformation types differ between the mappings.