Setting Up the B2C Commerce Intelligence JDBC Driver

This section describes how to configure your JDBC driver and connect it to the B2C Commerce Data Lakehouse. The B2C Commerce Data Lakehouse is a centralized data repository that stores and organizes B2C Commerce Cloud transactional data, customer interactions, and business metrics in a structured format optimized for analytics and reporting. It serves as the foundation for the Commerce Intelligence JDBC Driver, providing access to pre-aggregated business data through standardized SQL queries.

The data lakehouse contains:

• Fact tables (for example, line items, orders, payments, inventory snapshots)
• Dimension tables (for example, products, customers, sites, dates, geographies)
• Aggregate tables (for example, daily sales summaries, search analytics, promotion performance)

This architecture enables business users to perform complex analytics on e-commerce data, supporting use cases like sales analytics, customer behavior analysis, inventory optimization, and marketing campaign effectiveness measurement.

1. Account Setup: Your current account typically has the necessary permissions to access data using the JDBC driver. No additional roles are required.
2. Role: Salesforce Commerce API
3. Tenant Filter: To authenticate successfully, you must add the appropriate tenant filter. You can specify multiple tenant IDs as needed.

The Commerce Intelligence JDBC Driver enables seamless connectivity to your B2C Commerce data lakehouse through a JDBC interface, ensuring compatibility with various SQL editors and data visualization tools.

There are two ways to download the Commerce Intelligence JDBC Driver:

Download the latest version directly from the Salesforce Commerce Cloud GitHub Repository. For detailed installation steps, refer to the README file in the repository.

Include the driver as a Maven dependency in your Java project. This is helpful if you're building a custom application and want to manage dependencies via Maven.

Add the following to your pom.xml:

<dependency>
    <groupId>com.salesforce.commerce.intelligence.jdbc.client</groupId>
    <artifactId>cip-client-dataconnector</artifactId>
    <version>[VERSION]</version> <!-- Replace with the latest version -->
</dependency>

Find the latest version on Maven Repository.

For demonstration purposes, this document uses DBeaver as an example SQL editor. However, the Commerce Intelligence JDBC Driver is compatible with any JDBC-compliant tool, such as SQL Workbench, DataGrip, or custom Java applications.

To use the driver in DBeaver:

1. Open DBeaver.
2. Go to Database → Driver Manager.
3. Click New.
4. Enter the Driver Name (for example, "CommerceIntelligenceDriver").
5. Specify the Driver Class: com.salesforce.commerce.intelligence.jdbc.client.CIPDriver.
6. Add the Driver JAR:
   1. Click the Libraries tab.
   2. Click Add File, and select the driver JAR file you downloaded.
   3. Click Find Driver.
7. Click OK.
8. Create a database connection:
   1. Navigate to Database → New Database Connection.
   2. Select the driver you registered and click Next.

The following properties must be configured to connect to your B2C Commerce data lakehouse:

Starting with B2C Commerce release 26.1, you can connect the B2C Commerce Intelligence JDBC Driver to non-production B2C Commerce environments. Supported non-production instances include:

• On-demand sandboxes (ODS)
• Development instances
• Staging instances
• Production instances in designated test realms (realms not serving live traffic)

To use the JDBC driver with a non-production instance, turn on the Enable Reports & Dashboards Data Tracking feature switch in Business Manager. For instructions, see Set Feature Switches (Toggles) in B2C Commerce. After turning on the feature switch, wait up to 2 hours for provisioning to complete.

For non-production environments, use the non-production JDBC URL instead of the production URL:

Replace {your_instance_id} with your actual instance identifier (for example, bjnl_dev or bjnl_stg).

