Model Context Protocol (MCP) finally gives AI models a way to access the business data needed to make them really useful at work. CData MCP Servers have the depth and performance to make sure AI has access to all of the answers.
Try them now for free →
Use CData Connect Cloud to Access HubSpot Data in Microsoft Power Pivot
Use CData Connect Cloud to connect to live HubSpot data in the Power Pivot business intelligence tool.
This article will explain how to use CData Connect Cloud to present HubSpot data as OData services and then consume the data in Microsoft Excel's Power Pivot business intelligence tool. Follow the steps below to retrieve and edit HubSpot data in Power Pivot.
About HubSpot Data Integration
CData provides the easiest way to access and integrate live data from HubSpot. Customers use CData connectivity to:
- Access HubSpot without worrying about API updates or changes..
- Access custom objects and fields in HubSpot with no extra configuration steps involved.
- Use SQL stored procedures to perform functional operations like uploading or downloading attachments, inserting engagements, and creating or deleting custom objects or fields.
Users frequently integrate HubSpot with analytics tools such as Tableau, Power BI, and Excel, and leverage our tools to replicate Workday data to databases or data warehouses.
To learn about how other customers are using CData's HubSpot solutions, check out our blog: Drivers in Focus: Simplified HubSpot Connectivity.
Getting Started
Connect to HubSpot from Power Pivot
To work with HubSpot data from Power Pivot, we need to connect to HubSpot from Connect Cloud, provide user access to the connection, and create OData endpoints for the HubSpot data.
(Optional) Add a New Connect Cloud User
As needed, create Users to connect to HubSpot through Connect Cloud.
- Navigate to the Users page and click Invite Users
- Enter the new user's email address and click Send to invite the user
![]( "Inviting a new user")
- You can review and edit users from the Users page
![]( "Connect Cloud users")
Add a Personal Access Token
If you are connecting from a service, application, platform, or framework that does not support OAuth authentication, you can create a Personal Access Token (PAT) to use for authentication. Best practices would dictate that you create a separate PAT for each service, to maintain granularity of access.
- Click on your username at the top right of the Connect Cloud app and click User Profile.
- On the User Profile page, scroll down to the Personal Access Tokens section and click Create PAT.
- Give your PAT a name and click Create.
![]( "Creating a new PAT")
- The personal access token is only visible at creation, so be sure to copy it and store it securely for future use.
Connect to HubSpot from Connect Cloud
CData Connect Cloud uses a straightforward, point-and-click interface to connect to data sources.
- Log into Connect Cloud, click Connections and click Add Connection
![]( "Adding a Connection")
- Select "HubSpot" from the Add Connection panel
![]( "Selecting a data source")
-
HubSpot uses OAuth to authenticate. Click "Sign in" to authenticate with HubSpot.
![]( "Authenticating with OAuth (Salesforce is shown).")
- Navigate to the Permissions tab in the Add HubSpot Connection page and update the User-based permissions.
![]( "Updating permissions")
Configure HubSpot Endpoints for Power Pivot
After connecting to HubSpot, create a workspace and virtual dataset for your desired table(s).
- Navigate to the Virtual Datasets page and click Add to create a new Workspace (or select an existing workspace).
![]( "The Virtual Datasets page.")
![]( "Adding a new Workspace.")
- Click Add to add new assets to the Workspace.
- Select the HubSpot connection (e.g. HubSpot1) and click Next.
![]( "Selecting an Asset.")
- Select the table(s) you wish to work with and click Confirm.
![]( "Selecting Tables.")
- Make note of the OData Service URL for your workspace, e.g. https://cloud.cdata.com/api/odata/{workspace_name}
With the connection and Workspace configured, you are ready to connect to HubSpot data from Power Pivot.
Import HubSpot Tables in Power Pivot
Follow the steps below to import tables that can be refreshed on demand:
-
In Excel, click Data -> Get Data -> From Other Sources -> From OData Feed
![]( "Starting the OData connection.")
-
Fill out the OData feed URL (e.g. https://cloud.cdata.com/api/odata/{workspace_name})
![]( "The URL of the OData endpoint for Connect Cloud.")
-
On the next screen, select Basic and configure the username and password. Password will be your PAT.
![]( "Authenticating with Connect Cloud")
-
Select the tables that you would like to import into Excel
![]( "Selecting tables (Salesforce is shown.)")
-
After selecting your table(s) and clicking Load, you should see the data appear in an Excel spreadsheet
![]( "Loading tables (Salesforce is shown.)")
-
Now, click the Power Pivot tab on the ribbon, and then select Add to Data Model.
![]( "Loading data into Power Pivot (Salesforce is shown.)")
This can now work with your HubSpot data in Power Pivot.
![]( "The Pivot Table that is consuming OData feeds from Connect Cloud. (Salesforce is shown.)")
This can now work with your HubSpot data in Power Pivot.
Free Trial & More Information
If you are interested in connecting to your HubSpot data (or data from any of our other supported data sources) from Power Pivot, sign up for a free trial of CData Connect Cloud today! For more information on Connect Cloud and to see what other data sources we support, refer to our CData Connect Cloud page.
