How do I use Luminesce to read/write custom entities?

Views:

Related resources:

Providing you are a LUSID user with sufficient access control permissions, you can create your own read and write providers to interact with custom entities stored in LUSID.

Note: If you are the LUSID domain owner, you are automatically assigned the built-in lusid-administrator role, which has all the permissions necessary to perform the operations in this article.

By default, Luminesce knows nothing about your custom entities, so providers for them do not exist out-of-the-box. You must create a read/write provider pair for each type of custom entity you wish to interact with. The first step is to define a custom entity type; this cannot be done in Luminesce, so you must use the LUSID REST API instead.

Once created, you can use a read/write provider pair to:

Create custom entities if they do not exist, and update them if they do.
Retrieve custom entities you have created.
Delete custom entities.

In this guide we'll see how to create a read/write provider pair to interact with example 'Office' custom entities, representing corporate office locations:

Step 1: Defining an Office custom entity type using the LUSID API
Step 2: Creating identifiers for Office custom entities as property types
Step 3: Creating an Office read/write provider pair in the Luminesce catalog
Step 4: Creating Office custom entities using Lusid.CustomEntity.Office.Writer
Step 5: Retrieving Office custom entities using Lusid.CustomEntity.Office
Step 6: Administering the Office read/write provider pair

Defining an Office custom entity type using the LUSID API

The first step is to define a custom entity type. This cannot currently be done in Luminesce, so you must use the LUSID CreateCustomEntityDefinition REST API. See how to do this.

Consider the following response from this API for an example Office custom entity type:

{
    "entityTypeName": "Office",
    "displayName": "Office location",
    "description": "An office or branch location",
    "entityType": "~Office",
    "fieldSchema": [
        {
            "name": "isHeadOffice",
            "lifetime": "TimeVariant",
            "type": "Boolean",
            "required": true,
            "description": "Whether or not the location is a head office"
        },
        {
            "name": "seatingCapacity",
            "lifetime": "TimeVariant",
            "type": "Decimal",
            "required": false,
            "description": "The seating capacity of the location"
        },
        {
            "name": "address",
            "lifetime": "Perpetual",
            "type": "String",
            "required": true,
            "description": "The address of the location"
        }
    ]
}

Note the following:

The entity type is ~Office (highlighted in red above); the ~ is automatically applied and distinguishes custom entity types from LUSID's built-in entity types.
All custom entity types have two mandatory built-in data fields: displayName and description.
The entity type has three user-specified custom data fields: isHeadOffice, address and seatingCapacity, the first two of which are required.
isHeadOffice and seatingCapacity are time-variant custom data fields (that is, data values are expected to vary during different time periods), whereas address is perpetual.

Creating identifiers for Office custom entities as property types

Each custom entity must have at least one identifier.

Consider the following JSON fragment representing 'One Carter Lane', a corporate location that is both FINBOURNE Technology's London office and European headquarters, and so has an identifier for each context:

"displayName": "One Carter Lane",
"description": "FINBOURNE office and EMEA regional headquarters",
"identifiers": [
  {
    "identifierScope": "Location",
    "identifierType": "OfficeId",
    "identifierValue": "London"
  },
  {
    "identifierScope": "Location",
    "identifierType": "HeadquartersId",
    "identifierValue": "Europe"
  }
],
...

An identifier consists of three components: an identifierScope, identifierType and identifierValue. The values you assign to these components combine to uniquely identify One Carter Lane in each of the contexts in which it operates. In this example:

To uniquely identify One Carter Lane as a ...	identifierScope	identifierType	IdentifierValue
Office location	`Location`	`OfficeId`	`London`
Headquarters	`Location`	`HeadquartersId`	`Europe`

For each identifier you intend to give a custom entity, you must first call the Lusid.Property.Definition.Writer provider to create an underlying property type with the following characteristics:

A Domain of CustomEntity.
A PropertyScope with the identifierScope value, for example Location.
A PropertyCode with the identifierType value, for example OfficeId or HeadquartersId.
A ConstraintStyle of Identifier.
A LifeTime of Perpetual.

Creating a property type to constitute an 'Office location' identifier

The following query creates a property type with a 3-stage property key of CustomEntity/Location/OfficeId:

@table_of_data = select 'CustomEntity' as Domain, 'Location' as PropertyScope, 'OfficeId' as PropertyCode, 'Identifier' as ConstraintStyle,
'Perpetual' as LifeTime, 'system' as DataTypeScope, 'string' as DataTypeCode, 'This is an Office ID' as DisplayName, 'insert' as WriteAction;

select * from Lusid.Property.Definition.Writer where ToWrite = @table_of_data;

Creating a property type to constitute a 'Headquarters' identifier

The following query creates a property type with a 3-stage property key of CustomEntity/Location/HeadquartersId:

@table_of_data = select 'CustomEntity' as Domain, 'Location' as PropertyScope, 'HeadquartersId' as PropertyCode, 'Identifier' as ConstraintStyle,
'Perpetual' as LifeTime, 'system' as DataTypeScope, 'string' as DataTypeCode, 'This is a Headquarters ID' as DisplayName, 'insert' as WriteAction;