You can also use the Commerce Intelligence JDBC Driver directly in your Java applications. The following sample code demonstrates how to connect and query your B2C Commerce data:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class JDBCExample {
    public static void main(String[] args) {
        String jdbcUrl = "jdbc:salesforcecc://jdbc.analytics.commercecloud.salesforce.com:443/bjnl_prd"; // Replace with your actual JDBC URL
        String username = "your-client-id"; // Replace with your Client ID
        String password = "your-client-secret"; // Replace with your Client Secret

        try {
            // Load the JDBC driver explicitly
            Class.forName("com.salesforce.commerce.intelligence.jdbc.client.CIPDriver");

            try (Connection connection = DriverManager.getConnection(jdbcUrl, username, password)) {
                System.out.println("Connection established successfully!");

                String query = "SELECT * FROM your_table_name"; // Replace with your actual query
                try (PreparedStatement preparedStatement = connection.prepareStatement(query);
                     ResultSet resultSet = preparedStatement.executeQuery()) {

                    while (resultSet.next()) {
                        System.out.println("Column 1: " + resultSet.getString(1));
                        System.out.println("Column 2: " + resultSet.getString(2));
                        // Add more columns as needed
                    }
                }
            }
        } catch (ClassNotFoundException e) {
            System.err.println("JDBC Driver not found. Ensure the Commerce Intelligence Driver JAR is in the classpath.");
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Keep the following best practices in mind when issuing queries to Commerce Cloud:

1. Use an index column with your queries to ensure optimal performance.
2. Rewrite your query against an aggregate table whenever possible because queries that target aggregate tables are significantly faster.
3. Never run queries like SELECT * FROM <TABLE> against tables with thousands of rows.
4. Design data extraction by using incremental processing techniques to retrieve data from large datasets in multiple batches. Primary keys generated with sequence numbers allow for incremental extraction of data.
5. Avoid SELECT * in JDBC queries. List only the columns you need so that queries remain stable when table schemas change.
6. Use LOWER() to normalize search terms in SQL and make results match SFCC dashboards.

The following non-functional limits and behaviors apply when using the B2C Commerce Intelligence connector. Please review these carefully to avoid unexpected errors or interruptions:

• Queries have a maximum execution time of 30 minutes.
• Any query exceeding this limit will be automatically terminated.
• To avoid timeouts, optimize your queries using indexing, aggregations, and batching techniques.

• SQL queries are case-sensitive.
• Make sure all table and column names match the exact casing used in the schema documentation.

• You can download up to 25 GB of data per day through the JDBC driver.
• The quota resets daily at 12:00 AM UTC.
• Once the limit is reached, further queries will be blocked until the quota resets.

• You can execute up to 10 queries per minute.
• Each query that returns more than 450,000 rows will count as multiple queries toward this limit.
• For example, a query that returns 900,000 rows will count as 2 queries.
• If you exceed the rate limit, additional queries will be temporarily throttled.

Here are some sample queries:

--
-- Sales Trend Query
--
SELECT
 "submit_date",
 SUM("num_orders") AS orders,
 SUM("num_units") AS units,
 SUM("std_revenue") AS gmv,
 SUM("std_total_discount") AS discounts,
 SUM("std_shipping") AS shipping,
 SUM("std_tax") AS tax
FROM
 "ccdw_aggr_sales_summary"
WHERE
 "submit_date" between ? AND ?
GROUP BY "submit_date"
ORDER BY "submit_date" DESC

---
--- Geography Analysis (Shipping Region)
---
SELECT
 g."region_name" AS region,
 SUM(quantity) AS units
FROM
 "ccdw_fact_line_item" AS li,
 "ccdw_dim_geography" AS g
WHERE
 li."shipping_geography_id" = g."region_id" AND
 li."utc_submit_timestamp" between ? AND ?
GROUP BY g."region_name"

---
--- Top Search Terms (Conversion)
---
SELECT
 sc."query" AS query,
 SUM(sc."num_searches") AS searches,
 SUM(sc."num_orders") AS search_orders,
 SUM(sc."std_revenue") AS search_gmv
FROM
 "ccdw_aggr_search_conversion" sc
WHERE
 sc."search_date" between ? AND ?
GROUP BY sc."query"
ORDER BY 2 DESC

Why do my custom queries give different results than the standard CC Reports and Dashboards (CCAC) reports?

When analyzing search data, you might see discrepancies when comparing results from the data warehouse tables accessed via the JDBC driver and reports viewed in CC Reports and Dashboards (CCAC). If you attempt to reproduce a specific CCAC report metric using a direct table query, be aware that your results might not align with the dashboard figures due to underlying proprietary logic. We recommend using established table definitions for custom reporting while acknowledging that reproducing proprietary dashboard metrics is not supported.

Is there any difference in data availability (time) in Reports and Dashboards vs via JDBC driver?

No. Data will be available at the same time in Reports and Dashboards as well as JDBC driver.

Do you plan to make more data available for access through JDBC driver?

Yes, more information will be shared closer to release time.

j

Announcements

B2C Commerce Storefront Options

B2C Commerce

Quick Start

Overview of B2C Commerce

Get Set Up

Build Your Site

Customize and Extend

Follow Best Practices

Test and Launch

Monitor and Maintain

Track Analytics

B2C Commerce Intelligence JDBC Driver

Setting Up the JDBC Driver

Business Use Cases

Revenue Field Guide and Calculation Logic

B2C Commerce Data Warehouse Schema Reference

SCAPI

Account Manager

Composable Storefront

Storefront Reference Architecture (SFRA)

B2C Commerce Solutions

B2C Commerce Platform Release Notes

Commerce Cloud Reference Information

Ask the Community