# Custom REST Connector Config: Tables

The Table definition objects represent the tables you import into the Datamodel. 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.

Each table is effectively an API call to one endpoint - for example, when building a connector for Linkedin REST API, the people table will extract data from the /people endpoint of the Linkedin API while the

Properties

Name	Type	Required	Details
`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	See Headers
`AccumulativeSettings`	`Object`	No	See AccumulativeSettings
`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. See Body
`BodyXml`	`String`	No	See BodyXml Details
`DataFormat`	`String`	No	Format of a source data. The only supported value is `"Json"`.
`DataPath`	`string`	Yes	See DataPath Details
`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`	`object`	No	See Transformation Object
`PagingConfig`	`object`	No	Data pagination configuration. See PagingConfig Object
`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	See Cookies Details
`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.

# BodyXML Details

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 quotes "" as displayed in the example below:

"BodyXml": "<xml><credentials><username>MyUser</username></credentials></xml>""

# Cookies Details

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": "[@Credentials.Auth.@~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.

# DataPath Details

Sisense expects a result set to be an object. This field defines which property of the result set contains the actual data.

For example, for the following response object the DataPath should be "employees":

{
   "employees": [
      {
         "name": "John Johnson"
      },
      {
         "name": "Merry Khana"
      }
   ]
}

If the response is a JSON array, like so:

[
   {
      "name": "John Johnson"
   },
   {
      "name": "Merry Khana"
   }
]

Sisense automatically converts the array into a JSON object with the array contained inside a property called results, and so the DataPath should have the value "results"

# Headers Object

Hashtable of string key-value pairs to be sent to the REST service provider as the request headers.

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.

Example

"Headers": {
   "Authorization": "Basic YWxleGV0Imagine0This0Is0A0Token0dFNnlkYlhG"
}

# AccumulativeSettings Object

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.

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. ADD LINK

Properties

Name	Type	Required	Details
`DefaultValue`	`string`	Yes	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`	`string`	No	When the DefaultValue is a datetime field, this key defines the format for the DefaultValue. Click here for formatting examples.

Example

"AccumulativeSettings": {
   "DefaultValue": "20160810",
   "DateTimeFormat": "yyyyMMdd"
}

# Body Object

Sisense supports using nested objects with the Body object for HTTP POST requests.

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.

# Transformation Object

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.

Properties

Name	Type	Required	Details
`TransformProperties`	`object[]`	No	Change column's name and flatten a nested structure. For more information, see TransformProperties Object
`TransformTypes`	`object[]`	No	Change column's type. For more information, see TransformTypes Object.
`ExcludeList`	`string[]`	No	Exclude specific columns from a resulting table. For more information, see ExcludeList Details.
`IncludeList`	`string[]`	No	See IncludeList Details
`LinkedTable`	`object[]`	No	Define a relation with another table. For more information, see LinkedTable Object.

Example

"Transformation": {
   "ExcludeList": ["events.recipients", "events.attachments"],
   "TransformTypes": [
      {
         "Name": "events.value",
         "Type": "String"
      },
      {
         "Name": "events.previous_value",
         "Type": "String"
      }
   ]
}

# IncludeList Details

Specify a list of columns that will be shown while hiding everything else. When you transform your data through the TransformProperties object, IncludeList is executed first, then Transform properties.

It is important to note the order as IncludeList uses first-level properties and this prevents you from transforming data in deeper levels.

For example, if you include the following columns:

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

then attempt to transform latitude, longitude, name, and id, Sisense will not locate them as only location remains.

# ExcludeList Details

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

Example

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

# TransformProperties Object

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

Properties

Name	Type	Required	Details
`SourceName`	`String`	Yes	Name of the column to be changed.
`TargetName`	`String`	Yes	The new name of the column.

Example

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

# TransformTypes Object

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.

Name	Type	Required	Details
`Name`	`String`	Yes	Name of the column to be changed.
`Type`	`String`	Yes	The new column type.

Example

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

# LinkedTables Object

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.

Properties

Name	Type	Required	Details
`Name`	`String`	Yes	Linked table name.
`LinkedTableReference`	`String`	Yes	Reference to a linked table definition.

Example

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

# PagingConfig Object

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.

Properties

Name	Type	Required	Details
`PageSize`	`Integer`	Yes	A numeric value indicating the amount of entries per page.
`KeepUrl`	`Boolean`	No	Indicates if the next request should use the same URL, e.g. ignoring NextPageURL.
`PagingMethod`	`String`	Yes	Name of the method to use. Currently the only supported value is "URL".
`NextPageURL`	`String`	Yes	Name of a key in the returned JSON data associated with the URL of the next page.
`NextToken`	`String`	No	Name of a key in the returned JSON data associated with a token for fetching the next page. Click here for an example.
`Headers`	`Object`	No	String -> String key-value pairs for specifying HTTP request headers.
`PathParameters`	`Array`	No	List of strings of path parameters. For more information, see Configuration.
`QueryParameters`	`Object`	No	String -> String key-value pairs for specifying query parameters in a URL query string for the next page.

# NextPageUrl Method

The NextPageUrl method supports pagination by returning a value in the NextPageUrl that defines the URL for the next set of 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 Method

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.

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 response
{
   "nextPageToken" : "GIYDAOBNGEYS2MBWKQYDAORQGA5DAMBOGAYDAKZQGAYDALBRGA3TQ===",
   "results" : [{
      // ...
   }]
}

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

{
   "PagingConfig": {
      "PagingMethod": "URL",
      "PageSize": "100",
      "KeepUrl": "true",
      "NextToken": "nextPageToken",
      "QueryParameters": {
         "nextPageToken": "[@Tables.Users.@.nextPageToken]"
      }
   }
}

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, [@Tables.Users.@.nextPageToken], 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 Method

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

{
   "QueryParameters": {
         "page_offset": "[@Settings.Parameters.firstPage]"
   },
   "PagingConfig": {
      "PagingMethod": "URL",
      "PageSize": "100",
      "KeepUrl": "true",
      "FirstPage": "[@Settings.Parameters.firstPage]",
      "NextPage": "${[@currentpage] + 1}",
      "QueryParameters": {
         "page_offset": "[@nextpage]"
      }
   }       
}

In this example, the value of KeepUrl is true to continue to use the previous URL. In the QueryParameters object, you should provide the page token in the next HTTP response.

The FirstPage key is set to a parameter or a direct number that represents the number of a first page. The value of the NextPage is a formula you define that calculates the number of the next page of data to be returned. While you can apply different mathematical operations for NextPage, the formula must be enclosed in the figure brackets with a preceding dollar $ sign.

In the example above, the [@currentpage] In Memory parameter stores a current page number. Initially, this value is initialized with FirstPage value. You should use it in a NextPage formula to calculate a next page. The [@nextpage] In Memory parameter returns a next page number after calculating of a predefined formula.

An empty response from the service indicates an end of the data.

# NextPage and In Memory Parameters Method

The fourth method is similar to the previous method. The difference between the third method and this method is that you must return data sent by the data source after modifying it.

Example

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

In the response above, the data source return the current page number.

This information needs to be modified, because if you use it to make a next call, you will return the same page, and the reading will never end.

The example below provides a solution to this case:

{
   "PagingConfig": {
      "PagingMethod": "URL",
      "PageSize": "100",
      "KeepUrl": "true",
      "NextPage": "${[@Tables.Calls.@.page] + 1}",
      "QueryParameters": {
         "page": "[@nextpage]",
         "api-key": "[@Settings.Parameters.apiKey]"
      }
   }        
}

In this example, KeepUrl is set to true to continue to use the previous URL. The QueryParameters key should be provided to use given page token in the next HTTP response.

The NextPage key contains the formula used to calculate a number of a next page. While you can apply different math operations for NextPage, the formula must be enclosed in the figure brackets with a preceding dollar $ sign.

In the example above, the value of NextPage is a token that refers to the page, [@Tables.Calls.@.page] and increases the requested data by one each time.

# Accessing Array Items

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:

@Tables.EmailClicks.@.result.emailClick.#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,

Example

Given this result structure:

{
   "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:

Example

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

# Full Example

The following JSON 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.

{
   "tables": {
      "Name": "Audits",
      "Public": "True",
      "Schema": "Http",
      "Method": "GET",
      "Base": "[@Settings.Parameters.uri]",
      "Path": "api/v2/tickets/{0}/audits.json",
      "Headers": {
         "Authorization": "Basic YWxleGV5Lm0This0Is0A0Token0R1dFNnlkYlhG"
      },
      "AccumulativeSettings": {
         "DefaultValue": "20160810",
         "DateTimeFormat": "yyyyMMdd"
      },
      "PathParameters": ["[@Tables.Tickets.@~Doc.id]"],
      "QueryParameters": {
         "api-key": "....",
         "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"
            }
         ]
      }
   }
}

# Custom REST Connector Config: Tables

# BodyXML Details

# Cookies Details

# DataPath Details

# Headers Object

# AccumulativeSettings Object

# Body Object

# Transformation Object

# IncludeList Details

# ExcludeList Details

# TransformProperties Object

# TransformTypes Object

# LinkedTables Object

# PagingConfig Object

# NextPageUrl Method

# NextToken Method

# FirstPage and NextPage Method

# NextPage and In Memory Parameters Method

# Accessing Array Items

# JSON Traversing

# Full Example

SISENSE

SUPPORT

RESOURCES