The Action (table definition) object represents the tables you import into the ElastiCube Manager. In this object, you define how the tables are to be displayed in the ElastiCube Manager, which columns are included and excluded, and other information such as how to and where to fetch the data from, such as endpoints, query string, headers etc.

For example, the following code sample imports a table called Audits and excludes two columns, events.recipients and events.attachments while transforming the values of columns events.value and events.previous_value to strings. 

"Name": "Audits",
      "Public": "True",
      "Schema": "Http",
      "Method": "GET",
      "Base": "[@Settings.Parameters.uri]",
      "Path": "api/v2/tickets/{0}/audits.json",
      "Headers": {
        "Authorization": "Basic YWxleGV5LmF2cmFtZW5rb0BzaXNlbnNlLmNvbS90b2tlbjpTY09TbzNkR0k0UFJKbldQakZ2dTZxb1lqR3ZEWURRR1dFNnlkYlhG"
      },
		"AccumulativeSettings": {
		"DefaultValue": "20160810",
		"DateTimeFormat": "yyyyMMdd"
	  },
      "PathParameters": [ "[@[email protected]~Doc.id]" ],
      "QueryParameters": {
			"api-key": "47fh3hd4n4jc4nnjvjvjfn99nnr",
			"Begin-date": "[@accumulative]"
		},
      "Body": {},
      "DataFormat": "Json",
      "DataPath": "audits",
      "Transformation": {
        "ExcludeList": [
          "events.recipients",
          "events.attachments"
        ],
        "TransformTypes": [
          {
            "Name": "events.value",
            "Type": "String"
          },
          {
            "Name": "events.previous_value",
            "Type": "String"
          }
        ]
      }
    },

It also contains a set of transformation methods that modify how the data is returned in a table.

JSON Format

 

Name	Type	Mandatory	Comment
Name	String	Yes	The name of a table. This string cannot be longer than 25 characters. Note: Underscores in the name are not supported.
Public	Boolean	No	Indicates if the table should be publicly visible. True by default.
Schema	String	Yes	Protocol to use for communicating with a data source endpoint. "Http" or "Https".
Method	String	Yes	HTTP request methods to use. "GET" or "POST".
Base	String	Yes	Data source's REST API endpoint URL.
Path	String	Yes	URI of a resource that the table represents. This value is defined relative to the Base value.
Headers	Object	Yes	String -> String key-value pairs required by the REST service provider. These key-value pairs are typically information you need to provide the RESTful service, but do not want to expose in the URL of the request, for example authentication details.
AccumulativeSettings	Object	No	This object contains keys whose values define the starting point for your accumulative builds. Accumulative Builds are supported only for connectors that support them and for integer and datetime fields. Within the AccumaltiveSettings object, there are two keys that you can define. DefaultValue: (Mandatory) Defines the starting point for the build. The first time you must define this value, and for each subsequent build, Sisense updates this value automatically. This can be an integer or datetime value. DateTimeFormat:(Optional) When the DefaultValue is a datetime field, this key defines the format for the DefaultValue. Click here for formatting examples. This object should only be defined when using the [@accumulative] InMemory parameter within the same table. For each table that you want to support accumulative builds, you must include the [@accumulative] reference and the AccumulativeSettings object.   For more information about InMemory parameters, click here.
PathParameters	Array	Yes	List of strings of path parameters. For more information, click here.
QueryParameters	Object	No	String -> String key-value pairs for specifying query parameters in URL query string.
ArrayQueryParameters	Object	No	String -> array key-value pairs, used for handling arrays in URL query string.
Body	Object	No	HTTP request body. Sisense supports using nested objects with the Body object for HTTP Post requests. For example: "Body": {  "credentials": {   "username": "MyUser",   "password": "MyPassword",   "company": "123456"     } } Some data sources require parameters represented as JSON objects, such as credentials stored in objects as shown in the example above.
BodyXml	String	No	When the data source expects XML, set the content as the value of BodyXml and leave the Body object empty. As the data source provider expects XML, the value of BodyXml should be in XML format, not JSON. The string should be inside double qoutes “ “ as displayed in the example below: "BodyXml": "<xml><credentials><username>MyUser</username></credentials></xml>""
DataFormat	String	No	Format of a source data. The only supported value is "Json".
AllowDuplicateElementNames	Boolean	No	Some REST sources return duplicate elements and this may trigger an error. If you receive a Duplicate Name error, set include this value and set it as true to prevent the error and return the full response as expected. Do not include this key:value pair unless you receive the Duplicate Name error as it can decrease performance.
ValidateJsonPropertyName	Boolean	No	Set to true if you receive the error message below. This is caused by unsupported element names according to the JSON standard, such as elements that begin with '.' or '$'. When you set this key to true, Sisense will convert these symbols to underscores, '_' when importing them into Sisense.
Transformation	Transformation	No	Transformation object. For more information, click here.
PagingConfig	PagingConfig	No	Data pagination configuration.
ResponseFormat	String	No	Defines the type of response to be returned from the data source provider to Sisense. Sisense currently accepts JSON (default), CSV, and XML.
Cookies	String	No	When a data source provides cookies in a response to your initial request, this value defines where the cookies are to be sent back to the provider. For example: "Cookies": "[@[email protected]~Cookies]", In this example, the data source returns the cookie as the value of Cookies inside the Auth object. This value can then be sent to the data source provider in future requests.
CsvHeader	Boolean	No	When True, returns the Header of content of a CSV.
DataPath	String	Yes	The name of the top level key containing the data set. For more information see DataPath.

 

