Google Analytics CDATA Connector

What You’ll Learn:

In this course users will learn how to:

Download a CDATA Google Analytics (GA) driver
Install a CDATA GA driver
Configure a CDATA GA driver
How to connect using a CDATA GA driver

Prerequisites:

This article will be helpful for users with basic knowledge of the following:

Familiarity with Sisense UI
Familiarity with the concept of data connectors
Basic knowledge of Linux/ Windows

Introduction:

In May 2022, after Google’s April announcement deprecating an old API, Sisense announced that they will also be deprecating the native Google Analytics connector.

For existing customers, the native connector will continue to work until early August 2023. Despite these hurdles, users can still use CDATA drivers as a workaround in order to connect.

Instructions On How To Connect To Google Analytics Using A CDATA Driver:

There are 2 ways to use a CDATA connector:

Option 1, involves using the CDATA connector's internal generic clientId and clientSecret. It takes less effort but is also less customizable.
Option 2, requires creating an app in Google to generate credentials (clientId and clientSecret). Although this option is more customizable, it also takes more effort.

Option 1: Less Customized, Less Effort:

Download the driver from Sisense Connectors | Sisense Data Connectivity | CData Software.
Install it locally in the customer’s environment.
Navigate to (if installed in the default location):

/Applications/CData/CData JDBC Driver for Google Analytics 2022/lib if using MacOS.
C:\Program Files\CData\CData JDBC Driver for Google Analytics 2022\lib if using Windows.

Execute cdata.jdbc.googleanalytics.jar
Add the following parameters to make it work:

AuthScheme=OAuth;
OAuth=GETANDREFRESH;
Resulting connection string looks as follows: jdbc:googleanalytics:AuthScheme=OAuth;InitiateOAuth=GETANDREFRESH

Hit “Test Connection”. You will be redirected to the web browser to log in to your Google account.
Once authenticated, the connection successful message will appear in the jar and the connection string will be updated. Click “Copy to Clipboard” to copy the connection string.
OAuthSettings.txt will be generated containing the connection parameters in the following location:

%APPDATA%\\CData\\GoogleAnalytics Data Provider\\OAuthSettings.txt for Windows
~/Library/Application Support for macOS.
If you can’t find the file, I recommend setting the OAuthSettingsLocation in the jar itself.

Copy OAuthSettings.txt to the Sisense server:

C:\OAuthSettings.txt for Windows deployments.
/opt/sisense/storage/data/OAuthSettings.txt for Linux deployments.

Copy cdata.jdbc.googleanalytics.jar to the Sisense server. You can check the following documentation for an exact step-by-step guide. For example:

/opt/sisense/storage/data/GoogleAnalytics - for Linux
C:\Program Files\Sisense\DataConnectors\JVMContainer\Connectors\CData JDBC Driver for Google Analytics 2022\lib - for Windows

Connect to Google Analytics using Sisense:
1. Use the connection string from step 7 but change the OAuthSettingsLocation: jdbc:googleanalytics:AuthScheme=OAuth;InitiateOAuth=GETANDREFRESH;OAuthSettingsLocation="/opt/sisense/storage/data/OAuthsettings.txt";_persist_oauthrefreshtoken=oauthRefreshToken;_persist_oauthexpiresin=3599;_persist_oauthaccesstoken=oauthAccessToken;_persist_oauthtokentimestamp=XXXXXXXXXXXXX;
2. Jar’s folder (the one you copied the jar file into in step 10):
  1. /opt/sisense/storage/data/GoogleAnalytics
  2. C:\Program Files\Sisense\DataConnectors\JVMContainer\Connectors\CData JDBC Driver for Google Analytics 2022\lib
3. Driver class name: cdata.jdbc.googleanalytics.GoogleAnalyticsDriver

Option 2: More Customized, More Effort:

Download the driver from Sisense Connectors | Sisense Data Connectivity | CData Software page.
Install it locally in the customer’s environment.
Navigate to (if installed in the default location):

