Skip to content

How to Define and Use Data Transformation Definitions (DTD) in CDQ Cloud Apps

Overview

Data Transformation Definitions (DTD) allow you to map, transform, and enrich data between your source systems and the CDQ data model. This guide explains the structure of a DTD and provides practical mapping examples.

Learning Goals

In this tutorial, you will learn how to:

  • Understand the structure of a DTD
  • Create mappings between source and target attributes
  • Apply value mappings and transformations
  • Use advanced mapping techniques (arrays, constants, selections, etc.)

Understand the Structure of a DTD

A DTD consists of the following main sections:

  • imports: Reuse mappings from another DTD.
  • name: Name of your DTD.
  • type: Type of data (e.g., Business Partner, Business Partner Updates).
  • mappings: Define how to map data from input (source) to output (target).
  • reverseMappings: Define reverse mappings (output to input).
  • valueMappings: Specify how to convert values from input to output.
  • values: Define allowed values for attributes.

Here is a basic template of a DTD:

Loading...

Use interactive console to build your Data Transformation Definition.

Create a DTD Based on Examples

Follow the examples below to implement common mapping scenarios.

Map Company Name

Source data:

{
    "companyName": "Adidas AG"
}

Target mapping:

{
    "businessPartner": {
        "names": [
            { "value": "Adidas AG" }
        ]
    }
}

DTD mapping:

{
  "mappings": [
    {
      "sourceAttributes": ["companyName"],
      "targetAttributes": ["businessPartner.names[0].value"]
    }
  ]
}

Map Business Partner Status Using Value Mappings

Source data:

{ "inactive": "0" }

Value mapping:

  • "0" → "INACTIVE"
  • "1" → "ACTIVE"

DTD mapping:

{
  "valueMappings": [
    {
      "sourceValue": "0",
      "targetValue": "INACTIVE",
      "targetAttribute": "businessPartner.status.technicalKey"
    },
    {
      "sourceValue": "1",
      "targetValue": "ACTIVE",
      "targetAttribute": "businessPartner.status.technicalKey"
    }
  ],
  "mappings": [
    {
      "sourceAttributes": ["inactive"],
      "targetAttributes": ["businessPartner.status.technicalKey"]
    }
  ]
}

Transform Data to Uppercase

Source data:

{ "id": "idToUpperCase" }

Target mapping:

{ "externalId": "IDTOUPPERCASE" }

DTD mapping:

{
  "mappings": [
    {
      "sourceAttributes": ["id"],
      "targetAttributes": ["externalId"],
      "targetTransformations": [
        { "transformationType": "TO_UPPERCASE" }
      ]
    }
  ]
}

Use Constant Mapping

Target mapping:

{
  "businessPartner": {
    "addresses": [
      {
        "country": { "shortName": "PL" }
      }
    ]
  }
}

DTD mapping:

{
  "mappings": [
    {
      "targetAttributes": ["businessPartner.addresses[0].country.shortName"],
      "targetTransformations": [
        {
          "transformationType": "CONSTANT_MAPPING",
          "constantValue": "PL"
        }
      ]
    }
  ]
}

Map Value and Type Together

Source data:

{ "localCompanyName": "Company Name" }

Target mapping:

{
  "businessPartner": {
    "names": [
      {
        "value": "Company Name",
        "type": { "technicalKey": "LOCAL" }
      }
    ]
  }
}

DTD mapping:

{
  "mappings": [
    {
      "sourceAttributes": ["localCompanyName"],
      "targetAttributes": [
        "businessPartner.names[0].value",
        "businessPartner.names[0].type.technicalKey"
      ],
      "targetTransformations": [
        {
          "transformationType": "CONSTANT_MAPPING",
          "targetAttributes": ["businessPartner.names[0].type.technicalKey"],
          "constantValue": "LOCAL"
        }
      ]
    }
  ]
}

Map Arrays Using Wildcards

Source data:

{
  "names": [
    { "value": "name 1" },
    { "value": "name 2" }
  ]
}

Target mapping:

{
  "businessPartner": {
    "names": [
      { "value": "name 1" },
      { "value": "name 2" }
    ]
  }
}

DTD mapping:

{
  "mappings": [
    {
      "sourceAttributes": ["names[*].value"],
      "targetAttributes": ["businessPartner.names[*].value"]
    }
  ]
}

Select First Existing Value (XOR)

Source data:

{
  "names": [
    { "shortName": "short name" },
    { "value": "first existing value" },
    { "value": "second existing value" }
  ]
}

Target mapping:

{ "NAME": "first existing value" }

DTD mapping:

{
  "mappings": [
    {
      "sourceAttributes": [
        "names[0].value",
        "names[1].value",
        "names[2].value"
      ],
      "sourceSelections": [
        { "selectionType": "XOR" }
      ],
      "targetAttributes": ["NAME"]
    }
  ]
}

Concatenate Values

Source data:

{
  "names": [
    { "line1": "name 0" },
    { "line2": "name 1" },
    { "line3": "name 2" }
  ]
}

Target mapping:

{
  "names": [
    { "value": "name 0, name1, name2" }
  ]
}

DTD mapping:

{
  "mappings": [
    {
      "sourceAttributes": [
        "names[0].line1",
        "names[0].line2",
        "names[0].line3"
      ],
      "sourceSelections": [
        { "selectionType": "CONCATENATION", "value": ", " }
      ],
      "targetAttributes": ["names[0].value"]
    }
  ]
}

Select First Value or ShortName in Array

Source data:

{
  "names": [
    { "shortName": "short name 0" },
    { "value": "name 1" },
    { "value": "name 2" }
  ]
}

Target mapping:

{
  "names": [
    { "value": "short name 0" },
    { "value": "name 1" },
    { "value": "name 2" }
  ]
}

DTD mapping:

{
  "mappings": [
    {
      "sourceAttributes": ["names[*].value", "names[*].shortName"],
      "sourceSelections": [
        { "selectionType": "XOR" }
      ],
      "targetAttributes": ["names[*].value"]
    }
  ]
}

Split Long Name into Multiple Lines

Source data:

{ "name": "CDQ AG Corporate Data League Very Long Name" }

Target mapping:

{
  "names": [
    { "value": "CDQ AG Corporate" },
    { "value": "Data League Very" },
    { "value": "Long Name" }
  ]
}

DTD mapping:

{
  "mappings": [
    {
      "sourceAttributes": ["name"],
      "targetAttributes": ["names[*].value"],
      "targetTransformations": [
        { "transformationType": "WORD_LENGTH_SPLIT", "constantValue": "17" }
      ]
    }
  ]
}

Map Many Identifiers Except a Specific One

Source data:

{
  "identifiers": [
    { "value": "A", "type": "X" },
    { "value": "B", "type": "X" },
    { "value": "C", "type": "EU_VAT_ID_ABC" },
    { "value": "D", "type": "X" }
  ]
}

Target mapping:

{
  "IDENTIFIER_1": "A",
  "IDENTIFIER_2": "B",
  "IDENTIFIER_3": "D"
}

DTD mapping:

{
  "mappings": [
    {
      "sourceAttributes": [
        "identifiers[?(@.type =~ /^(?!EU_VAT_ID_).*?/i)].value"
      ],
      "targetAttributes": [
        "IDENTIFIER_1",
        "IDENTIFIER_2",
        "IDENTIFIER_3"
      ],
      "targetTransformations": [
        { "transformationType": "MULTIPLE_SOURCE_AND_TARGET_ATTRIBUTE_SUPPORT" }
      ]
    }
  ]
}

Provide Default Value if Source is Empty

Source data:

{
  "country1": "",
  "country2": "PL",
  "country3": null
}

Target mapping:

{
  "COUNTRY_CODE_1": "CH",
  "COUNTRY_CODE_2": "PL",
  "COUNTRY_CODE_3": "CH"
}

DTD mapping:

{
  "mappings": [
    {
      "sourceAttributes": ["country1"],
      "targetAttributes": ["COUNTRY_CODE_1"],
      "targetTransformations": [
        { "transformationType": "DEFAULT_IF_EMPTY", "defaultValue": "CH" }
      ]
    },
    {
      "sourceAttributes": ["country2"],
      "targetAttributes": ["COUNTRY_CODE_2"],
      "targetTransformations": [
        { "transformationType": "DEFAULT_IF_EMPTY", "defaultValue": "CH" }
      ]
    },
    {
      "sourceAttributes": ["country3"],
      "targetAttributes": ["COUNTRY_CODE_3"],
      "targetTransformations": [
        { "transformationType": "DEFAULT_IF_EMPTY", "defaultValue": "CH" }
      ]
    }
  ]
}

Select First or Last Value from an Array

Source data:

{
  "businessPartner": {
    "names": [
      { "type": { "technicalKey": "LOCAL" }, "value": "Name 1" },
      { "type": { "technicalKey": "LOCAL" }, "value": "Name 2" },
      { "type": { "technicalKey": "LOCAL" }, "value": "Name 3" }
    ]
  }
}

Target mapping:

{
  "organization": {
    "nameDetails": { "formattedOrgName": "Name 1" }
  }
}

DTD mapping:

{
  "mappings": [
    {
      "sourceAttributes": [
        ".names[?(@.type.technicalKey=='LOCAL')].value.getFirst()"
      ],
      "targetAttributes": [
        "organization.nameDetails.formattedOrgName"
      ],
      "targetTransformations": []
    }
  ]
}

Change Values Without Changing Structure

Source data:

"legalForm": {
  "name": "Spółka akcyjna",
  "mainAbbreviation": "S.A.",
  "technicalKey": "PL_2629"
}

Target mapping:

"legalForm": {
  "name": "Spółka akcyjna",
  "mainAbbreviation": "SA",
  "technicalKey": "PL_2629"
}

DTD mapping:

{
  "dataTransformationDefinition": {
    "mappings": [],
    "reverseMappings": [],
    "valueMappings": [
      {
        "sourceConditions": [
          {
            "sourceValue": "PL",
            "sourceAttribute": "businessPartner.addresses[0].country.shortName"
          }
        ],
        "sourceValue": "S.A.",
        "targetValue": "SA",
        "targetAttribute": "businessPartner.legalForm.mainAbbreviation"
      }
    ]
  }
}

Your opinion matters!

We are constantly working on providing an outstanding user experience with our products. Please share your opinion about this tutorial!

Previous page
Next page