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. Once done click OK to close.
   

7. If you have v1.9 or higher then expand Other Properties and change below two settings. These settings needed if you like to import Salesforce CDP Objects in Table mode in tools like Power BI, Excel, MS Access… etc. These tools generate some custom SQL behind the scene it may contain unsupported syntax so below settings will help.
   Set Identifier quote chars to None
   Set Catalog/Schemas support to NotSupported

9. Click Test Connection see it works?
10. Go to Preview Tab to query some data (Enter a simple query and click Preview Data button

Load Salesforce CDP data into Power BI (need v1.9 and higher)

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

Load Multiple Tables in Power BI

NOTE: If you have Version v1.8 or older, then Salesforce CDP Driver Table import mode in Power BI may not work. In that case you have two options. You can either update the JDBC Bridge driver to the latest version (1.9 or higher) or use the Query Mode (see next section) during the connection / import data wizard step.

Load Query in Power BI

If you like to import data using custom query then during load expand following textbox and enter your Salesforce CDP query.