Comparing the three main ways to get Dynamics 365 data into Power BI

There are a few different ways to get your Dynamics 365 data into Power BI. Now that the Common Data Service connector has come out of preview and is generally available, I figured it would be a good time to have a look at the different methods and how they stack up.

The different direct methods are:

  • Dynamics 365 Online connector
  • Common Data Service (CDS) Connector
  • FetchXML (via Power Query Builder for XrmToolBox)

You can also use OData directly, but since this option doesn’t have any benefits over the other three, I am going to leave it out. The Data Export Service does have some benefits, but I will have to leave that for a different blog post.

The Dynamics 365 Online connector is probably the most commonly used option as it is the result you get when searching for “Dynamics 365” in Power BI’s Get Data dialog.

D365 legacy connector

The Common Data Service or CDS connector is newer and has some additional benefits. Primarily that you can include the display values on option sets. While it has been in preview for a while, it recently became generally available.

CDS connector
Make sure you search for “Common Data Service” and not “CDS”

The CDS Connector has two options when querying data. “Entities” and “System”.

CDS Connector options

When I talk about the CDS connector I’m referring to querying through the “Entities” option, as this area has the new features. Using “System” is identical to querying via the Dynamics 365 Online connector. Hence any features mentioned for that method also applies to the CDS Connector when using “System”. Just keep in mind that when using the CDS connector, it is either or. You cannot get the best of both methods in one query.

Note: If you are currently using the Dynamics 365 Online connector, I’d highly recommend switching to the CDS connector. Or alternately try out the next option.

FetchXML via the Power Query Builder is not available in Power BI, but is a tool in the XrmToolBox. It uses metadata from Dynamics 365/CDS to generate a Power Query string you can copy and paste into Power BI and do the transformations you need. The Power Query Builder has additional benefits beyond what is outlined in this post. Read more about those here.

PQB toolbox.jpg

This post compares the three methods on a couple of different areas. In particular, the areas that can cause some headaches for people working with Dynamics 365 data.

Those areas are:

  • Option Sets, Multi-Select Option Sets, and Two Option fields
  • Lookup fields
  • Customer, Owner, and Regarding fields
  • Querying from multiple entities
  • Filtering on multiple entities
  • Performance

Option Sets, Multi-Select Option Sets, and Two Option fields

Option Sets and its related fields types have probably been the most annoying field types to work with for people creating Power BI reports. This was because OData and the Dynamics 365 Connector only retrieved the database value of the option set rather than the label. I.e. “0” instead of “Open”, “1” instead of “Won”, etc. There’s a good reason for this, but it also meant that the person creating the data model needed to manually handle every single option set field by creating an option set table somewhere in the data-set and then replace the values with the labels.

Values only is an issue with the Dynamics 365 connector, but it has been solved in the CDS (Entities) connector, or by using FetchXML/Power Query Builder. However, the Common Data Service option will only include the display values for option sets. Not Multi-Select and Two Option fields.

Here’s a comparison of the different query methods on the same entity and fields. What varies is the type and content of columns I can get per field.

Dynamics 365 Online standard connector

Option Sets Dynamics 365 Standard
Two Option fields, Option Sets, Multi-Select Option Sets are available as database values only.

CDS connector

Option Sets CDS Connector
Display values included for Option Sets, but not Two Option fields, and Multi-Select Option Sets.

Power Query Builder/FetchXML

Option Sets FetchXML
Display values included for all types, Option Sets, Two Option fields, and Multi-Select Option Sets.

Winner: Power Query Builder/FetchXML

While the new CDS connector has display values for option sets, two options and multi-select option sets are not included. Only FetchXML will include the display values for all those types.

Lookup fields

Lookup fields had a different challenge. With the Dynamics 365 connector we had the option to retrieve the GUID of the record in the lookup field. It was also possible to select a field that represented the relationship and then “expand” it to show the name of the record or any other field(s) from the related record. While this was easy to do, it was a very expensive operation, meaning it would significantly slow down the data querying. It was considered best practice to include both tables and then create a relationship between in the data model.

Dynamics 365 Online Standard connector

Lookup Fields Dynamics 365 Standard
GUID value available for lookup field, along with expandable field which can be used to display the Account name. No name of record without expansion.

CDS connector

Lookup Fields CDS Entities
The CDS connector only brings in the GUID of the record. No name of record or expandable field.

Power Query Builder/FetchXML

Lookup Fields FetchXML
Both the GUID and the name of the record is automatically included.

Winner: FetchXML/Power Query Builder

FetchXML/Power Query Builder wins this as it includes the name of the record in the lookup field. This is particularly useful when you need the name of the record, but don’t necessarily need to include the entity in your data model.

Customer, Owner, and Regarding fields

