# Using the Datamodel API

The Datamodels API allows you to develop scripts and applications that create and modify Sisense Datamodels. This guide will walk you through the steps required to understand the API's structure and correct use, including a basic use-case example.

The Datamodels API is fully RESTful and JSON-based, and is currently available on Sisense Linux versions starting from L8.1.

# Datamodel API Webinar

# Resources

The primary resource (entity) this API deals with is called Datamodel, which contains a hierarchy of child-resources comprising your Datamodel's schema. See the diagram below depicting this object model structure.

Object Model Diagram

# Datamodel

The Datamodel resource is the root of the object model hierarchy. Its OID will be used throughout all actions related to Datamodels.

A Datamodel can currently be of either extract or live type - mixed type Datamodels are not supported.

# Dataset

A Dataset resource represents a single data source of your Datamodel - for example, if your Datamodel contains data from a mix of several CSV files and MySQL databases, each CSV file and each database will be represented by a Dataset entity. Accordingly, the Dataset entity will contain the connection information to that data source.

In extract type Datamodels, Datasets will be of either extract or custom type. In live Datamodels, Datasets will be of either live or custom type.

# Table

A Table resource always belongs to a specific Dataset, representing various tables or collections in the data source.

Table resources can be of either base or custom type.

Tables of base type contain a collection of columns, telling Sisense how to represent the columns/properties of the original table in your datasource, and which ones. Tables of custom type will instead be built around the expression property, which is a SQL expression.

Each Table is assigned a build behavior, specifying how and when the Table's data should change when a Build or Publish occur. Additionally, Tables may have supplemental configuration telling Sisense how to process the data from the original data source. Tables can also be hidden, so they cannot be queried directly, which is handy when they are only used as a base for a more elaborate or user-friendly custom Table.

# Column

Table Columns are not represented as a resource in the RESTful sense, but they are an object contained within the Table resource and have their own specific structure you should be familiar with.

A Column object represents a Column or Dimension in your Datamodel, and can take on one of three forms:

A normal Column that represents a column or field in your original data source
A custom (calculated) column, defined by a SQL expression in its expression property
An auto-generated Column in a custom Table, derived from the Table's SQL expression

In all cases, Columns are described by various attributes such as their data type, size/precision (where applicable), name (which will be used to query the Column) and whether the Column should be visible/queryable or not.

# Relation

A Relation resource represents a relationship between 2 or more Tables. Each Relation contains an array of Columns from different tables that should be connected. These Relations are used as the JOIN path when running queries across multiple Tables.

# Endpoints

Each of the resource types described above except for Columns is represented by an API endpoint that supports CRUD (Create Read Update Delete).

Generally, each endpoint is represented by a specific URI path according to REST architecture standards, that is comprised of the following components:

A host address, containing the protocol, IP/DNS and port. For example: https://reporting.myapp.com:8081
An API base path, containing the API version (this API exists in version 2.0): /api/v2
The resource name, and sometimes oid: /datamodels or /datamodels/1234-some-resource-id)

Child resources as seen in the diagram above, such as Tables, have a nested URI structure and are accessible via the endpoints and oids of their parent resources.
For example: /datamodels/1234-some-resource-id/datasets/5678-other-resource-id.

With only a few exception, for each resource/endpoint the following operations are available:

Read (List): By using the
GET
HTTP method without a resource's oid, returning a list of matching resources
Read (Get): (aka get by id) By using the
GET
HTTP method and providing a specific resource's oid, returning that specific resource
Create: By using the
POST
HTTP method without a resource oid, creating a new resource of that type
Update: By using the
PATCH
HTTP method and providing a specific resource's oid, updating that specific resource's properties
Delete: By using the
DELETE
HTTP method and providing a specific resource's oid, removing that entire resource

# Creating a new Datamodel

# Creating a blank Datamodel object

To create a new Datamodel, use the

POST /datamodels

endpoint, with the following payload structure:

{
  "title": "My Datamodel",
  "type": "extract"
}

The only required field for the payload is title, as type defaults to extract.

# Creating a Dataset

