Logging Users Out Of Sisense From Your Site
Download: Post Message Introduction The following forum post explains how to log your users out of Sisense from your native application. Purpose Single sign-on (SSO) systems enable your users to authenticate themselves in your online services and then in Sisense with one authentication credential. After your users' session has ended, it is your responsibility to develop and implement the means of logging your users out of Sisense through your application. For example, if your user is logged into Sisense through an embedded iFrame on your site or application and clicks a “Log Out” link on your site, your implementation must invoke the Sisense Logout API. Note, your users' browser may prevent invoking the Sisense Logout API from your application because of CORS (Cross-Origin Resource Sharing). For more information about CORS, click here. One way you can log your users out and overcome CORS is through the Post-Message method. The window.postMessage method securely enables cross-origin communication. The Post-Message plugin attached to this post implements a listener for the windows.postMessage() method. When the windows.postMessage() method is called, it triggers a MessageEvent in the browser. The Post-Message plugin listens for this event and calls the auth/logout method of the Sisense REST API, logging your user out of Sisense. Sisense logout endpoint (GET): /api/v1/authentication/logout The postMessage() method takes two parameters: message: A object to be sent to Sisense that should contain {logout:true}, so the plugin will be triggered only with this parameter included. targetOrigin: The Sisense URL of the window that the message is being sent to including the HTTP heading and the port. For example: postMessage({logout:true},'http://localHost:8081'); Steps 1. Download the Zip file below and extract its files to \...\Sisense\PrismWeb\plugins. If you do not have this folder, create the \plugins folder and then extract the files to it. If you are using version 7.2 and higher unzip the contents into your C:\Program Files\Sisense\app\plugins\ folder. 2. Develop the functionality in your site or application that implements the Post-Message plugin to call the logout method of the Sisense REST API.Sample Code for Sharing Dashboards Using the REST API in .NET
Introduction A sample code to demonstrate adding shares using the Sisense API in .NET. The programs flow is a follows: Retrieve the authentication token using: Retrieve userID using: Retrieve the current shares for the dashboard using: Share the dashboard with the user by updating the shares of the dashboard using: Pre-Req. This code uses the RestSharp and the Newtonsoft.Jason.linq packages both available through NuGet. If you are using Microsoft Visual Studio, see here for instructions on how to install the NuGet extension and here for instructions on how to add packages to your solution. Once installed, use the NuGet package manager to install the RestSharp and Newtonsoft.Jason.linq packages. Running the Program To run the program, modify the code as follows: In the main method, replace the following: <User Email> with a user's email <Dashboard ID> with your dashboard's ID static void Main(string[] args) { string token = getToken(); Console.WriteLine(token); string userID = getUserID("<User Email>", token); Console.WriteLine(userID); JArray curr_shares = getDashboardShares("<Dashboard ID>", token); Console.WriteLine(curr_shares.ToString()); addShares("<Dashboard ID>", curr_shares, userID, "user", "view", false, token); } In the getToken() method, replace the following: <Admin User Email> with the admin's userID <Admin User Password> with the admin's password <Host:Port> with you host and port static string getToken() { var client = new RestClient("http://<Host:Port>/api/v1/authentication/login"); var request = new RestRequest(Method.POST); request.AddHeader("cache-control", "no-cache"); request.AddHeader("accept", "application/json"); request.AddHeader("content-type", "application/x-www-form-urlencoded"); request.AddParameter("username", "<Admin User Email>"); request.AddParameter("password", "<Admin User Password>"); IRestResponse response = client.Execute(request); JObject obj = JObject.Parse(response.Content); return obj.GetValue("access_token").ToString(); } The Script C# code attached below. Share_Dahsboard_REST_API.csQuerying the Sisense Elasticube with Python
This article will cover two methods you can use to connect Python to the Elasticube. Method 1: Using ODBC Method 2: Using the REST API Method 1: Connect to the EC with ODBC Download Sisense ODBC This method will require you to download the ODBC driver. Follow the instructions on the ODBC documentation page before starting to work in Python. Connect to the system DSN ODBC with pyODBC The following libraries are required for this step (both are standard with anaconda) pyodbc pandas Step 1: Import libraries import pyodbc import pandas as pd Step 2: Establish a connection to the EC cnxn = pyodbc.connect('DSN=Sisense ODBC Driver') Step 3: (optional) Print out available tables # Create a cursor that can execute commands against the connection cursor = cnxn.cursor() # Print all available tables for t in cursor.tables(): print(t[2]) Step 4: Put the data into a dataframe Substitute <Table Name> with the table you want to query # Bring the data into a dataframe called df df = pd.read_sql('select * from <Table Name>', cnxn) # Print out the top records from df df.head() Method 2: Query the EC with the REST API This method will require the following python libraries: requests urllib.parse pandas Step 1: Import the libraries import requests import urllib.parse as parse import pandas as pd Step 2: Define the datasource and your query # Your elasticube name is your dataSource dataSource = 'leapYearUseCase' # Query the data as if you were writing a custom SQL query query = 'select Years, min(Amount) as maxAmount from <Your Table> group by Years' Step 3: Parse the queries dataSourceURI = parse.quote(dataSource) queryRUI = parse.quote(query) Step 4: Establish your connection To retrieve your Bearer token follow instructions under USING THE SISENSE API - Authentication # http://localhost:8081 may vary depending on your configuration # The request asks for the data formatted as a csv for convenience endPoint = 'http://localhost:8081/api/elasticubes/{}/Sql?format=json&query={}'.format(dataSourceURI, queryRUI) # Enter your bearer token in place of <your bearer token> header = {'Authorization': 'Bearer <your bearer token>'} Step 5: Post and Get the Response # Post to get the response response = requests.get(endPoint, headers=header) # Collect the response text formatted as a JSON data = response.json() # Put the data into a dataframe df = pd.DataFrame(data['values'], columns=data['headers']) Cheers!Python Script to Export Dashboards to CSV File
The following Python script allows exporting dashboard data to CSV files. The script will export each widget to a separate CSV file. ✨This new version of the script uses Sisense's REST API an eliminates the direct connection to the mongodb. Important: Starting with Sisense V8.0.1The GET /dashboards/{id} endpoint returns only the Dashboard Owner ID and no other information about dashboard owners when called by non-Admin users (see Sisense Release Notes.) Therefore the user used to run this scrip must have admin rights for the script to work properly. Download exportDashboardToCSV_01272020.zip - contains the script executable and configuration file and does not require Python installation on the server. Settings The script uses a settings.yaml file. You should configure the following settings: Setting Description email Admin email address. If you're using the api token to authenticate leave this value blank. password Admin password. If you're using the api token to authenticate leave this value blank. api_token The API token can be use instead of the password. To retrieve you API token use the POST /authentication/login REST API command. See here for more info. Note: The admin email and password are used for retrieving the API token. The token is required for running API command. See the REST API documentation for more information. file_logLevel Log level for the log file. Available options: DEBUG INFO WARNING ERROR CRITICAL console_logLevel Log level for the console. Available options: DEBUG INFO WARNING ERROR CRITICAL logFileName Path to the script log file. protocol Protocol for accessing Sisense http or https. Default: http verify Set verify to False toignore verifying the SSL certificate. Default: true port Sisense port. Default 8081 host Sisense host. Default: localhost csvFilePath CSV files output directory csvFileNameConvention Naming convention for the generated CSV files. Options are: WIDGET_NAME (e.g. Total csv) WIDGET_AND_DASHBOARD_NAME (e.g. Total Revenue_Sales Dashboard.csv) WIDGET_AND_DASHBOARD_ID (e.g. 5783b9eb25ddaf78420000a6_56f1aa1dbc4b62043a000028.csv) Usage exportDashboardToCSV.exe [-h] [-dashboards [DASHBOARDS ...]] [-widgets [WIDGETS ...]] optional arguments: -h, --help show this help message and exit -dashboards DASHBOARDS [DASHBOARDS ...] List of dashboard IDs to export separated by space. -widgets WIDGETS [WIDGETS ...] List of widget IDs to export separated by space. Each widget ID should be in the format of <dashboard id>.<widget id> Output The script will create an output directory 'csv' in the scripts directory. Each widget in the dashboard will be exported to a separate CSV file named as: dashboardID_widgetID.csv Examples Export an entire dashboard: exportDashboardToCSV.exe -dashboards 5e2a1bccbc60327e98fb4635 Export multiple dashboards: exportDashboardToCSV.exe -dashboards 5e30922fa9ab7536081a6ee9 5e3087a78d9e7f0e3cb3e6f8 Export individual widget: exportDashboardToCSV.exe -widgets 5e3087a78d9e7f0e3cb3e6f8.5e3087a78d9e7f0e3cb3e6f9 Export multiple widgets: exportDashboardToCSV.exe -widgets 5e3087a78d9e7f0e3cb3e6f8.5e3087a78d9e7f0e3cb3e6f95e3087a78d9e7f0e3cb3e6f8.5e3087a78d9e7f0e3cb3e6f9 Export a combination of dashboards and widgets: exportDashboardToCSV.exe -dashboards 5e30922fa9ab7536081a6ee9 5e3087a78d9e7f0e3cb3e6f8 -widgets 5e3087a78d9e7f0e3cb3e6f8.5e3087a78d9e7f0e3cb3e6f9 5e3087a78d9e7f0e3cb3e6f8.5e3087a78d9e7f0e3cb3e6f9Inserting New Users From CSV File
In some cases you would like to add many users manually and in bulk, and won't want to go through the process of entering one at a time. With the following python app you will be able to insert users from a CSV by just running it. Steps: Download the attached zip file, and extract it. Download the example users.csv file The file has no headers and the structure is as followed: Email, User Name, First Name, Last Name, Role, Group The roles are - admin, contributor, consumer and dataDesigner If you have more than one group add the second group using a pipe "|". Example Group 1|Group 2 In the folder open Settings.yaml file Change the "api_token" to your admin token - view "To retrieve the User Token for authenticating requests" on this page Under "mongoWriteUserPass" - Insert your WriteUser password - To create the password view first section on this page In "Sisense host" enter the domain you are using for Sisense In "protocol" Enter https or http In "port" Change it to the port you are using for SSL(https) set it for 443 In "csvFilePath" specify the location of your CSV file, make sure to have double backslash Save Run as Admin the bulk_user_load.exe fileHow to Share a Dashboards and Folder with Users and Groups using Python (2.7)
»»» Sisense Verison 7.1 and below: UPDATED 6/10/2016 ««« »»» Sisense Verison 7.2 and above: UPDATED 2/22/2019 ««« Introduction This Python script will demonstrate using the REST API for sharing dashboards and folders. There are two versions of this script, one is for Sisense version 7.1 and below. The newer script is for Sisense version 7.2 and above. The script includes the following examples: Share a dashboard with a single user Share a dashboard with a group Share a folder with a single user Share a folder with a group Get the folder ID by the folder name Get a list of IDs of the dashboards in a folder Pre-Req. REST API Key token (see here on how to obtain your token Sisense REST API) Python installed on your system. If setting up on Sisense 7.1 and below - install Python 2.7 on your system If setting up on Sisense 7.2 and above - install Python 3.7 on your system Admin permissions Download the appropriate script based on the Sisense version you are running: If setting up on Sisense 7.1 and below - updatesSharesAPI-7-1-below.zip If setting up on Sisense 7.2 and above - updateSharesAPI-7-2-above.zip Configuration To configure the script run on your system You will need to modify the following settings in the script. Running the Script First, edit the script's main() function and uncomment the calls you want to use. The available calls are: Then, from a command line window run the following command: C:\>python <path to script>\updatesSharesAPI.pyHow To Add Images To Pivot Widgets Using The New Pivot 2.0 API
The new Pivot 2 (in Linux deployments) has a powerful API to select and edit cells using a widget script. You can use CSS & HTML to enrich your pivot widgets. In this example we'll use a star based product rating. Have the URLs to your images ready, whether they are hosted in your Sisense deployment or somewhere else. Decide on the dimensions. 12x12px was a good size for these stars. Use the following script to target the row, column or value that will have the images. In this case, it's on values, on the Product Rating column: Code Snippet: //Create target object to select cells of type Value with the title "Product Rating" const myTarget = { type: ['value'], values: [ { title: 'Product Rating' } ] }; //Pass target to the transformPivot method and add the images in the cell widget.transformPivot(myTarget, function(metadata, cell) { cell.contentType = 'html'; if (!isNaN(cell.content) && cell.content.indexOf('star') < 1) { var pctVal = parseInt(cell.content); var offStars = 5 - pctVal; cell.content += " "; for (var idx=0; idx <offStars; idx++) { cell.content += "<img src='/plugins/BloX/blox-images/starOff.png' width='12' height='12'/>"; } for (var idx=0; idx<pctVal; idx++) { cell.content += "<img src='/plugins/BloX/blox-images/star.png' width='12' height='12'/>"; } } }); Make sure to check the API. You can select cells in the rows, columns, or values sections of the pivot; and select them by title, position or even dimensional values.File Management Automation for File Management
On article Uploading Files Locally Using Rest (Linux) we saw examples of how to automate the upload of files to File Management. This article describe methods to automate the management of files on File Manager. Compatible with Linux Version <= L2021.3 import requests import json import re import time from datetime import datetime import websocket from websocket import create_connection # Sisense API token apiToken = 'Bearer YOUR_API_TOKEN' # Sisense hostname sisenseHost = 'https://test.sisense.com' # Retreive Droppy session token url = sisenseHost + '/app/explore/!/token' headers = {'content-type': 'application/x-www-form-urlencoded', 'accept': '*/*', 'accept-encoding' : 'gzip, deflate, br', 'accept-language' : 'en-US,en;q=0.9,ru;q=0.8', 'dnt' : '1', 'Authorization': apiToken, 'sec-fetch-dest' : 'empty', 'sec-fetch-mode' : 'same-origin', 'sec-fetch-site' : 'same-origin', 'user-agent' : 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/80.0.3987.149 Safari/537.36', 'x-app': 'droppy' } r = requests.get(url, headers=headers) token = r.content token = str(token, 'utf-8') def downloadFolder(externalPath, localPath): # Downloads folder content as ZIP archive headers = { 'Authorization': apiToken, } if localPath[-1] != '\\': localPath += '\\' if externalPath[0] == '/': externalPath = externalPath[1:] url = '%s/app/explore/!/zip/%s'%(sisenseHost, externalPath) r = requests.get(url, headers=headers, allow_redirects=True) m = re.search('filename="(.*?)"', r.headers.get('content-disposition')) if m: filename = m.group(1) open(localPath+filename, 'wb').write(r.content) def downloadFile(externalPath, localPath): headers = { 'Authorization': apiToken, } if localPath[-1] != '\\': localPath += '\\' if externalPath[0] == '/': externalPath = externalPath[1:] url = '%s/app/explore/!/dl/%s'%(sisenseHost, externalPath) r = requests.get(url, headers=headers, allow_redirects=True) m = re.search('filename="(.*?)"', r.headers.get('content-disposition')) if m: filename = m.group(1) open(localPath+filename, 'wb').write(r.content) def deleteObject(ws, objectPath): command = {"vId":0,"type":"DELETE_FILE","data":objectPath,"token":token} ws.send(json.dumps(command)) def renameObject(ws, oldPath, newPath): command = {"vId":0,"type":"RENAME","data":{"src":oldPath,"dst": newPath},"token":token} ws.send(json.dumps(command)) def copyObject(ws, oldPath, newPath): command = {"vId":0,"type":"CLIPBOARD","data":{"type":"copy","src":oldPath,"dst":newPath},"token":token} ws.send(json.dumps(command)) def moveObject(ws, oldPath, newPath): command = {"vId":0,"type":"CLIPBOARD","data":{"type":"cut","src":oldPath,"dst":newPath},"token":token} ws.send(json.dumps(command)) def listFolder(ws, folderPath): command = {"vId":0,"type":"REQUEST_UPDATE","data":folderPath,"token":token} ws.send(json.dumps(command)) result = json.loads(ws.recv()) # Following code is for pretty print folder content for k, v in result.get('data').items(): val = v.split('|') timeModified = datetime.utcfromtimestamp(int(val[1])).strftime('%Y-%m-%d %H:%M:%S') if val[0] == 'f': print ('File %s; Modified(UTC) %s; Size %s B'%(k, timeModified, val[2])) elif val[0] == 'd': print ('Directory %s; Modified(UTC) %s; Size %s B'%(k, timeModified, val[2])) websocket.enableTrace(False) ws = create_connection('wss%s/app/explore/!/socket'%(sisenseHost.replace('https',''))) # Function for deleting file/folder #deleteObject(ws, '/data/Test/wetwt.txt') # Function for copying file/folder #copyObject(ws, '/data/Test/asd.txt', '/data/Test/New folder/asd.txt') # Function for moving file/folder #moveObject(ws, '/data/Test/asd1.txt', '/data/Test/New folder/asd1.txt') # Function for viewing folder content # listFolder(ws, '/connectors/excel') # Function for renaming file/folder #renameObject(ws, '/data/Test/asd.txt', '/data/Test/asd1.txt') # Function for downloading folder #downloadFolder('/data/Test/New folder', r'C:\Users\vladislav.kokh\Documents') # Function for downloading file #downloadFile('/data/Test/New folder/assssd.txt', r'C:\Users\vladislav.kokh\Documents') Compatible with Linux Version >= L2021.5 # Install dependency: # pip3 install requests # Sisense hostname url = 'https://test.sisense.com' # Sisense API token # token = 'Bearer YOUR_API_TOKEN' import requests import json import urllib.parse import os def get_auth(token): headers = { 'Authorization': token, } x_auth = requests.post(f'{url}/app/explore/api/login', headers=headers) if x_auth.status_code==200: return x_auth.text else: print ('Cannot get x-auth token. Check API token validity') return False # # SERVICE FUNCTION # # Do NOT modify x_auth = get_auth(token) def get_files(token, x_auth, path): headers = { 'Authorization': token, 'x-auth': x_auth } if path: x = requests.get(f'{url}/app/explore/api/resources{path}', headers=headers) else: print (f'Wrong path {path}') return False if x.status_code==200: return json.loads(x.text).get('items') else: print ('Wrong API token') return False def upload_files(token, x_auth, src_path, dst_path): headers = { 'Authorization': token, 'x-auth': x_auth, } if dst_path: if dst_path[-1] != '/': dst_path += '/' dst_path = f'{dst_path}{os.path.basename(src_path)}' with open(src_path,'rb') as ff: x = requests.post(f'{url}/app/explore/api/resources{dst_path}?override=true', headers=headers,data=ff) else: print (f'Wrong path {dst_path}') return False if x.status_code==200: return x.text def download_file(token, x_auth,path, dst_folder): headers = { 'Authorization': token, 'x-auth': x_auth, 'content-type': 'application/x-www-form-urlencoded' } local_filename = path.split('/')[-1] if dst_folder[-1] != '\\': dst_folder += '\\' with requests.get(f'{url}/app/explore/api/raw{path}', headers=headers, stream=True) as r: r.raise_for_status() with open(dst_folder+local_filename, 'wb') as f: for chunk in r.iter_content(chunk_size=8192): # If you have chunk encoded response uncomment if # and set chunk_size parameter to None. #if chunk: f.write(chunk) return local_filename def download_folder(token, x_auth,src_folder, dst_path): headers = { 'Authorization': token, 'x-auth': x_auth, 'content-type': 'application/x-www-form-urlencoded' } with requests.get(f'{url}/app/explore/api/raw{src_folder}/?algo=zip', headers=headers, stream=True) as r: r.raise_for_status() with open(dst_path, 'wb') as f: for chunk in r.iter_content(chunk_size=8192): f.write(chunk) return dst_path def download_zipped_files(token, x_auth,src_files, dst_path): headers = { 'Authorization': token, 'x-auth': x_auth, } files_url_string = urllib.parse.quote_plus(','.join(src_files)) with requests.get(f'{url}/app/explore/api/raw/?files={files_url_string}&algo=zip', headers=headers, stream=True) as r: r.raise_for_status() with open(dst_path, 'wb') as f: for chunk in r.iter_content(chunk_size=8192): f.write(chunk) return dst_path def copy_object(token, x_auth, src_path, dst_path): headers = { 'Authorization': token, 'x-auth': x_auth, } if src_path and dst_path: dst_path = urllib.parse.quote_plus(dst_path) x = requests.patch(f'{url}/app/explore/api/resources{src_path}?action=copy&destination={dst_path}&override=true&rename=false', headers=headers) else: print (f'Wrong path {src_path} {dst_path}') return False if x.status_code==200: return x.text def move_object(token, x_auth, src_path, dst_path): headers = { 'Authorization': token, 'x-auth': x_auth, } if src_path and dst_path: dst_path = urllib.parse.quote_plus(dst_path) x = requests.patch(f'{url}/app/explore/api/resources{src_path}?action=rename&destination={dst_path}&override=true&rename=false', headers=headers) else: print (f'Wrong path {src_path} {dst_path} ') return False if x.status_code==200: return x.text def delete_object(token, x_auth, path): headers = { 'Authorization': token, 'x-auth': x_auth, } if path: x = requests.delete(f'{url}/app/explore/api/resources{path}', headers=headers) else: print (f'Wrong path {path}') return False if x.status_code==200: return x.text # Function for viewing folder content # print (get_files(token, x_auth, path='/')) # Function for uploading file to Sisense # print(upload_files(token, x_auth, src_path=r'C:\Users\user\Documents\aaaaa.zip', dst_path='/usage')) # Function for download file from Sisense # print (download_file(token, x_auth, path='/usage/regex.txt', dst_folder=r'C:\Users\user\Documents')) # Function for download folder from Sisense. Downloads in ZIP format. # print (download_folder(token, x_auth, src_folder='/usage', dst_path=r'C:\Users\user\Documents\aaaaa.zip')) # Function for download multiple files from Sisense. Downloads in ZIP format. Must be minimum 2 files provided. # print( download_zipped_files(token, x_auth, src_files=['/usage/regex.txt','/usage/values2.yaml'], dst_path=r'C:\Users\user\Documents\aaaaa.zip') ) # Function to copy file inside Sisense # print (copy_object(token, x_auth, src_path='/usage/regex.txt', dst_path='/styles/regex.txt')) # Function to move or rename file inside Sisense # print (move_object(token, x_auth, src_path='/styles/regex.txt', dst_path='/usage/regex.txt')) # Function to delete file inside Sisense # print (delete_object(token, x_auth, path='/styles/regex.txt')) File Management is using an open source application name Droppy, while the upload is done on REST the management itself is done on a web socket. Below examples in Python Python script to manage files via Droppy socket interface Script requires installed websocket client library Install via pip: pip install websocket_client Get Sisense user API token and paste it to apiToken variable(row 12) Token format should be following 'Bearer TOKEN' (ex. 'Bearer eyJhbGciOiJIUzI1NiIsInR5cC...') Set sisenseHost variable(row 15) with your Sisense URL (ex. 'https://test.sisense.com') Starting from row 124 we provide Droppy management function examples. You can find function for deleting, moving, renaming, etc.. Each function can be run independently or together with others (To run a couple functions you need to uncomment all of them) Compatible with Linux Version <= L2021.3 DELETING FILE OR FOLDER(OBJECT): Copy object location from Droppy (For ex. '/connectors/genericjdbc/manifest.json') Note that object path should NOT end with '/'(ex. '/connectors/genericjdbc/manifest.json/' is wrong) Find deleteObject function commented (row 125) Uncomment this line (Remove # from the beggining of the row) Paste the object location to the function as a second argument ( deleteObject(ws,'/connectors/genericjdbc/manifest.json') ) Run python script (Open CMD and type 'python droppyFileManager.py') Object should be deleted Close droppyFileManager.py without saving changes GET FOLDER CONTENT: Copy folder location from Droppy (For ex. '/connectors/genericjdbc') Note that object path should NOT end with '/'(ex. '/connectors/genericjdbc/' is wrong) Find listFolder function commented (row 131) Uncomment this line (Remove # from the beggining of the row) Paste the folder location to the function as a second argument ( deleteObject(ws,'/connectors/genericjdbc') ) Run python script (Open CMD and type 'python droppyFileManager.py') Folder content should be printed to console (ex. 'File description.json; Modified(UTC) 2020-03-06 15:39:41; Size 472 B') Close droppyFileManager.py without saving changesExtracting / Retrieving Results from Widgets to JSON Format (using JAQL Queries)
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 - http://localhost:8081/api/elasticubes/northwind/jaql 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: http://localhost:8081/api/elasticubes/northwind/jaql 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.Sharing A Dashboard By Using The REST API
Sharing a dashboard by using the REST API is very useful in case you need to automate the sharing process. However, in order to do so more than one API call is required, and not using them all in the right order will result in an unsuccessful share. Sharing Dashboard through API steps: Get Auth token(POST api/v1/authentication/login) - this is required in order to run any of our API calls. Get the id of the user you want to share the dashboard with(GET api/v1/users) - in order to share a dashboard with a user you need to provide the system with the user id, and the user email/username is not enough. This id can be retrieved by using this call. Get the id of the dashboard you want to share - you can get the dashboard id from the API(GET api/v1/dashboards) or from the dashboard url (the id will appear at the end of the dashboard url: http://localhost:8081/app/main#/dashboards/5829b46bd009e91420000047) Share the dashboard with the user - In order to do so you will need to add the user to the shares object of the dashboard. You can use the PATCH api/v1/dashboards/{id} call for this. Important - updating the shares object by using this call will override it, hence if you want to keep the current users which the dashboard is shared with, you need to first get the list of current shared users, append to it the user you want to add, and only then run the PATCH command. You can get this object from the GET api/v1/dashboards call. Important#2 - you need to make sure that each user appears in the list only once, or else the share will fail. Publish the dashboard to the user - After you have added the user to the shares object of the dashboard, in order for the user to see it you need to republish the dashboard to him. In order to do so, use the following API call: POST api/v1/dashboards/{id}/publish Here is an example in Python for doing this: import requests import time import json user_name = '[email protected]' #Change to your admin user name. password = 'Sisense' #Change to your password. Sisense_url = "http://localhost:8081" #Change to your Sisense address. new_user = "[email protected]" #Change to the email of the user you want to share the dashboard with. user_rule = "edit" # change to "view" if the user is a viewer. If it is a designer ir admin, leave it as it is. dashboard_id = '5829b46bd009e91420000047' #------------------------------------------------------------------------------ # Get auth_token url = Sisense_url+"/api/v1/authentication/login" payload = "username="+user_name.replace('@','%40')+"&password="+password headers = { 'cache-control': "no-cache", 'content-type': "application/x-www-form-urlencoded" } response = requests.request("POST", url, data=payload, headers=headers) auth_token = response.json()["access_token"] auth_token = "Bearer "+auth_token # Get user url = Sisense_url + "/api/v1/users" headers = { 'accept': "application/json", 'authorization': auth_token } response = requests.request("GET", url, headers=headers) response = response.json() user_id = '' for user in response: try: if user["userName"] == new_user: user_id = user["_id"] except KeyError: pass new_user_share = { "shareId": user_id, "type": "user", "rule": user_rule, "subscribe": False } #Get current shared users url = Sisense_url + "/api/v1/dashboards/"+dashboard_id headers = { 'accept': "application/json", 'authorization': auth_token } response = requests.request("GET", url, headers=headers) response = response.json() dashboard_shares = response["shares"] dashboard_shares.append(new_user_share) arr_users = [] for user in dashboard_shares: try: arr_users.index(user["shareId"]) dashboard_shares.remove(user) except: arr_users.append(user["shareId"]) dashboard_shares = {"shares": dashboard_shares} #Add user to dashboard shares payload = json.dumps(dashboard_shares) url = Sisense_url + "/api/v1/dashboards/"+dashboard_id headers = { 'accept': "application/json", 'content-type': "application/json", 'accept': "application/json", 'authorization': auth_token } response = requests.request("PATCH", url, data=payload, headers=headers) #Publish the dashboard to the user url = Sisense_url + "/api/v1/dashboards/"+dashboard_id+"/publish?force=false" headers = { 'accept': "application/json", 'content-type': "application/json", 'authorization': auth_token } response = requests.request("POST", url, headers=headers) print "Done!" Please note, this code flow will not work for removing user access to dashboards. Only for adding user (or group) access. To change from user to group, please try the code below: import requests import time import json user_name = '[email protected]' #Change to your admin user name. password = 'Sisense' #Change to your password. Sisense_url = "http://localhost:8081" #Change to your Sisense address. new_group = "market group" #name the group you want to share with group_rule = "edit" # change to "view" if the group is a viewer. If it is a designer or admin, leave it as it is. dashboard_id = '5dc5c237832aeb53441186bb' #------------------------------------------------------------------------------ # Get auth_token url = Sisense_url+"/api/v1/authentication/login" payload = "username="+user_name.replace('@','%40')+"&password="+password headers = { 'cache-control': "no-cache", 'content-type': "application/x-www-form-urlencoded" } response = requests.request("POST", url, data=payload, headers=headers) auth_token = response.json()["access_token"] auth_token = "Bearer "+auth_token # Get groups url = Sisense_url + "/api/v1/groups" headers = { 'accept': "application/json", 'authorization': auth_token } response = requests.request("GET", url, headers=headers) response = response.json() group_id = '' for group in response: try: if group["name"] == new_group: group_id = group["_id"] except KeyError: pass new_group_share = { "shareId": group_id, "type": "group", "rule": group_rule, "subscribe": False } #Get current shared groups url = Sisense_url + "/api/v1/dashboards/"+dashboard_id headers = { 'accept': "application/json", 'authorization': auth_token } response = requests.request("GET", url, headers=headers) response = response.json() dashboard_shares = response["shares"] dashboard_shares.append(new_group_share) #arr_shares = [] #for group in dashboard_shares: # try: # arr_groups.index(group["shareId"]) # dashboard_shares.remove(group) # # except: # arr_groups.append(group["shareId"]) dashboard_shares = {"shares": dashboard_shares} #Add group to dashboard shares payload = json.dumps(dashboard_shares) url = Sisense_url + "/api/v1/dashboards/"+dashboard_id headers = { 'accept': "application/json", 'content-type': "application/json", 'accept': "application/json", 'authorization': auth_token } response = requests.request("PATCH", url, data=payload, headers=headers) #Publish the dashboard to the group url = Sisense_url + "/api/v1/dashboards/"+dashboard_id+"/publish?force=false" headers = { 'accept': "application/json", 'content-type': "application/json", 'authorization': auth_token } response = requests.request("POST", url, headers=headers) print ("Done!") Please test this out before adding to your automation