Semarchy xDM Integration Guide

Publishers

This term refers to the original application from which the data originates, and not necessarily to the middleware tool or user-designed program actually doing the data movement. The publisher is typically an operational application such as a Customer Relationship Management (CRM), a Product Lifecycle Management (PLM) or an Enterprise Resource Planner (ERP).

A middleware tool may be an Extract-Transform-Load (ETL), Enterprise Service Bus (ESB) or any other data integration product. It may also be a user-designed program or script implemented in SQL, Java, etc. The middleware tool or user-designed program communicates with the publisher to extract data and communicates with the MDM Hub using the publishing methods described in this guide to load this data.

Consumers

Similarly to the publisher, this term refers to the applications consuming data from the hub. This consumption usually takes place via a middleware tool or a user-designed program, and uses the consumption methods described in this guide.

Publishing Concepts

Publishing source data for certification into golden data is performed as a transaction. Such publisher transaction is called an External Load. It is a Semarchy xDM transaction identified by a sequential Load ID.

An External Load represents a source data load transaction.

When an External Load is submitted with an Integration Job Name, a Batch - identified by a Batch ID - is created, and the Job starts processing the data published in this load.

A Batch represents a transaction certifying loaded data and writing in the hub the resulting golden data.

External Load Lifecycle

An external load lifecycle is described below:

The middleware can also Cancel the External Load to abort the external load instead of submitting it.

Continuous Loads

Continuous loads enable integration developers to push data into the MDM hub in a continuous way without having to take care of Load Initialization or Load Submission.

With continuous loads:

vLoad_id := <repository_schema>.INTEGRATION_LOAD.GET_CONTINUOUS_LOADID(
	'<continuous_load_name>'           /* Name of the continuous load. */
	);

vLoad_id := <repository_schema>.get_continuous_loadid(
	'<continuous_load_name>'           /* Name of the continuous load. */
	);

EXEC <repository_schema>.dbo.GET_CONTINUOUS_LOADID
    @LOAD_ID OUTPUT,
    @P_CONTINUOUS_LOAD_NAME  = '<continuous_load_name>' /* Name of the continuous load. */

Batch Lifecycle

When an external load is submitted, the following operations take place:

Even when multiple loads take place simultaneously, the sequence into which the external loads are submitted defines the order into which the data is processed by the integration jobs and golden data certified from this source data.

REST API

This interface is available for application to consume or publish data from the hub in a programmatic way.

SQL Interface

This interface is available for data integration and ETL products to publish or consume data. It is composed of the Integration Load Functions and the Data Location Database Schema.

The Integration Load Functions are stored in the repository schema, contains function to manage the External Load Lifecycle, for the purpose of publishing data in batch mode.

The Data Location Database Schema stores a set of tables that contain the hub data.
This schema contains the landing (or staging) tables used to publish data into the hub. It contains also the golden records tables and the intermediate tables handled by the integration job that create golden records from the source records. There is a single data structure for the entire hub, and a single set of tables for each entity regardless of the model edition.

The data location schema is accessed for integration purposes to:

The structure of the tables stored in the data location schema is detailed in the Table Structures section.

Integration Job

The integration job processes the data submitted in an external load and runs this data through the Certification Process, which is a series of steps to create and certify golden data out of this source data.

This job is generated from the certification rules defined at design time, and it uses the data structures automatically created in the MDM hub when deploying the model edition.

An integration job is a sequence of tasks used to certify golden data for a group of entities. The model edition deployed in the data location brings several integration jobs definitions with it. Each of these job definitions is designed to certify data for a group of entities.

Integration jobs definitions as well as integration job logs are stored in the repository

For example, a multi-domain hub contains entities for the PARTY domain and for the PRODUCTS domain, and has two integration jobs definition:

Integration jobs are started when source data has been loaded in the landing tables and is submitted for golden data certification.

Each integration job is the implementation of the overall certification process template. It may contain all or some of the steps of this process. The following section details the structure of the certification process.

Certification Process

Tables

For each entity, a pre-defined set of tables is created. These tables are named with the following convention: <Prefix>_<Physical Table Name>

The prefix is automatically generated depending on the table nature (Source Data, Source Error, etc.), and the Physical Table Name is set in the entity definition.

The following list describes the tables created for a given entity.

