Lusid.Relationship · Customer Self-Service

Views:

Related resources:

Type	Read/write	Author	Availability
Data provider	Read	Finbourne	Provided with LUSID

The Lusid.Relationship provider enables you to write a Luminesce SQL query that retrieves relationships for one or more entities that support relationships in LUSID.

Note: The LUSID user running the query must have sufficient access control permissions to both use the provider and read relationship data stored in LUSID. This should automatically be the case if you are the domain owner.

Lusid.Relationship can query a relationship from either end. For example, if portfolio entities are related to person entities, then you can either look up a particular person and get related portfolios, or look up a particular portfolio and get related persons. Note by default you only retrieve simple identifiers for the entity at the other end, but you can join to the provider for that entity in order to retrieve more useful information (see examples below).

See also: Lusid.Relationship.Writer

Basic usage

@lookup_table = <select-statement>;
select * from Lusid.Relationship where toLookUp = @lookup_table and <filter-expression>

Query parameters

Lusid.Relationship has parameters that help you construct a valid table of data to look up.

The toLookUp parameter is mandatory. To look up an entity and retrieve its relationships, you require an identifier for that entity. 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 toLookUp to identify the entity to look up:

Data field Status Explanation

EntityType Required The type of entity to look up. Must be a string that is one of the entity types supporting relationships, for example Portfolio, PortfolioGroup, LegalEntity, Person, Instrument or the name of a custom entity preceded by ~, for example ~Office.

EntityScope

Required for all except Instrument. If omitted for Instrument, uses the default instrument scope.

Part of the identifier for the entity to look up. The origin of this value depends on the entity type:

Entity type	Origin of field value
`LegalEntity`, `Person`	`idTypeScope`
`Instrument` (optional)	`scope`
Custom entity	`identifierScope`
All other types, including `Portfolio` and `PortfolioGroup`	`scope`

EntityCode

Required

Part of the identifier for the entity to look up. The origin of this value depends on the entity type.

Entity type	Origin of field value
`LegalEntity`, `Person`	`idTypeCode`
`Instrument`	`identifierType`
Custom entity	`identifierType`
All other types, including `Portfolio` and `PortfolioGroup`	`code`

EntityValue

Required for LegalEntity, Person, Instrument and custom entities. Do not specify for other types.

Part of the identifier for the entity to look up. The origin of this value depends on the entity type:

Entity type	Origin of field value
`LegalEntity`, `Person`	`code`
`Instrument`	`identifierValue`
Custom entity	`identifierValue`
All other types, including `Portfolio` and `PortfolioGroup`	N/A. Do not specify this field.

RelationshipScope, RelationshipCode Optional The scope and code of a particular relationship type. If omitted, relationships of every type are returned, though note this may take some time.

To list all 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' and FieldType = 'Parameter';

Data fields

By default, Lusid.Relationship returns a table of data populated with particular fields (columns). You can return a subset of these fields.

Note: Some of these fields are mandatory to specify in your query; see the section above.

To list all fields available to return, 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' and FieldType = 'Column';

Note: Fields marked 'main' are returned by queries that start select ^ from Lusid.Relationship...

Errors

We recommend examining the results of every query using the Error field.

This field is populated if Lusid.Relationship cannot retrieve an entity, or relationships for that entity, for some reason. For example, the Error field reveals that this query:

@lookup_table = select 'Portfolio' as EntityType, 'UK-Equities' as EntityCode;
select Error from Lusid.Relationship where toLookUp = @lookup_table;

...fails because EntityScope has not been provided in the lookup table.

Examples

Note: For more examples, try the Luminesce Github repo.

Example 1: Retrieve all relationships for a particular portfolio

@lookup_table = select 'Portfolio' as EntityType, 'Finbourne-Examples' as EntityScope, 'Global-Equity' as EntityCode;
select * from Lusid.Relationship where toLookUp = @lookup_table;

Example 2: Retrieve relationships of a particular type for a particular person

Note this query assumes PortManId is an identifier that has been explicitly 'inlined' as a field for the Lusid.Person provider.

@lookup_table = select 'Person' as EntityType, 'PortfolioManagers' as EntityScope, 'ManagerId' as EntityCode,
'PortMan1' as EntityValue, 'PortfolioManagementTeam' as RelationshipScope, 'Managers' as RelationshipCode;

select * from Lusid.Relationship where toLookUp = @lookup_table;

Example 3: Retrieve more information about people related to a particular portfolio

This query:

Looks up all the relationships belonging to a Global Equity portfolio.
Joins Lusid.Relationship to Lusid.Person to retrieve more information about related people.
Displays the DisplayName and LusidEntityId of related people.

@lookup_table = select 'Portfolio' as EntityType, 'Finbourne-Examples' as EntityScope, 'Global-Equity' as EntityCode;
@relationships = select * from Lusid.Relationship where toLookUp = @lookup_table;

select 
    r.*,
    p.DisplayName, p.LusidEntityId
from @relationships r
left join Lusid.Person p
    on r.RelatedEntityValue = p.PortManId

Example 4: Retrieve all the portfolios related to two legal entities

In this example:

The Lusid.LegalEntity provider is used to populate values for EntityValue in the lookup table, and its where clause selects the two legal entities to look up.
More information is retrieved about related portfolios by joining Lusid.Relationship to Lusid.Portfolio on the related entity scope.

@lookup_table = select 'LegalEntity' as EntityType, 'InternalIdentifier' as EntityScope, 'Id1' as EntityCode,
Value as EntityValue, 'Production' as RelationshipScope, 'PortfolioLe' as RelationshipCode from Lusid.LegalEntity where Value in ('IdA', 'IdB');

@relationships = select * from Lusid.Relationship where toLookUp = @lookup_table;

select
    r.EntityType, r.EntityScope, r.EntityCode, r.EntityValue,
    p.*
from @relationships r
left join Lusid.Portfolio p
    on r.RelatedEntityScope = p.PortfolioScope
    and r.RelatedEntityCode = p.PortfolioCode

Example 5: Retrieve all the legal entities related to UK portfolios

In this example the Lusid.Porfolio provider is used to populate values in the lookup table, and the Lusid.LegalEntity provider is used to retrieve more information about related legal entities.

@lookup_table = select 'Portfolio' as EntityType, PortfolioScope as EntityScope, PortfolioCode as EntityCode, 
'Production' as RelationshipScope, 'PortfolioLe1' as RelationshipCode from Lusid.Portfolio where PortfolioCode like 'UK-%';

@relationships = select * from Lusid.Relationship where toLookUp = @lookup_table;

select
    r.EntityType, r.EntityScope, r.EntityCode, r.EntityValue,
    legal.*
from @relationships r
inner join Lusid.Legalentity legal
    on (r.RelatedEntityCode = 'Id1' and r.RelatedEntityValue = legal.IdA)

Keywords: luminesce, provider, relationship