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:

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:

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;