/Applications/CData/CData JDBC Driver for Google Analytics 2022/lib if using MacOS.
C:\Program Files\CData\CData JDBC Driver for Google Analytics 2022\lib if using Windows.

Execute cdata.jdbc.googleanalytics.jar
You will need to add the following parameters to make it work:

AuthScheme=OAuth;
OAuth=GETANDREFRESH;
OAuthClientId=clientId; NOTE: If the customer is new, use this documentation to create a google app. For customers who already use our native Google Analytics driver, you will need to specify the clientId which can be retrieved from manifest.json from the old connector. Possible locations:
1. /opt/sisense/storage/connectors/googleanalytics/manifest.json
2. C:\Program Files\Sisense\DataConnectors\DotNetContainer\Connectors\GoogleAnalytics\manifest.json
3. C:\Program Files\Sisense\DataConnectors\JVMContainer\Connectors\GoogleAnalytics\manifest.json

OAuthClientSecret=clientSecret; NOTE: The same as OAuthClientId retrieved from manifest.json.

The resulting connection string should look like the following: jdbc:googleanalytics:AuthScheme=OAuth;InitiateOAuth=GETANDREFRESH;OAuthClientId=clientId;OAuthClientSecret=clientSecret;

6. Before hitting “Test connection” in the jar file you will need to navigate to your Google application using Google API Console and add “http://localhost:33333” to the ‘Authorized redirect URIs’ field.

7. Hit “Test Connection”. You will be redirected to the web browser to log in to your Google account.

8. Once authenticated the connection successful message will appear in the jar.

9. The connection string will be updated to incorporate tokens obtained from Google. Click “Copy to Clipboard” to copy the connection string. You will need it to set up the connection in Sisense:

10. OAuthSettings.txt will be generated containing the connection parameters in the following location:

%APPDATA%\\CData\\GoogleAnalytics Data Provider\\OAuthSettings.txt for Windows
~/Library/Application Support for MacOS
If you can’t find the file, I recommend setting the OAuthSettingsLocation in the jar itself.

11. Copy OAuthSettings.txt to the Sisense server:

C:\OAuthSettings.txt for Windows deployments.
/opt/sisense/storage/data/OAuthSettings.txt for Linux deployments.

12. Copy cdata.jdbc.googleanalytics.jar to the Sisense server. You can check the following documentation for an exact step-by-step guide. For example:

/opt/sisense/storage/data/GoogleAnalytics - for Linux
C:\Program Files\Sisense\DataConnectors\JVMContainer\Connectors\CData JDBC Driver for Google Analytics 2022\lib - for Windows

13. Connect to Google Analytics using Sisense:

Use the connection string from step 9, but change the OAuthSettingsLocation: jdbc:googleanalytics:AuthScheme=OAuth;InitiateOAuth=GETANDREFRESH;OAuthClientId=clientId;OAuthClientSecret=clientSecret;OAuthSettingsLocation="/opt/sisense/storage/data/OAuthsettings.txt";_persist_oauthrefreshtoken=oauthRefreshToken;_persist_oauthexpiresin=3599;_persist_oauthaccesstoken=oauthAccessToken;_persist_oauthtokentimestamp=XXXXXXXXXXXXX;
Jar’s folder (the one you copied the jar file into in the step 12):
1. /opt/sisense/storage/data/GoogleAnalytics
2. C:\Program Files\Sisense\DataConnectors\JVMContainer\Connectors\CData JDBC Driver for Google Analytics 2022\lib
Driver class name: cdata.jdbc.googleanalytics.GoogleAnalyticsDriver

Please keep in mind that the CDATA table/column structure is different from Sisense’s native drivers which means that you might need to redesign your data models.

To learn about the CDATA table/column structure and the nuances of using the connector, I highly recommend checking out the following CDATA documentation:

CData JDBC Driver for Google Analytics - CData JDBC Driver for Google Analytics

Conclusion:

If you were worried about Sisense’s announcement deprecating the native Google Analytics connector, don’t worry! This practical article shows users how to use the CDATA driver to connect to and keep using Google Analytics as a data source.