| Type | Read/write | Author | Availability |
| Data provider | Write | Finbourne | Provided with LUSID |
The Lusid.Relationship.Writer provider enables you to write a Luminesce SQL query that either upserts or deletes relationships between entities that support relationships, for example a person and a portfolio.
Note: The LUSID user running the query must have sufficient access control permissions to both use the provider and interact with relationship data in LUSID. This should automatically be the case if you are the domain owner.
You must construct a valid table of data to write, one relationship per record. Lusid.Relationship.Writer lists the fields (columns) available to populate with values for each record, and has a set of parameters to help you construct a valid table.
Your query can use the WriteAction field to perform one of the following operations:
- Upsert a relationship; that is, create one if it does not exist, and update it if it does. This is the default operation if you omit
WriteAction. - Delete a relationship.
See also: Lusid.Relationship
Basic usage
@table_of_data = <select-statement>;
select * from Lusid.Relationship.Writer where toWrite = @table_of_data;
Query parameters
Lusid.Relationship.Writer has parameters that help you construct a valid table of data to write.
Note: The toWrite parameter is mandatory and used to actually write the table of data into LUSID.
To create a relationship, you must unambiguously identify the two entities at either end. Note that identifiers for different entity types have different components:
| Entity type | Components of identifier |
LegalEntity, Person | idTypeScope, idTypeCode, code |
Instrument | identifierType, identifierValue, scope (if not in the default scope) |
| Custom entity | identifierType, identifierScope, identifierValue |
All other types, including Portfolio and PortfolioGroup | scope, code |
For more information, consult this table of entities and their identifiers.
Use the following data fields in the select statement for toWrite to identify the two entities at either end and provide other mandatory information, such as the relationship type:
| Data field(s) | Status | Explanation | ||||||||||
EntityType, RelatedEntityType | Required | The types of the two entities. Allowed values are the entity types supporting relationships, for example Portfolio, PortfolioGroup, LegalEntity, Person, Instrument or the name of a custom entity preceded by ~, for example ~Office. Both entities can be of the same type, for example to connect two people. | ||||||||||
EntityScope, RelatedEntityScope | Required for all except Instrument. If omitted for Instrument, uses the default instrument scope. | Part of the identifier for either entity. The origin of this value depends on the entity type:
| ||||||||||
EntityCode, RelatedEntityCode | Required | Part of the identifier for either entity. The origin of this value depends on the entity type:
| ||||||||||
EntityValue, RelatedEntityValue | Required for LegalEntity, Person, Instrument and custom entities. Do not specify for other types. | Part of the identifier for either entity. The origin of this value depends on the entity type:
| ||||||||||
RelationshipScope, RelationshipCode | Required | The scope and code of the relationship type to which the relationship belongs. |
To list all available parameters, their data types, default values, and an explanation for each, run the following query using a suitable tool:
select FieldName, DataType, ParamDefaultValue, Description from Sys.Field where TableName = 'Lusid.Relationship.Writer' and FieldType = 'Parameter';
Data fields
Lusid.Relationship.Writer lists the fields you can populate in your table of data to write.
Note: Some of these fields are mandatory to specify in your query; see the section above.
To list all available fields, their data types, whether fields are considered 'main', and an explanation for each, run the following query using a suitable tool:
select FieldName, DataType, IsMain, IsPrimaryKey, SampleValues, Description from Sys.Field where TableName = 'Lusid.Relationship.Writer' and FieldType = 'Column';
Write errors
We recommend examining the results of every write query using one or more of the WriteError, WriteErrorCode and WriteErrorDetail fields.
For each record in the table of data to write, Lusid.Relationship.Writer returns an error code. If the operation is successful, the error code is 0. If unsuccessful, a positive error code and explanation help you discover why LUSID considers the operation invalid.
For example, the query:
@table_of_data = select 'PortfolioManagementTeam' as RelationshipScope, 'Managers' as RelationshipCode, 'US-Investments' as EntityScope,
'US-Equities' as EntityCode, 'Portfolio' as EntityType, 'upsert' as WriteAction;
select WriteErrorCode, WriteError, WriteErrorDetail from Lusid.Relationship.Writer where toWrite = @table_of_data;
...fails because no related entity information is provided in the query.
Examples
Note: For more example Luminesce SQL queries, visit our Github repo.
Example 1: Create a relationship between a portfolio and a person
You can create a relationship by supplying all mandatory fields for both entities and using 'upsert' as WriteAction.
Note that if a relationship has either a portfolio or a portfolio group at either end (or at both ends), then you must specify the EffectiveFrom field with a date that is later than the creation date of these time-variant entities. This is not required if the relationship only connects instrument, person, custom or legal entities, which are perpetual, although you can optionally specify this field if the relationship itself is time-variant.
@table_of_data = select 'PortfolioManagementTeam' as RelationshipScope, 'Managers' as RelationshipCode, 'Portfolio' as EntityType,
'US-Investments' as EntityScope, 'US-Equities' as EntityCode, #2022-06-01# as EffectiveFrom, 'Person' as RelatedEntityType,
'PortfolioManagers' as RelatedEntityScope, 'ManagerId' as RelatedEntityCode, 'PortMan1' as RelatedEntityValue, 'upsert' as WriteAction;
select * from Lusid.Relationship.Writer where ToWrite = @table_of_data;
Example 2: Update a time-variant relationship effective from/until a certain date
You can update the EffectiveFrom and/or EffectiveUntil dates of a time-variant relationship by supplying all mandatory fields for both entities and using 'upsert' as WriteAction.
Note you cannot update a perpetual relationship.
@table_of_data = select 'PortfolioManagementTeam' as RelationshipScope, 'Managers' as RelationshipCode, 'Portfolio' as EntityType,
'US-Investments' as EntityScope, 'US-Equities' as EntityCode, #2023-01-01# as EffectiveFrom, 'Person' as RelatedEntityType,
'PortfolioManagers' as RelatedEntityScope, 'ManagerId' as RelatedEntityCode, 'PortMan1' as RelatedEntityValue, 'upsert' as WriteAction;
select * from Lusid.Relationship.Writer where ToWrite = @table_of_data;
Example 3: Delete a relationship
You can delete a relationship by specifying all mandatory fields for both entities and using 'delete' as WriteAction.
@table_of_data = select 'PortfolioManagementTeam' as RelationshipScope, 'Managers' as RelationshipCode, 'Portfolio' as EntityType,
'US-Investments' as EntityScope, 'US-Equities' as EntityCode, 'Person' as RelatedEntityType, 'PortfolioManagers' as RelatedEntityScope,
'ManagerId' as RelatedEntityCode, 'PortMan1' as RelatedEntityValue, 'delete' as WriteAction;
select * from Lusid.Relationship.Writer where ToWrite = @table_of_data;
Example 4: Create multiple relationships at the same time
You can create all your relationships at the same time in one Luminesce query. In this example, the query defaults to an upsert operation as no explicit WriteAction is specified.
@vals = values ('PortfolioManagementTeam', 'Managers', 'Portfolio', 'US-Investments', 'US-Equities', Null, 'Person', 'PortfolioManagers', 'ManagerId', 'PortMan1'), ('InstrumentIssuer', 'IssuerLEI', 'Instrument', 'Ibor', 'LusidInstrumentId', 'LUID_00003D93', 'LegalEntity', 'InternationalBanks', 'BankId', 'AcmeInc'); @table_of_data = select column1 as RelationshipScope, column2 as RelationshipCode, column3 as EntityType, column4 as EntityScope, column5 as EntityCode, column6 as EntityValue, column7 as RelatedEntityType, column8 as RelatedEntityScope, column9 as RelatedEntityCode, column10 as RelatedEntityValue from @vals; select * from Lusid.Relationship.Writer where ToWrite = @table_of_data;