Skip to end of metadata
Go to start of metadata



SQL for Extracting Data

You can run SQL queries to extract data from ElastiCubes. You can either use the SQL Runner, or your own tools. Both options are explained below.

Note: Following security enhancements made in Sisense V7.0 and later, this feature is only available to Administrators. You can allow additional user roles to access it but customizing that user's role

Using the SQL Runner to Extract Data from the ElastiCube

With the SQL Runner, you can run standard SQL queries on data within an ElastiCube, and see results in a JSON format.

To access the SQL Runner:

  1. Open Sisense web in a browser and log in.
  2. In your browser, add the extension sqlrunner#datasource=:ElastiCubeName at the end of the Sisense web URL. For example: If the Sisense URL is localhost:8081, and the ElastiCube is called "Training", type in localhost:8081/sqlrunner#/datasource=Training
  3. Enter your SQL queries in the left panel and click execute. Results will be returned in the right panel.

For Sisense 7.2 Beta Users

In Sisense V7.2 Beta, the location has been changed to http://localhost:8081/app/sqleditor

For more information, please see the v7.2 Developer Release Notes

Using the SQL API to Extract Data from the ElastiCube

You can also run SQL queries using API calls from your own tools. The syntax below executes an SQL query against a data source, and returns json results:

site/api/datasources/[datasource-name]/sql?query=[sql-query]
For example: http://www.sitename.com/api/datasources/laptopsales/sql?query=select sales.city count(*) from sales group by sales.city

The example returns the city name and number of occurrences from the sales table, and corresponds to the SQL statement below:

Select city, count(*)
From sales
Group By city

Your results can also be downloaded in a CSV format by adding the &format=csv query parameter.

Example: http://beta.sisense.com/api/datasources/laptopsales/sql?query=select sales.city count(*) from sales group by sales.city&format=csv

If you are connecting to a remote server and want to download your data in CSV, the name of the remote server or its address must be included in the API call

Example: 

http://localhost:8081/api/datasources/remoteserver/Usage%20Analytics%20Model/sql?query=select%20*%20from%20usage&format=csv

In the example above, remoteserver represents the location of where the name or address of the remote server should be defined in your call. This source should be defined in the Data Sources table of the Admin page in Sisense. 

Usage Analytics

Sisense Usage Analytics collects usage information for your Sisense system. This information is collected and stored in a CSV file. You can download this file with the SQL Runner with the following call: 

http://localhost:8081/api/datasources/LocalHost/Usage%20Analytics%20Model/sql?query=select%20*%20from%20usage&format=csv 

In this example, LocalHost is the name of the server where the Usage Analytics ElastiCube is located. If you are connecting remotely, change this to the IP address or name of the server as defined in the Data Sources table in the Admin page of Sisense. 


 

  • No labels