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.

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.

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

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.

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

CDS connector

Power Query Builder/FetchXML

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

CDS connector

Power Query Builder/FetchXML

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 field for CDS connector

Owner field for Power Query Builder/FetchXML

Customer field

Customer field for Dynamics 365 Online standard connector

Customer field for CDS connector

Customer field for Power Query Builder/FetchXML

Regarding field

Regarding field for Dynamics 365 Online standard connector

Regarding field for CDS connector

Regarding field for Power Builder/FetchXML

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

Performance

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).

Conclusion

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 Follow @CRMChartGuy and sign up for my newsletter.