The Sisense Facebook connector is a standalone connector that allows you to import data from various Facebook APIs into the ElastiCube Manager without any custom queries. With just a few clicks, you can import tables directly from the Facebook API into your ElastiCube Manager.

Sisense supports two versions of the Facebook connector, the version described on this page and an earlier version described here. The difference between this version and the earlier version is that this version does not require custom SQL when importing tables, but does require Sisense V6.6.1 or higher.

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.

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.

Breaking Changes for the Marketing API Version 2.8

As of July 25, 2017, Facebook no longer supports their Marketing API version 2.8. Currently, all users whose Facebook app is v2.8 will receive an error. To overcome this issue, add the parameter ‘Version=2.10’ to the connection string.

For more information about the latest version of the Marketing API, see Facebook’s blog posts.

Prerequisites

  • The Facebook connector requires Sisense V6.6.1 and later.
  • This connector is compatible with previous Facebook connectors. The following tables have been moved to the Facebook Ads connector: AdAccounts, AdCreatives, Ads, AdScheduledReports, AdSets, Campaigns, AdInsights, AdInsightsActions to continue to use these connectors in an existing ElastiCube, see Changing Facebook Connectivity Settings.

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. After you install the Facebook connector, it is added to your list of Web Service 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

Adding Facebook Tables to your ElastiCube

Sisense uses connection strings to connect to Facebook and import data into the ElastiCube Manager. The default connection string for Exact is: jdbc:facebook:InitiateOAuth=GETANDREFRESH;

After you connect using the default connection string, Sisense redirects you to Facebook’s Login page where you log in and your account is authenticated. Once the account is authenticated, the relevant tables can be added to your ElastiCube.

If you want to customize your connection string, you can by creating a Facebook app and passing additional connection string parameters. For more information, see Creating an App at the end of this page.

Once you have connected to the Facebook datasource, Sisense displays a list of Facebook tables you can import. For a list of supported tables, see Facebook Tables.

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 the connection string: jdbc:facebook:InitiateOAuth=GETANDREFRESH;
  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.

Tip: Facebook leverages the Facebook Graph API, however, this API includes user request limitations. To overcome these limitations, try to reduce the date range or query fewer tables for a single build. For more information, see Facebook’s Graph API limitations.

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
GroupsThe Groups table returns information about the groups of the user defined in the connection string. Groups may require the user_groups permission.
GroupMembershipsThe GroupsMemberships table returns all members of all groups for the user defined in the connection string. Groups may require the user_groups permission. GroupMemberships in Facebook are the individual members of a given group.
AlbumsThe Albums table returns a information about albums associated with a user defined in the connection string. Accessing Album information typically requires the user_photos permission.
ApplicationsThe Application table returns information about a user’s applications.
BooksThe Books table returns information regarding books a user is interested in. Accessing Book information typically requires the user_books permissions.
EventsThe Events for a user based on either the Target or SearchTerms. May require the user_events permission.
UsersThe Users table returns information about a user according to SearchTerms or Id.
PagesThe Pages table returns the pages of a user.
PostsThe Posts table for a user 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.
Page_PostsThe Page_Posts table returns information regarding the posts for all pages for a user.
Post_CommentsAll Post_Ccomments table returns information regarding all the posts of a user.
Comments_For_Page_PostsAll Comments_For_Page_Posts table returns information regarding all the comments of a page post by a user.
Album_CommentsThe Album_Comments table returns information regarding comments for a user’s albums.
Photo_CommentsThe Photo_Comments table returns information regarding comments for a user’s photos.
Video_CommentsThe Video_Comments table returns information regarding comments for a user’s videos.
MyVideosThe MyVideos table returns information regarding videos for a user. Videos in Facebook are videos that have been uploaded by a user or to a page on Facebook. Normally requires the user_videos permission.
MyLikesThe Likes table returns information regarding a user’s likes. Likes in Facebook represent the users that like a particular target. The target may be a post, page, picture, or other type of valid entity that may have likes associated with it.
Post_LikesThe Post_Likes table returns information regarding posts a user liked.
Page_LikesThe Page_Likes table returns information regarding pages a user liked.
Photo_LikesThe Photo_Likes table returns information regarding photos a user liked.
MilestonesThe Milestones table returns information regarding milestones for the given Page. Milestones may only be inserted, updated, or deleted when authenticating as the target Page.
FriendsThe Friends table returns information regarding the friends of a user. Requires the user_friends permission.
GamesThe Games table returns information regarding games a user is interested in. Accessing Game information may require the user_likes and user_interests permissions.
InsightsByConsumptionTypeAllows retrieval of insights by consumption type.

