Querying the Sisense Elasticube with Python

Knowledge Base Docs

APIs

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!

intapiuser

Posted 3 years ago·Last reply 3 years ago

1 comment

JuanBoKnows

·3 years ago

Busted link: download the ODBC driver