How to connect Salesforce CDP in Power BI (Use JDBC-ODBC Bridge Driver)

[nayan]

If you like to connect Salesforce CDP (Cloud Data Platform) in some apps like Power BI or other ODBC Compliant apps such as Informatica, Python, Crystal Reports then try steps listed in this post.

Prerequisite

1. Download [official JDBC driver for Salesforce CDP (Cloud Data Platform) from here] and extract to some folder e.g. c:\jdbc\salesforce-cdp
2. Download and Install ZappySys ODBC PowerPack.

Create OAuth App (Get ClientId and ClientSecret)

In order to use Salesforce CDP JDBC Driver you will need ClientId and ClientSecet (It doesnt support the Traditional UserId and Password method) here is how to obtain ClientId and Secret

1. Follow these steps to register Salesforce App and obtain Client ID / Secret (for API Call / OAuth). Change steps a little bit to include the following permissions. You will need the following permissions

api
full
cdp_profile_api
cdp_query_api
refresh_token, offline_access

NOTE: You can exclude refresh_token, offline_access, full after some testing see it works for your case

Here is one more article [Connect Tableau Server to the Salesforce Data Cloud - Tableau]

2. Once you configured the app it may look like below. Click on manage credentials to obtain clientid and client secret which we will need later.
   
   

Configure JDBC-ODBC Connection

1. Create OAuth App for Salesforce and Obtain ClientId and ClientSecret with steps Here’s the documentation for setting up a connected app with OAuth.

2. Open ODBC Data Sources (x64):
   

3. If you want access for yourself then stay of User DSN Tab. If you want grant access other users then go to the System DSN tab. For SQL Server Integration go to System Tab and add new System DSN rather than User DSN. Click New button.
   

4. From the Driver list Select ZappySys ODBC Driver. For this example select [ZappySys JDBC Bridge Driver].
   

5. Enter the following values on JDBC Bridge UI
   Connection String: jdbc:queryService-jdbc:https://login.salesforce.com
   JDBC Driver File(s): c:\some-folder\Salesforce-CDP-jdbc-x.y.z.jar
   Username/password: <optional and may not be needed>
   Connection Parameters: Click Edit button to add 2 key/value pairs - ClientId and ClientSecret
   

   

6. Add ClientId and ClientSecert Like below
   

7. Click Test Connection see it works?

8. Go to Preview Tab to query some data (Enter a simple query and click Preview Data button

Load Salesforce CDP data into Power BI

Once DSN is created you can now use DSN in Power BI. Click here to learn how to connect JDBC Driver in PowerBI

NOTE: Salesforce CDP Driver doesnt work with Table mode in Power BI so you will have to use Query Mode as below during connection / import data wizard step. Future version of ZappySys JDBC Bridge might support table import mode but for now try below when prompted.