To create a Dataset, you will need your Datamodel's oid as well as the correct connection object to match your data source. As the required properties of this object vary depending on the data source type (for example CSV data sources will differ greatly from a MySQL data source) this guide will not cover this object's structure - refer to the Connection Reference for more information.

Use use the

POST /datamodels/{DatamodelId}/datasets

endpoint, including your Datamodel's oid instead of {DatamodelId}, with the following payload structure:

{
    "name": "My Dataset",
    "type": "extract",
    "connection": {
        // Depends on connector type!
    }
}

# Custom Datasets

To create Custom Tables in your model, you will need to create a Custom type Dataset to contain it.

A Custom Dataset is created just like any other Dataset, but with the type property set to "custom" and with no connection property provided, like so:

{
    "name": "My Custom Dataset",
    "type": "custom"
}

# Creating a Table

To create a table, you will need the oid of your Datamodel and Dataset. You will also have to be familiar with your data source's schema, to be able to specify the columns that will be queried from it.

Use the

POST /datamodels/{DatamodelId}/datasets/{DatasetId}/tables

endpoint, including your Datamodel's oid instead of {DatamodelId} and your Dataset's oid instead of {DatasetId}, with the following payload structure:

{
    "id": "OriginalTable",
    "name": "MyTable",
    "description": "My new table with 2 columns",
    "columns": [
        {
            "id": "OriginalColumn1",
            "name": "MyColumn1",
            "type": 8
        },
        {
            "id": "OriginalColumn2",
            "name": "MyColumn2",
            "type": 18
        }
    ],
    "buildBehavior": {
        "type": "sync"
    },
    "configOptions": {
        // Some connector types, such as CSV, require additional configuration here.
    }
}

In the example above, a base type table (default) called MyTable will be created to represent a table called OriginalTable in your data source. It will contain 2 columns, called MyColumn1 and MyColumn2 in your Datamodel, representing the columns OriginalColumn1 and OriginalColumn2 respectively in the original table in your data source.

If you omit the name properties, they will default to the same name as the original table had (as specified in the id properties).

Each Table also has a build behavior defined, applicable only to exract type Tables.

Note that many additional properties are supported for both the Table and the Column objects, and this example only shows the very minimum required payload.

# Creating custom Columns

When creating (or updating) a Table, the columns array can contain custom columns, that are not extracted/queried from the data source but rather calculated within Sisense. These columns vary slightly in structure from regular Columns. In addition to the fields used before, 2 additional fields are provided for custom Columns:

The property isCustom is set to true to define this Column as a custom Column
The property expression contains a SQL expression used to calculate the value of this Column for each row in the Table. The SQL expression will use the column identifiers as they are seen in the Datamodel itself, as specified in the name property of each column, and not the original names from the data source.

Example:

{
    "id":"MyCustomColumn",
    "name": "MyCustomColumn",
    "type": 18,
    "expression": "select [MyColumn1] + ' ' + [MyColumn2]",
    "isCustom": true
}

For more information on custom Columns, see Creating Custom Columns (opens new window)

# Creating custom Tables

To create a custom Table, you will first need to create a custom Dataset. Refer to Creating Custom Datasets.

Once you have a custom Dataset's ID you can create custom Tables using the same endpoint as regular (base) tables, with a few differences:

Table's type is set to custom instead of base
An expression property is provided
The columns property is not provided

Example:

{
    "id": "custom-table-1",
    "name": "custom1",
    "type":"custom",
    "description": "Custom table",
    "expression": "select 'UK' as Country, 'UK' as Code UNION\nselect 'Canada' as Country, 'CA' as Code"
}

# Creating a Relation

To link 2 or more tables together, you will need the exact coordinates of the Columns you wish to connect through. Those coordinates are the oids of the Dataset, Table and Column.

Use the

POST /datamodels/{DatamodelId}/relations

endpoint, including your Datamodel's oid instead of {DatamodelId}, with the following payload structure:

