A derived property is a special kind of property that you can add to most types of LUSID entity to extend the quality and quantity of information stored about those entities.
Rather than having property values assigned explicitly, LUSID calculates derived property values on-the-fly using one or more mathematical or related operations.
Consider the following example, of instruments loaded into LUSID from two different sources. Data relating to domicile from each source has been mapped to two (standard) properties, represented by the country_issue and origin columns. We can see that the same countries are represented using different values, and that some values are missing:
Because it seems likely this information has the same meaning, we can create a Domicile derived property that automatically maps and coalesces country_issue and origin values to a new, normalised set. This derived property can subsequently be used for reporting purposes or for consumption by a downstream system, without having altered the original data:
The derivation formula (see below) that controls how LUSID calculates Domicile values might look like this:
'United Kingdom'='UK', 'united_kingdom'='UK',
'Great Britain'='UK', 'GB'='UK', 'DE'='Germany',
'United States'='USA', 'usa'='USA', default='Unknown')
Creating a derived property definition
The first step is to create a derived property definition, which must consist of:
- A display name.
- A data type, for example string or number.
- A 3-stage property key that uniquely identifies the derived property. The first stage is the ‘domain’ (entity) to which the derived property belongs, for example
Instrument
orTransaction
. The second stage is a scope, or namespace, which can be any intuitive string. The third stage is a code, which must be unique within the scope, and again can be any intuitive string. So for example, the Domicile derived property in the picture above might have the property keyInstrument/Derived/Domicile
. - A derivation formula that specifies how LUSID should calculate derived property values on-the-fly.
Note the following:
- A derived property definition, like a property definition, is one of the few data structures in LUSID that is monotemporal. That is, the definition can be updated, and you can retrieve previous definitions by rolling back the as at timeline. However, for a given as at datetime, the prevailing definition will apply to all effective at datetimes.
- It is not possible to alter the derivation formula once a derived property is created. You would need to create a new derived property, or delete and recreate the existing one if you want to use the same scope and code.
- A derived property cannot derive from another derived property.
Using the REST API
- Obtain an API access token.
- Call the LUSID CreateDerivedPropertyDefinition API for your LUSID domain, passing in your API access token and a suitable definition, for example:
curl -X POST "https://<your-domain>.lusid.com/api/api/propertydefinitions/derived"
-H "Authorization: Bearer <your-API-access-token>"
-H "Content-Type: application/json"
-d '{
"domain": "Instrument",
"scope": "Derived",
"code": "Domicile",
"displayName": "Domicile",
"dataTypeId": {
"scope": "system",
"code": "string"
},
"propertyDescription": "Normalising domicile-related data",
"derivationFormula": "map(coalesce(Properties[Instrument/VendorA/country_issue], Properties[Instrument/VendorB/origin], 'Unknown'):
'United Kingdom'='UK', 'united_kingdom'='UK',
'Great Britain'='UK', 'GB'='UK', 'DE'='Germany',
'United States'='USA', 'usa'='USA', default='Unknown')"
}'
Using the LUSID web app
- Sign in to the LUSID web app using the credentials of a LUSID administrator.
- From the left-hand menu, select Data Management > Properties.
- On the Properties dashboard, click the Create property button (top right).
- Enter Basic data (domain, scope and code) and then choose Derived property.
- Choose a Data type and then enter the Derivation formula, for example:
Constructing a derivation formula
A derivation formula consists of one or more operations on one or more values. Note the following:
- Operations can be nested. So for example a Mathematical operation can add a number to the result of another, nested Mathematical operation.
- The term ‘entity object’ in the table below is used to refer to all the fields, properties, identifiers and other data structures belonging to entities that are available to be operated on in the formula.
Operation | Syntax | Allowed values |
Mathematical | <value> <operator> <value> |
|
Examples:
|
||
Concatenation | concat(<value>, <value>…) |
|
concat(Properties[Instrument/default/Name], ' - ', Properties[Instrument/default/Currency], '(', Properties[Instrument/default/Country], ')') concat(Name, ' - ', Luid) |
||
Numeric mapping | map(<value>: <expression>) |
|
map(Properties[Instrument/default/S_and_P]: 'AA'=1, 'BB'=2,'CC'=3, default=0) map(Properties[Instrument/default/Duration]: '3 months'=3, '9 months'=9) |
||
String mapping | map(<value>: <expression>) |
|
map(Properties[Instrument/default/Duration]: '3 months'='3m', '9 months'='9m') |
||
Average | average(<value>, <value>…) |
|
average(Properties[Instrument/default/Price], Properties[Instrument/default/Cost]) |
||
Coalesce | coalesce(<value>, <value>…) |
|
Note Coalesce prefers property values in the order they are specified. So in the first example below, the derived property takes its description value from
|
||
Replace | replace(<value>: <string> = <string>) |
|
Replace(Name: 'instr'='instrument') |
||
If | if(<value> <operator> <value>) then <trueValue> else <falseValue> |
|
if(Properties[Transaction/Commbank/Price] gt Properties[Transaction/Commbank/Cost]) then 'true' else 'false' |
||
Convert to string | toString(<value>) |
|
Convert to number | toNumber(<value>) |
If |
Using entity objects in derivation formulas
The data structures belonging to entities that can be used in a derivation formula differ depending on the type of entity the derived property belongs to.
For example, if the derived property belongs to holdings (that is, the first part of the 3-stage property key signals it is in the Holding
domain), then values can derive from:
- Any other (standard) property in the
Holding
domain, and/or - Certain fields on holdings themselves, and/or
- Certain fields, identifiers, sub-holding keys and properties on other entities.
Note: A derived property cannot derive from another derived property.
Consider the following example, of a derivation formula for a Holding
derived property that consists of a series of If operations and a nested Concat operation, all conditional on the HoldingType
entity field:
else if ( HoldingType eq 'R' ) then 'Payable/Receivable'
else if ( HoldingType eq 'B' ) then 'Cash'
else concat('Unknown holding type ', HoldingType)
Entity (derived property domain) | ||||
Instrument | Portfolio | Transaction | Holding | |
Fields on this type of entity that can be used in a formula | LusidInstrumentId Name State LookthroughPortfolio.Scope LookthroughPortfolio.Code |
Id.Scope Id.Code Type DisplayName Description Created IsDerived BaseCurrency ParentPortfolioId.Scope ParentPortfolioId.Code |
TransactionId CounterpartyId TransactionDate SettlementDate EntryDateTime Units ExchangeRate Source TransactionCurrency InstrumentUid Type TransactionPrice.Price TransactionPrice.Type TotalConsideration.Amount TotalConsideration.Currency |
InstrumentUid HoldingType SettledUnits CostPortfolioCcy.Amount Cost.Amount Cost.Currency |
Identifiers on this type of entity | For example:Identifiers[ClientInternal] Identifiers[Figi] Identifiers[LusidInstrumentId] |
n/a | For example:InstrumentIdentifiers[Instrument/default/ClientInternal] InstrumentIdentifiers[Instrument/default/Figi] InstrumentIdentifiers[Instrument/default/LusidInstrumentId] |
n/a |
Properties on this type of entity | For example:Properties[Instrument/X/Y] Properties[Instrument/Y/X] |
For example:Properties[Portfolio/X/Y] Properties[Portfolio/Y/X] |
For example:Properties[Transaction/X/Y] Properties[Transaction/Y/X] |
For example:Properties[Holding/X/Y] Properties[Holding/Y/X] |
Objects on other entity types | n/a | n/a |
Instrument properties (note you must also decorate the instrument property when decorating the derived property onto retrieved transactions). For example: |
Transaction fields: |
Calculating derived property values on-the-fly
There's no equivalent step for explicitly adding a derived property to entities of a particular type, as there is with standard properties. LUSID calculates values on-the-fly when asked to decorate a derived property onto retrieved entities of that type.
For example, to retrieve the Domicile derived property for the BP instrument (LUID_00003D5D), call the LUSID GetInstrument API for your LUSID domain, passing in your API access token and the Instrument/Derived/Domicile
3-stage property key:
-H "Authorization: Bearer <your-API-access-token>"
The derived property value is automatically calculated and returned, in this case UK
:
"href": "https://<your-domain>.lusid.com/api/api/instruments/LusidInstrumentId/LUID_00003D5D?scope=default",
"scope": "default",
"lusidInstrumentId": "LUID_00003D5D",
"version": {
"effectiveFrom": "0001-01-01T00:00:00.0000000+00:00",
"asAtDate": "2022-03-02T11:18:11.5634600+00:00"
},
"name": "BP",
"identifiers": {
"ClientInternal": "imd_43535553",
"Ticker": "BP/LN",
"ShareClassFigi": "BBG001S61CN0",
"LusidInstrumentId": "LUID_00003D5D",
"Figi": "BBG000C05BD1",
"CompositeFigi": "BBG000C059M6",
"Isin": "GB0007980591"
},
"properties": [
{
"key": "Instrument/Derived/Domicile",
"value": {
"labelValue": "UK"
},
"effectiveFrom": "0001-01-01T00:00:00.0000000+00:00"
}
],
"instrumentDefinition": {
"identifiers": {},
"domCcy": "GBP",
"instrumentType": "Equity"
},
"state": "Active",
"assetClass": "Equities",
"domCcy": "GBP",
...
}