How to connect HubSpot in Power BI

ODBC PowerPack API Driver
1

Introduction

This guide will walk you through connecting Power BI to the Hubspot connector using the ZappySys ODBC API Driver for seamless data retrieval. The ZappySys SSIS Hubspot connector offers a powerful solution for integrating your Hubspot data into Power BI, enabling easier management and deeper analysis of your campaigns and subscribers.

Prerequisites

Steps

Create/Select an App from the Hubspot Developers Page

  1. Log in to your HubSpot account and use the account with Super Admin.

  2. Click the settings icon in the main navigation bar and go to Integrations > Private Apps.

  3. Select Create private app.

    HubSpot Create private app
    HubSpot Create private app824×676 33.2 KB

  4. Configure your app details in the Basic Info tab: Enter the app’s name, add a description (optional), and upload an image (optional).

  5. Navigate to the Scopes tab and select the appropriate read/write permissions for each scope.

  6. Click Create app in the top right corner to save your app configuration and obtain the token.

Create a New Driver

  1. Open the ODBC Data Source by typing “ODBC” in the search box and launching the ODBC Data Source.
    Open ODBC Data Source

  2. To gain access for yourself or other users, go to the User DSN or System DSN. For SQL Server integration, go to the System tab and add a new System DSN by clicking the “Add” button.
    ZappySys ODBC Driver - Open UI

  3. From the driver list, select ZappySys API Driver, then select Hubspot in the connector list and click Continue.

    Select Dropbox Connector
    Select Dropbox Connector625×506 50.3 KB

Hubspot Connector Configuration

  1. Enter the Token you generated earlier and test the connection

    Hubspot Connector
    Hubspot Connector804×633 46 KB

  2. Go to the Preview Tab, select any table, and preview the result. Press OK to save the configuration.

    Preview Data
    Preview Data804×633 40.4 KB

Read Hubspot Data in Power BI Using ODBC

  1. Open Power BI Desktop and click Get Data to retrieve data from an ODBC source.

    image
    image1094×577 34.4 KB

  2. In the Get Data window, search for “ODBC” and select it to connect to the ODBC data source.
    image

  3. Select the ODBC Data Source Name (DSN) you created earlier, such as HubspotxDSN, and click OK.
    image

  4. If prompted, authenticate using Windows Authentication or other credentials as required, then click Connect.

    image
    image695×364 18.1 KB

  5. Choose the Hubspot table or view you want to load, then click Load to import the data into Power BI.

    image
    image871×521 35.7 KB

  6. Use the imported Hubspot data to create Power BI reports, visualizing your marketing metrics.

    image
    image867×557 49.7 KB

Import Hubspot Data Using SQL Queries

If you prefer to import Hubspot data via SQL queries instead of selecting tables, you can do so by using Advanced Options in the import dialog:

  1. After selecting the DSN in the Get Data window, click Advanced Options to open the SQL Query editor.
    image

  2. Write the SQL query that targets the Hubspot data you wish to extract.

  3. Once the query is executed, the data will be loaded directly into Power BI for further analysis.

Considerations

  • Credential Management: Save your Hubspot API credentials in Power BI to avoid re-entering them each time you connect.
  • Data Refresh: Set up scheduled refreshes in Power BI to automatically update your Hubspot data.

Video Tutorial

Conclusion

Following these steps, you can efficiently connect Power BI to Hubspot using the ZappySys ODBC API Driver, enabling smooth data integration for reporting and analysis. If you encounter any issues or need further assistance, please get in touch with the ZappySys support team.

References

For more information, refer to the Hubspot connector article.