Integration Guides
This page contains various Integration Guides
Table of Contents
1.1 Excel
2.1 SAS

1. BI Integrations

1.1 Excel

Below are the steps you can follow to establish a connection to Cinchy from Microsoft Excel.​
  1. 1.
    Excel connects to Queries in Cinchy. If you don't have one that represents your dataset, you'll need to create that first. In this example, we will use a query called API Test.
  2. 2.
    When you open the query you'll notice on the right-hand side a green button that says REST API​
  3. 3.
    Click on the REST API button and you'll see the below popup. In the textbox is the URL for the API endpoint. You can click the clipboard icon to copy the URL.
Could not load image
4. An this example the URL is: http://your.cinchy.instance.domain/API/YourQueryDomain/API Test
You'll notice that the structure here is <your Cinchy instance URL>/API/<the name of your query> and optionally at the end you may have querystring parameters.
For access via Excel we're going to use Basic authentication and a result format of CSV, which uses a slightly different URL endpoint. Instead of /API/ in the URL, it's going to be /BasicAuthAPI/ and we're going to add a querystring parameter - ResultFormat=CSV. For this example your URL for accessing this dataset through PowerBI is going to be: http://your.cinchy.instance.domain/BasicAuthAPI/YourQueryDomain/API Test?ResultFormat=CSV
5. Launch Excel. To access the dataset, click on Data in the menu bar, then Get Data > From Other Sources > Blank Query from the menu:
Could not load image
6. In the window that launches there is a textbox where you can enter an expression. Here you will enter the below text (note your modified URL from Step 4 between quotes): =Csv.Document(Web.Contents("http://your.cinchy.instance.domain/BasicAuthAPI/YourQueryDomain/API Test?ResultFormat=CSV"))
Could not load image
7. Once you've entered that text either click the check mark to the left of the input box or click away and it will automatically attempt to run the expression. The data may return in HTML format initially and not be what you're expecting:
Could not load image
To correct this, click the Data Source Settings button, then under Credentials click the Edit button:
8. Select Basic on the left, enter the credentials for a Cinchy User Account that has access to run this query and click OK.
Click OK in the Edit Permissions dialog and click Close in the Data Source Settings Dialog. This process of entering your credentials won't occur with each query, it's just the first time and then they're saved locally.
Could not load image
9. Click Refresh Preview and you should see the data. Click Close & Load and your dataset will now be displayed in the Excel worksheet.
Could not load image

1.2 Power BI

Below are the steps you can follow to establish a connection to Cinchy from Power BI.
  1. 1.
    Power BI connects to Queries in Cinchy. If you don't have one that represents your dataset, you'll need to create that first. In this example, we will use a query called API Test.
  2. 2.
    When you open the query you'll notice on the right-hand side a green button that says REST API​
  3. 3.
    Click on the REST API button and you'll see the below popup. In the textbox is the URL for the API endpoint. You can click the clipboard icon to copy the URL.
