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.

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	Yes but required must be false	No	displayName and description required but only to false dataTypeId but only to the same valueType
  Value	Yes	Yes but at least one must remain with required set to true	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	Yes	Yes	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 calling the QueryRelationalData API.