Data – it makes the world – and every organization go round. But what happens when you want to extract mass amounts of data and put it into a tool such as Power BI? In the case of using the Dynamics 365 (Dataverse) connector, you may face long data load times, run into performance issues or time outs, which ultimately can create frustrated consultants and end users.

When thinking about Dynamics data and its quantity related to reporting, the connector is actually fairly performant regarding the amount of data (& how quickly) that can be imported to Power BI.

However, if data performance becomes an issue, I might start to consider migrating that client to a solution such as an Azure Data Lake and using that as a connection instead (typically this is blazing fast). But what about those cases where you may not have mass amounts of data, but you still want a streamlined performance?

This is where I would like to introduce XRM Toolbox. XrmToolBox is shipped with all tools managed by Tanguy Touzard, it is designed to facilitate the development of projects using Power Platform, CRM, and Dataverse. There are currently 100+ tools embedded in the software which can make for a highly personal and customized solution. In future blogs I will cover some other tools that I find helpful, however in this post I will be focusing on the tool called FetchXML Builder.

Getting Started with XRM Toolbox

To download and get started with XRM Toolbox, click this link and “Download Latest Version”. This will install XRM Toolbox onto your Desktop. Select whichever settings and options are most appropriate for your preferences.

Once you have XRM Toolbox installed and opened, you can begin to download tools. Upon opening the toolbox, you may automatically be greeted by the long list of tools. You will want to search for “Fetch” and should then see FetchXML Builder. Install this.

Now, you might be asking yourself, “Why?” I am glad you asked. Here is our use case scenerio..

NOTE: If you don’t want to read the Use Case, scroll down to the “How To” Section.

A totally not real Use Case that happened to Savannah…

Background

In the heart of Techville, a city renowned for its digital innovation, Alex, a seasoned data analyst, was presented with a new challenge. They were tasked with creating a Power BI report for Acme Corp, a client that relied heavily on a CRM system to manage their sales and customer interactions. Acme Corp’s management was eager to gain deeper insights into their operations through a custom Power BI dashboard that would showcase sales performance metrics, customer satisfaction scores, and lead conversion rates. The goal was clear: transform raw CRM data into actionable insights that could drive strategic business decisions.

Objective

Alex’s mission was to connect to Acme Corp’s CRM database, retrieve the necessary data, and craft an efficient Power BI report that met the client’s specific requirements. The report needed to provide a custom view that seamlessly integrated various data points to offer a comprehensive picture of Acme Corp’s business operations.

Actors

• Alex (You): The data analyst responsible for creating the Power BI reports.
• Acme Corp (Client): A dynamic organization utilizing a CRM system for managing sales and customer relationships.
• CRM System: The client’s database, a complex web of tables housing sales data and customer interactions.
• Power BI: The powerful tool chosen for transforming data into meaningful visualizations.

Pre-conditions

Before embarking on this project, Alex had several key elements in place:

• Access to CRM Credentials: Alex had secure access to Acme Corp’s CRM database, allowing them to delve into the wealth of data stored within.
• Power BI Setup: With Power BI already installed and configured on their workstation, Alex was well-prepared to begin the data analysis journey.
• Complex CRM Structure: The CRM system was intricate, featuring a main table that referenced numerous other tables, forming a dense network of relationships that needed to be navigated.

The Journey Begins

As Alex sat down at their workstation, they were optimistic about the task ahead. Connecting to Acme Corp’s CRM was the first step. With the credentials in hand, Alex initiated the connection, navigating through the familiar interface of the CRM system.

Upon accessing the CRM data in Power BI, Alex immediately noticed the complex architecture of the data. The main table, intended to provide insights into sales and customer interactions, was a labyrinthine structure interwoven with references to countless other tables. Instead of straightforward data fields, Alex was met with a sea of GUIDs—unique identifiers that served as cryptic placeholders for more meaningful data.

The Challenge Unveiled

Alex realized that to fulfill Acme Corp’s request, they needed to create countless relationships in Power BI that seamlessly integrated these disparate data sources. The traditional approach would involve manually linking each GUID to its corresponding table, a daunting task given the sheer volume of data and the intricate web of relationships. This manual process could potentially lead to performance issues and an unwieldy report that was difficult to manage and slow to load.

