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

Once you have connected to Facebook, you can import a variety of tables from multiple Facebook APIs.

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

Installing the Facebook Connector

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

To install the Facebook connector:

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

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

facebook1

Connecting to the Facebook REST API

To access Facebook’s REST API from Sisense, you must provide Sisense with valid Oauth Facebook credentials in a connection string. These credentials are provided by Facebook when you create an application.

Creating an App

To obtain the OAuth client credentials, follow the steps below:

  1. Log into Facebook and navigate to https://developers.facebook.com/apps.
  2. Click + Add a New App.
  3. Define your app’s name and click Create App ID. The Product Setup page is displayed.
  4. In the Product Setup page, click Settings.
    fbsettings
  5. In the Settings page, the OAuth client credentials, App Id and App Secret, are displayed.
    fbsettings1
  6. In the App Domains field, enter localhost.
  7. In the Settings page, click + Add Platform and select Website. The Website area is added to the Settings page.
  8. In the Site URL field, enter http://localhost. This value is represents the callback URL in your connection string where the user will return with the token that verifies that they have granted your app access.
    fbsettings2
  9. In the bottom-right corner of the Settings page, click Save Changes. Facebook requests that you authenticate your account.
  10. Enter your password to authenticate your account and click OK. Your app is saved.

Authenticating through Facebook

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

  • OAuthClientId: Set this to the App ID in your app settings.
  • OAuthClientSecret: Set this to the App Secret in your app settings.
  • CallbackURL: Set this to the Return URL 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.

Note: When switching accounts, you must sign out of your account and then sign it with the new account.

When you connect the Sisense connector completes the OAuth process:

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

Adding Facebook Tables to your ElastiCube

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

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

jdbc:Facebook: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. Sisense generates this file automatically after you connect to Facebook successfully. 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:facebook:OAuthClientId=11276856774486;OAuthClientSecret
=064c70d78567jm2b7e7e4224fad;InitiateOAuth=GETANDREFRESH;Version=2.8;CallbackURL=http://localhost/;

To add Facebook data:

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

Switching between Accounts

When you connect to the Facebook data source, Sisense saves your OAuth values in the file OAuthsettings.txt file located at …\Users\xxx\AppData\Roaming\CData\Facebook Data Provider on your Sisense server. To connect to the Facebook 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 OAuthSettingsLocation parameter. 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 Facebook 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:facebook:OAuthSettingsLocation=C:\facebook\auth\john.txt;OAuthClientId=11276856774486;OAuthClientSecret
=064c70d78567jm2b7e7e4224fad;InitiateOAuth=GETANDREFRESH;Version=2.8;CallbackURL=http://localhost/;

jdbc:facebook:OAuthSettingsLocation=C:\facebook\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:\facebook\auth\.

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

Facebook Tables

The table below describes the tables you can import from various Facebook APIs. Facebook limits the number of calls and the amount of data that you can retrieve from their APIs. These limitations depend on which Facebook API you are using and your account type. When you reach a limit, Sisense returns the error provided by Facebook. For example, if you receive any of the following error codes, this usually indicates that you have reached one of Facebook’s limitations:

  • error code = 4, CodedException
  • error code = 32, CodedException
  • error code = 17,  User request limit reached

If you encounter one of these errors, refer to the Facebook API that exposed the table for more details regarding that API’s limitations.

Note: Some Facebook tables require custom SQL, the table below provides examples of custom SQL statements you can use to access the relevant table.

Available Tables

NameDescription
CommentsQuery the Comments for a Target. Comments may also be inserted based on a Target or deleted based on Id.

When querying comments, either the Target or the Id of the comment must be specified. For example, to retrieve all the comments about a specific post, your SELECT statement could look something like this:

SELECT * FROM Comments WHERE Target = '15526475270_410830705612736'

Alternatively, you can specify the Id to retrieve a specific comment. For example: SELECT * FROM Comments WHERE Id = '15526475270_410830705612736_5193593'
LikesQuery the Likes for a Target. Alternatively, lists Pages that the specified User or Page Likes. Authentication is required to use this table.
MilestonesQuery a list of Milestones for the given Page. Milestones may only be inserted, updated, or deleted when authenticating as the target Page. Unless you are using the option Authenticate As Page, you need to supply a Target when retrieving milestones. For instance:

SELECT * FROM Milestones WHERE Target='facebook'
PostsQuery the Posts for a Target based on either the Target or Id. Posts can also be inserted based on a Target, or deleted based on Id. This table requires authentication.
UsersQuery Users by SearchTerms or Id.

 

Available Views

