Custom schemas & transformations

The PIM connector exports and imports data from and into censhare PIM. For this purpose, the connector uses a multi-stage process in censhare and an external ETL (Extract, Transform, Load) tool. This article shows how to customize the process.

Target groups

• Administrators

Context

Product information management (PIM) is an application that runs in censhare Web. The PIM connector handles the data export and import using a third-party ETL software

Prerequisites

You should be familiar with the key concepts of the PIM connector. In order to configure export/import schemas, you need the respective knowledge of the third-party ETL software connected to censhare PIM.

Introduction

The censhare PIM connector is a bidirectional interface that collects and processes data records in order to export or import them from or into censhare PIM. Both processes (export and import) are configurable in the censhare Admin-Client. The PIM connector requires a third-party ETL (Extract, Transform, Load) process software that connects via a REST API. In the export direction, the ETL software calls the raw data from censhare to create an export file, for example, Excel. In the import direction, the ETL software passes the processed data to censhare. The import also allows to update existing data records.

The process stages of the PIM connector can be customized independently. The following table shows the configurable stages and available default configurations in the censhare standard setup.

JSON schemas

When you export data from censhare, in the first step the connector creates a JSON representation of the asset properties. When you import data into censhare, the ETL processor creates a JSON representation from the imported data sets. The JSON schema provides the data model for the import and update of assets. censhare maps the JSON data from the ETL processor to this JSON schema. The schemas are stored in a Module / Schema asset. The root object defines the asset type that is processed. The properties contain the mapping between the keys of the import/export data and the censhare feature ID:

