Use SemQL in models

During the design phase, SemQL is used:

In entities to generate automatically the source ID of an entity when creating or importing new records.
In the various rules (i.e., enricher, validation, matching, and survivorship rules) that define the certification process.
In the conditions used for securing data row-based filtering.
In applications to define the filters applied to the lists in business views and form or collection attributes.
In properties that support SemQL in addition to literal values.

SemQL for ID generation

The source ID of an entity can be generated using a SemQL expression. This expression is executed and the ID is generated when a record form is saved for the first time in a stepper, or when a record is imported from a file containing no IDs.

SemQL expressions are not applicable for generating child record IDs based on parent record IDs during import operations. This limitation arises from the import wizard, which has been deliberately designed so that only attributes provided in the Excel or CSV import files are used. Consequently, parent record IDs cannot be accessed for generating child record IDs during import operations.

The SemQL expression can include:

Attributes from the entity.
Attributes of the referenced (parent) entities.
SemQL functions and operators (e.g., the SEQ_NEXTVAL function can be used to retrieve the next increment of a named sequence).

Examples of SemQL for ID generation

To create an ID using a literal concatenated with a sequence increment for a Customer entity:
'CUST_' || SEQ_NEXTVAL('CUST_SEQ')
To create an ID using values from the record itself for a Customer entity:
Upper(LastName) || '_' || CustomerNumber
To create an ID concatenating the IDs of two related records. Typically, for an entity that relates products and distribution markets:
DistributedProduct.ProductID || '_' || DistributionMarket.MarketID

An ID generated with a SemQL expression is immutable. Once generated, the ID is not affected by any subsequent modifications to the attributes used in the expression. For example, generating an ID using a customer’s name and later modifying the name will not alter the ID.

SemQL in the certification process

The certification process takes source records pushed to the data 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, conditions, and ORDER BY clauses used in the certification process.

Process step Clause type Description

Process step	Clause type	Description
SemQL enricher filter	Condition	This condition filters the source records of the entity that must go through this enricher.
SemQL enricher expression	Expression	The expression result loads the enriched entity’s attributes.
API enricher filter	Condition	This condition filters the source records of the entity that must go through this enricher.
API enricher input	Expression	Each expression result is pushed to an enricher input. The outputs of the enricher load the entity’s attributes with enriched values.
SemQL validation condition	Condition	This condition defines which source or golden records pass validation.
Plugin validation input	Expression	Each expression result is pushed to a plugin input. The plugin outputs a boolean defining the records that pass validation.
Matcher binning expression	Expression	Master records having the same value for all binning expressions are in the same bin, and will be matched according to the matching condition.
Matcher matching condition	Condition	Master records in the same bin for which the matching condition returns true are considered matches.
Consolidation rule ranking expression	`ORDER BY`	In a custom ranking strategy, the consolidation process uses values from the first record as per the order determined by this expression. In other strategies, when the consolidation returns two records with the same rank, the process sorts them based on this expression and uses the value from the first record.

SemQL enricher filter

Condition

This condition filters the source records of the entity that must go through this enricher.

SemQL enricher expression

Expression

The expression result loads the enriched entity’s attributes.

API enricher filter

Condition

This condition filters the source records of the entity that must go through this enricher.

API enricher input

Expression

Each expression result is pushed to an enricher input. The outputs of the enricher load the entity’s attributes with enriched values.

SemQL validation condition

Condition

This condition defines which source or golden records pass validation.

Plugin validation input

Expression

Each expression result is pushed to a plugin input. The plugin outputs a boolean defining the records that pass validation.

Matcher binning expression

Expression

Master records having the same value for all binning expressions are in the same bin, and will be matched according to the matching condition.

Matcher matching condition

Condition

Master records in the same bin for which the matching condition returns true are considered matches.

Consolidation rule ranking expression

ORDER BY

In a custom ranking strategy, the consolidation process uses values from the first record as per the order determined by this expression. In other strategies, when the consolidation returns two records with the same rank, the process sorts them based on this expression and uses the value from the first record.

Available attributes by clause

In the certification process:

Attributes from the entity being processed are always available.
The availability of built-in attributes depends on the type of entity and the clause being created.
The availability of attributes from related entities (parent or child entities, as well as lineage parent and child records) also depends on the type of entity and the clause being created.

Use the SemQL editor for the list of attributes available in each situation.

Examples

Enricher expressions

Examples of enricher conditions

FirstName: InitCap(FirstName)
Name: InitCap(FirstName) || ' ' || Upper(LastName)
City: Replace(Upper(InputAddress.City),'CEDEX','')
EmployeeCostCenterName: CostCenter.CostCenterName (the current Employee entity references the CostCenter entity, and this expression returns an employee’s cost center name)

Validation conditions

Checking the validity of the customer’s InputAddress complex attribute:

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.

It is possible to use child records in validations.

For example, a Customer is valid only if all its attached Contacts have an email:

ALL Contacts HAVE (EmailAddress is not null)

When running such validation pre-consolidation, only the contacts within the same load are considered. Post-consolidation, contacts from the load as well as those already in the hub are taken into account.

Matcher

Binning expression to group customers by their country or postal code:
```
InputAddress.Country || InputAddress.PostalCode
```

Matching condition: matching two customer records by name, address, and city name similarity:

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 the second example, SEM_EDIT_DISTANCE_SIMILARITY is a SemQL function. Record1 and Record2 are predefined names for qualifying the two records to match.

Matching on child entities is possible by configuring the rule to use child records.

For example, to match two customers when the Contact entity includes the same first name and last name and a CEO title, and the customer names are 50% similar, configure the matching rule with Child Records set to Contact and use the following clause :

    Record1.FirstName = Record2.FirstName
AND Record1.LastName  = Record2.LastName
AND Record1.JobTitle  = 'CEO' and Record2.JobTitle = 'CEO'
AND SEM_EDIT_DISTANCE_SIMILARITY (Record1.Customer.Name, Record2.Customer.Name) > 50

This rule matches customers, but its base entity is Contact. Therefore, the Record1.Customer.Name expression returns the name of the customer to whom the matched contact is attached.

SemQL in privilege grants

Entity privileges support row-level filtering for applying privileges only to a subset of the entity’s records. 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, CreationDate, UpdateDate, Creator, and Updator.

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

SemQL in applications

Business view transition paths

Business entities in business views are related using transition paths. A transition path is an expression pointing to records in a directly or indirectly related entity.

For example, to display all the siblings of an employee in a manager/report hierarchy, the following transition path is used: Manager.Reports.

Business view filters

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

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 relationship between a cost center and its parent cost center.

Form fields, table columns, and properties

Form fields and table columns, as well as application properties support SemQL expressions. These expressions allow building composite attributes for display purposes and customizing the appearance of the application.

When a form is used for data authoring, form fields using SemQL expressions appear as read-only.

This expression has access to all attributes of the entity, its related entities and built-in attributes.

If you use in a form field a built-in attribute specific to a certain view (e.g., PublisherID, which exists for source and master data, but not for golden data), this form field is automatically hidden when the form is used with a view that does not support the built-in attribute.

To display a custom attribute called ContactsStatus on a Customer form 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 views, you can define an expression that is used to sort the records under the node in the business view.

Reference picker filters

In business views, SemQL conditions can filter the selectable records in reference pickers.

Search forms

In search forms, a SemQL condition is defined to filter records based on the search parameter values entered in the form. This condition has access to all attributes of the searched entity, its related entities, and built-in attributes.

Some built-in attributes should be used with caution, as not all of them are applicable to every view a search form might be employed in.