How do I use Luminesce to read/write properties for LUSID entities?

Providing you are a LUSID user with sufficient access control permissions, you can retrieve custom properties decorated onto LUSID entities such as instruments, portfolios, holdings, transactions and so on.

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.

You can also:

• Write properties into LUSID; that is, add properties to entities.
• Read and write property definitions.

There are two ways to interact with properties, to suit different use cases:

• The easiest way is to use the dedicated property provider for the type of entity. For example, use the Lusid.Instrument.Property provider to retrieve properties decorated onto instruments, or the Lusid.Portfolio.Txn.Property.Writer property to add properties to existing transactions.
• The more complicated, but possibly more powerful, way is to first configure the entity provider to 'inline' properties into the standard set of entity fields. You can then use the entity provider in the normal way and read or write properties as you would data fields. See a list of configurable entity providers.

Note: For types of entity that support identifiers (such as instruments and legal entities), providers can interact with identifiers as well as properties.

Using a dedicated property provider

You can write a Luminesce SQL query using the following dedicated property providers:

To interact with...	Read using the ... provider	Write using the ... provider
Properties on instruments, portfolios and portfolio groups	Lusid.Property	Lusid.Property.Writer
Instrument properties (also identifiers)	Lusid.Instrument.Property
Holding properties	Lusid.Portfolio.Holding.Property
Transaction properties	Lusid.Portfolio.Txn.Property	Lusid.Portfolio.Txn.Property.Writer
Property definitions	Lusid.Property.Definition	Lusid.Property.Definition.Writer

For example, to retrieve all the properties for a particular instrument, run the following query using a suitable tool:

select * from Lusid.Instrument.Property where InstrumentId = 'LUID_ZGFGPRBE' and InstrumentIdType = 'LusidInstrumentId';

See the individual provider reference documentation above for more examples, and lists of parameters and fields.

Configuring an existing entity provider

By default, an entity provider such as Lusid.Portfolio.Txn does not retrieve properties decorated onto entities of that type (in this case, transactions).

Instead, Lusid.Portfolio.Txn returns a set of fields (columns) mapped to the required and optional attributes for transactions. You can see what these are by running the following query using a suitable tool:

select FieldName, DataType, IsMain, IsPrimaryKey, SampleValues, Description from Sys.Field where TableName = 'Lusid.Portfolio.Txn' and FieldType = 'Column';

Alternatively, you can expand the Lusid.Portfolio.Txn category in the LUSID web app to see the same list:

You can configure Lusid.Portfolio.Txn to retrieve some or all transaction properties. You can then write queries using this 'new' provider, which replaces the original. If desired, you can factory reset the provider to return to the default behaviour.

Configuring the Lusid.Portfolio.Txn provider

You write a special Luminesce query to specify the properties you want Lusid.Portfolio.Txn to return. This query uses the system Sys.Admin.File.SaveAs direct provider to save the configuration to Luminesce's file system, and make it available from this point on.

Note: Configuring a provider affects your entire domain, and so every user who wants to use that provider.

Consider the following query:

@propertiesToReturn = values
('Transaction/IBORUserJourney/Broker', 'Text', 'ExecutingBroker'),
('Transaction/IBORUserJourney/InvestorId' , 'Text', 'InvestorName'),
('Transaction/middle_office/Strategy' , 'Text', 'ClientStrategy');

@outputFromSaveAs = use Sys.Admin.File.SaveAs with @propertiesToReturn
--path=/config/lusid/factories/
--type:Csv
--fileNames
portfoliotransactionproviderfactory
enduse;
select * from @outputFromSaveAs;

This query:

1. Specifies a comma-separated list of values in parentheses determining which properties Lusid.Portfolio.Txn should return. Each value itself consists of three comma-separated parts:

   	First part	Second part	Third part
   Example	Transaction/IBORUserJourney/Broker	Text	ExecutingBroker
   Explanation	This is the 3-stage property key uniquely identifying the property. Note transaction properties must exist in the Transaction domain.	This is the data type. It must match that of the property. Valid values are: Boolean, Int, BigInt, Double, Decimal, Text, Date, DateTime	This is the name you want for the column in the query results. Please do not include spaces. Specify null to default to the third stage of the property key.
2. Passes the properties into the Sys.Admin.File.SaveAs provider using the @propertiesToReturn variable.
3. Saves the changes to Lusid.Portfolio.Txn to a system configuration CSV file with the special name portfoliotransactionproviderfactory.csv.
4. Returns a table of results summarising the actions performed.

Note: For entities that support identifiers, you can return them using the special _identifer keyword as the second value instead of the data type, for example ('LegalEntity/CreditAgency/Identifier' , '_identifier', 'CreditAgencyId').

The portfoliotransactionproviderfactory.csv system configuration file stores the changes you have made to Lusid.Portfolio.Txn (note it also makes these changes available to Lusid.Portfolio.Txn.Writer). To discover the name of the *providerfactory.csv system configuration file you need to configure other providers, run the following query:

select distinct Name, Description from Sys.Registration where Name like 'Lusid.%.Factory';

For example:

To configure the ... entity provider	Save changes to the ... system configuration file
Lusid.Instrument	instrumentproviderfactory.csv
Lusid.Portfolio	portfolioproviderfactory.csv
Lusid.Portfolio.Holding	portfolioholdingproviderfactory.csv
Lusid.Person	personproviderfactory.csv
Lusid.LegalEntity	legalentityproviderfactory.csv

Using the configured Lusid.Portfolio.Txn provider

If you expand the Lusid.Portfolio.Txn category in the LUSID web app again, you should now see the three new properties added to the list of fields you can return or filter:

You can now write a standard Luminesce query using the Lusid.Portfolio.Txn provider that includes the three properties. For example, the following query returns just those transactions that have a record of the executing broker:

select * from Lusid.Portfolio.Txn where ExecutingBroker <> '';

Factory resetting the Lusid.Portfolio.Txn provider

You can see a list of all currently-configured entity providers by running the following query:

select * from Sys.File where Name like '%providerfactory'

For the Lusid.Portfolio.Txn provider, this returns a result of the form: config/lusid/factories/portfoliotransactionproviderfactory.csv

To return to the default behavior, re-run the Sys.Admin.File.SaveAs query, this time specifying the --removeFiles option and supplying the name and file extension of the system configuration file, for example:

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