{
    "columns": [ 
        {
            "dataset": "<dataset 1 oid>",
            "table": "<table 1 oid>",
            "column": "<column 1 oid>"
        },
        {
            "dataset": "<dataset 2 oid>",
            "table": "<table 2 oid>",
            "column": "<column 2 oid>"
        }
    ]
}

# Modifying a Datamodel

While the Datamodel entity itself has no editable fields, the various sub-entities do.

Various manipulations can be performed via the REST APIs, such as:

Changing a dataset's connection
Renaming, hiding and showing tables and columns
Adding or removing tables, columns and relations

# Changing a Dataset's connection

Datasets can be updated by using the

PATCH /datamodels/{DatamodelId}/datasets/{DatasetId}

endpoint, including your Datamodel's oid instead of {DatamodelId} and your Dataset's oid instead of {DatasetId}.

Notes:

Within the Dataset entity, only the connection property can be modified.
You must include all relevant attributes of the connection object, identical to the operation of Creating a Dataset.
As the required properties of this object vary depending on the data source type (for example CSV data sources will differ greatly from a MySQL data source) this guide will not cover this object's structure - refer to the Connection Reference for more information.
Tables that are part of the Dataset have a property id that specifies the source table identifier. In some instances, you may have to update these properties as well, such as with the CSV connector where a Table's id must be identical to the CSV filename.
The connection object contains several read-only properties that should not be passed to the PATCH endpoint. These are:
- id
- owner
- autoRefresh
- protectedParameters

For example, the following Dataset PATCH payload:

{
    "connection": {
        "provider": "CSV",
        "schema": "/opt/sisense/storage/datasets/storage/72/72761a45-0e91-47f5-bdf1-e422efe58131.csv",
        "fileName": "demo.csv",
        "parameters": {
            "ApiVersion": "2",
            "files": [
                "/opt/sisense/storage/datasets/storage/72/72761a45-0e91-47f5-bdf1-e422efe58131.csv"
            ]
        }
    }
}

Together with the following Table PATCH payload:

{
    "id": "72761a45-0e91-47f5-bdf1-e422efe58131.csv"
}

Will change the Dataset's connection property so it extracts data from the specified CSV file, and re-connect the table to the correct CSV file.

# Updating a Table

Tables can be updated by using the

PATCH /datamodels/{DatamodelId}/datasets/{DatasetId}/tables/{TableId}

endpoint, including your Datamodel's oid instead of {DatamodelId}, your Dataset's oid instead of {DatasetId}, and your Table's oid instead of {TableId}.

PATCH

operations can accept partial payloads, there are no strict requirements for which fields must be present in the body, but at least one property of the Table object must be present. Properties that are read-only will be ignored, while properties that do not belong to the Table object will result in an error.

For example, the following payload:

{
    "name": "MyTable2"
}

Will change the Table's name to MyTable2. Meanwhile, as the id property is unchanged, this Table will still represent the same corresponding Table in your original data source. However, any SQL expressions (such as those in custom Columns and custom Tables) will have to be updated as they do use the Table's name!

# Updating (or removing) a Table's Columns

As Columns are not represented by their own API endpoint, any changes to a Table's Columns is done via the Tables endpoint's

PATCH

operation. Because of the way

PATCH

methods work, properties provided in the payload are replaced, not merged!

This behavior means you MUST provide in the

PATCH

request's payload all the columns you would like the Table to have after the operation, in their entirety and not just the ones you have modified. The easiest way to do this is by using the corresponding

GET

request first, making the necessary changes, and sending that as the request payload for your update operation.

Special attention needs to be paid to existing Column's oid property - that's the way Sisense recognizes an existing Column vs a new one. If the oid property is missing, a new Column will be created!

For example, the Table created earlier in this guide contains 2 Columns:

{
    // ... rest of table object ...
    "columns": [
        {
            "id": "OriginalColumn1",
            "name": "MyColumn1",
            "type": 8
        },
        {
            "id": "OriginalColumn2",
            "name": "MyColumn2",
            "type": 18
        }
    ]
    // ... rest of table object ...
}

Assume you wish to:

Add a new custom Column
Hide the first Column
Delete the second Column

