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 condition.
Matcher Matching Condition Condition Master records in the same bin for which the matching condition 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 Attributes Parent ID Parent Attributes Child Attributes
SemQL Enricher Filter LoadID, PublisherID, SourceID, ClassName, Creation Date, UpdateDate, Creator, Updator Yes Yes Yes (2)
SemQL Enricher Expressions LoadID, PublisherID, SourceID, ClassName, Creation Date, UpdateDate, Creator, Updator Yes Yes Yes (1)(2)
Plug-in Enricher Filter LoadID, PublisherID, SourceID, ClassName, Creation Date, UpdateDate, Creator, Updator Yes Yes Yes
Plug-in Enricher Inputs LoadID, PublisherID, SourceID, ClassName, Creation Date, UpdateDate, Creator, Updator Yes Yes Yes (1)(2)
SemQL Validation Condition ClassName, Creation Date, UpdateDate, Creator, Updator No Yes Yes (1)(2)
Plug-in Validation Input ClassName, Creation Date, UpdateDate, Creator, Updator No Yes Yes (1)(2)
Matcher Binning Expression BatchID, PublisherID, SourceID, ClassName, Creation Date, UpdateDate, Creator, Updator Yes No No
Matcher Matching Condition BatchID, PublisherID, SourceID, ClassName, Creation Date, UpdateDate, Creator, Updator Yes No No
Consolidator Custom Ranking PublisherID, SourceID, Creation Date, UpdateDate, Creator, Updator Yes No No
Consolidator Additional Order By PublisherID, SourceID, Creation Date, UpdateDate, Creator, Updator Yes No No

Notes:
(1) Children records are available only in the contexts of conditions clauses expressed within the expressions or conditions.
(2) Only the children existing in the current load are visible in source validations and enrichers. All children are visible for post consolidation validations.

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

Predefined Sort

In Business Object Views, you can defined an Expression that is used to sort the records under the node in the business object view.

Customized Display Name

The default display name defined for an entity can be customized:

Both these customizations are expressed as SemQL Expressions that use the attributes of the entity.

Reference Picker Filters

In Business Object Views, SemQL conditions can filter the records selectable with reference pickers. See Reference Pickers for more information.

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