Dataset API

Prerequisites:

Table of contents:

  1. Access Admin > System > API Toolkit
  2. Configure Dataset Info
    1. Get Dataset Info
    2. Create Dataset
    3. Update Dataset Info
      1. Validate Dataset
      2. Delete Dataset Data
      3. Get Dataset Access Map (User Map)
      4. Set Dataset Access Map (User Map)
      5. Enable Dataset
      6. Disable Dataset
      7. Update Dataset Data
    4. Delete Dataset
  3. Configure Dataset Data
    1. Get Dataset Data
    2. Filtering Dataset Data
      1. Limit, Offset, Amount
      2. Contains
      3. Equals, Is Greater Than, Is Less Than
      4. Operator AND
      5. Operator OR

1. Access Admin > System > API Toolkit

2. Configure Dataset Info

2.1. Get Dataset Info

  1. Item: dataset
  2. Methods: GET
  3. Enter ID of the Dataset
    • If no ID is provided, the API will return the data of all Datasets
  4. Enter an API Token
  5. [Run request]
  6. The returned object contains Dataset's info
Example Response 
{
            "id": "63",
            "source_dataset_id": "0",
            "source_dataset_filter_id": "0",
            "name": "Suppliers Analysis",
            "description": "Suppliers Analysis",
            "measurement_interval": "3",
            "data_storage": "1",
            "category": "65",
            "data_collection_trigger": "1",
            "keep_history": "yes",
            "measurement_time_source": "Measurement time command",
            "can_historical_instances_be_backfilled": "yes",
            "weekly_data_for": "Last day of Week",
            "custom_effective_date": "19",
            "data_source": "57_sql",
            "data_fetch_command": "SELECT * from dataset_61\nWHERE date = :measurement_time",
            "omit_partial_periods_ind": "no",
            "generate_empty_instance_ind": "skip generation",
            "has_access_map_ind": "user has access to all rows",
            "instances_to_keep": "1095"
        }

Fields Description

Field Name Value Type Description
can_historical_instances_be_backfilled string Whether or not multiple instances of the Snapshot Dataset can be computed at run time.
category string The ID of Dataset Category.
data_collection_trigger string The ID of the Data Collection Trigger.
data_fetch_command string The command that is used to fetch the Dataset data.
data_source string The source of the Dataset data.
data_storage string The ID of the Data Storage.
description string The description of the Dataset.
has_access_map_ind string Whether the user has access to all Dataset rows or an access map is applied.
id string The ID of the Dataset.
instances_to_keep string The number of Dataset Shapshot instances to keep.
keep_history string Whether the Dataset is a Snapshot Dataset.
measurement_interval string The ID of the measurement interval.
name string The name of the Dataset
source_dataset_filter_id string The ID of the Dataset's filter from which this Dataset has been sourced.
source_dataset_id string The ID of the Dataset from which this Dataset has been sourced.

2.2. Create Dataset

  1. Item: dataset
  2. Methods: POST
  3. Enter ID of an existing Dataset that you want to use as a model for a new Dataset
    • After you enter the Dataset ID, the Request field will display the settings for that Dataset
  4. Select JSON request and modify the new Dataset's data
  5. Enter an API Token
  6. [Run request]
  7. The returned object contains the new Dataset's info
    • Note that the new Dataset's ID is auto-created

2.3. Update Dataset Info

  1. Item: dataset
  2. Methods: PUT
  3. Enter ID of the Dataset you want to update
  4. Select JSON request and modify the Dataset's data
  5. Enter an API Token
  6. [Run request]

2.4. Validate Dataset

  1. Item: dataset
  2. Methods: PUT
  3. Enter ID of the Dataset you want to validate
  4. Actions: Validate
  5. Enter an API Token
  6. [Run request]
  7. If the Dataset is validated successfully, a corresponding message is returned

2.5. Delete Dataset Data

  1. Item: dataset
  2. Methods: PUT
  3. Enter ID of the Dataset which data you want to delete
  4. Actions: Delete data
  5. Select JSON request and enter the time interval for which you want to delete Dataset data in the following format:
{
    "call": "delete_data",
    "delete_measurements_from": "<Date and time from which the Dataset data is deleted>",
    "delete_measurements_through": "<Date and time until which the Dataset data is deleted>"
}
  1. Enter an API Token
  2. [Run request]
  3. If the Dataset data is deleted successfully, a corresponding message is returned

2.6. Get Dataset Access Map (User Map)

  1. Item: dataset
  2. Methods: PUT
  3. Enter ID of the Dataset for which you want to get an Access Map
  4. Actions: Get Access Map
  5. Enter an API Token
  6. [Run request]
  7. If the Dataset's Access Map data is fetched successfully, its data is returned

