The Sisense Twitter connector is a standalone connector that allows you to import data from Twitter’s API into the ElastiCube Manager. After you have downloaded and installed the connector, you can connect through a connection string you provide Sisense in the ElastiCube Manager. The connection string is used to authenticate users who connect to Twitter’s API. To obtain a connection string, you will need to create a Twitter app.

Once you have connected to Twitter, you can import a variety of tables from the Twitter API.

This page describes how to install the Twitter connector, how to connect to Twitter with a connection string, and what tables you can import into the ElastiCube Manager:

Installing the Twitter Connector

Sisense provides the Twitter connector as a standalone connector that you can download and add to your list of default Sisense connectors.

To install the Twitter connector:

  1. Download the Twitter installation file.
  2. Open the installation file and click Install.
  3. After the installation process is complete, click Close.

The Twitter connector is displayed in the ElastiCube Manager under Add Data > Web Services.

twitter

Connecting to the Twitter REST API

To access Twitter’s REST API from Sisense, you must provide valid Oauth Twitter credentials. These credentials are provided by Twitter when you create an application.

Creating an App

You can follow the steps below to obtain the OAuth client credentials:

  1. Log in to https://dev.twitter.com/apps.
  2. Click Create New App.
  3. Enter the name, description, and website to be displayed to users when they connect.
  4. Define the callback URL setting.If you are making a Web app, set the callback URL to the URL you would like users to be returned to after they have granted your application privileges.If you are making a desktop application, set the callback URL to http://localhost/.

Once you have created the app, you will be redirected to a page with information on your app.

If you intend to communicate with Twitter only as the currently authenticated user, then you can obtain the OAuthAccessToken and OAuthAccessTokenSecret directly from this page on Twitter. The OAuthAccessToken and OAuthAccessTokenSecret are listed under the OAuth Settings in the Your Access Token section. You can then connect by setting these connection string properties and do not need to continue to the other steps.

Otherwise, if you need to generate access tokens for other user accounts besides the one you used to create the app on Twitter, use the consumer key and consumer secret in the following steps.

Authenticating through Twitter

After setting the following connection properties, you are ready to connect:

  • OAuthClientId: Set this to the consumer key in your app settings.
  • OAuthClientSecret: Set this to the consumer secret in your app settings.
  • CallbackURL: Set this to the callback URL you specified in your app settings.
  • InitiateOAuth: Set this to GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken and OAuthAccessTokenSecret.

When you connect the driver opens the OAuth endpoint in your default browser. Log in and grant permissions to the application. The driver then completes the OAuth process:

  1. Extracts the access token from the callback URL and authenticates requests.
  2. Refreshes the access token when it expires.
  3. Saves OAuth values in OAuthSettingsLocation to be persisted across connections.

Adding Twitter Tables to your ElastiCube

Sisense uses connection strings to connect to Twitter and import data into the ElastiCube Manager.

The connection string to connect to Twitter has the following structure:

jdbc:twitter:Property1=Value1;Property2=Value2;

Mandatory properties are OAuthClientId, OAuthClientSecret, and InitiateOAuth.

  • OAuthSettingsLocation: The location of the settings file where OAuth values are saved for each user when InitiateOAuth is set to GETANDREFRESH. See Switching between Accounts for more information.
  • OAuthClientId: Set this to the App ID in your app settings.
  • OAuthClientSecret: Set this to the App Secret in your app settings.
  • InitiateOAuth: Set this to GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken connection property.

An example of a connection string:

jdbc:twitter:OAuthSettingsLocation=D:\twitter\auth\user.txt;OAuthClientId=xxxxxxxxxxxxx;OAuthClientSecret=xxxxxxxxxxxxxxxxx;InitiateOAuth=GETANDREFRESH;CallbackURL=http://localhost/;Timeout=0;

Note: To switch between accounts, you need to delete the file OAuthsettings.txt file located at …\Users\xxx\AppData\Roaming\CData\Twitter Data Provider.

To add Twitter data:

  1. In ElastiCube Manager, click Add Data and then, Twitter. The Connect to Twitter window is displayed.
    twitter2
  2. In Datasource Connection String, enter your connection string.
  3. Click Connect to Server. Twitter is displayed in the Select Database list.
  4. Click OK. Sisense connects to Twitter and displays a list of tables available for you to import.
  5. Select the relevant tables and click Add. The tables are displayed in the ElastiCube Manager.

Switching between Accounts

When you connect to the Twitter data source, Sisense saves your OAuth values in the file OAuthsettings.txt file located at …\Users\xxx\AppData\Roaming\CData\Twitter Data Provider on your Sisense server. To connect to the Twitter data source with another user on the same machine, you must delete the OAuthsettings.txt file. Sisense will then generate a new file for that user.

Another option to support multiple users is to define the location and file name of an OAuthsettings file for each unique user in your connection string through the OAuthSettingsLocationparameter. When each user connects to the data source, Sisense generates the OAuth file with the file name you specify in the location you define. In the examples below, two users are allowed to access the Twitter data source and for each user, Sisense generates a file that contains that user’s OAuth values in the location defined in the string.

jdbc:Twitter:OAuthSettingsLocation=C:\Twitter\auth\john.txt;OAuthClientId=11276856774486;OAuthClientSecret
=064c70d78567jm2b7e7e4224fad;InitiateOAuth=GETANDREFRESH;Version=2.8;CallbackURL=http://localhost/;

jdbc:Twitter:OAuthSettingsLocation=C:\Twitter\auth\sally.txt;OAuthClientId=11276856774486;OAuthClientSecret
=064c70d78567jm2b7e7e4224fad;InitiateOAuth=GETANDREFRESH;Version=2.8;CallbackURL=http://localhost/;

In the example above, to OAuth files are created, one for John and one for Sally in the location C:\Twitter\auth\.

This is useful if you support many users who each need to access the Twitter data source.

Twitter Tables

The Sisense Twitter connector allows you to import the following tables into the ElastiCube Manager.

NameDescription
DirectMessagesReceivedQuery and delete direct messages received by the authenticated user.
DirectMessagesSentSend direct messages and query messages sent by the authenticated user.
FavoritesCreate, delete, and query a list of favorite tweets of the authenticated user and allow the user to favorite new tweets or remove existing favorites.
FollowingCreate, delete, and query a list of users that the current Twitter account is following, otherwise known as friends.
TweetsCreate, delete, and query status updates and tweets from the authenticated user.
AccountSettingsQuery account settings about the currently authenticated user.
FollowersQuery a list of users following the current Twitter account.
ListMembersQuery the members of a specified list.
ListsQuery Twitter list information based on a set of criteria.
ListSubscribersQuery the subscribers to a specified list.
MentionsQuery a list of retweets of the authenticated user.
RetweetsQuery a list of retweets of the authenticated user.
TrendsQuery the daily trending topics from Twitter.
UsersQuery a list of users based on the SearchTerms, Id, or Screen_Name.