Power BI - Dataverse Connector verses OData Connector

Colin Maitland, 04 February 2022

In this article, I will describe some of the differences when using the Dataverse instead of the Dynamics 365 (Online), i.e., OData, connector in Power BI to retrieve data from Dynamics 365 (Online).

When creating Power BI data models, I use a template data model that connects to Dynamics 365 (Online) and retrieves a set of commonly used tables and records such as Accounts, Contacts and Opportunities. This data model also includes some calculated tables and columns in addition to those retrieved from Dynamics 365 (Online). These include a Date table and various custom columns.

To date, I have used the Dynamics 365 (Online) connector, i.e., the OData connector, which uses the HTTP REST API. However, I am now transitioning to using the Dataverse connector which uses the TDS (Tabular Data Steam) endpoint.

Both the OData and the Dataverse connectors can be used for Power BI Data Flows and Power BI Data Models.

Some Benefits

The biggest benefit of using the Dataverse connector is the time required to load data. The data is retrieved from Dynamics 365 using the TDS (Tabular Data Stream) endpoint. Use of this endpoint is very fast in comparison to using the Dynamics 365 (Online) connector.

My Power BI template data model takes 3.5 minutes to load sample data from Dynamics 365 when using the OData connector but only 1.3 minutes when using the Dataverse connector. On a large customer deployment, the data model was taking many hours to load the data, but by converting the queries in the Power BI data flows and data model to use the Dataverse connector this was reduced to 20 minutes.

Another benefit is that you can configure Incremental Refresh if using the Dataverse connector. This is explained in the Date and Time Column sections of this article.

Finally, there are several benefits regarding related record columns and option set columns. These are described in the Lookup Column and Option Set Column sections of this article.

Some Limitations

Compared to using the OData connector, the Dataverse connector is limited to retrieve no more than 80 MB of data per query.

Comparison

The following is a comparison of the two connectors in some specific areas, especially with respect to different types of columns.

Source

The connection string is simpler, i.e., CommonDataService.Database("acme.crm6.dynamics.com") versus OData.Feed("https://acme.api.crm6.dynamics.com/api/data/v9.1").

When using OData.Feed, I use three parameters to build the connection string, but I only need to use one when using CommonDataService.Database.

This image shows the Source Step in a Power BI query using the Dataverse connector and a single parameter.

This image shows the Source Step in a Power BI query using the OData connector and three parameters.

Table Names

Table names are singular rather than plural, e.g., ‘opportunity’ instead of ‘opportunities’.

Date and Time Columns

Date and Time columns such as the ‘createdon’ column for Opportunities, are retrieved as Date/Time data type columns rather than Date/Time/Zone data type columns.

This has the benefit that Incremental Refresh can be configured using the Dataverse connector. This could not be done using the OData connector because Incremental Refresh requires Date/Time rather than Date/Time/Zone data type columns to be used for its configuration.

However, with both connectors, the returned Dates and Times are in the UTC Time Zone. For both connectors, I use a custom function to convert the Date and Time to New Zealand Local Date and Time. My custom functions take New Zealand Daylight Saving into account. When using the Dataverse connector, I use a custom function named DateTimeUTCToDateNZLT() and when using the OData connector, I use a custom function named DateTimeZoneUTCToDateNZLT().

This image shows the ‘createdon’ Date/Time column when loaded using the Dataverse connector.

This image shows the ‘createdon’ Date/Time/Zone column when loaded using the OData connector.

This image shows these dates after they have been converted from the UTC Time Zone to the NZ Local Time Zone using one of my custom functions.

Lookup Columns

There is no need to use an Expand Record Column step to get the name of a parent record, such as the Parent Account for an Opportunity.

The Dataverse connector provides separate columns for both the id and the name of the parent record in addition to a column for the parent record, e.g., parentaccountid, parentaccountidname and account(parentaccountid) respectively.

The OData connector only provides the id column in addition to a column for the parent record, e.g., ‘_parentaccountid_value’ and ‘parentaccountid’.

You will also notice that there are some column name differences; e.g. ‘parentaccountid’ versus ‘_parentaccountid_value’ for the id column, and ‘parentaccount’ versus ‘account(parentaccountid)’ for the parent record column.

Not using Expand Record Column steps in your queries equates to significant data load performance improvements. However, if you want to retrieve additional columns from a parent record, then with both connectors you will need to expand the relevant parent record column to obtain these.

This image shows an example of the Parent Account columns for an Opportunity provided by the Dataverse connector. The ‘account(parentaccountid)’ column can be expanded to access other columns from the Parent Account.

