Connecting to Box
The Sisense Box connector is a standalone connector that allows you to import data from Box’s API into the ElastiCube. After you have downloaded and installed the connector, you can connect through a connection string you provide Sisense. The connection string is used to authenticate users who connect to the Box APIs. To obtain a connection string, you will need to register a Box app.
Once you have connected to Box, you can import a variety of tables from the Box API.
This topic describes how to install the Box connector, how to connect to Box with a connection string, and what tables you can import into the ElastiCube:
- Installing the Box Connector
- Connecting to the Box REST API
- Adding Box Tables to your ElastiCube
- Box Tables
Sisense provides the Box connector as a standalone connector that you can download and add to your list of default Sisense connectors.
To install the Box connector:
- Download the Box installation file.
- Open the installation file and click Install.
- After the installation process is complete, click Close.
The Box connector is displayed in Sisense under Add Data > Web Services.
To access Box’s REST API from Sisense, you must provide valid Oauth Box credentials through a connection string. These credentials are provided by Box when you create an application.
After you receive your credentials from Box, you can create the connection string and provide Sisense with it to connect to your data.
Creating an App
You can follow the procedure below to register an app and obtain the OAuth client credentials, the OAuthClientId and OAuthClientSecret:
- Log in to your Box developers dashboard and click Create a Box Application.
- If you are making a desktop application, set the Redirect URI to http://localhost:33333 or a different port number of your choice. If you are making a web application, set the Redirect URI to a page on your Web app you would like the user to be returned to after they have authorized your application. The OAuthClientId and OAuthClientSecret are also displayed in the same page.
- Select the scope of user permissions your app will request.
Sisense uses connection strings to connect to Box and import data into Sisense. Connection strings contain authentication parameters that the data source uses to verify your identity and what information you can export to Sisense.
Box requires the following parameters that you receive when registering an app with Box:
- OAuthClientId: The client ID assigned when you register your application with an OAuth authorization server.
- OAuthClientSecret: Set this to the App Secret in your app settings.
- CallbackURL: The OAuth callback URL to return to when authenticating. This value must match the callback URL you specify in your app settings. Set this to http://localhost.
- InitiateOAuth: Set this to GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken connection property.
- Timeout: The value in seconds until the timeout error is thrown, cancelling the operation. Set to 0 to disable time outs.
For more information, see Connection String Parameters.
Connection strings have the following structure:
The following is an example of a Exact connection string:
If you have any issues connecting to your data source, see Troubleshooting JDBC Data Connectors.
To add Box data:
- Sisense, click Add Data and then, Box. The Connect to Box window is displayed.
- In Datasource Connection String, enter your connection string.
- Click Connect to Server. Box is displayed in the Select Database list.
- Click OK. Sisense connects to Box and displays a list of tables available for you to import.
- Select the relevant tables and click Add. The tables are displayed in Sisense.
Box’s RESTful APIs expose the following Box tables that you can import into Sisense through the Sisense Box connector:
|Collaborations||Create, update, delete, and query the available Collaborations in Box.|
|Comments||Create, update, delete, and query the available Comments in Box.|
|Files||Update, delete, and query the available Files in Box.|
|Folders||Create, update, delete, and query the available Folders in Box.|
|Groups||Create, update, delete, and query the available Groups in Box.|
|Memberships||Create, update, delete, and query the available Memberships in Box.|
|TaskAssignments||Create, update, delete, and query the available Task Assignments in Box.|
|Tasks||Create, update, delete, and query the available Tasks in Box.|
|TrashedItems||Delete and query the available TrashedItems in Box.|
|Users||Create, update, delete, and query the available Users in Box.|
|SharedItems||Query the available shared items in Box.|
|UserEvents||Query the available Events in Box.|
Aggregate functions are not supported.
To query the Collaborations table, specify an Id, FolderId, or a GroupId.
SELECT * FROM Collaborations WHERE Id = '123'
To select from the Tasks table you need to specify an Id or an ItemId.
SELECT * FROM Tasks WHERE Id = '123'
To select from the TasksAssignments table you need to specify an Id or a TaskId.
SELECT * FROM TasksAssignments WHERE Id = '123'
To select from the Memberships table you need to specify an Id or a GroupId.
SELECT * FROM Memberships WHERE Id = '123'
To select from SharedItems specify the Shared Link that points to the item.
SELECT * FROM SharedItems WHERE SharedLink = 'www.url.com'
If the SharedItem has a password also specify the password.
SELECT * FROM SharedItems WHERE SharedLink = 'www.url.com' AND SharedLinkPassword = 'password'
To query the Comments table specify an Id or a FileId.
SELECT * FROM Comments WHERE Id = '123'
Due to these limitations, the ElastiCube cannot be built without custom SQL.