The payload for the

PATCH

request would be:

{
    // ... rest of table object ...
    "columns": [
        {
            "oid": "<column oid>", // as retrieved from the GET request
            "id": "OriginalColumn1",
            "name": "MyColumn1",
            "type": 8,
            "hidden": true
        },
        {
            "id":"MyCustomColumn",
            "name": "MyCustomColumn",
            "type": 18,
            "expression": "select [MyColumn1] + ' is cool!'",
            "isCustom": true
        }
    ]
    // ... rest of table object ...
}

And will result in the following:

The new custom Column does not yet have an OID, and will be created
The first Column, with all the properties it had including oid will be recognized as an existing Column to update. It will be updated so that hidden is now true
The last column was omitted from the payload and will thus be deleted

# Updating Relations

You can update a single Relation object to:

Link new Columns to an existing Relation, such as when adding a table that uses the same key
Remove a Column from a relation, such as when deleting a Table or a Column
Change which of a Table's Columns is used to link it to other Tables

Note that any Relation object must have at least 2 Columns specified at any time, or the Schema becomes invalid.

In a similar manner to the process for updating a Table's Columns described above, when updating a Relation object you must provide a payload containing all columns that should be kept or added, and not just the ones you have modified. The easiest way to do this is by using the corresponding

GET

request first, making the necessary changes, and sending that as the request payload for your update operation. Any Column you omit from the columns property in the payload will be removed from the Relation and become unlinked.

Use the

PATCH /datamodels/{DatamodelId}/relations/{RelationId}

endpoint, including your Datamodel's oid instead of {DatamodelId} and your Relation's oid instead of {RelationId}, with the same payload structure as a

POST

{
    "columns": [ 
        {
            "dataset": "<dataset 1 oid>",
            "table": "<table 1 oid>",
            "column": "<column 1 oid>"
        },
        {
            "dataset": "<dataset 2 oid>",
            "table": "<table 2 oid>",
            "column": "<column 2 oid>"
        }
    ]
}

# Deleting Resources

All endpoints support deleting the resources they represent, by oid, using the

DELETE

HTTP method, but not all operations are achieved the same way.

# Deleting an entire Datamodel

Use the

DELETE /datamodels/{DatamodelId}

endpoint, including your Datamodel's oid instead of {DatamodelId}, to delete an entire Datamodel.

Note that this will remove the Datamodel from the system entirely, and it will no longer be possible to query it or use it for Dashboards.

Additionally, Dashboards using the Datamodel are not removed automatically and need to be migrated to a new datasource or removed explicitly.

# Deleting a Dataset

Use the

DELETE /datamodels/{DatamodelId}/datasets/{DatasetId}

endpoint, including your Datamodel's oid instead of {DatamodelId} and your Dataset's oid instead of {DatasetId}, to delete a specific Dataset from your Datamodel.

Note that while the Dataset and all Tables within it will be deleted, you must remove any references to them from Relations as well as any custom SQL expressions.

This operation may impact Dashboards that use any of the Tables removed by this operation, and they will need to be updated accordingly.

# Deleting a Table

Use the

DELETE /datamodels/{DatamodelId}/datasets/{DatasetId}/tables/{TableId}

endpoint, including your Datamodel's oid instead of {DatamodelId}, your Dataset's oid instead of {DatasetId}, and your Table's oid instead of {TableId}.

Note that you must remove any references to the deleted Table from Relations as well as any custom SQL expressions.

This operation may impact Dashboards that use the Table removed by this operation, and they will need to be updated accordingly.

# Deleting a Column

As Columns are not represented by an endpoint of their own, removing Columns is achieved by updating the Table object, as described in Updating (or removing) a Table's Columns.

# Deleting Relations

To delete an entire Relation, removing the link between all participating tables, Use the

DELETE /datamodels/{DatamodelId}/relations/{RelationId}

endpoint, including your Datamodel's oid instead of {DatamodelId} and your Relation's oid instead of {RelationId}.

To remove one or more Columns from a Relation without deleting the rest, use the process described in Updating Relations.

