Querying 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!