Defining canonical schemas for data publishing

You can apply transformation logic to NexJ CRM data as it is exported to Kafka, HTTP or JSON File target types. This makes it even easier to integrate CRM with Enterprise Data Warehouse and other systems that require updates of CRM data in a specific organization-defined format.

Target data schemas allow configuring the complete set of attribute names, types, and values that are expected as a part of the output. Attribute values can be described with a formula-based calculation syntax, allowing for data transformation, including formulas with nested functions. You can also specify default values for each attribute.

Target data schemas are defined in JSON and imported into Data Bridge using the new Schemas Explorer workspace . Each data schema is subject-area specific and can be applied to one or more CRM Views based on the same subject area (for example, Companies).

To use a data schema to transform exported data to the expected target format:

Define the schema in a JSON file, using the elements and functions described below.
Use the Schema Explorer workspace to add the schema to the application or to update an existing schema to the latest version.
When defining a CRM view on the View Explorer workspace , specify the schema in the Create View or Edit View dialog on the Data Selection tab.

Canonical schema example

JSON

{
   "name":"Canonical Client Record",
   "description":"A canonical representation of a Client record",
   "subjectArea":"dataengine:Companies",
   "targetFields":[
      {
         "name":"client_name",
         "type":"string",
		 "value":"a:lastName",
         "default":"n/a"
      },
	  {
         "name":"addresses",
         "type":"object",
         "value":"a:addrs",
         "collection":true,
         "fields":[
            {
               "name":"full_address",
               "type":"string",
               "value":"f:stringAppend a:address1 \", \" a:city \", \" a:country",
			   "default":""
            }
         ]
      },
	  {
         "name":"classification",
         "type":"string",
         "default":"restricted"
      },
	  {
         "name":"portfolio_total",
         "type":"decimal",
		 "value":"a:totalAssets",
         "default":"0"
      }	  
      
   ]
}

This example describes the following four target fields:

The string field client_name is mapped to the source attribute lastName. The default value for this field is “n/a”.
The addresses target field is an object mapped to the collection attribute addrs, where the value is calculated by using the formula stringAppendto combine sub-attributes address1, city, and country.
The string field classification in not mapped to any source attributes. The default value for this field is “restricted”.
The decimal field portfolio_total is mapped to the source attribute totalAssets. The default value for this field is “0”.

Data schema structure and syntax

The data schema is in a JSON format that includes the following elements.

Root elements

Name	Description
name	Required The caption that will be displayed on the interface for this data schema. Only one active data schema with the same name and subject area can exist.
description	The description of the data schema.
subjectArea	Required The subject area that the data schema is referencing. Only views that use the same subject area can use this data schema. The subjectArea must be referencing an existing subject area. When updating a data schema, the subjectArea cannot be changed. Only one active data schema with the same name and subject area can exist.
targetFields	Required The fields that will be used in the Publishing Target. It includes various parameters for each field such as the name, the expected type, and the value.

TargetFields elements

Name	Description
name	Required The name of the field.
type	Required The expected type for the field (for example, string, object, integer, or timestamp) The target field type need to match the output type of the value field. The field type for enumerations is “object” and all direct decedents of an enumeration have the “string” type. For example, the output type for `a:enum1>caption` is “string”. The enumeration attribute is validated. The sub-attributes are not validated, so the user creating the data schema JSON must ensure it uses a value that matches the CRM enumeration attribute.
collection	Boolean value indicating whether this field is a collection. The default value is `false`.
value	The value field can be used either to directly reference a source field from corresponding subject area or to call a transformation to apply a formula. To reference a source attribute field, specify the path of the subject area attribute prefixed with `a:`. For sub-attributes, add the greater then character (`>`). For example: `"value":"a:firstName"` `"value":"a:type>active"` To call a transformation formula, append the formula with `f:` and source attribute field references with `a:`. For example: `"value": "f:stringAppend a:lastName \", \" a:firstName"` At least one source field from the subject area must be referenced in any of the targetFields elements.
default	The default value to use if the attribute value resolves to null or empty. This property cannot reference any source fields or use formulas.
fields	When the targetField is an object, use `fields` to define additional child elements. The child elements use the same parameters as targetFields and can be nested as many times as needed.

Transformation formulas

The transformation formula allows users to make modifications to the data to be exported. The following formulas are available.

Function	Description	Example
arithmetic operator number1 number2	Arithmetic operators. operator Supported values: `+`, `-`, ``, `/` number1, number2* Number. May be `()`. Returns: String.	`(arithmetic "+" "10" "5")`
comparison operator argument1 argument2 argumentN	Comparison operators. operator Supported values: `=`, `!=`, `>`, `>=`, `<`, `<=` argument1 Any format. May be `()`. argument2 Any format. May be `()`. argumentN Any format. Any additional arguments, if they exist. May be `()`. Returns: String.	`(comparison "=" "age" "5")`
stringAppend string1 string2 … stringN	Combines multiple string values into a single string. string1, string2, stringN String. May be `()`. Returns: String.	`(stringAppend "a:lastName" "\", \"" "a:firstName")`
stringAffix delimiter string1 string2 … stringN	Combines multiple string values into a single string with a specified delimiter. delimiter String. string1, string2, stringN String. May be `()`. Returns: String.	`(stringAffix "\", \"" "a:lastName" "a:middleName" "a:firstName")`
stringReplace string1 pattern string2 all	Matches a string against a regular expression and replaces the match with another string. string1 String. The string to match. May be `()`. pattern Any format. Either a string regular expression or a precompiled pattern. string2 String. The replacement string. May contain `$<digit>`, where `<digit>` is the ordinal number of the matched group, as returned by the string-match. May be `()`. all Boolean. Use `#t` to replace all the matches, `#f` to replace only the first one. Returns: String. The string with the performed replacements.	`(stringReplace "a:stringToReplace" "\",\"" "\" \"")`
substring string start end	Extracts a substring from a string. string String. start Number. The index of the first character of the substring, 0-based. end Number. The index of the next character after the last character of the substring, 0-based. Returns: String.	`(substring "a:lastName" "0" "8")`
stringToLowerCase string	Coverts all letters in the specified string to lower case. string String. May be `()`. Returns: String	`(stringToLowerCase "a:lastName")`
stringToUpperCase string	Converts all letters in the specified string to upper case. string String. May be `()`. Returns: String.	`(stringToUpperCase "a:lastName")`
dateFormat pattern timestamp timezone	Converts a timestamp to a string in the specified pattern format. pattern String. Pattern for the format of the timestamp. timestamp Timestamp. timezone String. Returns: String.	`(dateFormat "\"YYYY-MM-DD\"" "a:birthTime" "UTC")`
dateGetPart unit timestamp	Extracts the value of the specified date or time unit from a timestamp. unit String. Supported values: `year`, `month`, `week`, `day`, `doy`, `dow`, `hour`, `minute`, `second`, `millisecond` timestamp Timestamp. Returns: Integer.	`(dateGetPart "month" "a:createdTime")`
cast type argument	Casts the value to the specified type. type String. Supported values: `string`, `binary`, `integer`, `long`, `decimal`, `float`, `double`, `timestamp`, `Boolean`, or a class derived from an object. A timestamp cast to long gives the number of milliseconds since1-Jan-1970 00:00:00 UTC. argument Any. Returns: Any.	`(cast "string" "a:someInteger")`
currentTime	Returns current date and time as a timestamp value. Returns: Timestamp.	`(currentTime)`