Using Luminesce to write different kinds of property for LUSID entities

  • Published on Aug 22, 2024

You can use Luminesce to write (that is, add, update or delete) properties for LUSID entities in two ways:

  • For some entities such as instruments, portfolios and transactions, you can use dedicated property providers.

  • For any entity, by first configuring its entity provider to 'inline' properties into the Luminesce catalog. You can then use that entity provider to interact with properties in the same way as standard entity data fields.

Properties in LUSID are multi-faceted. This article demonstrates nuances of writing different kinds of property to an example instrument mastered in LUSID:

  • Using a query like this for the dedicated Lusid.Property.Writer property provider: 

    @table_of_data = select 'Figi' as EntityIdType, 'BBG000C05BD1' as EntityId, 'Instrument' as Domain, 'Test' as PropertyScope, 
'MyStringProperty' as PropertyCode, 'Fred Bloggs' as Value, 'Upsert' as WriteAction;
select * from Lusid.Property.Writer where ToWrite = @table_of_data;
    SQL

  • Using a query like this for a configured Lusid.Instrument.Equity.Writer entity provider: 

    @table_of_data = select 'BBG000C05BD1' as Figi, 'BP' as DisplayName, 'GBP' as DomCcy, 
'Fred Bloggs' as MyStringProperty, 'Upsert' as WriteAction;
select * from Lusid.Instrument.Equity.Writer where toWrite = @table_of_data;
    SQL

The mandatory Value field is a text field so you must supply property values as strings. Luminesce attempts to cast string values to the native type, or raises an error. For example:

@table_of_data = select 'MyNumericProperty' as PropertyCode, '200' as Value
@table_of_data = select 'MyDateTimeProperty' as PropertyCode, '2023-11-15 15:59:59' as Value
@table_of_data = select 'MyBooleanProperty' as PropertyCode, 'False' as Value
SQL

You specify the data type of the property when you inline it, so you can supply values in the native format. Note that dates in Luminesce must be encapsulated in # characters. For example:

@table_of_data = select 200 as MyNumericProperty
@table_of_data = select #2023-11-15 15:59:59# as MyDateTimeProperty
@table_of_data = select False as MyBooleanProperty
SQL

You should use the EffectiveFrom and EffectiveUntil fields to specify the validity period of a property value, for example:

@table_of_data = select 'MyTimeVariantProperty' as PropertyCode, 'Joe Bloggs' as Value, #2023-01-01# as EffectiveFrom, #2023-06-30# as EffectiveUntil
SQL

If you omit:

  • EffectiveFrom, a property value is valid from the datetime the query runs.

  • EffectiveUntil, a property value is valid until 9999-12-31.

Note: You can retrieve the values of a time-variant property as a time-series using the Lusid.Property provider with the GetHistorical parameter.

You should use the EffectiveFrom and PropertiesEffectiveUntil parameters to specify the validity period of a property value, for example:

@table_of_data = select 'Joe Bloggs' as MyTimeVariantProperty;
select * from Lusid.Instrument.Equity.Writer where toWrite = @table_of_data and EffectiveFrom = #2023-06-30# and PropertiesEffectiveUntil = #2023-06-30#;
SQL

If you omit:

  • EffectiveFrom, a property value is valid from the datetime the query runs.

  • PropertiesEffectiveUntil, a property value is valid until 9999-12-31.

Currently, multi-value properties in LUSID can only be strings. More limitations.

The value set must be a comma-separated list. The following examples use a dedicated property provider, but the principles apply when using a configured entity provider too:

@table_of_data = select 'MyMultiValueProperty' as PropertyCode, 'Value One, Value Two, Value Three' as Value
SQL

If a value has a comma or trailing white space, encapsulate that value in " characters, for example: 

@table_of_data = select 'MyMultiValueProperty' as PropertyCode, 'Value One, "Value Two, with a comma", " Value Three with trailing white space "' as Value
SQL