select * from Lusid.Property.Definition.Writer where ToWrite = @table_of_data;

Creating an Office read/write provider pair in the Luminesce catalog

With the custom entity type and identifier property types in place, you can create:

A Lusid.CustomEntity.Office provider to retrieve Office custom entities from LUSID.
A Lusid.CustomEntity.Office.Writer provider to create, update or delete Office custom entities.

These providers are added to the Luminesce catalog for suitably-permissioned users to use:

You create the read and write providers in the same operation by writing a special Luminesce SQL query to define the provider fields (that is, columns; these are the same for both providers). There must be one column for each:

Custom data field in the custom entity type, for example isHeadOffice, seatingCapacity and address. You can retrieve information about these data fields using the Lusid.CustomEntity.Definition provider.
Identifier property type, for example CustomEntity/Location/OfficeId and CustomEntity/Location/HeadquartersId. You can retrieve information about these identifiers using the Lusid.Property.Definition provider.

This query must use the Sys.Admin.File.SaveAs direct provider to save a table of configuration data to a CSV file in Luminesce's file system, and make it available in the catalog. The goal of the query is to assemble a table that, for the Office read and write providers, might look like this:

ConfigFileKey	ConfigFileDataType	ConfigFileColumnName (optional)	ConfigFileColumnTooltip (optional)
`address`	`String`		`This is a custom data field for the custom entity`
`CustomEntity/Locations/HeadquartersId`	`_identifier`	`HeadquartersId^`	`This is an identifier for the custom entity`
`CustomEntity/Locations/OfficeId`	`_identifier`	`OfficeId^`	`This is an identifier for the custom entity`
`isHeadOffice`	`Boolean`		`This is a custom data field for the custom entity`
`seatingCapacity`	`Decimal`		`This is a custom data field for the custom entity`

Note the following:

The column headers in this table can be anything, but order is important: the ConfigFileKey column containing the FieldName of custom data fields and the PropertyKey of identifiers must be first.
The presence of the ^ character after ConfigFileColumnName values designates these as 'main' columns in the providers; that is, returned by a query such as select ^ from Lusid.CustomEntity.Office.
If you do not specify ConfigFileColumnName values, ConfigFileKey values are used for provider column names. Note, however, column names must be unique in the provider, so the ability to alias is useful.
ConfigFileColumnTooltip values appear in tooltips after column names in the Luminesce catalog to help users understand the expected data.

The query must save the table to a CSV configuration file with a name of the form <custom-entity-type>customentityproviderfactory.csv, for example Officecustomentityproviderfactory.csv. For example:

-- Name of custom entity
@@entityTypeName = select 'Office';

-- Define columns for read and write providers
@definition = 
-- Retrieve custom data fields from custom entity type using Lusid.CustomEntity.Definition
select FieldName as ConfigFileKey, FieldType as ConfigFileDataType, null as ConfigFileColumnName, 
'This is a custom data field for the custom entity' as ConfigFileColumnDescription
from Lusid.CustomEntity.Definition where EntityTypeName = @@entityTypeName
union
-- Retrieve identifiers from property types using Lusid.Property.Definition, and set as main fields
select PropertyKey, '_identifier', PropertyCode || '^', 'This is an identifier for the custom entity'
from Lusid.Property.Definition 
where Domain = 'CustomEntity' and PropertyScope = 'Location' and PropertyCode like '%Id' and ConstraintStyle = 'Identifier';

-- Save to CSV configuration file
@x =
use Sys.Admin.File.SaveAs with @definition, @@entityTypeName
--path=/config/lusid/factories/
--type:Csv
--fileNames
{@@entityTypeName}customentityproviderfactory
enduse;
select * from @x;

Click the Refresh button in the Luminesce catalog (yellow box below) to see the populated Lusid.CustomEntity.Office and Lusid.CustomEntity.Office.Writer providers:

Note the following:

Green columns are custom data fields extracted from the custom entity type.
Red columns are identifiers extracted from property types.
Blue columns are built-in data fields of the custom entity type; these are automatically extracted.

Creating Office custom entities using Lusid.CustomEntity.Office.Writer

You can now use Lusid.CustomEntity.Office.Writer to create as many Office custom entities (that is, instances of the ~Office custom entity type) as you need.

For example, the following query creates three Office custom entities for FINBOURNE, one for each of the Singapore, London and New York offices. Note the following:

You must specify a value for at least one identifier (OfficeId and/or HeadquartersId)
You must specify a value for each built-in data field (displayName and description)
You must specify a value for each custom data field described as required in the custom entity type (isHeadOffice and address)
'Upsert' as WriteAction sets the operation to 'insert or update' (as opposed to delete):