The common denominator between these fields, is that the GUID can refer to a record in two or more entities. Customer is either a Contact or an Account. Owner is either a User or a Team. Regarding can be anything activities can be related to. This presents a challenge when creating a data model because you have a relationship that can go in two or more directions. Note that best practice around ownership would be to have an Owner table with both Users and Teams. More on creating an Owner table here. That will not work with Contacts and Accounts, and definitely not the Regarding entities for activities.

Let’s go through these types one by one.

Owner field

Owner field for Dynamics 365 Online standard connector

Owner OData
Owner represented by a mixed column with GUIDS for either Team or User. Separate fields are  available for owninguser and owningteam, both the GUID and an expandable field. Depending whether owninguser or owningteam is populated you can tell what type of ownership it is.

Owner field for CDS connector

Owner CDS
CDS connector has three owner related fields. GUIDs only. A combined owner field and separate owninguser and owningteam fields. Again, the type of ownership can deciphered from which fields are populated. No fields with the name of the user or team.

Owner field for Power Query Builder/FetchXML

Owner FetchXML
Using FetchXML, we get the Owner name (user or  team), a column indicating the type, and the owner GUID.

Customer field

Customer field for Dynamics 365 Online standard connector

Customer OData
The standard connector gives me the GUID of the customer field and two expandable fields with relationships to Account and Contact. This means I cannot see if the Customer record is an Account or a Contact without expanding the other two fields or doing some other manual work to find out. As mentioned earlier, I would prefer not to expand any fields for performance reasons unless absolutely needed.

Customer field for CDS connector

Customer CDS
The CDS connector gives me only the GUID of the record. There is now way for me in the query to determine if the record is an Account or a Contact.

Customer field for Power Query Builder/FetchXML

Customer FetchXML
Power Query Builder using FetchXML brings in three fields, similar to how owner fields are handled. The name of the customer, being either an account or a contact. The GUID of the record and a type field indicating contact or account.

Regarding field

Regarding field for Dynamics 365 Online standard connector

Regarding OData
The standard connector includes the GUID of the regarding record and an expandable field for each relationship that the activities have. That means you need to know what entities are used, and decide which you want to include in your data model. In the image I have only included the regarding account relationship.

Regarding field for CDS connector

Regarding CDS
The new CDS connector includes only the GUID for the regarding record. There is no indication as to what type of entity the activity is related to.

Regarding field for Power Builder/FetchXML

Regarding FetchXML
FetchXML brings in the name of the regarding record, the entity type, and the GUID itself. Note that the Power Query Builder currently does not automatically show the regarding entity type even though it is included in the data set. I added this column afterwards in the “Expand Column1” step. Note to self for next PQB update.

The standard connector has some benefits here over the new CDS connector. I can include some information using the either the expandable fields or in the case of the owner field, use some of the extra owner fields. However, FetchXML gives me the data I need in this case.

Winner: FetchXML/Power Query Builder

FetchXML in these scenarios adds some important data I need to properly filter my data and create relationships in my data model.

Querying from multiple entities

Continuing on the expand method for lookup fields, this process could, in theory, be expanded indefinitely to add fields from more entities. For example, an Opportunity, with the Account expanded, and then the Parent Account expanded. However, as I earlier would not even recommend doing the expand once, expanding two or the three times will completely break your process. This goes for both the Dynamics 365 connector and the CDS connector.

The FetchXML/Power Query Builder can retrieve data from many related entities without a significant hit on performance. Basically any query you can construct in the fantastic FetchXML Builder (XrmToolBox tool by Jonas Rapp) can be used in the Power Query Builder. There is even an integration between the two tools. Using this approach can save a significant amount of time when building your data models.

Winner: FetchXML Power Query Builder

Filtering on multiple entities

If you have a need for filtering on a related entity, for example you only want accounts with opportunities, then this is possible with all three options. However, for both the Dynamics 365 connector and the CDS connector, the filtering takes place after the data has been queried. That means if you only want 100k out of a million accounts, then this approach will query the full million and then filter them out.

Filtering on the same entity does reduce the amount queried for these two options. The issues is only when you want to filter on a related entity.

The FetchXML/Power Query Builder approach again lets you do anything FetchXML lets you do. Hence you can filter on data several relationships out from your starting point without any issues.

Winner: FetchXML/Power Query Builder


The performance, or the time it takes to pull my data into Power BI can be quite bothersome if it takes too long. We want to make awesome visuals now, not in 10-20 minutes or however long it might take.

Your specific setup and data queried here makes a big difference. If you do not take my advice and use the expand step anyways, then Power BI is gonna make you wait for it. If you include all or many fields in your data set, then you may have to wait even longer.

The performance without any expanded fields is fairly similar between the three options, but with an advantage to the CDS connector. The FetchXML/Power Query Builder version does come out a little slower, but the difference is minimal.

