What is a derived property?

A derived property is a special kind of property whose values derive from one or more data fields or other properties, using a mathematical, mapping or similar operation. Current restrictions and limitations.

Required reading: Part 1: Operations and allowed values and Part 2: Entity components.

Unlike standard properties, you do not explicitly add derived properties to entities and specify values. Rather, LUSID automatically calculates values when you choose to retrieve entities with their derived properties.

You can use derived properties to curate a 'golden source' representation of your data. Consider the following example, of instruments loaded into LUSID from two different data vendors. Data relating to domicile from each vendor 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 our having altered the original data:

Working with derived properties

1. The first step is to construct a derivation formula. For example, the Domicile derived property above might have a formula that uses the map and coalesce operations like this:

   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')

   For information on syntax, available operations, and which data fields and properties you can derive from, see this article.

2. The next step is to create a derived property type that stores the derivation formula and defines other characteristics such as the entity type to which derived properties belong.
3. The final step is to cause LUSID to automatically calculate derived property values by retrieving entities with their derived properties.

Restrictions and limitations of derived properties

1. Not all types of entity support derived properties. For example, custom entities, portfolio groups, and OMS-related entities (placements, blocks, executions and so on) do not support derived properties. See the last column in this table.
2. A derived property in a domain (ie. Holding) can have a formula that derives from any other property in the Holding domain (including other derived properties), but it is not guaranteed to be able to derive from all stored holding fields, nor can it necessarily derive from properties, fields, identifiers or similar components from other types of entity. See these tables.
3. In particular, note a derived property in the Instrument domain cannot have a formula that derives from fields in the nested economic definition of instruments.
4. A derivation formula cannot be longer than 8000 characters.
5. A derived property cannot be multi-value, nor time-variant.
6. A derived property cannot be used as a sub-holding key (SHK).
7. A derived property cannot be used in the definition of a transaction type.
8. A derived property in the Instrument domain can be retrieved for (that is, decorated onto) holdings and transactions, but it is not otherwise possible to retrieve derived properties across domains in the same way as standard properties.  
9. A derived property cannot be used in a filter expression when retrieving entities from LUSID. This is changing soon.