# 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:

  1. A normal Column that represents a column or field in your origina data source
  2. A custom (calculated) column, defined by a SQL expression in its expression property
  3. 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/queriable 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:

  1. A host address, containing the protocol, IP/DNS and port. For example: https://reporting.myapp.com:8081
  2. An API base path, containing the API version (this API exists in version 2.0): /api/v2
  3. 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, updaing 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 a POST HTTP method on the /api/v2/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 a POST HTTP method on the /api/v2/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 a POST HTTP method on the /api/v2/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:

  1. The property isCustom is set to true to define this Column as a custom Column
  2. 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

# 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:

  1. Table's type is set to custom instead of base
  2. An expression property is provided
  3. 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 a POST HTTP method on the /api/v2/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 HTTP method on the /api/v2/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 HTTP method on the /api/v2/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.

As 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 neccessary 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:

  1. Add a new custom Column
  2. Hide the first Column
  3. 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:

  1. The new custom Column does not yet have an OID, and will be created
  2. 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
  3. 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 neccessary 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 a PATCH HTTP method on the /api/v2/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 a DELETE HTTP method on the /api/v2/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 a DELETE HTTP method on the /api/v2/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 a DELETE HTTP method on the /api/v2/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 a DELETE HTTP method on the /api/v2/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.

# Additional References

LEARN

COMMUNITY

ABOUT US

Copyright © 2020 Sisense Inc. All rights reserved.

Submit Feedback Legal Privacy