Polymorphic Lookup Columns

A Customer lookup field in Dynamics 365 is a common example of a polymorphic lookup field. Another example is the Regarding field on Activities. The Customer field allows the user to lookup and store the id of a related parent Account or Contact. The Regarding field allows the user to lookup and store the id a range of related parent records such an Accounts, Contacts, Leads and Opportunities.

When using the Dataverse connector the following columns are provided for a Customer lookup, ‘customerid’, ‘customeridname’, ‘account(customerid)’ and ‘contact(customerid)’. The ‘account(customerid)’ and ‘contact(customerid)’ columns can be expanded to obtain other columns from the related parent Account or Contact.

When using the OData connector only the ‘_customerid_value’, ‘customerid_account’ and ‘customerid_contact’ columns are provided. You need to use an Expand Column Step to expand either the ‘customerid_account’ or ‘customerid_contact’ columns to obtain the name of the Customer.

If retrieving the Regarding column for Activities, then when using the Dataverse connector, only the ‘regardingobjectid’ and ‘regardingobjectidname’ columns are retrieved. Except for email records, expandable regarding related record type specific columns such as ‘account(regardingobjectid)’, ‘contact(regardingobjectid)’ and ‘lead(regardingobjectid)’ are not yet available. However, such expandable record type specific columns for all regarding related record type specific columns are available using the OData connector.

Multi-Select Option Set (Choices) Columns

There is no difference between the two connectors with how multi-select option set (i.e., choices) columns are retrieved. Both connectors return a single column that contains a comma delimited list of the selected Option Set Values.

I then use a custom function to transform these into a comma delimited list of Option Labels. This function retrieves the corresponding Option Set Label for the Option Set and each Option Set Value from a manually configured Option Set table in the data model. The custom function can be used to transform the existing column or to add an additional custom column.

This image shows an example of a column named ‘mag_colourcodes’ that contains the Option Set Values for three selected colour options.

This image shows an example of the same column after the comma delimited Option Set Values have been converted using a custom function into their corresponding Option Set Labels with the addition of a space before each subsequent option.

Single Select Option Set (Choice) Columns

There is no need to add additional steps to obtain the Option Set Label for Option Set Columns such as the Rating column for Opportunities.

The Dataverse connector provides separate columns for the Option Set Value and the Option Set Label; e.g. ‘opportunityratingcode’ and ‘opportunityratingcodename’.

When using the OData connector, only the Option Set Value column is provided. I therefore load that column and then add a custom column using a custom function named LookupOptionSetLabel() to retrieve the corresponding Option Set Label for the Option Set and Option Set Value from a manually configured Option Set table in the data model.

This additional time-consuming step and maintenance of the Option Set table is not required when using the Dataverse connector. Not using such a function equates to significant data load performance improvements.

This image shows an example of the Rating columns for an Opportunity provided by the Dataverse connector.

Status and Status Reason Columns

These are the same as Option Set columns. The same differences and benefits exist when using the Dataverse connector as those previously described for Option Set columns.

This image shows an example of the Status and Status Reason columns for an Opportunity provided by the Dataverse connector.

Two Option Columns

If you simply want to retrieve and use the values True and False for these columns, such as the ‘Is Revenue System Calculated’ column for Opportunities, there is no difference between the two connectors. However, if you want to transform the True/False values into their equivalent user-friendly values such as Yes/No, Allow/Do Not Allow, Send/Do Not Send, System Calculated/User Provided etc., the same differences and benefits exist when using the Dataverse connector as those previously described for Option Set columns.

This image shows an example of the ‘Is Revenue System Calculated’ columns for an Opportunity provided by the Dataverse connector.

Conclusion

In conclusion, when connecting Power BI to retrieve data from Dynamics 365 (Online) or from the Common Data Model use of the Dataverse rather than the OData connector is now my default choice. I have upgraded my template Power BI data model accordingly.

However, because of the 80 MB per query limitation, there will still be the need to use another approach for those queries that exceed that limitation. The options include limiting the number of records and columns that are retrieved, using either the Common Data Service (Legacy) or the OData connector instead of the Dataverse connector, or even perhaps using multiple Dataverse queries that each retrieve a non-overlapping subset of the records being retrieved before then being appended into a single final query withing the data model. You might possibly also consider using Incremental Refresh if the Incremental Refresh for the Archived and Refreshed records do not individually exceed the 80 MB per query limitation.

Note: The Common Data Service (Legacy) connector will be depreciated in the future.