@vals = values
('Singapore', 'Asia', '8 Marina Boulevard', '8 Marina Boulevard, 018981, Singapore', 10, 'FINBOURNE office and APAC regional headquarters'),
('London', 'Europe', 'One Carter Lane', London, England, EC4V 5ER', 150, 'FINBOURNE office and EMEA regional headquarters'),
('NewYork', 'USA', '25 W 39th St', '25 W 39th St, 7th Floor, New York, NY, 10018', 25, 'FINBOURNE office and Americas regional headquarters');

@table_of_data = select column1 as OfficeId, column2 as HeadquartersId, True as isHeadOffice, column3 as DisplayName, 
column6 as Description, column5 as seatingCapacity, column4 as address, 'Upsert' as WriteAction from @vals;

select * from Lusid.CustomEntity.Office.Writer where ToWrite = @table_of_data;

Updating existing custom entities

To update an existing custom entity, you must specify:

At least one identifier
The displayName and description built-in data fields
All required custom data fields (isHeadOffice and address)

...whether you are updating their actual values or not.

For example, to update Singapore's displayName and address:

@table_of_data = select
  'Singapore' as OfficeId,
  '7 Marina Boulevard' as DisplayName,
  'FINBOURNE office and APAC regional headquarters' as Description,
  True as isHeadOffice,
  '7 Marina Boulevard, 018981, Singapore' as address,
  'Upsert' as WriteAction;
select * from Lusid.CustomEntity.Office.Writer where ToWrite = @table_of_data;

Specifying values for time-variant custom data fields

A time-variant custom data field can have different values during different time periods. For example, Singapore's seatingCapacity might be 10 during the 2022 calendar year, but 15 during 2023.

You can use the EffectiveFrom column to specify a datetime from which a time-variant custom data field is valid. Note if you omit EffectiveFrom, time-variant custom data fields are valid from 1 January 0001 to 31 December 9999 (that is, effectively all of time), the same as perpetual custom data fields, identifiers, and the displayName and description built-in data fields.

Since in our example another time-variant custom data field, isHeadOffice, is required, we must union two records in order to set a value of 15 for seatingCapacity effective 1 January 2023:

The first record sets seatingCapacity and EffectiveFrom but specifies null for isHeadOffice to ignore it.
The second record sets isHeadOffice to True (its original value) and specifies null for seatingCapacity and EffectiveFrom to ignore them (essentially leaving isHeadOffice as-is).

The end result is that seatingCapacity is set to 10 between 1 January 0001 and 31 December 2022, and 15 between 1 January 2023 and 31 December 9999. Without the union, the required, time-variant isHeadOffice custom data field would also be divided into these two time periods:

@table_of_data = select
 'Singapore' as OfficeId,
 '7 Marina Boulevard' as DisplayName,
 'FINBOURNE office and APAC regional headquarters' as Description,
 null as isHeadOffice,
 15 as seatingCapacity,
 #2023-01-01# as EffectiveFrom
union all
select
 'Singapore' as OfficeId,
 '7 Marina Boulevard' as DisplayName,
 'FINBOURNE office and APAC regional headquarters' as Description,
 True as isHeadOffice,
 null as seatingCapacity,
 null as EffectiveFrom
;

select * from Lusid.CustomEntity.Office.Writer where ToWrite = @table_of_data;

Deleting custom entities

You can delete one or more custom entities by specifying a single identifier for each and using 'Delete' as WriteAction:

@vals = values
('Singapore'),
('NewYork');

@table_of_data = select column1 as OfficeId, 'Delete' as WriteAction from @vals;

select * from Lusid.CustomEntity.Office.Writer where ToWrite = @table_of_data;

Retrieving Office custom entities using Lusid.CustomEntity.Office

You can use Lusid.CustomEntity.Office to retrieve custom entities in the usual way for a read provider, specifying a filter on any column. For example:

select * from Lusid.CustomEntity.Office where OfficeId = 'London';

Note that filtering is currently faster on DisplayName than on any identifier. So for example the query:

select * from Lusid.CustomEntity.Office where DisplayName = 'One Carter Lane';

...is faster than the query above.

To examine different values for time-variant custom data fields, use the EffectiveAt parameter. For example, to retrieve values for Singapore's seatingCapacity:

-- Specifying an effective at date during the 2022 calendar year returns 10
select * from Lusid.CustomEntity.Office where OfficeId = 'Singapore' and EffectiveAt = #2022-06-01#;
-- Specifying an effective at date during the 2023 calendar year returns 15
select * from Lusid.CustomEntity.Office where OfficeId = 'Singapore' and EffectiveAt = #2023-06-01#;

Administering the Office read/write provider pair

Changing the providers

You can retrieve the CSV configuration file used to create a read/write provider pair using the Sys.File provider, for example:

select Content from Sys.File where Name = 'Officecustomentityproviderfactory' and Extension = '.csv';

You might want to examine the file in preparation for making a change. You can re-run the Sys.Admin.File.SaveAs query at any time to create a different CSV configuration file, and then click the Refresh button in the Luminesce catalog to reload the providers.

Deleting the providers

You can remove a read/write provider pair from the Luminesce catalog by re-running the Sys.Admin.File.SaveAs query, this time specifying the --removeFiles option and supplying the name and file extension of the CSV configuration file, for example:

@cleared = use Sys.Admin.File.SaveAs
--path=/config/lusid/factories
--removeFiles
--fileNames
Officecustomentityproviderfactory.csv
enduse;
select * from @cleared;

Keywords: luminesce, provider, custom entity