on - edited on by
DRay
- Introduction
This post detail how to retrieve data from Sisense Elasticubes to results in JSON format, using the Sisense provided APIs.
The solution consists of 4 stages:
- Creation of one or more ElastiCubes
- Creation of the desired widget/s
- Obtaining the JAQL query which generates the desired result set
- Preparing and sending the JAQL query to the EC (plus any desired filters/manipulations) from any external process and retrieving the result data-set in JSON format.
Phase 1: Creation of ElastiCube
Admin can create one or multiple ElastiCubes, from any number of different data sources, using the Sisense ElastiCube Manager.
Phase 2: Creation of Widget
Designers can create and visualize business questions from the ElastiCube/s via the Sisense editor, to create as many dashboards and widgets as desired.
Phase 3: Obtaining the JAQL Query
Sisense allows to query the ElastiCube/s by means of a JAQL query. This is especially useful to query large quantities of data and handle Big Data results.
There are numerous ways to retrieve the underlying JAQL query of a Sisense widget. After obtaining the JAQL, it can be manipulated and changed per the desired results.
Sisense provides a complete JAQL Reference (https://sisense.dev/guides/querying/jaqlSyntax/#jaql-syntax-reference), which depicts the needed properties and options that can be added to the query to define the result set.
Option A: Obtain the widget JAQL using any HTML5 browser console (or headless browser).
This allows to capture the JAQL query sent from any widget to the ElastiCube. This operation is required only once after designing the desired widget.
Option B: Obtain the desired widget’s JAQL via a REST API call.
Prepare the response to retrieve correct JAQL. Below you can find the code on how to prepare the payload. Use value of parameter [payload] as body to send API request at /api/datasources/{datasource}/jaql
var dashboardId = '5b2bc0c85c523d24c48c3e36';
var widgetId = '5b2bc0ee5c523d24c48c3e4f';
var elastiCubeName = 'Sample Lead Generation';
//Get widget's structure
var widgetStructure = sendAPIRequest('GET', '/api/v1/dashboards/'+dashboardId+'/widgets/'+widgetId, '');
//Prepare response to use it in next API request
var currectStructure = prepareResponse(widgetStructure)
//Send request to server
//var jaqlResponse = sendAPIRequest('POST', '/api/datasources/'+elastiCubeName+'/jaql', currectStructure); /*optional*/
//process request
processRequest(jaqlResponse);
function prepareResponse(a) {
var result = [];
for (i=0; i<a.metadata.panels.length; i++) {
for (j=0; j<a.metadata.panels[i].items.length; j++) {
if (a.metadata.panels[i].items[j]) {
result.push(a.metadata.panels[i].items[j].jaql);
};
}
};
var payload = {metadata: result};
payload = JSON.stringify(payload);
return payload;
};
function sendAPIRequest(method, url, data) {
var response = $.ajax({
method: method,
url: url,
async: false,
data: data,
contentType: 'application/json'}).responseJSON;
return response;
}
function processRequest(jaqlResponse) {
//console.log(jaqlResponse) /*optional if using the Send request to server*/
console.log(currectStructure)
}Phase 4: Sending the JAQL query to the EC (plus any desired filters/manipulations)
Using the JAQL query obtained in phase 3, this JAQL can now be used from any other tool to obtain the same result set as the widget.
The process can be done from any external tool or script, using the REST API “/elasticubes/{elasticube}/jaql” call.
This JAQL can be sent to any Elasticube, and can be modified according to the JAQL reference https://sisense.dev/guides/querying/jaqlSyntax/#jaql-syntax-reference, including additional filters and constraints.
Important Note: The JAQL obtained in phase 3 contains some parameters and fields that might not be needed when sending to the ecube.
The most important change is to remove the quotation marks in the beginning & end of the retrieved JAQL before sending through the POST query.
Also, notice the end point to send has to include the right elasticube name:
/elasticubes/{elasticube}/jaql for the cube "northwind" will become -
Note: In this method, any query, regardless of the widgets existing in the dashboard UI, can be set to the ElastiCube and returns results from any Elasticube.
Example:
Below is a pie chart widget from a cube name "Northwind".
After extracting the JAQL via the panel on the right (see phase 2), the following JAQL was retrieved:
"{
"datasource": {
"title": "NorthWind",
"fullname": "LocalHost/NorthWind",
"id": "aLOCALHOST_aNORTHWIND",
"address": "LocalHost",
"database": "aNorthWind"
},
"metadata": [
{
"jaql": {
"dim": "[Categories.CategoryName]",
"datatype": "text",
"column": "CategoryName",
"table": "Categories",
"title": "CategoryName",
"collapsed": true,
"filter": {
"explicit": false,
"multiSelection": true,
"all": true
}
},
"field": {
"id": "[Categories.CategoryName]",
"index": 0
},
"format": {
"members": {
"Beverages": {
"color": "#ff8c88",
"title": "Beverages",
"inResultset": true
},
"Condiments": {
"color": "#9b9bd7",
"title": "Condiments",
"inResultset": true
},
"Confections": {
"color": "#6EDA55",
"title": "Confections",
"inResultset": true
},
"Dairy Products": {
"color": "#fc7570",
"title": "Dairy Products",
"inResultset": true
},
"Grains/Cereals": {
"color": "#fbb755",
"title": "Grains/Cereals",
"inResultset": true
},
"Meat/Poultry": {
"color": "#218A8C",
"title": "Meat/Poultry",
"inResultset": true
},
"Produce": {
"color": "#06e5ff",
"title": "Produce",
"inResultset": true
},
"Seafood": {
"color": "#b2b2f7",
"title": "Seafood",
"inResultset": true
}
}
},
"handlers": [
{}
]
},
{
"jaql": {
"table": "Order Details",
"column": "Quantity",
"dim": "[Order Details.Quantity]",
"datatype": "numeric",
"agg": "sum",
"title": "Total Quantity"
},
"format": {
"mask": {
"type": "number",
"abbreviations": {
"t": true,
"b": true,
"m": true,
"k": true
},
"separated": true,
"decimals": "auto",
"isdefault": true
}
},
"field": {
"id": "[Order Details.Quantity]_sum",
"index": 1
},
"handlers": [
{}
]
},
{
"jaql": {
"dim": "[Categories.CategoryName]",
"datatype": "text",
"column": "CategoryName",
"table": "Categories",
"title": "CategoryName",
"collapsed": true,
"filter": {
"explicit": false,
"multiSelection": true,
"all": true
}
},
"field": {
"id": "[Categories.CategoryName]",
"index": 0
},
"format": {},
"panel": "scope",
"handlers": []
}
],
"count": 20000,
"offset": 0,
"isMaskedResult": true,
"format": "json"
}"Notice the quotation marks " in red that should be removed prior to sending back to the cube.
When sending the JAQL above (minus the quotations) to the following URL via POST:
The following response is received:
{ "headers": [ "CategoryName", "Total Quantity" ], "metadata": [ { "jaql": { "dim": "[Categories.CategoryName]", "datatype": "text", "column": "CategoryName", "table": "Categories", "title": "CategoryName", "collapsed": true, "filter": { "explicit": false, "multiSelection": true, "all": true, "filterType": "all" } }, "field": { "id": "[Categories.CategoryName]", "index": 0 }, "format": { "members": { "Beverages": { "color": "#ff8c88", "title": "Beverages", "inResultset": true }, "Condiments": { "color": "#9b9bd7", "title": "Condiments", "inResultset": true }, "Confections": { "color": "#6EDA55", "title": "Confections", "inResultset": true }, "Dairy Products": { "color": "#fc7570", "title": "Dairy Products", "inResultset": true }, "Grains/Cereals": { "color": "#fbb755", "title": "Grains/Cereals", "inResultset": true }, "Meat/Poultry": { "color": "#218A8C", "title": "Meat/Poultry", "inResultset": true }, "Produce": { "color": "#06e5ff", "title": "Produce", "inResultset": true }, "Seafood": { "color": "#b2b2f7", "title": "Seafood", "inResultset": true } } }, "handlers": [ {} ] }, { "jaql": { "table": "Order Details", "column": "Quantity", "dim": "[Order Details.Quantity]", "datatype": "numeric", "agg": "sum", "title": "Total Quantity" }, "format": { "mask": { "type": "number", "abbreviations": { "t": true, "b": true, "m": true, "k": true }, "separated": true, "decimals": "auto", "isdefault": true } }, "field": { "id": "[Order Details.Quantity]_sum", "index": 1 }, "handlers": [ {} ] } ], "datasource": { "fullname": "LocalHost/NorthWind", "revisionId": "d7f3f31f-4f98-49ef-851d-951d5105eaf4" }, "values": [ [ { "data": "Beverages", "text": "Beverages" }, { "data": 9532, "text": "9532" } ], [ { "data": "Condiments", "text": "Condiments" }, { "data": 5298, "text": "5298" } ], [ { "data": "Confections", "text": "Confections" }, { "data": 7906, "text": "7906" } ], [ { "data": "Dairy Products", "text": "Dairy Products" }, { "data": 9149, "text": "9149" } ], [ { "data": "Grains/Cereals", "text": "Grains/Cereals" }, { "data": 4562, "text": "4562" } ], [ { "data": "Meat/Poultry", "text": "Meat/Poultry" }, { "data": 4199, "text": "4199" } ], [ { "data": "Produce", "text": "Produce" }, { "data": 2990, "text": "2990" } ], [ { "data": "Seafood", "text": "Seafood" }, { "data": 7681, "text": "7681" } ] ] }
Notice the results appearing for each value under the "data" tag.
