Using SemQL in Convergence for MDM | ||
---|---|---|
Previous | Next | |
Attributes and Variables | The SemQL Editor |
This section describe the various uses of SemQL in Convergence for MDM, as well as the attributes available for these uses.
At design-time, SemQL is used:
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.
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. |
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.
InitCap(FirstName)
InitCap(FirstName) || Upper(FirstName)
Replace(Upper(InputAddress.City),'CEDEX','')
CostCenter.CostCenterName
: Current
Employee entity references the
CostCenter entity and this expression returns an employee’s cost center name
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.
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.
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 isEmailAddress = :USER_EMAIL
.
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 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
In Business Object Views, you can defined an Expression that is used to sort the records under the node in the business object view.
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.
In Business Object Views, SemQL conditions can filter the records selectable with reference pickers. See Reference Pickers for more information.
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.
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
Previous | Top | Next |
Attributes and Variables | The SemQL Editor |