"$ref": "#/definitions/person",
 "definitions": {
  "person": {
   "cs:asset.type": "person.",
   "cs:$no_new_masterdata": true,
   "title": "person",
   "type": "object",
   "properties": {
    "id": {
     "cs:feature.key": "censhare:asset.id",
     "type": "integer" },

The schemas are written in standard JSON schema format. The data bindings use censhare proprietary extension. For more information see http://json-schema.org. In the schemas, asset properties with multiple values are defined as arrays. Child properties of an asset feature are defined as objects inside the parent property.

In the censhare standard configuration, these three preconfigured schemas are ready to use with the respective assets and asset structures:

• Person schema: This schema works with Person assets and provides a data model for all person-related properties, like name, address and contact data.

• Product schema: This schema works with Product assets. It provides a data model for all product features like technical specifications, order and EAN numbers. It also works with marketing-related data like product texts and images. The product schema includes product items and therefore references the Product item schema, which can also be used as a stand-alone schema (see next point).

• Product item schema: This schema works as a standalone with Product item assets. It is part of the Product schema, which includes products and their product items. Its data model is similar to the Product schema.

Besides the asset data that are specific to each asset type (product data, person data, etc.), the JSON schema also retrieves generic data. These are, for example, asset ID, name, description and localized names. For a sample search for the default schemas provided in the censhare standard configuration. The resource keys are as follows: censhare:schema.productItem, censhare:schema.product, censhare:schema.person.

To customize the schemas, create resource replace variants from the default schemas. For example:

• Add properties that are not included or remove properties that are not needed for your business case.

• Create custom schemas for other asset types or asset structures. Default schemas are available for products (with and without product items) and persons.

• Customize the mapping of properties. Each property (for example: "title") is mapped to a an internal feature key (for example: "censhare:asset.name").

• Customize the unique identifiers that are used to check if an imported data record already exists in censhare. Unique identifiers determine whether an existing asset is updated or a new one created. For more information see the following section.

Customize unique identifiers

The import pipeline of the PIM connector can determine when to create a new asset and when to update an existing one. For this purpose, unique identifiers are used. If these match in both, the imported data and the existing asset data, censhare overwrites the existing data. No new asset is created, in order to avoid duplicates. You can define an array of multiple identifiers. These are checked in the respective order.

By default, only the Product schema uses unique identifiers. The following code snippet shows how these are implemented in the JSON schema:

"$ref": "#/definitions/product",
 "definitions": {"product": {
  "cs:asset.type": "product.",
  "cs:$no_new_masterdata": true,
  "cs:$selection_keys": [
   ["id"],
   ["uuid"],
   ["externalId"],
   ["gtin"],
   ["ean"],
   ["orderNumber"]
  ],

The code snippet defines the cs:$selection_keys for the asset type product. The array inside cs:$selection_keys contains an ordered list of keys that are checked during import. The keys ["id"], ["uuid"], ["externalId"], ["gtin"], ["ean"], ["orderNumber"] represent the respective properties. If they match with the data of an existing asset, no new asset is created. The imported data record overwrites the data of the existing asset.

To customize these properties, you can do the following:

• Change the order in which these properties are checked. For example: ["gtin"], ["ean"], ["id"], ["uuid"], ["externalId"], ["orderNumber"].

• Remove properties, if they are not needed. For example: ["id"], ["gtin"], ["ean"], ["orderNumber"].

• Add new properties that you want to use as unique identifiers. For example: ["id"], ["uuid"], ["externalId"], ["gtin"], ["ean"], ["articleNumber"], ["orderNumber"]. Note: You must add the property definition and the mapping for new keys, if your JSON schema does not contain them. For example:

  JAVA

  "articleNumber": {
   "cs:feature.key": "censhare:product.article-number",
   "type": "string"
  },

XSL transformation

In order to provide an exchange format that can be forwarded to the third-party ETL software configured with your PIM connector, you can use Java or XSLT templates. If XML is required as exchange format, the connector can be configured to transform the JSON data representation into XML first. This stage of the process can be added in your custom configuration in the censhare Admin-Client. The XML can then be transformed into the required exchange format.

The censhare standard configuration provides transformations for the export and import to and from Pentaho. If you are using Pentaho PDI CE as ETL process tool, these transformations work out of the box. If you are using other ETL tools, for example, Apache NiFi or Talend Open Studio please ask our professional services for assistance.

The XSLT transformation enhances and maps the raw XML data from censhare so that they can be parsed in Pentaho (or any other ETL solution). For one ETL export it is possible to configure different output formats, for example, Excel on a single sheet, Excel on several sheets or CSV. Each output format requires an Export / Import asset with the respective schemas. The XSLT transformation in this stage checks for available output formats and creates a dialog where users can select the desired one.

ETL transformations

Custom ETL export and import transformations are configured in the ETL software. These are third-party solutions and not part of censhare. Apart from the default configurations, censhare does not provide support for customizations. If you want to write custom export/import transformations, you should be familiar with the ETL software you are using.

Create custom Pentaho transformations

Pentaho's ETL engine is called Kettle. When you execute a data export or import, censhare passes the raw XML data to Pentaho. It also passes the information on which transformation must be executed by Pentaho. The Kettle transformations that are executed on the Pentaho server are stored in censhare in assets of the type Module / Configuration / Pentaho export and Module Configuration / Pentaho import. The master file of these assets is a ZIP archive that contains the following files:

Configuration

To create custom transformations use the transformations provided with the censhare standard configuration. These are, for example:

• Pentaho-Export-Excel-products-and-items-on-one-sheet

• Pentaho-Export-Excel-products-and-items-on-separate-sheets

The parameters and default values are as follows:

To test and run your custom Pentaho transformation, create a ZIP archive from the Kettle transformation file(s), Kettle job file (optional) and test data file. The test data file is optional. If used, it must be named "test-data.xml". Next, create a new asset in censhare with the Module / Configuration / Pentaho export asset type. or, respectively, a Module / Configuration / Pentaho import type. These two asset types have been introduced specifically for the Pentaho ETL solution. Upload the ZIP archive as master file to this asset. When executing an export or import in censhare, the connector checks all available Pentaho transformations and creates a drop-down list in the dialog where the user can select one of them. Your custom transformation should appear in the dialog.

Result

When executing a Pentaho export, users can select custom transformations.