There are a few different ways to get your Microsoft 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 had been in preview for a while, it recently became generally available.
Make sure you search for "Common Data Service" and not "CDS"
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 apply 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 highly recommend switching to the CDS connector. 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.
This post compares the three methods on a couple of different areas, and in particular on the areas that can cause 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
Option Sets, Multi-Select Option Sets, and Two Option fields
Option Sets and 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
Two Option fields, Option Sets, Multi-Select Option Sets are available as database values only.
Display values included for Option Sets, but not Two Option fields, and Multi-Select Option Sets.
Power Query Builder/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 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
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.
The CDS connector only brings in the GUID of the record. No name of record or expandable field.
Power Query Builder/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 for Dynamics 365 Online standard connector
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
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
Using FetchXML, we get the Owner name (user or team), a column indicating the type, and the owner GUID.
Customer field for Dynamics 365 Online standard connector
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 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
The CDS connector gives me only the GUID of the record. There is no way for me in the query to determine if the record is an Account or a Contact.
Customer field for Power Query Builder/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 for Dynamics 365 Online standard connector
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 above, I only included the regarding account relationship.
Regarding field for CDS connector
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.
Regarding field for Power Builder/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.
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 important data I need to properly filter 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, I would not even recommend doing the expand once, and expanding two or three times will 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 issue 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, or the time it takes to pull 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 make a big difference. If you use the expand step (against our advice!), then Power BI is going to make you wait for it. If you include all or many fields in your data set, then you may have to wait even longer.
Performance without any expanded fields is fairly similar across the three options, 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 was 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 do 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 win this test.
Here is where some of the limitations of FetchXML become relevant. If your list of attributes is more than ~100 characters long, then you’ll have to use the <all-attributes> property instead. When <all-attributes> 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. 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 <all-attributes>, 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 bit.
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 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.
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. I 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 miss in the CDS connector.
Hope you now have a good overview of the different methods and know what to pick for your next report, or maybe try out something new. As always, thanks for reading.