SQL Mapping

The SQL Mapping feature is available to use for complex mapping requirements, and also may be used to replace multiple wildcard * to * mapping rules with a single pass of the database.

In this example, mapping takes approximately 3 minutes, and with a single SQL mapping rule is should only take about 30 seconds. A single SQL mapping rule can be used to replace all of the "like" rules, and would look like the following:

Image shows single SQL mapping rule.

The actual SQL that is generated and executed is as follows:

Image shows actual SQL.

In this case the SQL mapping was defined on the ACCOUNT dimension, and the other * to * mapping rules were deleted. The total time for this one SQL mapping rule was 29 seconds, and no other mapping rules were required.

The ACCOUNT and ENTITY dimension may be referenced by those names, but the other dimensions are mapped to UD dimensions. To find the set of dimensions you need for the SQL mapping, you need to either look at the Application definition, or the log file to see which dimensions to use. In this example Product and Scenario are mapped to UD1 and UD3. The source dimension members use the column without the "X," and the mapped values are in the column with an "X" as a suffix. For the ACCOUNT dimension, the value from the source file is in the column named ACCOUNT, and the mapped value is stored in the ACCOUNTX column. The SQL mapping is used to set the "X" column for each dimension.

This same type of mappings may be used in the Account Reconciliation, but note that the Profile dimension is classified as ACCOUNT, so any SQL mappings for the Profile dimension should be specified on the ACCOUNT dimension. Other dimensions in Account Reconciliation should be referenced based on the mapping defined in the application definition.

Each type of mapping uses resources differently, and the mapping performance is in the following order, where Explicit is the fastest, and Multi-Dim is the slowest:

  1. EXPLICIT
  2. IN
  3. BETWEEN and LIKE
  4. MULTI-DIM

Multi-dim mappings are the slowest mapping, and try to limit multi-dim rules for complex use cases where you need to use a combination of EXPLICIT and LIKE mapping. For example, ENTITY = 100 AND ACCOUNT LIKE 4*.

As an additional tuning strategy, you may be able to replace multi-dim mappings with explicit mappings by combining source dimensions. For example if ENTITY=100 AND ACCOUNT=4100 you can concatenate ENTITY and ACCOUNT as the source, and define an EXPLICIT mapping for 100-4000.

Note:

Although the performance is similar when the data volume is very large (greater than 3 million rows), SQL mappings can fail due to database governor limits. Import expressions are processed when the data is imported and does not involve a SQL operation so the import does not fail. For this reason for very large data volume it is recommended to use import expressions instead of SQL mapping.

SQL Mapping in Quick Mode

Quick Mode supports SQL-based expression mappings, enabling integrations that use SQL expressions in Standard Mode to achieve improved performance with Quick Mode.

SQL mapping is useful when mapping logic needs to reference multiple columns from the current source row or when migrating complex SQL mappings from Standard Mode to Quick Mode. When migrating an existing integration, review any dependencies on subqueries, external tables, multidimensional mappings, or Oracle-specific SQL functionality before enabling the integration in Quick Mode.

How SQL Mapping Works in Quick Mode

Unlike Standard Mode, where data can be persisted to TDATASEG_T before SQL processing, Quick Mode processes the source file directly in Java. To support SQL mappings in this processing model, Quick Mode establishes a database connection and evaluates the mapping SQL for each source row that meets the applicable source criteria.

Enter the SQL mapping as a single row SELECT expression. Source values that need to be read from the input file must be enclosed in dollar ($) delimiters. These source-value tokens correspond to the dimensions mapped in the import format.
For example:
SELECT
    CASE
        WHEN $ENTITY$ IS NOT NULL THEN
            REPLACE(
                SUBSTR($ENTITY$, 2, INSTR($ENTITY$, '.') - 2),
                ']',
                ''
            ) || 'Elim'
        ELSE
            NULL
    END
FROM DUAL
When Quick Mode processes the mapping, it identifies the source-value tokens, reads their values from the current source-file row, and replaces the tokens with bind variables. For example, the preceding SQL is executed in the following form:
SELECT
    CASE
        WHEN :1 IS NOT NULL THEN
            REPLACE(
                SUBSTR(:1, 2, INSTR(:1, '.') - 2),
                ']',
                ''
            ) || 'Elim'
        ELSE
            NULL
    END
FROM DUAL

The SQL expression is executed for each qualifying source row. Therefore, use SQL mappings in Quick Mode only when other mapping options are not feasible.

Configuring SQL Expressions in Quick Mode

SQL expressions can be configured in two locations:

  • Dimension-level target expressions: Define a SQL expression as a target expression for a dimension:
    1. Select SQL as the target expression type.
    2. Enter the required SQL expression.
    3. Enclose source column names in dollar ($) symbols.

    For example, an expression that determines the target value for the Account dimension can also reference Entity, Amount, or other columns from the current source row.

  • Member mappings using Process Map expressions: Use SQL expressions in member mappings:
    1. Add the Process Map target expression.
    2. Create the required member mapping.
    3. Specify the appropriate sequence for the source.
    4. Select #SQL for the target.
    5. Enter the SQL expression in the Script field.

    The configuration is similar to SQL mappings in Standard Mode, making it easier to migrate existing integrations to Quick Mode.

Referencing Source Columns

SQL expressions operate on the current source row and can reference any supported column available on that row. A single expression can combine values from multiple source columns.

Enclose source columns that need to be evaluated by the SQL expression in dollar ($) delimiters. For example:

$ENTITY$

A CASE expression can evaluate Account, Entity, Amount, and user-defined dimensions together to determine the resulting mapped value.

When referencing dimensions:

  • Use the dimension column names for Account and Amount.
  • For other dimensions, use the corresponding UD column names, as applicable.

Cross-column expressions are supported. This allows the target expression for one dimension to evaluate values from several source columns before determining the target value.

This capability is particularly useful when mapping logic depends on multiple columns and would otherwise require complex conditional mappings.

Combining SQL and Non-SQL Mappings

SQL mappings can be combined with regular, non-SQL mappings in the same integration.

Quick Mode evaluates the configured processing order, matches the source data, and executes the applicable mapping. This allows existing mapping strategies to be retained while introducing SQL only for mapping logic that requires more complex processing.

The resulting mapped data is intended to be consistent with equivalent SQL mappings executed in Standard Mode.

Limitations

SQL mappings in Quick Mode have the following limitations:

  • SQL expressions operate only on data available in the current source row. They cannot retrieve values from other source rows or reference other database tables. Hence, you can’t reference TDATASEG_T and other database tables from the mapping expression.
  • Target columns such as ACCOUNTX, ENTITYX, and other target values cannot be referenced.
  • The expression must be a single-row expression and should not contain complex subqueries or other constructs that require multi-row or complex query processing.
  • Multidimensional mappings available in Standard Mode aren’t supported in Quick Mode.
  • Although many commonly used SQL expressions can be migrated from Standard Mode, expressions that depend on Oracle-specific SQL functionality should be reviewed and tested before migration.