For publishing and consuming data, internal tables are not needed. The structure of these tables is not explained in this document.

Columns

Class Name

Several entities involved in an inheritance relation have their data stored in the same set of tables. These tables store the superset of the attributes of the parent and all its child entities. The B_CLASSNAME column is used to identify the class (entity) of a record in a table.

For example, when Person and Company inherit from the Party entity, the resulting table is named after the parent entity (Party), and will contain all the attributes of Person and Company as well. In the GD_PARTY table for example, records representing persons will have B_CLASSNAME='Person'. When publishing person or company information in the SD_PARTY table, the middleware must set B_CLASSNAME='Person' or B_CLASSNAME='Company' accordingly.

Constraints

The only constraints physically enforced in the tables are the primary keys and the not null columns. These constraints apply to system columns only.

For example, on an SD_ table, the following constraints are enforced:

Other constraints defined in the model (mandatory attributes, references, etc.) are not enforced in the physical model but checked during the validation phase.

Tables to Load

Make sure to load all the SD and SA tables for the entities that will be taken into account in the integration job. For example, if the integration job processes the Customer and Contact entities, then you should load the SD_CUSTOMER and SD_CONTACT tables.
If you insert data into the SD table of an entity that is not taken into account by the integration job, this data will be ignored. For example, if the integration job processes the Customer and Contact entities, data loaded the SD_EMPLOYEE table will not be taken into account by the integration job to certify golden records for the Employee entity.

Referential Integrity and Load Order

There is no required order to load the SD and SA tables, as no foreign keys are implemented on these tables.
Reference validation is performed by the integration job, as a consequence references between entities must be loaded as indicated in the Columns to Load section.

Using Transactions

It is recommended to use a database transaction when writing to the SD and SA tables.

Columns to Load

This section provides some guidance for loading the columns of the SD and SA tables.

Publishing Golden Records Deletion

Golden record deletion applies to all entities and is similar to deleting a golden record from an application.

To publish a golden record deletion, you must load the following columns into the SA table (even for ID and fuzzy matched entities):

You can optionally load the B_DELETEAUTHOR and B_DELETEDATE columns to provide an author and a date for the delete operation.

Publishing Master Records Deletion

For matching entities, you can publish master records deletion.
To publish such deletion, use the process described for deleting golden records, with the following differences:

Load the B_LOADID, B_DELETETYPE, B_DELETEOPERATION, B_CLASSNAME, B_DELETEAUTHOR and B_DELETEDATE columns as explained in Publishing Golden Records Deletion.

When deleting one of the master records contributing to a golden record, the values consolidated in the golden record are updated to take into account this master record deletion.

Golden Data Table Structure

The complete list of system columns available is provided in the Table Structures section of this guide. The following table lists the system columns used when consuming information from the hub:

Accessing Golden Data Using SQL

To access golden data using SQL, you query on the GD table. The queries are filtered to access data for one or more entity classes,

The following code sample gives a query to access golden records in the GD table.

select G.* (1)
from GD_<Physical_Table_Name> G (2)
where G.B_CLASSNAME in ( <classname_list> ) (3)

Explanation of the query:

select CONTACT_ID, FIRST_NAME, LAST_NAME
from GD_CONTACT G
where G.B_CLASSNAME = 'Contact'

Golden Data Primary Key

The primary key for a golden data table depends on the Entity Type and the ID Generation for the entity. A column named after the Physical Column Name of the primary key attribute stores the golden record primary key for the GD table and has the following datatype:

Accessing Master Data Using SQL (ID Matched Entity)

With ID Matched Entity, the master data table has a structure similar to the golden data table, and contains in addition the publisher code. The same primary key is stored in the golden and master data in a column named after the physical column name of the primary key attribute (<primary_key_column> in the sample below)

select M.B_SOURCEID, G.*
from MD_<Physical_Table_Name> M
inner join GD_<Physical_Table_Name> G on (
 	G.<primary_key_column> = M.<primary_key_column>
  and	G.B_CLASSNAME in ( <classname_list> )
)
where
      	M.B_CLASSNAME in ( <classname_list> )
  and 	M.B_PUBID = '<publisher_code>'
  and 	M.<primary_key_column> = '<searched_source_id>'

