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 records have been added to a RDD, note the following update restrictions to the fieldSchema:

Deleting a relational dataset definition

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

curl -X POST 'https://<your-domain>.lusid.com/api/api/relationaldatasetdefinitions/MyRDDs/Addresses'
  -H 'Content-Type: application/json-patch+json'
  -H 'Authorization: Bearer <your-API-access-token>'