Example

{
  "Name": "Employees",
  "Public": "True",
  "Schema": "Https",
  "Method": "GET",
  "Base": "https://www.datasource.com",
  "Path": "api/v2/employees.json",
  "Headers": {
    "Authorization": "Basic YWxleGV5LmF2cmFtZW6zb0BzavNlbnNlLmNdbS90b2tlbjpTY09TbzNkR0k0UFJKbldQakZ2dTZxb1lqR3ZEWURRR1dFNnlkYlhG"
  },
  "PathParameters": [],
  "QueryParameters": {},
  "Body": {},
  "DataFormat": "Json",
  "DataPath": "employees",
  "Transformation": {
    "ExcludeList": [
      "Address",
      "City"
    ],
    "TransformTypes": [
      {
        "Name": "HireDate",
        "Type": "String"
      }
    ]
  }
}

DataPath

The framework expects a result set to have the following form:

{
  "error": {
    "code": 5002,
    "message": "Invalid token.",
    "status": 401,
    "httpMessage": "Unauthorized"
  }
}

Usage in the config.json:

{
	"DataPath":"error",
}

[
  "someElementInArray": {
  }
]

{
	"DataPath":"results",
}

Transformation

Transformation objects are used to perform changes on a table, which include changing a property, changing a column's type, or even defining a relation with another table.

The follow is a list of Transformation objects you can leverage through the Native REST API: 

   "IncludeList": [
      "location.latitude",
      "location.longitude",
      "location.name",
      "location.id"
    ],

TransformProperties

TransformProperties can be used to give a different name to a column.

JSON Format

Example

{
  "TransformProperties": [{
    "SourceName": "usr_id",
    "TargetName": "id"
  }, {
    "SourceName": "lstnm",
    "TargetName": "Last Name",
  }]
}

TransformTypes

The TransformTypes transformer can be used to convert a column's type. For example, if you have a result set with a column "created_at" that contains time stamps in a raw string format, using TransformTypes, you can transform this column to a DateTime type. At runtime the transformer attempts to perform a cast to a target type. In case of failure, the type remains unchanged.

JSON Format

Example

This example was taken from the complete Zendesk example, which you can view here. 

{
  "TransformTypes": [{
    "Name": "created_at",
    "Type": "DateTime"
  }, {
    "Name": "updated_at",
	"Type": "String"
  }]
}