In a test of querying the same 107k records with details from multiple tables, the CDS connector came in at 72 seconds. FetchXML came in at 76 seconds. A small, but consistent four second difference. Note here that CDS queried two tables and then joined them. FetchXML queried one view with fields from both the primary entity and a parent entity in one. The end result of the two queries were similar. The Dynamics 365 Online connector with expanded fields came in at almost 20 minutes, or 1200 seconds. If not expanding any fields, it is similar to the CDS connector in performance.

I would argue that smaller differences in performance does not matter here. After a report has been published the data set will update in the background and no-one is twiddling their thumbs while it is doing so.

I excluded the Data Export Service from this comparison, but even in its slowest form it would absolutely crush this test.

Here is where some of the limitations of FetchXML become relevant. If your list of attributes is more than about a 100 characters long (don’t know if that is the real figure, but it is around there) then you’ll have to use the property instead. When is used, every field, including its formatted values (the option set labels, regarding names etc.) are retrieved as well. Including every field will have an impact on performance. I don’t think I can test this in detail, but by comparing the speed and data retrieved, I believe that the reason FetchXML/Power Query Builder is a little slower is because it is actually querying more data per field than the other methods. And if you need a lot of fields, or need to use then this becomes more apparent. This additional data is what benefited us when creating the data model, but of course, that data has to be queried and does impact performance a little.

Winner: Common Data Service(CDS) connector, close call, but the more fields included, the bigger the difference is.

The performance is so similar here that it should not be a deciding factor. What should be relevant though, is that if you do get in to performance issues using one of these three methods, switching between them is not going to help. The next step up is either the Data Export Service or possibly Dataflows (haven’t had a chance to blog about Dataflows yet, but it is coming).


So, who is the overall winner? In my opinion, the FetchXML/Power Query Builder is still the way to go and then scale up to Data Export Service or Dataflows if you have “too much” data. Could it be because it is one of my babies? Possibly, but I do really appreciate the labels for the option sets, the names for the lookup fields, and names and entity types for customer and regarding fields. They save me a lot of time every single time I create a new Power BI report. Those are options I sorely miss in the CDS connector.

Maybe this may be my wishful thinking, but I would expect that the CDS will support some, if not all of these features in the fullness of time. The Power Query and data modeling step has the steepest learning curve for Dynamics 365 users getting started with Power BI. Simplifying this step would help out a lot of people. Mohamed Rasheed and I have taken some steps with the Power Query Builder, but I fully admit that it is not as easy to use as it should be. Especially not, if you are not already familiar with the XrmToolBox. That is on me and one area I hope to improve in the future.

Hope you got a good overview of the different methods and know what to pick for your next report, or maybe try out something new. Any insights or ideas, feel free to leave a comment.

As always, thanks for reading and please follow me on Twitter  and sign up for my newsletter.

14 thoughts on “Comparing the three main ways to get Dynamics 365 data into Power BI

  1. Great Article, I was planning on comparing the three methods and was glad to find your article!

    • None of them are subject to the 5k row limit. Only FetchXML is if you use it as standalone, but pagination is included when using the Power Query Builder. I think the most records I’ve queried with FetchXML is 1.6 million.

  2. Any idea how to integrate the odata.nextlink into the Power Query FetchXml code so we can support direct api calls without needing FetchXML?

    Something like:

    GetResults = (path) =>
    S = Json.Document(Web.Contents(CRMServiceUrl,
    [RelativePath=”/audits?$select=auditid”, Headers=[Prefer=”odata.include-annotations=*”]])),
    R = if S[#”@odata.nextLink”]? null
    then List.Combine({S[value],
    @GetResults( …

    else S[value]
    ResultsList = GetResults(“”, 1),

  3. Aren’t you missing the Data Export Service option? Replicate the data to Azure SQL, do some of the “heavy lifting” there and then import to Power BI.

    • Hi Anders. I do mention it in the post, so its definitely not forgotten. While the Data Export Service and Azure SQL does do a lot for performance, I did leave it out for two reasons. Primarily because it is not connecting to CDS directly. It’s an intermediate step which may be out of scope for a lot of people. The other reason is that it doesn’t do a whole lot for the data availability in terms of what fields, labels and other metadata that can be queried without any additional effort. What will be interesting in the near future is to see how the new SQL based CDS connector stacks up against the Data Export Service.

      • Hi Ulrik. My bad, I missed that.
        I look forward to trying the SQL base CDS connector. We started off with using the DES, but have had recurring problems with syncing errors as the data profile has grown. Going forward it looks like using CDS might be a better option for us.

  4. This is a great write up! I’ve been using the Power Query Builder for some time, but I’ve noticed that recently it has stopped working smoothly and is requiring a lot of manually intervention to make a functional script.

    I looked at the GitHub page, but it feels like project may have been abandoned or I’m simply looking at the wrong one.

    Any chance you have a link to a version that works smoothly?

  5. Any thoughts/testing/analysis on the “Dynamics 365 (Dataverse)” connector when compared to these options? The Dynamics Online and CDS connector both show as ‘legacy’ now.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s