How to read WooCommerce data in Power BI using ODBC

Introduction

WooCommerce is one of the most widely used eCommerce platforms for WordPress websites. Store owners and analysts often need to combine WooCommerce order, product, and customer data with other business metrics in Power BI for reporting and decision-making. Using Zappysys ODBC Driver for REST APIs, you can connect WooCommerce to Power BI and query API data using SQL without custom coding.

This tutorial demonstrates how to configure WooCommerce ODBC connectivity and load WooCommerce data into Power BI for interactive dashboards.

Prerequisites

1. A WordPress site with WooCommerce installed.
2. Microsoft Power BI Desktop is installed on your machine.
3. Basic knowledge of ODBC and SQL queries.
4. Finally, install the ZappySys ODBC Driver.

Steps

Generate WooCommerce API Credentials

1. Sign in to your WordPress admin dashboard.
2. Go to WooCommerce > Settings > Advanced > REST API.
3. Click Add key.
4. Enter a description (for example, Power BI Integration), select a user, and set permissions to Read or Read/Write as needed.
5. Click Generate API key.
6. Copy the Consumer Key and Consumer Secret, then store them securely.
   

   Woocommerce – Get API key

Create a new ODBC DSN using ZappySys JSON Driver

1. Search for ODBC in the Windows Start menu and open ODBC Data Source Administrator.
   
2. In the User DSN or System DSN tab, click Add.
3. Select ZappySys JSON Driver and click Continue.
   

Configure WooCommerce API Connection

1. In the DSN configuration window, enter the base endpoint in Base URL:
   
   https://your-website.com/wp-json/wc/v3/orders

   1	https://your-website.com/wp-json/wc/v3/orders

2. Set Connection Type to HTTP and click to configure.
3. Select Basic Auth (Username/Password).
4. Set Username to your WooCommerce Consumer Key.
5. Set the password to your WooCommerce Consumer Secret.
6. Click OK to save the connection.
   

   Woocommerce – ODBC HTTP connection

7. Set HTTP Request Method to GET.
8. Set JSON Path Filter to:
   
   $.data[*]

   1

   $.data[*]

9. Click Test Connection to verify connectivity, then click OK to save the DSN.
   

   Woocommerce – ODBC JSON Driver configuration

Preview WooCommerce Data in ODBC Driver

1. Open the DSN and go to the Preview tab.
2. Click Preview Data to validate that WooCommerce order rows are visible.
3. If rows are not returned, check the store URL, credentials, SSL, and API permissions.
   

   Woocommerce – ODBC Preview data

Connect Power BI to WooCommerce via ODBC

1. Open Power BI Desktop.
2. Click Get Data from the Home ribbon.
3. Click More…, search for ODBC, and click Connect.
   

   Power BI – Get data from ODBC connection

4. Select your WooCommerce ODBC DSN from the dropdown.
5. Optionally, enter a custom SQL query in Advanced options.
6. Click OK, then click Load to import data.
   

   Woocommerce – Power BI select connection

7. Verify the result and click Load.
   

   Woocommerce – Power BI loads the data

8. In the Navigator window, select the columns you want to load.
   

   Woocommerce – Power BI final result

Conclusion

By connecting WooCommerce to Power BI through the ZappySys ODBC REST/JSON driver, you can quickly turn raw store API data into interactive dashboards. This approach simplifies integration by allowing you to use SQL for data extraction and Power BI for modelling and visualisation.

Explore the full capabilities of the Zappysys ODBC Drivers to integrate multiple data sources and build comprehensive Power BI dashboards. By combining GitHub data with other sources, you can create unified views of your operational data.

Need Help?

If you encounter any issues, our support team is here to help:

1. Live Chat — Use the chat widget (bottom-right corner of this page)
2. Email — support@zappysys.com
3. Support Center — Visit the ZappySys Support Portal