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

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.

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

• Create custom entities of the type 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.

Defining an Office custom entity type

The first step is to create a custom entity type defining core characteristics for Office custom entities. Find out more about custom entity types.

Note the following:

• LUSID automatically applies a ~ character to the EntityTypeName to create an EntitytType identifier (in this case) of ~Office. The ~ is to distinguish custom entity types from LUSID's built-in entity types.

• All custom entity types have two mandatory built-in data fields: DisplayName and Description.

• Our Office custom entity type has three user-specified data fields: address, isHeadOffice and seatingCapacity, the first two of which are required.

• isHeadOffice and seatingCapacity are time-variant data fields (that is, values are expected to vary during different time periods), whereas address is perpetual.

You can use the Lusid.CustomEntity.Definition.Writer provider to create a custom entity type, for example:

@data_fields = values
('address', 'Perpetual', 'String', 'Single', True, 'The postal address of the location'),
('isHeadOffice', 'TimeVariant', 'Boolean', 'Single', True, 'Whether or not the location is a head office')
('seatingCapacity', 'TimeVariant', 'Decimal', 'Single', False, 'The seating capacity of the location');

@table_of_data = select 'Office' as EntityTypeName, 'Office location' as DisplayName,
'An office or branch location' as Description, column1 as FieldName,
column2 as FieldLifeTime, column3 as FieldType, column4 as FieldCollectionType,
column5 as FieldRequired, column6 as FieldDescription from @data_fields;

select * from Lusid.CustomEntity.Definition.Writer where toWrite = @table_of_data;

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:

For each identifier you intend to give a custom entity, you must first use 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 using the Sys.Admin.Lusid.Provider.Configure provider. You must supply the property keys of the identifiers and the name of the custom entity type; Sys.Admin.Lusid.Provider.Configure automatically extracts information about the built-in and user-specified data fields from the custom entity type. For example:

@identifiersToCatalog = values
('CustomEntity/Location/HeadquartersId', 'HeadquartersId', True, 'An identifier for a custom entity as a headquarters'),
('CustomEntity/Location/OfficeId', 'OfficeId', True, 'An identifier for a custom entity as an office');

@config = select column1 as [Key], column2 as Name, column3 as IsMain, column4 as Description from @identifiersToCatalog;

select * from Sys.Admin.Lusid.Provider.Configure
where Provider = 'Lusid.CustomEntity.Office'
and Configuration = @config;

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 that:

• Blue columns are built-in data fields extracted from the custom entity type.

• Green columns are user-specified data fields extracted from the custom entity type.

• Red columns are identifiers extracted from property types. 

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 user-specified 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 user-specified 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 user-specified data fields

A time-variant 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 data field is valid. Note if you omit EffectiveFrom, time-variant data fields are valid from 1 January 0001 to 31 December 9999 (that is, effectively all of time), the same as perpetual data fields, identifiers, and the DisplayName and Description built-in data fields.

Since in our example another time-variant 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 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 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 definition used to create a read/write provider pair using the Sys.File provider and supplying the name of the CSV file used to store it. This takes the form <custom-entity-type>customentityproviderfactory.csv, for example Officecustomentityproviderfactory.csv:

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

You might want to examine the CSV file in preparation for making a change. You can re-run the Sys.Admin.Lusid.Provider.Configure query at any time to specify a different definition, 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.Lusid.Provider.Configure query, this time with WriteAction = 'Reset', for example:

select * from Sys.Admin.Lusid.Provider.Configure
where Provider = 'Lusid.CustomEntity.Office'
and WriteAction = 'Reset';