Connect to PayPal Using ODBC

Starting in MicroStrategy ONE Update 12, a PayPal ODBC driver is used to connect to PayPal to import data on Web and Workstation, rather than a JDBC driver.

If you are using MicroStrategy ONE Update 11 or earlier, see Connect to PayPal Using JDBC.

PayPal is an online payment system that supports online money transfer, invoice sending and receiving, and payment transactions.

PayPal is a SaaS data source so it does not provide many query processing capabilities except for some filtering. The PayPal ODBC driver supports standard SQL-92 syntax and functions but is limited in write back and performance. MicroStrategy will support in-memory data import and choose to process data on our side.

Data Retrieve Mode: ODBC Call Only
Data Import supported: Select Tables, Free Form SQL, Query Builder (In-memory)

ODBC Connection URL

MicroStrategy ships the PayPal ODBC driver to connect PayPal as a data source with the following connection methods. You can find them on the data source dialog when importing data.

Using OAuth 2.0 authentication client ID and client secret
DRIVER={MicroStrategy PayPal ODBC Driver}; host=[host];port=[port]; Auth_Client_ID=[Client_ID]; Auth_Client_Secret=[Client_secret]; Auth_Type=OAuth 2.0;
Using access token
DRIVER={MicroStrategy PayPal ODBC Driver}; host=[host]; port=[port]; Auth_Type=OAuth 2.0; Auth_AccessToken=[Access_Token];

See How to Get PayPal Connection String Parameters for steps to acquire the correct parameters.

PayPal may impose a rate limit if it detects abusive traffic patterns. Go to the PayPal website for more information.

ODBC Schema Tables

PayPal stores data in structures which do not follow the rules of traditional tables and columns. The ODBC drivers map PayPal data into its designed relational schema 'PayPal' with fixed schema tables. The mapping method is complicated, so lower performance is expected when querying tables with large amounts of data.

Configure PayPal using ODBC on Web and Workstation

Workstation
Web

Add an environment connection and log in to Workstation.
Click Data Source under Administration.
Find and choose PayPal.
Expand Default Database Connector and choose Add New Database Connection.
Enter the required information.

In the Authentication Mode drop-down, choose Access Token or Standard:
- Access Token: Enter the required fields Name and Access Token.
- Standard: Enter the required fields Name, Client ID, and Client Secret.
Click Save.
In the Add Data Source dialog, choose a Project to assign and access the data source.

Click Save.

In-Memory Data Import

After creating and connecting to a PayPal data source, you can retrieve data through ODBC and then publish a cube for dashboards, reports, and documents.

The examples below are using the Select Tables import option.

In the left Navigation pane, click next to Datasets.
Choose a Project and select Data Import Cube.

Click OK.
Click on the data source to connect to PayPal.
If needed, click Prepare Data to wrangle your data or rename columns.
Click Finish.
Select Import as an In-memory Dataset.

Click Create > Add External Data.

Find and choose PayPal.
Choose Select Tables, Type a Query, or Build a Query to import data.
It is recommended to use Select Tables to reduce joins on the driver side for best performance.
In the Data Source interface, select DSN-less Data Source and choose PayPal for both Database and Version. Use one of the following methods to configure the DSN-less connection:
- Standard Connection: Enter the required fields Host Name, Port Number, Client ID, and Client Secret.
- Access Token: Enter the required fields Host Name, Port Number, and Access Token.
- Edit the Connection String: Click Advanced Settings and select Edit connection string. Enter the connection information of the ODBC connection string to connect.
Click Save.
Click on the new Data Source to connect.

In-Memory Data Import

After creating and connecting to a PayPal data source, you can retrieve data through ODBC and then publish a cube for dashboards, reports, and documents.

The examples below are using the Select Tables import option.

Click your PayPal data source and select the tables you want to import.
If needed, click Prepare Data to wrangle your data or rename columns.
Click Finish.
Select Import as an In-memory Dataset Mode to publish a cube.

How to Get PayPal Connection String Parameters

Prepare a sandbox or a live developer account that has access to API call.
For Standard authentication, remove https:// from the host name.
In this case:
- Sandbox (Host) Link: sandbox.paypal.com
- Developer Link: developer.paypal.com
Create business and personal accounts from Sandbox > Accounts > Create Account.
Click Sandbox > Generate Sandbox Access Token to link to your business sandbox account and generate an Access Token. Click your sandbox account to see the access token list.
Click My Apps & Credentials.
Enter the app name and link it to your Sandbox Developer account.
Click Create App. Open the app from the My Apps & Credentials page to view the Client ID and Client Secret for the app.
Open the app from the My Apps & Credentials page to view the Client ID and Client Secret for the app.

If your access token does not work, you can use the Client ID and Client Secret generated above to create a short-term access token using the Postman app by following the instructions below. This access token will expire 8 hours after creation.

Generate an Access Token with Postman

Download the Postman app.
Create a POST request with the following information:
- POST to URL: https://api.sandbox.paypal.com/v1/oauth2/token
- Authorization: Basic Auth
- Username: Client ID
- Password: Client Secret
In the Headers tab, enter the following information:
- Accept: application/json
- Accept-Language: en_US
- Content-Type: application/x-www-form-urlencoded
In the Body tab:
- Select the application/x-www-form-urlencoded option
- grant_type: client_credentials
Click Send.