The CData Power BI Connector for HubSpot models HubSpot CRM API v3 objects as relational Tables, Views, and Stored Procedures. This section describes API limitations and requirements; you can use the SupportEnhancedSQL feature, set by default, to circumvent most of these limitations.

Tables

Tables describes a sample of the available standard tables. The standard table definitions and custom tables are dynamically retrieved during runtime; here, we show the sample table definitions for the standard HubSpot Objects. Note that Custom Objects are exposed as tables with a "__c" appended to the end of the name.

You can find more detailed information for the available HubSpot Custom Objects by querying the views: CustomObjects, CustomObjectProperties and CustomObjectAssociations.

You can Create new HubSpot Custom Objects (tables) or Update and Delete existing ones via the stored procedures: CreateCustomObject, UpdateCustomObject and DeleteCustomObject. Additionally, you can add, update or remove Custom Properties (columns) to both Standard and Custom HubSpot Objects (tables) through the stored procedures: CreateCustomProperty, UpdateCustomProperty and DeleteCustomProperty.

The HubSpot Data Provider exposes the HubSpot Object Associations as tables, through which the user can retrieve and manage the associated records between different object types. By default, and if the ExpandAssociations connection property is false, one associations table is exposed for each HubSpot object with available associations (ex. DealAssociations, CompanyAssociations, ContactAssociations, etc.). In case the ExpandAssociations connection property is true, the driver exposes one associations table for each association between two HubSpot object types (for example, CompaniesToContactsAssociations, ContactsToDealsAssociations, DealsToLineitemsAssociations, etc.). Refer to ContactAssociations and ContactsToDealsAssociations for a simple guide with examples on how to get the associated records and add new ones.

To retrieve and add between a custom object type and other objects types (standard or custom objects) the user can utilize the table CustomObjectAssociations.

DELETE statements are not supported on association tables for the HubSpotV3 schema, but they are supported on the HubSpotV4 schema. On the other hand, UPDATE statements on association tables are not supported in either schema.

Stored Procedures

Stored Procedures are function-like interfaces to the data source. You can use them to search, update, and modify information in the data source.

CRM API V3 improvements (compared to V1 and V2)

• Filtering capabilities for most important tables. Supported operators include =, !=, >, <, <=, >=.
• Filtering using AND and OR combinations. A maximum of 2 OR operators are supported server-side, with each of these operators supporting up to 2 AND operators inside of them.
• Sorting capability for most important tables. Only 1 column is supported to be sorted by the API.
• Support for 'Batch' operations (Batch INSERT, Batch Update, Batch Delete).
• Support for Creating, Updating and Deleting Custom HubSpot Objects (tables) and Properties (columns).

CRM API V3 limitations

• PageSize cannot be bigger than 100.
• Filtering is available only when the number of rows to be returned is less than 10,000. If there are more rows to be returned for the query then all rows will be retrived first and filtering is then applied on them by the connector
• When issuing either filtering requests or sorting requests, no more than one request/second is allowed.
• A few columns (such as "CreatedAt" and "UpdatedAt") do not support filters.
• The stored procedures to Create/Update Custom Objects may not work for the OAuth Authentication Scheme, since the OAuth apps are limited to the read-only access scope for Custom Objects. To work around this limitation, you can connect via a Private App Token.