How do I create a relational dataset definition?

You must create a relational dataset definition (RDD) to define the schema of a dataset before you can add records to it.

A dataset is equivalent to a table in a relational database. It can have a finite set of columns, a (nominally) infinite set of rows, and a nominated subset of columns that form (part of) the primary key. Anatomy of a dataset.

Methods

curl -X POST 'https://<your-domain>.lusid.com/api/api/relationaldatasetdefinitions'
  -H 'Content-Type: application/json-patch+json' 
  -H 'Authorization: Bearer <your-API-access-token>'
  -d '{
  "id": {
    "scope": "MyDatasets",
    "code": "Addresses"
  },
  "displayName": "Address custom dataset",
  "description": "A custom dataset modelling addresses for people and organisations",
  "applicableEntityTypes": ["Person","LegalEntity"],
  "fieldSchema": [
    {
      "category": "SeriesIdentifier",
      "fieldName": "AddressType",
      "displayName": "Address type",
      "description": "Whether a correspondence or a registered address",
      "dataTypeId": {"scope": "MyCustomDataTypes", "code": "AddressType"},
      "required": true,
    },
    {
      "category": "Value",
      "fieldName": "Street",
      "displayName": "Street",
      "description": "Building number/name and street",
      "dataTypeId": {"scope": "system","code": "string"},
      "required": true,
    },
    {
      "category": "Value",
      "fieldName": "City",
      "displayName": "Town or city",
      "dataTypeId": {"scope": "system","code": "string"},
      "required": true,
    },
    {
      "category": "Metadata"
      "fieldName": "Email",
      "displayName": "Contact email",
      "description": "Email address for primary contact",
      "dataTypeId": {"scope": "system","code": "string"}
    }
  ]
}'

Request fields

This section supplements the API documentation and on-screen help text in the LUSID web app.

A RDD:

• Must have a scope and a code that together uniquely identify it.

• Must have a displayName and can have a description.

• Must reference at least one of the applicableEntityTypes in the following list: Portfolio, Instrument, Transaction, Person, LegalEntity, InvestorRecord, InvestmentAccount, or a custom entity. Note each record in the dataset can be associated with one, some, or all entities of a particular type.

• Must have a fieldSchema containing at least one mandatory Value field (see below).

A fieldSchema:

• Can have one or more SeriesIdentifier fields. It may have none. Note that each forms part of the primary key, in conjunction with information about associated entities. While a SeriesIdentifier can be optional, note that if a user omits a value then part of the key will be missing for that record.  

• Must have at least one mandatory Value field. The remainder can be optional.

• Can have one or more Metadata fields, mandatory or optional.

The maximum number of fields is 100, of which no more than 50 can be SeriesIdentifier fields. Each:

• Must have a unique fieldName (no spaces), and can have a verbose displayName and description.

• Must have a category of either SeriesIdentifier, Value or Metadata.

• Can set required to true. The default is false. At a minimum, one Value field must be true.

• Must reference the dataTypeId of a built-in or custom data type with an underlying valueType of String, Int, Decimal, DateTime or Boolean. Note arrays are not supported; if you have a list of values (for example, email addresses) then you can either delimit multiple values in a string using a suitable character, or provide a separate field for each.

Subsequent updates

If no records have yet been added to a RDD, you can update any aspect of it by calling the UpdateRelationalDatasetDefinition API.

If records have been added, do not call this API. Instead, call:

• The UpdateRelationalDatasetDetails API to change the displayName and description, or to add or remove applicableEntityTypes. Note you cannot remove an entity type if records are associated with it.

• The UpdateRelationalDatasetFieldSchema API to change the fieldSchema. Note the following restrictions:

  Field type	Add new?	Remove existing?	Update existing?
  SeriesIdentifier	✅ but required must be false	❌	✅ but note the following: displayName and description required but only to false dataTypeId but only to the same valueType
  Value	✅	✅ but at least one must remain with required set to false	✅ but note the following: displayName and description required between true and false dataTypeId but only to the same valueType category to Metadata, but at least one mandatory Value field must remain
  Metadata	✅	✅	✅ but note the following: displayName and description required between true and false dataTypeId but only to the same valueType category to Value

Deleting a relational dataset definition

Call the DeleteRelationalDatasetDefinition API. Note any records added to the dataset are also deleted, and can only be retrieved by rolling back the asAt timeline when querying.