Views:
TypeRead/writeAuthorAvailability
Data providerReadFinbourneProvided with LUSID

The Lusid.Relationship provider enables you to write a Luminesce SQL query that retrieves relationships between 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 identifiers for different entity types have different components:

Entity typeComponents of identifier
LegalEntity, PersonidTypeScope, idTypeCode, code
InstrumentidentifierType, identifierValue, scope (if not in the default scope)
Custom entityidentifierType, identifierScope, identifierValue
All other types, including Portfolio and PortfolioGroupscope, 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 fieldStatusExplanation
EntityTypeRequiredSpecify the type of entity to look up. Must be a string that is one of the entity types that support relationships, for example Portfolio, PortfolioGroup, LegalEntityPerson, Instrument or the name of a custom entity preceded by ~, for example ~Office.
EntityScopeRequired

The value of this field depends on the type of entity you want to look up:

Type of entity to look upOrigin of EntityScope value
LegalEntity, PersonidTypeScope
Instrumentscope
Custom entityidentifierScope
All other types, including Portfolio and PortfolioGroupscope
EntityCodeRequired

The value of this field depends on the type of entity you want to look up.

Type of entity to look upOrigin of EntityCode value
LegalEntity, PersonidTypeCode
InstrumentidentifierType
Custom entityidentifierType
All other types, including Portfolio and PortfolioGroupcode
EntityValueRequired for LegalEntityPerson and custom entities

The value of this field depends on the type of entity you want to look up:

Type of entity to look upOrigin of EntityValue value
LegalEntity, Personcode
InstrumentidentifierValue
Custom entityidentifierValue
All other types, including Portfolio and PortfolioGroupN/A. Do not specify this field.
RelationshipScopeOptionalSpecify the scope of a particular relationship to return for the entity. If omitted, all relationships are returned, though note this may take some time.
RelationshipCodeOptionalSpecify the code of a particular relationship to return for the entity. If omitted, all relationships 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.

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.

In the following examples, a single entity is looked up for the sake of clarity, but Lusid.Relationship is designed to handle looking up multiple entities simultaneously.

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 a particular relationship for a particular person

Note this query assumes a person entity with an identifier key of Person/PortfolioManagers/ManagerId exists, and has a value of PortMan1.

@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.Person to Lusid.Relationship to retrieve more information about related people. Note PortManId is an identifier key that has been explicitly 'inlined' as a field for the Lusid.Person provider.
  • Displays the DisplayName, LusidEntityId and PortManId values 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, PortManId

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 and type.
@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 = @table_of_data;

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)