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 theLusid.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:
- 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. - Passes the properties into the
Sys.Admin.File.SaveAs
provider using the@propertiesToReturn
variable. - Saves the changes to
Lusid.Portfolio.Txn
to a system configuration CSV file with the special nameportfoliotransactionproviderfactory.csv
. - 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.SaveA
s 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;