Using SemQL in Convergence for MDM

This section describe the various uses of SemQL in Convergence for MDM, as well as the attributes available for these uses.

Using SemQL at Design-Time

At design-time, SemQL is used:

SemQL in the Certification Process

The certification process takes source records pushed to the MDM Hub by identified Publishers and creates enriched, validated, matched and consolidated golden records. SemQL is involved in the various phases of this process.

Certification Process Clauses

The following table describes the expressions, condition and order by clauses used in the consolidation process.

Certification Phase Clause Type Description
SemQL Enricher Filter Condition This condition filters the source records of the entity that must go through this enricher.
SemQL Enricher Expressions Expression The result of the expressions load the enriched entity’s attributes.
Plug-in Enricher Filter Condition This condition filters the source records of the entity that must go through this enricher.
Plug-in Enricher Inputs Expression Each expression result is pushed to a plug-in input. The outputs of the plug-in load the entity’s attributes with enriched values.
SemQL Validation Condition Condition This condition defines which source or golden records pass the validation.
Plug-in Validation Input Expression Each expression result is pushed to a plug-in input. The plug-in outputs a boolean defining the records that pass the validation.
Matcher Binning Expression Expression Master records having the same value for all binning expressions are in the same bin, and will be matched using the matching expression.
Matcher Matching Expression Condition Master records in the same bin for which the matching expression returns true are considered as matches.
Consolidator Custom Ranking Order By Consolidation uses values from the first of the records ordered by this expression.
Consolidator Additional Order By Order By If the consolidation strategy returns two records with the same rank, the consolidation sorts them by this expression and uses the value of the first one.

Available Attributes by Clause

In the certification process:

Certification Phase Built-in Attribute Available Parent ID Available
SemQL Enricher Filter LoadID, PublisherID, SourceID, ClassName, Creation Date, UpdateDate, Creator, Updator Yes
SemQL Enricher Expressions LoadID, PublisherID, SourceID, ClassName, Creation Date, UpdateDate, Creator, Updator Yes
Plug-in Enricher Filter LoadID, PublisherID, SourceID, ClassName, Creation Date, UpdateDate, Creator, Updator Yes
Plug-in Enricher Inputs LoadID, PublisherID, SourceID, ClassName, Creation Date, UpdateDate, Creator, Updator Yes
SemQL Validation Condition ClassName, Creation Date, UpdateDate, Creator, Updator No
Plug-in Validation Input ClassName, Creation Date, UpdateDate, Creator, Updator No
Matcher Binning Expression BatchID, PublisherID, SourceID, ClassName, Creation Date, UpdateDate, Creator, Updator Yes
Matcher Matching Expression BatchID, PublisherID, SourceID, ClassName, Creation Date, UpdateDate, Creator, Updator Yes
Consolidator Custom Ranking PublisherID, SourceID, Creation Date, UpdateDate, Creator, Updator Yes
Consolidator Additional Order By PublisherID, SourceID, Creation Date, UpdateDate, Creator, Updator Yes

Certification Process SemQL Example

Enricher Expressions
Validation Conditions
InputAddress.Address is not null and 
( InputAddress.PostalCode is not null or InputAddress.City is not null)

In this example, the IS NOT NULL, AND and OR SemQL operators are used to build the condition.

Matcher
InputAddress.Country || InputAddress.PostalCode
SEM_EDIT_DISTANCE_SIMILARITY( Record1.CustomerName, Record2.CustomerName ) > 65 
and SEM_EDIT_DISTANCE_SIMILARITY( Record1.InputAddress.Address, Record2.InputAddress.Address ) > 65 
and SEM_EDIT_DISTANCE_SIMILARITY( Record1.InputAddress.City, Record2.InputAddress.City ) > 65

In this last example, SEM_EDIT_DISTANCE_SIMILARITY is a SemQL function. Record1 and Record2 are predefined names for qualifying the two record to match.

SemQL in Privilege Grants

Entity privileges support Row-Level Filtering, to apply privileges only to a subset of the records of the entity. The subsets are defined using SemQL filters.
Each entity privilege grant may be associated with a filtering Condition.
This condition has access to all attributes of the entity, its related entities and built-in attributes ClassName, BranchID, CreationDate, UpdateDate, Creator and Updator.

Example: To grant a given privilege to a user which email address (stored in a USER_EMAIL variable) appears in the current record in the EmailAddress attribute, the filter is EmailAddress = :USER_EMAIL.

SemQL in Applications

Business Object Filters

Each entity object of a business object supports a Condition that filters the records displayed in this entity object.
This condition has access to all attributes of the entity, its related entities and built-in attributes ClassName, BranchID, CreationDate, UpdateDate, Creator and Updator.

Example: To filter at the root of the hierarchy of cost centers only those with no parent, the following filter is applied: FID_ParentCostCenter is null. FID_ParentCostCenter is the attribute representing the relation between a cost center and its parent cost center.

Form/Table View Attributes

Form and table view attributes support SemQL Expressions. These expressions help building composite attribute for display purposes. In form views used for data entry, such composite attributes appear as read-only.

This condition has access to all attributes of the entity, its related entities and built-in attributes LoadID, BatchID, OriginalBatchID, BranchID, FromEdition, ToEdition, ClassName, PublisherID, SourceID, MatchGroupID, Status, CreationDate, UpdateDate, Creator and Updator.

Example: To display a custom attribute called ContactsStatius on a Customer form view which displays whether this customer has contacts and whether these contacts have the IsInfluencer flag set to ‘1’:
CASE
WHEN ANY Contacts HAVE (IsInfluencer = 1 ) THEN ‘One or More Influencers’
WHEN NOT ANY Contacts HAVE (1=1) THEN 'No Contacts'
ELSE ‘No Influencer Contact’
END

Using SemQL at Design-Time

Filters

All date view in an application support filters in the form of Conditions.
This condition has access to all attributes of the entity, its related entities and built-il attributes that depend on the view that is accessed.
For example, on a Golden Record: BatchID, BranchID,FromEdition, ToEdition, ClassName, CreationDate, UpdateDate, Creator and Updator are available.

Duplicates Filters

When filtering duplicates, for example to filter records to checkout in a duplicate management activity, it is possible to use Record1 and Record2 as in a Matcher, except that the comparison takes place on the pairs of records that have matched.

Example: to show matching records, but with the CustomerName fields less similar by less than 95%: SEM_EDIT_DISTANCE_SIMILARITY(Record1.CustomerName, Record2.CustomerName) < 95