The Sisense Box connector is a standalone connector that allows you to import data from Box’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 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 page 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 Manager:
- 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 the ElastiCube Manager under Add Data > Web Services.
To access Box’s REST API from the ElastiCube Manager, 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.
Registering 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.
Authenticating through Box
After setting the following properties, you are ready to connect:
- OAuthClientId: Set this to the Client Id in your app settings.
- OAuthClientSecret: Set this to the Client Secret in your app settings.
- CallbackURL: Set this to the Redirect 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.
When you connect the driver opens the OAuth endpoint in your default browser. Log in and grant permissions to the application. The driver completes the OAuth process.
- Extracts the access token from the callback URL and authenticates requests.
- Refreshes the access token when it expires.
- Saves OAuth values in OAuthSettingsLocation to be persisted across connections.
Sisense uses connection strings to connect to Box and import data into the ElastiCube Manager.
The connection string to connect to Box has the following structure:
The following is an example of a Box connection string:
Note: To switch between accounts, you need to delete the file OAuthsettings.txt file located at …\Users\xxx\AppData\Roaming\CData\Box Data Provider.
To add Box data:
- In ElastiCube Manager, 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 the ElastiCube Manager.
Switching between Accounts
When you connect to the Box data source, Sisense saves your OAuth values in the file OAuthsettings.txt file located at …\Users\xxx\AppData\Roaming\CData\Box Data Provider on your Sisense server. To connect to the Box 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 Box data source and for each user, Sisense generates a file that contains that user’s OAuth values in the location defined in the string.
In the example above, to OAuth files are created, one for John and one for Sally in the location C:\Box\auth\.
This is useful if you support many users who each need to access the Box data source.
Box’s RESTful APIs expose the following Box tables that you can import into the ElastiCube Manager 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, ElastiCubes cannot be built without custom SQL.
Hey! Was this article helpful?
Questions? Ask the community.