Use SemQL in Models

At design-time, SemQL is used:

In the entities, to generate automatically the source ID of an entity when creating or importing new records.
In the various rules defining the certification process, that is the enrichers, validations, matchers and survivorship rules.
In the applications to define the filters applied to the lists in the business views and the form/collections attributes.
In properties that support SemQL in addition to literal values.
In the conditions used for securing data row-based filtering.

SemQL for ID Generation

The Source ID of an entity can be generated using a SemQL expression. This expression is executed and the ID 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.

This expression can use:

Attributes from the entity.
Attributes of the referenced (parent) entities.
SemQL functions and operators. You can for example use the SEQ_NEXTVAL function 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. This ID will not change after the initial record creation even if the value of the attributes used in the expression change. For example, if you generate the ID using the customer name and edit a saved customer, modifying its 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, condition and order by clauses used in the certification process.

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 Expressions	Expression	The result of the expressions load 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 Inputs	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 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.
Consolidation Rule Ranking Expression	Order By	For Custom Ranking strategy, the consolidation uses values from the first of the records ordered by this expression. For other strategies, 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.

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 Expressions

Expression

The result of the expressions load 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 Inputs

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 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.

Consolidation Rule Ranking Expression

Order By

For Custom Ranking strategy, the consolidation uses values from the first of the records ordered by this expression. For other strategies, 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:

Attributes from the entity being processed are always available.
Built-in attributes availability depends on the type of entity and on the clause being created.
Attributes from related entities (parent or child entities as well as Lineage parent and child records) availablity also depends on the type of the entity and on 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: Current Employee entity references the CostCenter entity and this expression returns an employee’s cost center name

Validation Conditions

Checking the Customer’s InputAddress complex attribute validity:

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/PostalCode:
```
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 this 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 they have a Contact with the same FirstName and LastName and a CEO title, and the Customer names are 50% similar, configure the match rule with the 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. So the Record1.Customer.Name expression returns the name of the customer the contact we match is attached to.

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, 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 Path

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 Filter condition that filters the records displayed in this business entity.
This condition has access to all attributes of the entity, 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/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 (for example, the PublisherID, which exists on source and master data but not on 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, you define a SemQL condition to filter records based on the search parameters values entered in the form.

This condition has access to all attributes of the searched entity, its related entities and built-in attributes. Note that certain built-in attributes should be used with caution as some do not apply to all the views the search form may apply to.