Lusid.Relationship

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:

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:

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)