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:

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

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.