However, Alex knew there was a better way. The solution lay in FetchXML, a powerful tool tailored for Dynamics 365/Dataverse environments. By leveraging FetchXML, Alex could craft a precise query that bypassed the need for manual GUID linking. This approach would allow Alex to construct a custom OData URL, streamlining the data retrieval process and ensuring that only the necessary fields were pulled into Power BI.

The Power of FetchXML

With a clear strategy in mind, Alex opened the XRM Toolbox, a trusted ally in navigating complex CRM landscapes. Using the FetchXML Builder, Alex meticulously crafted a query that captured all the relevant data points required for the Power BI report. This query included specific fields from the main table and its related tables, filtering and sorting the data to ensure it aligned perfectly with Acme Corp’s needs.

Once the FetchXML query was ready, Alex returned to Power BI and input the custom OData URL generated by the query. As if guided by an invisible hand, the tangled web of GUIDs transformed into a coherent and meaningful dataset, ready to be visualized.

Crafting the Masterpiece

Now armed with a clean and structured dataset, Alex set to work building the Power BI dashboard. The result was a stunning array of visualizations that brought Acme Corp’s data to life. Interactive charts displayed sales performance metrics, highlighting key trends and patterns. Customer satisfaction scores were elegantly represented, offering insights into the areas of strength and opportunities for improvement. Lead conversion rates were broken down into detailed segments, providing Acme Corp with the intelligence they needed to refine their strategies.

A New Perspective

As Alex presented the final report to Emma, Acme Corp’s head of sales, a sense of accomplishment filled the room. Emma was impressed. “This report is exactly what we needed,” she exclaimed. “The insights are clear, and the performance is exceptional. We can now make data-driven decisions with confidence.”

By harnessing the power of FetchXML, Alex had not only delivered a solution that met Acme Corp’s requirements but had also exceeded expectations by optimizing the report’s performance and usability. The once-daunting CRM puzzle had been transformed into a powerful tool for driving business success.

How to do this..

Now that we have an understanding of our Use Case and why we might want to use this tool, let’s talk about how to actually get to our end product (a custom OData URL).

With XRMToolbox open and the FetchXLM tool installed and opened you will be prompted to create a connection. Because CRM is built on the foundation of Dataverse, use the Dataverse connector and enter your credentials.

Once you are connected, you will be greeted with the query builder. Here is where you will be able to explore different entities and attributes. FetchXLM will default to the top 50, which I prefer in testing.

On the left side of the Query Builder, you will have a navigation panel where you can select your entities and attributes.

Entity = Entities in CRM are Tables that are visible

Attributes = Attributes in CRM are Feilds that are available

A popular field that produces a GUID in CRM is accountid. This attribute on an entity typically links back to the account entity to source and gather information such as name, address, etc.

In this example, I will be looking at a page which houses opportunities for new business in CRM.

1. Select your Entity from the drop down. (Opportunity)
2. Select your attributes. This is great for filtering your query down to just what you need for your report. In this case I will just select the customerid
3. Execute query to preview. In this case customerid is a GUID, linked to the Customer Name.
   • In this case, I would not really need to link to the account card — but we will act like we only have access to the GUID and no additional information (thus the need to go get it).

To get information that untangles the web of GUID mapping in CRM we will need to create a link entity. In this case, we know we need to go to the account entity.

1. Click on Link-Entity
2. If you click on the “Relationship” drop down you will greeted with linked entities.
   • In this case we will look for the customerid and its link to the account entity.

3. Your query builder may then look something like this
4. Once you hit execute you may get this error – Using Layout: Alias is needed to show these attributes. This can be solved by entering an Alias (this was done in the example above)
5. Lastly you will need to select which attribute you want carried over from the linked entity

Once you have all of your linked entitles and their attributes set, you can hit execute to review your query. Once you are happy with your query you can convert it to an OData feed to use in your Power BI Reports.

An OData link will then be generated below the query preview which you can copy and paste into Power BI using the Odata Connector

NOTE: You can and should remove the top 50 filter when you are ready, so you do not unintentionally miss data.

FetchXLM Builder is awesome. XrmToolBox has a slew of other tools that may be reviewed in later blog posts.