2.7. Set Dataset Access Map (User Map)

  1. Item: dataset
  2. Methods: PUT
  3. Enter ID of the Dataset for which you want to get an Access Map
  4. Actions: Set Access Map
  5. Select JSON request and provide JSON mapping Dataset parameters with User Map parameters
  6. Enter an API Token
  7. [Run request]
  8. If the Dataset's Access Map data is set successfully, a corresponding message is returned

2.8. Enable Dataset

  1. Item: dataset
  2. Methods: PUT
  3. Enter ID of the Dataset you want to enable
  4. Actions: Enable
  5. Enter an API Token
  6. [Run request]
  7. If the Dataset is enabled successfully, a corresponding message is returned

2.9. Disable Dataset

  1. Item: dataset
  2. Methods: PUT
  3. Enter ID of the Dataset you want to disable
  4. Actions: Disable
  5. Enter an API Token
  6. [Run request]
  7. If the Dataset is disabled successfully, a corresponding message is returned

2.10. Update Dataset Data

  1. Item: dataset
  2. Methods: PUT
  3. Enter ID of the Dataset which data you want to update
    • This action is an equivalent of [Update Dataset] from the Dataset Editor
  4. Actions: Update Dataset
  5. Enter an API Token
  6. [Run request]
  7. If the Dataset has been updated successfully, a corresponding message is returned

2.11. Delete Dataset

  1. Item: dataset
  2. Methods: DELETE
  3. Enter ID of the Dataset you want to delete
  4. Enter an API Token
  5. [Run request]
  6. If the Dataset is deleted successfully, a corresponding message is returned

3. Configure Dataset Data

3.1. Get Dataset Data

  1. Item: dataset_data
  2. Methods: GET
  3. dataset: Enter ID of the Dataset
  4. view: Optionally, enter ID of the Dataset View
  5. Optionally, enter measurement_time for which you want to retrieve Dataset data
  6. Enter an API Token
  7. [Run request]
  8. The returned object contains Dataset's info
  9. See Example Response  and Fields Description for details
Example Response

This example demonstrates an object which represents a single element.

{
	"data": 
	[
		{"Name":"User8","Country":"Algeria","Customer ID":"3","Items Purchased":"4","Date":null},
		{"Name":"User7","Country":"Algeria","Customer ID":"3","Items Purchased":"10","Date":null},
		{"Name":"User10","Country":"Italy","Customer ID":"2","Items Purchased":"12","Date":null}, 
		{"Name":"User6","Country":"Algeria","Customer ID":"4","Items Purchased":"13","Date":null}
	]
	"metadata": 
	[
		{"name":"Name","type":"text"},
		{"name":"Country","type":"text"},
		{"name":"Customer ID","type":"numeric"}, 
		{"name":"Items Purchased","type":"numeric"},
		{"name":"Date","type":"datetime"}
	],
	"amount":6
}
Click to copy

Fields Description

Field Name Value Type Description
data array An array of objects with returned Dataset data.
metadata array The metadata containing column names and types.
amount integer The total number of Dataset rows. Is displayed only if the amount parameter is set to “Y”

3.2. Filtering Dataset Data

This functionality is not available in the API Toolkit and can be performed via direct API calls, such as described in this section.

3.2.1. Limit, Offset, Amount

This API call demonstrates how to apply filtering and sorting by using additional query parameters:

$.ajax({
  	"url":  "/api/dataset_data?dataset=<Dataset ID>&limit=<Number of returned rows>&offset=<Number of rows to skip>&amount=<Y/N>", 
  	"type":  "POST", 
  	data:  
    {
      "sort":  
             [
               {"field":"<Field 1 Name>","dir":"<ASC/DESC>"},
               {"field":"<Field 2 Name>","dir":"<ASC/DESC>"}
             ]
    },
  	"headers": {"Accept":"application/json"}
}).done(response=>console.log(response))

Assign dataset the value of Dataset ID and enter value of the following query parameters to filter out the Dataset data:

  • limit - the number of returned rows,
  • offset - the number of rows to be skipped before returning any rows,
  • amount -  whether to display the total amount of rows in the Dataset.

To sort out the returned data, add an object to the sort array providing two properties' values:

  • field: The name of the column by which the Dataset data is filtered.
  • dir: The direction in which the Dataset data is sorted can take one of the two values:
    • ASC: Ascending
    • DESC: Descending

Example request:

$.ajax({
  	"url":  "/api/dataset_data?dataset=568&limit=4&offset=1&amount=Y", 
  	"type":  "POST", 
  	data:  
    {
      "sort":  
             [
               {"field":"items_purchased","dir":"DESC"},
               {"field":  "country","dir":"ASC"}
             ]
    },
  	"headers": {"Accept":"application/json"}
}).done(response=>console.log(response))
Click to copy

3.2.2. Contains

This API call demonstrates how to filter out the Dataset data based on text entry and return only columns that contain entered words or letters.

