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
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)
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
# 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!
