Connect HubSpot CRM to Power BI via OData

Why connect HubSpot CRM to Power BI?

In Power Query, rename the OData table HubSpot_Deals to Deals before using the DAX measure below. HubSpotDeal is the backend model name, not the feed table name. Stage IDs and open/closed classifications can differ by portal; adapt the example filter to your pipeline.

HubSpot provides built-in dashboards, but growing organizations quickly reach limitations when sales data must be combined with financial ERP data (such as Exact Online or Twinfield) or project software (such as Bouw7 or Robaws). Leadership and commercial managers need a consistent overview in Power BI of sales funnels, outstanding deals, pipeline value, and realized revenue.

Without an automated data connection, this leads to weekly manual spreadsheet exports. That costs time, creates version conflicts, and makes recurring executive reporting prone to errors. An OData connection via DigiData structures CRM data and makes tables directly available for Power BI.

Technical architecture: OAuth and scheduled synchronization

Querying the HubSpot REST API directly from Power Query presents significant challenges: OAuth 2.0 token refreshes, strict pagination, and daily API call limits.

DigiData separates this process into two distinct stages:

1. Authorization in the DigiData portal: You connect your HubSpot account once via official OAuth 2.0. DigiData requests strictly bounded read scopes (oauth, crm.objects.contacts.read, crm.objects.companies.read, crm.objects.deals.read). No owner permissions (owners.read) or custom schema permissions are requested.
2. Background synchronization: DigiData periodically retrieves the selected data and stores it in an isolated tenant database. Interactive dashboard refreshes in Power BI therefore never directly impact your HubSpot API limits during business hours.

Downstream OData feed and Power BI authentication

After synchronization, DigiData serves the data via a secure OData feed. In Power BI Desktop, configure the connection as follows:

1. Open Power BI Desktop, select Get Data, and choose OData feed.
2. Paste the unique DigiData feed URL from your dashboard.
3. For authentication, select Basic. Enter your DigiData OData API key in the Password field. The username can be any non-empty value (such as apikey), as DigiData validates the key in the password field and ignores the username.
4. Click Connect. The tables appear immediately in the Navigator.

Supported data entities and scope

DigiData's HubSpot synchronization focuses deliberately on the three central CRM core entities:

• Deals (HubSpotDeal): Includes deal name (Name), order amount (Amount), pipeline identifier (Pipeline), stage identifier (Stage), expected close date (CloseDateUtc), external ID (ExternalId), and timestamps.
• Companies (HubSpotCompany): Includes company name (Name), domain name (Domain), city (City), country (Country), sector (Industry), and phone number (Phone).
• Contacts (HubSpotContact): Includes first name, last name, email address, phone number, job title (JobTitle), and company name (Company).

[!NOTE] Important scope boundary: The feed delivers current snapshot records. There is no historical pipeline stage table, no automatic probability weighting, and no calculation of time-in-stage included in the data layer. You configure this analytical logic according to your needs in Power BI.

Data modeling, relationships, and reporting grouping

Because the source dataset does not supply a relational link table between deals and companies, model relationships in Power BI based on the available identifying attributes:

1. Stage and pipeline grouping: The fields Pipeline and Stage contain technical HubSpot IDs (such as pipeline = "default" and stage = "contractsent"). Create a simple reference dimension (Dim_Stage) in Power BI with columns for the technical Stage ID, display name (e.g. "Proposal Sent"), and a sort order (SortOrder 1 through 5). Connect this to the deals table for an intuitive funnel.
2. Time horizon and cohorts: Connect CloseDateUtc to a central date dimension (Calendar) to visualize quarterly forecasts and monthly conversion rates.

Worked practical example with explicitly fictional deal data

The following table provides a worked practical example showing how the deals table arrives structured in Power BI and how it can be aggregated. All mentioned company names, amounts, and dates are explicitly fictional and serve solely to illustrate calculations:

In Power BI, define concise, powerful DAX measures based on this table:

• Open Pipeline Value: Total Pipeline = CALCULATE(SUM(Deals[Amount]), Deals[Stage] IN {"qualifiedtobuy", "presentationscheduled", "contractsent"})
• Expected Quarterly Closing: filter on CloseDateUtc within the current reporting quarter.

Scheduled refresh in Power BI Service

After publishing the report to Power BI Service, configure scheduled refresh:

• Navigate to the semantic model settings and open Data source credentials.
• Select Basic authentication, enter your DigiData API key as the password, and set privacy level to Organizational.
• Microsoft documents in the official OData connector guide that Basic authentication in Power BI Service depends on host and gateway support. Refer to Microsoft Learn guidelines for credential troubleshooting.
• Schedule the refresh after DigiData completes its daily synchronization.

Summary

By connecting HubSpot to Power BI via DigiData, you combine sales metrics directly with other business-critical reports. You work with current, structured tables without manual exports or fragile custom scripts. Learn more about the HubSpot integration or explore Power BI reporting.