Could not load image
4. An this example the URL is: http://your.cinchy.instance.domain/API/YourQueryDomain/API Test
You'll notice that the structure here is <your Cinchy instance URL>/API/<the name of your query> and optionally at the end you may have querystring parameters.
For access via Power BI we're going to use Basic authentication and a result format of CSV, which uses a slightly different URL endpoint. Instead of /API/ in the URL, it's going to be /BasicAuthAPI/ and we're going to add a querystring parameter - ResultFormat=CSV. For this example your URL for accessing this dataset through PowerBI is going to be: http://your.cinchy.instance.domain/BasicAuthAPI/YourQueryDomain/API Test?ResultFormat=CSV
5. Launch PowerBI. To access the dataset, click on Get Data and then select Blank Query from the menu:
6. In the window that launches there is a textbox where you can enter an expression. Here you will enter the below text (note your modified URL from Step 4 between quotes): =Csv.Document(Web.Contents("http://your.cinchy.instance.domain/BasicAuthAPI/YourQueryDomain/API Test?ResultFormat=CSV"))
7. Once you've entered that text either click the checkmark to the left of the input box or click away and it will automatically attempt to run the expression. What you should then see is a prompt to edit credentials.
8. Click the Edit Credentials button and you should see the following popup:
9. Select Basic on the left, enter the credentials for a Cinchy User Account that has access to run this query and select the level at which to apply these settings (only if you want to, by default it's the root URL).
This process of entering your credentials won't occur with each query, it's just the first time and then they're saved locally.
10. Once you click Connect you should see the data
11. You can now apply any transformations to the dataset that you wish. For instance, in this example we need to click the button at the top that says Use First Row as Headers, but you may have additional changes.
In this example we also changed the name from Query1 to Product Roadmap
12. Once you're done, click Close & Apply. Now the metadata shows up on the right hand side and you can begin to use it to create your visualizations

1.3 Tableau

1.3.1 Web Data Connector

Cinchy exposes a Tableau Web Data Connector that provides access to Cinchy Saved Queries as data sources in Tableau. Tableau versions 2019.2+ are supported.
An active internet connection is required in order to use the Web Data Connector.

1.3.2 Prerequisites

To get started, you must add a record into the Integrated Clients table in the Cinchy domain with the below values.
Column
Value
Client Id
tableau-connector
Client Name
Tableau
Grant Type
Implicit
Permitted Login Redirect URLs
Permitted Logout Redirect URLs
Permitted Scopes
Id, OpenId, Email, Profile, Roles
Access Token Lifetime (seconds)
3600
Show Cinchy Login Screen
Checked
Enabled
Checked

1.3.3 Connecting From Tableau

  1. 1.
    Launch Tableau
  2. 2.
    Under Connect -> To a Server select the Web Data Connector option
  3. 3.
    Enter the URL from the Permitted Login Redirect URLs field on the Integrated Clients record created under the Prerequisites section above
  4. 4.
    The Cinchy login screen will appear, enter your credentials
  5. 5.
    Select one or more queries to add to your data set. The result of each query will be available as a Table in Tableau. If a query has parameters, you will be prompted to provide the parameter values before you can add it to your collection.
  6. 6.
    Click the Load button
The Cinchy query results will now be accessible for you to create your visualization.

2. Other Integrations

2.1 SAS

Cinchy’s JDBC Driver can be used with the SAS/ACCESS Interface to JDBC Driver capability to connect to your Cinchy instance. This section will discuss configuration and connection to your Cinchy data experience.

Prerequisites

Cinchy's JDBC Driver must be moved to the "/opt/Cinchy/client/JDBC" directory in the SAS server before attempting to configure.

Configuration

To configure SAS, you only need Cinchy’s JDBC driver that is accessible from your SAS session.
Argument
Description
Classpath
This option specifies the class path to your JDBC JAR files.
Class
This option specifies the class name for the JDBC driver to use for your connection. Here, we use JDBC driver class for Cinchy, org.cinchy.jdbc.Driver.
URL
This option specifies the JDBC connection string to use to connect to your data source. The Cinchy JDBC connection string used here is:jdbc:cinchy://<<Cinchy SSO URL>>/identity/connect/token?client_secret=<<GUID>>;client_id=<<ClientID>>;username=<<Username>>;password=<<PASSWORD>>;grant_type=password;scope=js_api;
The SAS/ACCESS Interface to JDBC LIBNAME statement allows you to assign a library reference to your data source. This feature lets you reference a table directly in a DATA Step or in a PROC Step. This example shows the basic LIBNAME statement to connect to Cinchy.
libname libCinchy jdbc classpath = "/opt/Cinchy/client/JDBC" class = "org.cinchy.jdbc.Driver" URL = "jdbc:cinchy://<<Cinchy SSO URL>>/identity/connect/token?client_secret=<<GUID>>;client_id=<<ClientID>>;username=<<Username>>;password=<<PASSWORD>>;grant_type=password;scope=js_api;";