For examples and more information regarding this table, see Facebook Insights
InsightsByFeedbackTypeAllows retrieval of insights by feedback type.

For examples and more information regarding this table, see Facebook Insights
InsightsByLikeSourceTypeAllows retrieval of insights by like source type.

For examples and more information regarding this table, see Facebook Insights
InsightsByPaidStatusAllows retrieval of insights by paid status.

For examples and more information regarding this table, see Facebook Insights
InsightsByReactionTotalsAllows retrieval of insights by like source type.

For examples and more information regarding this table, see Facebook Insights
InsightsByStoryTypeAllows retrieval of insights by like story type.

For examples and more information regarding this table, see Facebook Insights
InsightsByTabTypeAllows retrieval of insights by tab type.

For examples and more information regarding this table, see Facebook Insights
SimpleInsightsAllows the retrieval of simple insights with a single value in the response.

For examples and more information regarding this table, see Facebook Insights
SimpleVideoInsightsAllows the retrieval of simple video insights with a single value in the response.

For examples and more information regarding this table, see Facebook Insights
VideoInsightsByActionTypeAllows the retrieval of video insights by story action type.

For examples and more information regarding this table, see Facebook Insights
VideoInsightsByDistributionTypeAllows the retrieval of video insights by distribution type.

For examples and more information regarding this table, see Facebook Insights
VideoInsightsByReactionTypeAllows the retrieval of video insights by reaction type.

For examples and more information regarding this table, see Facebook Insights
MoviesThe Movies table returns information regarding movies a user is interested in. Accessing Movie information requires the user_likes and user_interests permissions.
MusicThe Music table returns information regarding music a user is interested in. Accessing Music requires the user_likes and user_interests permissions.
PhotosThe Photos table returns information regarding a user’s photos. Accessing Photo information typically requires the user_photos permission.
Album_PhotosThe Album_Photos table returns information regarding a user’s album photos. Accessing Album information typically requires the user_photos permission.
Event_PhotosThe Event_Photos table returns information regarding a user’s photos from an event. May require the user_events permission.
Page_PhotosThe Page_Photos table returns information regarding a user’s photos from any of their pages.
User_PhotosThe Photos table returns information regarding a user’s photos. Accessing Photo information typically requires the user_photos 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.
WallQuery Posts from the Wall of a Target.
PermissionsQuery the Permissions the User has granted the current application.
Likes_For_Page_PostsLikes of my posts on pages.

 

Note: For the following tables: InsightsByConsumptionType, InsightsByFeedbackType, InsightsByLikeSourceType, InsightsByPaidStatus, InsightsByTabType, SimpleInsights, the Object Insights period {day, week, month, lifetime} lifetime is not supported for all measures. For more information, see Facebook’s API documentation.

 

Creating an App

To access Facebook’s data from Sisense with a customized connection string, you must provide valid Facebook credentials in a connection string.

This section describes how to create a Facebook app to retrieve the necessary authentication parameters for the connections string and then how to construct the connection string.

Note: If you have already created a connection string for the Facebook Ads connector, you can use the same credentials in the Facebook connection string.

The credentials you need to connect to your data in Facebook are provided by Facebook when you create a Facebook app through the Facebook Developer site.

For more information, see Creating an App in Facebook.

Creating a Connection String

Sisense uses connection strings to connect to Facebook and import data into the ElastiCube Manager. Each connection string contains authentication parameters that the data source uses to verify your identity and what information you can export to Sisense.

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

After you have obtained the relevant credentials, you can create the connection used to connect to your Facebook account. The Facebook connection string has the following structure:

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

The following is an example of a Facebook connection string:

jdbc:facebook:OAuthClientId=112345383774486;OAuthClientSecret=064c524478d712534b7e7e4224fad;
InitiateOAuth=GETANDREFRESH;Version=2.10;CallbackURL=http://localhost/;Timeout=0;ThrottleSeconds=15;ThrottleMaxPercent=90;
Other=RetryWaitTime=60000;MaximumRequestRetries=5;

The example above includes both mandatory and optional parameters you can provide in the connection. The required parameters are emphasized in bold.

Mandatory Parameters

  • 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.

Optional Parameters

  • Aggregate Format: The format aggregate or collection columns should return in.
  • Authenticate As Page: The name or Id of a page to authenticate as when making requests to Facebook.
  • Auto Cache: The AutoCache property controls automatic caching of data.
  • Cache Connection: The connection string for the cache database.
  • Cache Driver: The database driver to be used to cache data.
  • Cache Location: Specifies the path to the cache when caching to a file.
  • Cache Metadata: Whether or not to cache the table metadata. The cache will be stored in memory unless CacheConnection or CacheLocation are set.
  • Callback URL: The OAuth callback URL to return to when authenticating. This value must match the callback URL you specify in your app settings.
  • Firewall Password: A password, if authentication is required to connect through a firewall.
  • Firewall Port: The TCP port for the firewall FirewallServer — see the description of the FirewallServer option for details.
  • Firewall Server: Specify a firewall name or IP address to authenticate requested connections, if necessary.
  • Firewall Type: The type of firewall to connect through.
  • Firewall User: The user name to authenticate with the firewall.
  • Initiate OAuth: Set this property to initiate the process to obtain or refresh the OAuth access token when you connect.
  • Location: A path to the directory that contains the schema files defining tables, views, and stored procedures.
  • Logfile: A path to the log file.
  • Max Log File Size: A string specifying the maximum size in bytes for a log file (ex: 10MB). When the limit is hit, a new log is created in the same folder with the date and time appended to the end.
  • MaximumRequestRetries: The maximum amount of request retries.
  • OAuth Access Token: The access token for connecting using OAuth.
  • OAuth Client Id: The client Id assigned when you register your application with an OAuth authorization server.
  • OAuth Client Secret: The client secret assigned when you register your application with an OAuth authorization server.
  • OAuth Settings Location: The location of the settings file where OAuth values are saved when InitiateOAuth is set to GETANDREFRESH or REFRESH.
  • Offline:Use offline mode to get the data from the cache instead of the live source.
  • Other: The other parameters necessary to connect to a data source, such as username and password, when applicable.
  • Pagesize: The maximum number of results to return per page from Facebook.
  • Proxy Auth: Scheme The proxy server authorization scheme (default: BASIC).
  • Proxy Auto Detect: This indicates whether to use the default system proxy settings or not. Set ProxyAutoDetect to FALSE to use custom proxy settings. This takes precedence over other proxy settings.
  • Proxy Password:A password, if authenticating with a proxy server.
  • Proxy Port: The TCP port for the proxy ProxyServer (default: 80).
  • Proxy Server: If a proxy server is given, then the HTTP request is sent to the proxy instead of the specified server.
  • Proxy SSL Type: The SSL type to use when connecting to the proxy server (default: AUTO).
  • Proxy User: A user name, if authentication is to be used for the proxy.
  • Pseudo Columns: Indicates whether or not to include pseudo columns as columns to the table.
  • Query Cache: The time duration, in seconds, for which the in-memory cached data is reused.
  • Readonly: You can use this property to enforce read-only access to Facebook from the provider.
  • RetryWaitTime: The amount of time in milliseconds to wait before retrying to query the Facebook API.
  • RTK: The runtime key used for licensing.
  • Search Terms: Default SearchTerms if none are specified. Used for some tables, such as Users, where SearchTerms may be specified.
  • SSL Server Cert: The certificate to be accepted from the server when connecting using TLS/SSL.
  • Support Enhanced SQL: If set to true, the provider will cache the entire table in memory and issue the current query against the memory cache. Allows queries with aggregate functions that are otherwise unsupported. The entire memory cache is discarded after the query is executed, unlike QueryCache.
  • Tables: Comma-separated list of tables to be listed. For example: Tables=TableA,TableB,TableC.
  • Target: A default target if none is specified. Used for some tables, such as Comments, where a target may be specified.
  • ThrottleSeconds: Indicates how many seconds to wait until sending another insight request when the ThrottleMaxPercent has been met.
  • ThrottleMaxPercent: The threshold set for throttling further insight requests. The defaults for ThrottleSeconds and ThrottleMaxPercent are 10 and 95.
  • Timeout:The value in seconds until the timeout error is thrown, canceling the operation.
  • Verbosity: The verbosity level that determines the amount of detail included in the log file.
  • Version: The Facebook Graph API version to use.
    You can see your version in the Facebook Developer dashboard. See the Facebook Platform Changelog to see if your version is still supported.

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.10;CallbackURL=http://localhost/;