NameDescription
AdAccountsQuery the Ad Accounts available for a User. Accessing Ad Account information requires the ads_read permission.
AdCreativesQuery information about an Ad Creative or the Ad Creatives on a specific Ad Account, Ad Set, or Ad. Accessing Ad Creative information requires the ads_read permission.
AdInsightsQuery an Ad Report. Accessing Ad Report information requires the ads_read permission.

When requesting AdInsights, a Target must be specified. This indicates what element to retrieve the insights from. It can be an AdAccount, Campaign, AdSet, or an Ad. For example:

view source SELECT * FROM AdInsights WHERE Target = 'act_123456'

A date range can be specified using DateStart and DateEnd, or DatePreset must also be specified. For example:

view source SELECT DateStart, DateEnd, AdAccountId, Spend, Impressions FROM AdInsights WHERE Target = 'act_123456' AND DateStart >= '01/01/2015' AND DateEnd <= '03/31/2015'

view source SELECT DateStart, DateEnd, AdAccountId, Spend, Impressions FROM AdInsights WHERE Target = 'act_123456' AND DatePreset='last_90_days'

The available values for DatePreset are:

  • today
  • yesterday
  • this_week
  • last_week
  • last_7_days
  • last_14_days
  • last_28_days
  • last_30_days
  • last_90_days
  • this_month
  • last_month
  • this_quarter
  • last_3_months


The TimeIncrement can used to specify how many days should be included in each report row. For instance:

view source SELECT DateStart, DateEnd, AdAccountId, Age, Spend, Impressions FROM AdInsights WHERE Target = 'act_123456' AND DatePreset='last_90_days' AND TimeIncrement='7' view source SELECT DateStart, DateEnd, AdAccountId, Age, Spend, Impressions FROM AdInsights WHERE Target = 'act_123456' AND DatePreset='last_90_days' AND TimeIncrement='monthly'

The Level column can be used to specify what level insights are retrieved at. This can be set to ad,adset,campaign, or account. For example: view source SELECT DateStart, DateEnd, AdAccountId, Age, Spend, Impressions FROM AdInsights WHERE Target = 'act_123456' AND Level='campaign'
AdInsightsActionsBreackdown of AdInsights to Actions and enables filtering on ActionAttributeWindows.
AdsQuery information about an Ad or the Ads in a specific Ad Set, Campaign, or Ad Account. Accessing Ad Information requires the ads_read permission.
AdScheduledReportsQuery the Scheduled Reports for a given Ad Account. Accessing Scheduled Report information requires the ads_read permission.
AdSetsQuery information about an Ad Set or the Ad Sets on a specific Campaign or Ad Account. Accessing Ad Set information requires the ads_read permission.
AlbumsQuery Albums associated with a Target. Accessing Album information typically requires the user_photos permission.
ApplicationsQuery the Application specified by the Id.
BooksQuery the Books a User is interested in. Accessing Book information typically requires the user_books permissions.
CampaignsQuery information about a Campaign or the Campaigns on a specific Ad Account. Accessing Campaign information requires the ads_read permission.

For example:

SELECT * FROM Campaigns WHERE Target = 'act_123456'
EventsQuery the Events for a Target based on either the Target or SearchTerms. May require the user_events permission.
FriendsQuery Friends of the authenticated User or Target User. Requires the user_friends permission. A Target may be specified to request Friend information for, but Friends may only be retrieved for the authenticated User or Friends of the authenticated User that use the same Facebook app.
GamesQuery the Games a User is interested in. Accessing Game information may require the user_likes and user_interests permissions.
GroupsQuery the Groups based on the supplied SearchTerms, Id, or Target. Groups may require the user_groups permission.
LeadValuesQuery information about a lead. Accessing Lead Information requires the ads_read permission.
MusicQuery the Music a User is interested in. Accessing Music requires the user_likes and user_interests permissions.
PagesQuery the Pages based on the supplied SearchTerms or Id.
PermissionsQuery the Permissions the User has granted the current application.
PhotosQuery Photos associated with a Target. Accessing Photo information typically requires the user_photos permission.
PlacesQuery the Places based on the supplied SearchTerms or Id. Places are stored as Pages in Facebook.
ScheduledReportRunsQuery a list of the recently completed runs of a given Scheduled Report. Accessing Scheduled Report information requires the ads_read permission.
TaggedByQuery information about Posts, Statuses, Photos, and other entities that have tagged the User or Page. This view is a derivative of the Wall connection where only entries that have tagged the Target User or Page will be returned. In general it is only available for Pages.
TelevisionQuery the TV shows a User is interested in. Accessing Television information may require the user_likes and user_interested permissions.
VideosQuery Videos from a Target. Normally requires the user_videos permission.
WallQuery Posts from the Wall of a Target.