In this code, access is filtered with the class name for both the golden data and the master data (lines #5 and #8). The two tables are joined on their common primary key (line #4). In addition, the master data is filtered by source publisher (line #9) and ID of the source record (line #10).

select 	M.EMPLOYEE_NUMBER, G.FIRST_NAME, G.LAST_NAME
from 	MD_EMPLOYEE M
inner join GD_EMPLOYEE G on (
 	G.EMPLOYEE_NUMBER = M.EMPLOYEE_NUMBER
  and	G.B_CLASSNAME = 'Employee'
)
where
      	M.B_CLASSNAME = 'Employee'
  and 	M.B_PUBID = 'HR'
  and 	M.EMPLOYEE_NUMBER = '100'

The resulting information can be used to update the source record with golden data.

Accessing Master Data Using SQL (Fuzzy Matched Entity)

With Fuzzy Matched Entity, the master data table has a structure similar to the golden data table. It contains a reference to the golden data primary key, but the master data primary key consists of two columns:

As a consequence, the link between golden and master records is done using the primary key column as in an ID Matched Entity, but the link to the source is done using the B_SOURCEID column.

select M.B_SOURCEID, G.*
from MD_<entity> M
inner join GD_<entity> G on (
 	G. <primary_key_column> = M. <primary_key_column>
  and	G.B_CLASSNAME in ( <classname_list> )
)
where
      	M.B_CLASSNAME in ( <classname_list> )
  and 	M.B_PUBID = '<publisher_code>'
  and 	M.B_SOURCEID = '<searched_source_id>'

In this code, the golden and master data tables are joined on their golden record primary key (line #4), but the master data is restricted by source publisher (line #9) and ID of the source record (line #10), using the B_SOURCEID column.

select M.B_SOURCEID, G.FIRST_NAME, G.LAST_NAME
from 	MD_CONTACT M
inner join GD_CONTACT G on (
 	G.CONTACT_ID = M.CONTACT_ID
  and	G.B_CLASSNAME = 'Contact'
)
where
      	M.B_CLASSNAME = 'Contact'
  and 	M.B_PUBID = 'CRM'
  and 	M.B_SOURCEID = '27030'

Example: Select side by side the duplicates detected for a given source Contact record with the ID 27030 in the CRM system (Fuzzy Matched Entity). In this example, the master data table is used twice (aliased as M and MM) to retrieve the two sides of a duplicate pair.

select
  M.B_PUBID DUP1_PUBLISHER, M.B_SOURCEID DUP1_ID, M.FIRST_NAME DUP1_FIRST_NAME, M.LAST_NAME DUP1_LAST_NAME,
  MM.B_PUBID DUP2_PUBLISHER, MM.B_SOURCEID DUP2_ID, MM.FIRST_NAME DUP2_FIRST_NAME, MM.LAST_NAME DUP2_LAST_NAME,
  G.CONTACT_ID GOLD_ID, G.FIRST_NAME GOLD_FIST_NAME, G.LAST_NAME GOLD_LAST_NAME
from 	MD_CONTACT M
inner join GD_CONTACT G on
 (
 	G.CONTACT_ID = M.CONTACT_ID
  and	G.B_CLASSNAME = 'Contact'
 )
inner join MD_CONTACT MM on
 (
 	MM.CONTACT_ID = M.CONTACT_ID
  and	MM.B_CLASSNAME = 'Contact'
 )
where
      	M.B_CLASSNAME = 'Contact'
  and 	M.B_PUBID = 'CRM'       /* Publisher ID */
  and 	M.B_SOURCEID = '27030'  /* Source ID */
  /* and M.B_PUBID = MM.B_PUBID */
  /* Uncomment the previous line to restrict the duplicates
     to those within the CRM application */

Accessing Pre-Consolidation Errors Using SQL

Pre-consolidation errors can be accessed via the Source Errors (SE, or AE for basic entities) tables.
These tables store the error information, that is the information about the constraints that caused the records to fail the validation. The latter is stored in the B_CONSTRAINTNAME (name of the constraint) and B_CONSTRAINTTYPE (type of the constraint) columns.

SE tables do not store the erroneous data itself, but they contain the identifiers to the source (SD or SA for basic entities) records in error.

Note that the B_ERROR_STATUS column on the SD table provides a simple way to detect the records with errors.

Example: Select the source errors for the Contact entity.
In this example, incorrect foreign references would appear. To identify them, we retrieve the incorrect referenced IDs from FP_CUSTOMER and FS_CUSTOMER.

  select * from SD_CONTACT where B_ERROR_STATUS = 'ERROR';

select SE.B_BATCHID, SD.B_LOADID,
  SE.B_CONSTRAINTNAME, SE.B_CONSTRAINTTYPE,
  SE.B_PUBID, SE.B_SOURCEID,
  SD.FIRST_NAME, SD.LAST_NAME,
  SD.FP_CUSTOMER, SD.FS_CUSTOMER
from SE_CONTACT SE, SD_CONTACT SD
where
  SE.B_CLASSNAME = 'Contact'
  and SD.B_CLASSNAME = 'Contact'
  and SD.B_PUBID = SE.B_PUBID
  and SD.B_SOURCEID = SE.B_SOURCEID
  and SD.B_LOADID = SE.B_LOADID
  and SD.B_ERROR_STATUS = 'ERROR';

Accessing Post-Consolidation Errors Using SQL

Post-consolidation errors can also be accessed via the Golden Errors (GE) tables.
These tables store the error information, that is the information about the constraints that caused the records to fail the validation. The latter is stored in the B_CONSTRAINTNAME (name of the constraint) and B_CONSTRAINTTYPE (type of the constraint) columns.

GE tables do not store the erroneous data itself, but they contain the identifier to the golden (GD) records in error.

Note that the B_ERROR_STATUS column on the GD table provides a simple way to detect the records with errors.

Example: Select the errors and data for the golden contacts in error.

select * from GD_CONTACT where B_ERROR_STATUS = 'ERROR';

select GE.B_BATCHID,
  GE.B_CONSTRAINTNAME, GE.B_CONSTRAINTTYPE,
  GE.CONTACT_ID,
  GD.FIRST_NAME, GD.LAST_NAME, GD.F_CUSTOMER
from GE_CONTACT GE, GD_CONTACT GD
where
  GE.B_CLASSNAME = 'Contact'
  and GD.B_CLASSNAME = 'Contact'
  and GD.CONTACT_ID = GE.CONTACT_ID
  and GD.B_BATCHID = GE.B_BATCHID
  and GD.B_ERROR_STATUS = 'ERROR';

REST API Documentation

The REST API exposes its built-in documentation as a link in the Welcome page. In addition, this documentation is available for tools as an OpenAPI specification.

To Access the REST API Documentation:

The documentation exposes the endpoint description and provides request and response samples.

The main operations available in the REST API are also described in the following sections.

Authentication & Privileges

Applications accessing Semarchy xDM using the REST API must be authenticated.

The REST API supports two authentication methods:

Operations in the REST API need privileges similar to those required to perform the same operations via a user interface:

See the Semarchy xDM Administration Guide for more information about defining roles and API keys, and the Semarchy xDM Developer’s Guide for more information about securing data, models and privilege grants with those roles.

Security Recommendation

The following simple recommendations will help you secure your code and data when using the REST API.

Data Formats

Errors

The API endpoints report in the response payload possible errors with an appropriate HTTP code. Refer to the documentation for each endpoint for the possible responses.

When calling an endpoint, note that:

Query Multiple Records

{
  "records": [
	{
  	"CustomerName": "Gadgetron",
  	"City": "Honolulu"
	},
	{
  	"CustomerName": "StayPaft",
  	"City": "Honolulu"
	}
  ]
}

Query a Single Record

{
    "CustomerName": "Gadgetron",
    "City": "Honolulu",
    "UPPER(CustomerName)": "GADGETRON"
}

Counting Records

{
  "count": "42",
}

Query Multiple Records with a Named Query

Counting Records in a Named Query

View Types

Certain REST endpoints use a view type as part of their URL. This view type, identified by an alias (GD, MD, etc) defines the type of data (golden, master, errors, etc) queried for the entity, using the endpoint.

/api/rest/query/HR/Employee/GD

/api/rest/query/HR/Employee/MD

Some of these view types must be contextualized to list records for a given Load ID or for a given point in history (identified by a Batch ID or an As of Date).

/api/rest/query/HR/Employee/GH(22565)

Note that the other view types ignore this contextualization syntax. You must use Filtering to query a subset of the records.

/api/rest/query/HR/Employee/GD?$f=BatchID=22565

The following table lists the most common view types.

Pagination

When calls return a large number of records, it is recommended to page the record set. By requesting smaller subsets of data, you will get a response much faster than when requesting the entire, potentially large, record set.

/query/CustomerAndFinancialMDM/Customer/GD?$offset=20&$limit=10

Query Expressions

Query expressions define which values are returned by the query for each record.

/query/CustomerAndFinancialMDM/Customer/GD?$baseExprs=USER_ATTRS&$expr=Creator&$expr=CreationDate

/query/CustomerAndFinancialMDM/Customer/GD?$baseExprs=NONE&$expr=Name:Upper(CustomerName)&$expr=Revenue:TotalRevenue

/query/CustomerAndFinancialMDM/Customer/GD?$baseExprs=USER_ATTRS&$expr=$exclude:TotalRevenue

Sorting

Sort expressions define the order of the records in the response.

/query/CustomerAndFinancialMDM/Customer/GD?$orderBy=TotalRevenue%20DESC,CustomerName%20ASC

Filtering

Use filters to request only specific records, that match the criteria you choose.

/query/CustomerAndFinancialMDM/Contact/GD?$f=FirstName%20LIKE%20%27Joe%25%27

Load Type

The loadType indicates the nature of the load:

Load Status

The possible values for the Load Status are listed in the table below.

Integration Job

If a job is attached to the load (the load was submitted), then the integrationJob object provides details about this job, including its start date, current task, duration and any error that may occur in this job.
It also includes the notificationStatus, which indicates whether the notifications were successfully sent.

Configuring Data Loading in REST

Updating Existing Records

You update an existing record by providing its ID when loading the data:

If you persist a record in a load with an existing record ID, a copy of the record is checked out and changes are applied only the field provided in the request JSON. Other attributes still keep their value.

Setting the Auditing Fields

By default, the auditing fields, Creator, Updator, CreateDate, UpdateDate are automatically set to the current user name and the current date when the user publishes the data. These values cannot be set by the user.

However, a user may be configured to push values in these auditing fields to publish data, for example, in the past, or on behalf of other users.

A user with a role having the Allow Publishing as user in API option selected in a model privilege grant is able to set the audit fields (Creator, Updator, CreateDate, UpdateDate) while publishing data via the REST API.

{
  "action":"PERSIST_DATA",
  "persistOptions": {
    "defaultPublisherId": "CRM",
    "optionsPerEntity": {
      "Person": {
        "queryPotentialMatches": true,  (1)
        "enrichers":["CleanseEmail"],
        "validations":[],
        "queryPotentialMatchesBaseExpressions": "NONE", (2)
        "queryPotentialMatchesExpressions": { (2)
          "Name": "Concat(FirstName, ' ', LastName)",
          "Email": "CleansedEmail",
          "Golden ID": "Gold_ID",
          "Master ID": "ID"
        }
      }
    },
    "missingIdBehavior": "FAIL",
    "persistMode": "NEVER"   (3)
  },
  "persistRecords": {
    "Person": [
    {
      "SourceID": "99998",
      "FirstName": "John",
      "LastName": "Doe",
      "DateOfBirth": "1974-01-25",
      "SourceEmail": "jass@ellerbusch.com"
    }
    ]
  }
}

{
  "status": "PERSIST_CANCELLED",
  "load": {
    ...
  },
  "records": {
    "Person": [
      {
        "entityName": "Person",
        "recordValues": {
          "CleansedEmail": "jass@ellerbusch.com",
          "SourceEmail": "jass@ellerbusch.com",
          "DateOfBirth": "1974-01-25",
          "FirstName": "John",
          "SourceID": "99998",
          "PublisherID": "CRM",
          "LastName": "Doe",
          ...
        },
        "failedValidations": [],
        "potentialMatches": [
          {
            "matchRuleName": "ExactEmailMatch",
            "matchScore": 74,
            "matchedRecordLocation": "MD",
            "matchedRecordId": {
              "SourceID": "1320830",
              "PublisherID": "CRM"
            },
            "matchedRecordData": {
              "Master ID": "CRM.1320830",
              "Email": "jass@ellerbusch.com",
              "Golden ID": 10002,
              "Name": "Jass Ellerbusch"
            }
          }
        ]
      }
    ]
  }
}