jdbc:facebook:OAuthSettingsLocation=C:\facebook\auth\sally.txt;OAuthClientId=11276856774486;OAuthClientSecret

=064c70d78567jm2b7e7e4224fad;InitiateOAuth=GETANDREFRESH;Version=2.10;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.

Changing Facebook Connectivity Settings

Prior to Sisense V6.6.1, Facebook Ads tables were part of the Facebook Connector. Sisense has created a new Facebook Ads connector that supports tables specific to Facebook Ads. These tables include:

AdAccounts, AdCreatives, Ads, AdScheduledReports, AdSets, Campaigns, AdInsights, AdInsightsActions

You can continue to use earlier versions of the Facebook connector to connect to these tables, but this may require custom SQL queries. To connect to these tables quickly and easily without any custom SQL, you need to install the Facebook Ads connector and change the connectivity settings to direct these tables to the Facebook Ads connector.

For more information on how to change connectivity settings after installing the Facebook Ads connector, see Changing Connectivity Settings.

Accumulative Builds

Sisense support accumulative builds for all numeric and dates data types. However, the data must be sorted before building the ElastiCube.

Versions

VersionRelease DateImprovements
6.6.1.113June 12, 2017New version of Graph API v2.9 Changelog
New views added: InsightsByReactionTotals, SimpleVideoInsights, VideoInsightsByActionType, VideoInsightsByDistributionType, VideoInsightsByReactionType
New insights added for the InsightsByConsumptionType view: POST_CONSUMPTIONS_BY_TYPE, POST_CONSUMPTIONS_BY_TYPE_UNIQUE
For more information about Version 6.6.1.113 including Breaking Changes, see Version 6.6.1.113 below.
6.6.0.74March 16, 2017JSON columns in the AdInsights table are available for querying in AdInsightActions by using the ActionCollection column
6.6.1.65March 9, 2017In some scenarios, getMetaData() returned unexpected results
6.6.0.52March 6, 2017Dynamic Targets
New table: GroupMemberShips

Version 6.6.1.113

Breaking Changes

In the latest version of the Facebook Graph API, permissions have been largely reworked. All of the friends* permissions have been removed. In addition, most permissions require Facebook approval if you wish to request information about other user accounts who are not an administrator of the Facebook App. Sisense recommends reviewing the new permissions listed by Facebook here:

https://developers.facebook.com/docs/facebook-login/permissions/v2.1

In addition, users may reject requested permissions in the Authorization dialog on Facebook. If this happens, the specific Permissions requested will state that they are declined under the Status column. The permission may be rerequested by executing GetOAuthAccessToken and setting AuthType to rerequest.

If you want to keep using an old version of the API, you can by simply setting the Version connection property in the connection string to the specific version you were using and also setting the Location to a folder where you have saved an older copy of the schema files (.rsd and .rsb files).

If you want to update your app to 2.9, then you don’t have to do anything

Note: Sisense removed few metrics from the AdInsights / AdInsightsActions due to their removal by Facebook in 2.9.

A number of various changes have been made across tables due to changes in Facebook. These changes are listed below:

  • Facebook no longer allows public posts to be searched. We have had to remove SearchTerms from the Posts table because of this.
  • Checkins have been removed from the Graph API. Due to this, we have removed the Checkins table.
  • Events may no longer be inserted, updated, or deleted via the Graph API. We have had to change the Events table to a view.
  • Usernames for Facebook Users may no longer be used as a target and are no longer returned. The Facebook username for Pages is unaffected.
  • User ID’s for Facebook Users will be different across different Facebook Apps. This is due to the new policy of App-scoped User ID’s.