ExcludeList

The ExcludeList transformer can be used to exclude columns from a table. The transformer is defined as an array of strings.

JSON Format

Array of strings that defines the Column names to be excluded.

Example

{
  "ExcludeList": [
    "changed_at",
    "events.complex_field"
  ]
}

LinkedTables

The LinkedTables transformer is used to define a relation with another table.

A result set can contain values that can be used to access other resources. For example, if an endpoint /api/persons.json contains a list of persons and each person entry contains a "person_id", using the person_id, you can query a different endpoint, /api/person/{person_id}.json containing more detailed information.

This essentially produces another logical table Persons_Information. The LinkedTables transformer can be used to define a hidden table Persons_Information like other regular tables and then set a reference to this table inside Persons LinkedTables transformer.

JSON Format

LinkedTables

Array of LinkedTable objects.

LinkedTable

Example

{
  "LinkedTables": [{
    "Name": "Comments",
    "LinkedTableReference": "[@[email protected]~Doc.All]"
  }, {
    "Name": "Audits",
    "LinkedTableReference": "[@[email protected]~Doc.All]"
  }]
}

Pagination

Data sources can contain a lot of data, which means that sending everything in a single large HTTP response may not be efficient or practical. A solution to this problem is to transfer data in fixed size pages. Pagination allows you to modify how large sets of data are split into individual pages.

You can implement pagination when there is more data to load than the data source provides in a single response.

Each data source has its own mechanisms for pagination. In the PagingConfig object, you specify how your connector pages through data sets that you want to import into an ElastiCube.

The Custom REST connector supports the following paging methods.

NextToken: This method passes a token when additional can be returned.

NextURL: This method  uses a value that contains the URL for the next set of records.

FirstPage/NextPage: This method defines the first page of the data set and returns the next page based on predefined formulas. This method is useful for files such as CSV where no URLs are provided.

NextPage/In Memory Parameters: This method is similar to the previous method differing in that  you must return data sent by the data source after modifying it to return additional pages.

JSON Format

The table below provides a list and descriptions of the relevant keys and values that can be added to the PagingConfig object for paging results.

Example

An endpoint returns a list of 200 person entries per page. The result in JSON contains a URL to the next page.

{
  "PagingConfig": {
    "PageSize": "200",
    "NextPageURL": "https://www.datasource.com/api/persons/???",
    "PagingMethod": "URL"
  }
}

If NextPageURL is undefined then the result set is treated as the final page and no more data is fetched.

NextToken

The NextToken method uses a token that is returned from the data source and referred to  in your following requests. When the data source provider does not return a token, this indicates the end of the records.

For example, An endpoint returns a list of 200 person entries per page. The result in JSON contains a token that has to be added to or referenced in the query string to return the next page.

The Source URL is the same. The first request URL is https://www.datasource.com/api/persons.json while the second request URL has to be: https://www.datasource.com/api/persons.json?nextPageToken=QGDDGAETBZ

Example  

An endpoint returns a list of 200 person entries per page. The result in JSON contains a token that has to be added to the query string to return the next page. The Source URL is the same. The first request URL is https://www.datasource.com/api/persons.json while the second request URL has to be: https://www.datasource.com/api/persons.json?nextPageToken=QGDDGAETBZ

// example response
{
  "nextPageToken" : "GIYDAOBNGEYS2MBWKQYDAORQGA5DAMBOGAYDAKZQGAYDALBRGA3TQ===",
  "results" : [{
    ...
  }]
}

The following is an example of a table which is using a nextPageToken to page a data set.

{
      "Name": "Users",
      "Public": "True",
      "Schema": "Https",
      "Method": "GET",
      "Base": "[@Settings.Parameters.uri]",
      "Path": "",
	 "Headers": {
		"Authorization": "[@Settings.Parameters.Authorization]"
	},
      "PathParameters": [],
      "QueryParameters": {},
	"PagingConfig": {
		"PagingMethod": "URL",
            "PageSize": "100",
            "KeepUrl": "true",
            "NextToken": "nextPageToken",
            "QueryParameters": {
                 "nextPageToken": "[@[email protected]]"
            }
	  },  	      
	  "Body": {},
        "DataPath": "results"
    }
}