$.ajax({
    "url": '/api/dataset_data?dataset=<Dataset ID>',
    "headers": { "Content-type": "application/json", "Accept": "application/json" },
    "type": "POST",
    "data": JSON.stringify({
        "filters": [
            {
                "column_name": "<Column Name>",
                "condition": "contains",
                "data": "<Data to filter Columns>"
            }
        ]
    })
});

Assign dataset the value of Dataset ID and enter values of the following filter parameters:

  • column_name: The filtered column name.
  • data: A text entry by which the column data is filtered.
    • The text entry can contain one or more symbols or words.

Example request:

$.ajax({
    "url": '/api/dataset_data?dataset=568',
    "headers": { "Content-type": "application/json", "Accept": "application/json" },
    "type": "POST",
    "data": JSON.stringify({
        "filters": [
            {
                "column_name": "country",
                "condition": "contains",
                "data": "Italy"
            }
        ]
    })
});

3.2.3. Equals, Is Greater Than, Is Less Than

This API call allows to filter the numeric data of the Dataset:

$.ajax({
    "url": '/api/dataset_data?dataset=<Dataset ID>',
    "headers": { "Content-type": "application/json", "Accept": "application/json" },
    "type": "POST",
    "data": JSON.stringify({
        filters: [
            {
                column_name: "<Column Name>",
                condition: "<Operator>",
                data: "<Numeric Value>"
            }
        ]
    })
});

Assign dataset the value of Dataset ID and enter the values of the following filter parameters:

  • column_name: The filtered column name.
  • condition: Select one of the three conditions:
    • equals: Returns rows where the column value is equal to the user input
    • is greater than: Returns rows where the column value is greater than the value of user input
    • is less than: Returns rows where the column value is less than the value of user input
  • data: A numeric value

Request example:

$.ajax({
    "url": '/api/dataset_data?dataset=568',
    "headers": { "Content-type": "application/json", "Accept": "application/json" },
    "type": "POST",
    "data": JSON.stringify({
        filters: [
            {
                column_name: "items_purchased",
                condition: "is less than",
                data: "12"
            }
        ]
    })
});

3.2.4. Operator AND

This API call allows to return Dataset data which corresponds to multiple filter conditions:

$.ajax({
    "url": '/api/dataset_data?dataset=<Dataset ID>',
    "headers": { "Content-type": "application/json", "Accept": "application/json" },
    "type": "POST",
    "data": JSON.stringify({
        filters: [
            {
                group: {
                    operator: "AND",
                    rules: [
                        {
                            column_name: "<Column Name>",
                            condition: "<Condition>",
                            data: "<Data to filter columns>"
                        },
                        {
                            column_name: "<Column Name>",
                            condition: "<Condition>",
                            data: "<Data to filter columns>"
                        }
                    ]
                }
            }
        ],
    })
});

Assign dataset the value of Dataset ID. Any number of rules can be applied, each rule can be either a rule with contains condition or equals, is greater than, is less than.

Example request:

$.ajax({
    "url": '/api/dataset_data?dataset=568',
    "headers": { "Content-type": "application/json", "Accept": "application/json" },
    "type": "POST",
    "data": JSON.stringify({
        filters: [
            {
                group: {
                    operator: "AND",
                    rules: [
                        {
                            column_name: "items_purchased",
                            condition: "is greater than",
                            data: "10"
                        },
                        {
                            column_name: "country",
                            condition: "contains",
                            data: "Italy"
                        }
                    ]
                }
            }
        ],
    })
});

3.2.5. Operator OR

This API call allows to return Dataset data which corresponds to at least one of the provided filter conditions:

$.ajax({
    "url": '/api/dataset_data?dataset=<Dataset ID>',
    "headers": { "Content-type": "application/json", "Accept": "application/json" },
    "type": "POST",
    "data": JSON.stringify({
        filters: [
            {
                group: {
                    operator: "OR",
                    rules: [
                        {
                            column_name: "<Column Name>",
                            condition: "<Condition>",
                            data: "<Data to filter columns>"
                        },
                        {
                            column_name: "<Column Name>",
                            condition: "<Condition>",
                            data: "<Data to filter columns>"
                        }
                    ]
                }
            }
        ],
    })
});

Assign dataset the value of Dataset ID. Any number of rules can be applied, each rule can be either a rule with contains condition or equals, is greater than, is less than.

Example request: 

$.ajax({
    "url": '/api/dataset_data?dataset=568',
    "headers": { "Content-type": "application/json", "Accept": "application/json" },
    "type": "POST",
    "data": JSON.stringify({
        filters: [
            {
                group: {
                    operator: "OR",
                    rules: [
                        {
                            column_name: "items_purchased",
                            condition: "is less than",
                            data: "15"
                        },
                        {
                            column_name: "country",
                            condition: "contains",
                            data: "Canada"
                        },
                    ]
                }
            }
        ],
    })
});