# Building/Publishing a Datamodel

To make a Datamodel queryable and its data available to users, it needs to be built or published.

extract type models (formerly called "Elasticube") are built, extracting data from all included sources into the proprietary Sisense columnar database (Elasticube)
live type models aren't built, instead the model is "published", setting the structure of the queryable model

In both cases, the action is performed via the /api/v2/builds endpoint.

# API Structure

The /builds endpoint is constructed around a "virtual" resource, the buildTask. This entity represents a request for Sisense to build or publish a model. Creating this entity queues up a task, generating an ID that can be used for checking the task's status (by retrieving it) and for cancelling the task (by deleting it),

Eventually, these entities are deleted and do not persist.

The endpoint supports the following actions:

GET /builds
List all Build tasks
POST /builds
Start a Build/Publish
DELETE /builds
Cancel/Stop build task(s) for a specific Datamodel
GET /builds/{buildId}
Get a specific Build Task by ID - used to inspect the task's status
DELETE /builds/{buildId}
Cancel/Stop a specific Build Task by ID

# Building Extract Datamodels

To start building a Datamodel, use the

POST /builds

endpoint. It requires a payload, for which you will need 2-3 parameters:

The oid of the Datamodel you wish to build.
The desired build type:
- full to build the entire Datamodel from scratch
- by_table to build the Datamodel according to each table's settings, such as accumulative table builds
- schema_changes to only build the parts of the Datamodel that have been modified
Optionally, a rowLimit if you wish to run a sample build which imports a set number of rows from datasources.
The optional schemaOrigin field determines which version of the schema will be built:
Note: this parameter is available starting from L2021.3
- latest will build the Datamodel as seen in the Data page, including all changes to it since the last build (default)
- running will build the last succesfully built schema - use this to avoid exposing incomplete changes to the schema.

The payload has the following structure:

{
  "datamodelId": "3fa85f64-5717-4562-b3fc-2c963f66afa6",
  "buildType": "full",
  "schemaOrigin": "latest",
  "rowLimit": 0
}

Once executed, you will receive a response which includes the build task's oid. At this stage, your task is queued up and Sisense will start the build process as soon as possible. Receiving this response only confirms that Sisense has accepted the task, and does not indicate that the build has finished or even started.

To check the status of the task, use the

GET /builds/{buildId}

endpoint providing the build task's oid as {buildId}. The response body will contain a status field, indicating the task's current status.

You can create a script that "polls" this endpoint at a preset interval in order to wait for the build task to complete. Keep in mind that a build can last anywhere from a few minutes to several hours, depending on how much data needs to be imported and how many custom tables and columns need to be calculated from it, amongst other factors.

# Publishing Live Datamodels

Publishing a live type model is very similar to the build process described above.

To publish a live type mode, simply set the buildType property to publish, like so:

{
    "datamodelId": "3fa85f64-5717-4562-b3fc-2c963f66afa6",
    "buildType": "publish"
}

Additionally, note that the publish operation generally takes much less time than a build, as it only updates the queryable model. However, this task is still performed asynchronously and therefore a response from

POST /builds

does not indicate that the publish task is done - only that it was accepted by Sisense and entered the build queue. Use the same method described above to check the task's status and determine when the Datamodel was published successfully.

# Using the Datamodel API

# Datamodel API Webinar

# Resources

# Datamodel

# Dataset

# Table

# Column

# Relation

# Endpoints

# Creating a new Datamodel

# Creating a blank Datamodel object

# Creating a Dataset

# Custom Datasets

# Creating a Table

# Creating custom Columns

# Creating custom Tables

# Creating a Relation

# Modifying a Datamodel

# Changing a Dataset's connection

# Updating a Table

# Updating (or removing) a Table's Columns

# Updating Relations

# Deleting Resources

# Deleting an entire Datamodel

# Deleting a Dataset

# Deleting a Table

# Deleting a Column

# Deleting Relations

# Building/Publishing a Datamodel

# API Structure

# Building Extract Datamodels

# Publishing Live Datamodels

SISENSE

SUPPORT

RESOURCES