In the example above, KeepUrl was set to ‘true’ to continue to use the previous URL. The QueryParameters object is set to take the the nextPageToken to be used when accessing the next set of data. The value of the nextPageToken key is a Sisense token, [@[email protected]], which refers to the next page token value in a server response document.

When "nextPageToken" is empty from a server response, this indicates a final page and the end of the data. Another indicator that the end of the data was met is an empty response from the service. 

FirstPage and NextPage

The third method is to define the first page of the data set and the next page through Sisense In Memory parameters as the values of the FirstPage and NextPage keys.

When returning data where there is no nextPageUrl or nextPageToken provided in the response, such as CSV files, you can calculate dynamically the next page number using a predefined formula.

It is important to have a number of a first page as this page is used to calculate next one.

Example 

{
      "Name": "GeoReport",
      "Public": "True",
      "Schema": "Https",
      "Method": "GET",
      "Base": "[@Settings.Parameters.uri]",
      "Path": "reporting/v1/std/geo",
	"Headers": {},
      "PathParameters": [],
      "QueryParameters": {
            "page_offset": "[@Settings.Parameters.firstPage]"
       },
	  "PagingConfig": {
		"PagingMethod": "URL",
            "PageSize": "100",
            "KeepUrl": "true",
            "FirstPage": "[@Settings.Parameters.firstPage]",
	      "NextPage": "${[@currentpage] + 1}",
            "QueryParameters": {
                 "page_offset": "[@nextpage]"
            }
	  },  	      
	  "Body": {},
        "Cookies": "[@[email protected]~Cookies]",
        "ResponseFormat":"Csv",
        "CvsHeader":"true", 
        "DataPath": ""
    }
}

// example response
{
  "page" : "1",
  "calls" : [{
    ...
  }]
}

{
      "Name": "Calls",
      "Public": "True",
      "Schema": "Https",
      "Method": "GET",
      "Base": "[@Settings.Parameters.uri]",
      "Path": "",
	"Headers": {
            "Authorization": "[@Settings.Parameters.Authorization]"
      },
      "PathParameters": [],
      "QueryParameters": {
            "api-key": "[@Settings.Parameters.apiKey]"
       },
	  "PagingConfig": {
		"PagingMethod": "URL",
            "PageSize": "100",
            "KeepUrl": "true",
	      "NextPage": "${[@[email protected]] + 1}",
            "QueryParameters": {
                 "page": "[@nextpage]",
                 "api-key": "[@Settings.Parameters.apiKey]"
            }
	  },  	      
	  "Body": {},
        "DataPath": "calls"
    }
}

Accessing Array Items

This functionality is available from Sisense V7.0 and later. 

In some cases, you may need to access the first or last items of an array returned by your REST source, for example, if you are returning multiple pages and you want to know the ID of the last item in an array. 

The example below shows how you can return the last item of an array: 

@[email protected]#lastItem.id

In this example, the ID of the last item in the result.emailClick array will be returned. 

JSON Traversing

There are situations when a result set has a nested structure and you want to access some of the nested elements.  Transformation objects support "dot notation" naming for traversing, for example:

{
  "results": [{
    "name": "John",
    "last_name": "Appleseed",
    "locations": {
      "work": {
        "address": "9158 Essex Court Beckley, WV 25801",
        "phone": "+1-202-555-0120"
      },
      "home": {
        "address": "2977 Edgewood Drive Dalton, GA 30721",
        "phone": "+1-202-555-0171"
      }
    }
  }]
}

To rename the work phone field into a mobile number, you can use TransformProperties as shown below:

{
  "TransformProperties": [{
    "SourceName": "locations.work.phone",
    "TargetName": "Mobile number"
  }]
}