Connecting Kyvos semantic models to Python using SQL connection

Applies to:Kyvos Enterprise  Kyvos Cloud (Managed Services on AWS)  Kyvos Azure Marketplace

Kyvos AWS Marketplace  Kyvos Single Node Installation (Kyvos SNI)  Kyvos Free (Limited offering for AWS)

This section explains how you can connect Python to Kyvos semantic models using SQL connection.

Prerequisites

To connect Kyvos using Python, you must have the following.

1. Python (3 and above) must be hosted either on an analyst’s machine or on Hadoop Cluster (Cloud/On-Premises).
2. PYODBC module 
3. Kyvos ODBC Driver

Steps to connect

1. Download the Kyvos ODBC driver from https://www.kyvosinsights.com/kyvos-odbc-driver/
   You will be asked to register yourself to download the driver and the Installation Guide to set up the driver.
2. Install the Kyvos ODBC driver using the system administrator credentials.
3. Create a System DSN named KyvosDSN, as explained:
   1. For Windows
   2. For Mac OS
4. Test the connectivity with Kyvos, and save the System DSN.
5. Now you can start browsing the Kyvos semantic models in Python Notebook.

For example:

import pyodbc

#connection string
cnxn = pyodbc.connect('DSN=KyvosDSN;UID=xxxx;PWD=xxxx', autocommit=True)
cursor = cnxn.cursor()

#execute the prepared SQL statement
cursor.execute("SELECT `ssb`.`brand1` AS `brand1`, SUM(`ssb`.`profit`) AS `sum_profit_ok` FROM `kyvos_browsing_automationcubes`.`ssb` `ssb` WHERE ((`ssb`.`mfgr` = 'MFGR#3') AND (`ssb`.`region` = 'ASIA')) GROUP BY `ssb`.`brand1`")

#iterate the results
D1=cursor.fetchall()

for showD1 in D1: