I have had an opportunity recently to start getting to grips with the wonderful world of PowerBI. For those who have walked the tightrope between Excel and SQL Server Reporting Service (SSRS) Reports, PowerBI appears to be the tool with these individuals in mind. It enables you to leverage existing Excel knowledge (through PowerQuery or Excel-based formulas/calculations), whilst also offering a number of easy to setup Visualisations, that are not too dissimilar to the charts, maps and other objects that can be setup on a .rdl report. What’s also good to know, from a Dynamics CRM/365 for Enterprise (D365E) point of view, is that you can very quickly hook up your CRM data to a PowerBI dashboard/report. In addition to this, integrating with other data sources - such as SQL, JSON or flat file sources - or even with completely different application systems is possible (even with SalesForce - shock, horror!). In the days of increasing need for tight integration between a dazzling array of different application and database systems, PowerBI gives you a broad framework to achieve any reporting goal you may have in mind. Having said that, it is still rough around the edges and in need of some further development before it, arguably, becomes the de facto tool to use. For example, I have found some of the formatting and design options available to be rather basic and light-years behind what is possible with SSRS or even Excel. There are also some features missing that are somewhat baffling, such as the ability to send out dashboards/reports via email on a predefined schedule. I would hope that we see increasing attention towards PowerBI in the months and years ahead in order to bring the very best of features from these more traditional applications but exposed in a non-daunting and wholly accessible way.
As referenced above, getting set up with your Online CRM/D365E data is incredibly straightforward via the inbuilt Dynamics 365 connector “wizard” - simply login into your online organisation, specify the entity data that you want to work with and PowerBi will push this data into a table for you, enabling you to start building your report in seconds. The connector “wizard” is suited to most typical data retrieval scenarios, providing a GUI interface to visualise the entity data within your CRM/D365E instance and the ability to put together related entities and return them as part of your query. Before you start using it, however, I would highlight the following:
- The OData feed is rather indiscriminate in its retrieval - all records from an entity will be returned. Some pre-filtering will occur based on CRM’s/D365E’s security model (e.g. if the account you log in as has Business Unit level Read privilege on the Lead entity, only records in the accounts Business Unit will be returned), but typically it will be System Administrators who set up a PowerBI Dashboard; therefore meaning you have to deal with a lot of records being returned into PowerBI. Given that the two different PowerBI plans have limitations in regards to record retrieval, this could cause problems if you are working with large CRM datasets.
- Tied in with the above, because you have no way via the “wizard” interface to specify record retrievals, you cannot take advantage of filtering your data based on certain attributes or even take advantage of some of the native functionality within CRM to aggregate your data. Whilst PowerBI is certainly more than capable of doing this, relatively n00bish users may find this an unwelcome barrier that hinders adoption.
- Lookup and Option Set attributes are returned as a special data type of Record - with the underlying properties of the related record (GUID, display name etc.) stored within this sub-record. Having the data stored in this manner causes additional administrative effort on the PowerBI side, as you will need to figure out how to access the underlying properties of this PowerBi data type.
Fortunately, if you are inclined towards building very specific and focused queries that you can execute against your Online CRM/D365E, there is a way - and the best thing is, we get to use something that has been recently introduced into CRM as well 🙂
The Web API to the rescue!
The Web API was introduced in Dynamics CRM 2016, which implements version 4 of the Open Data (OData) Protocol, and will eventually replace the traditional means that developers would use to access CRM via web services (namely, the Organization service and the old OData service). CRM Developers will need to start becoming increasingly familiar with the Web API in the years ahead, and no doubt a lot of work will need to be done updating existing coding to look at the new Web API.
Because the Web API is a web service, PowerBi can connect to it via the Web connector. By querying the Web API, you have access to all of the messages that are exposed to get data from CRM - Retrieve, Retrieve Multiple and Predefined Query - with multiple options available to use in terms of how you return, filter and aggregate your data. Results will be returned in JSON format, so there will be some additional work that needs to be done to get the data into an accessible format. This post will now take a look at what you need to do in order to return data based on a FetchXML query, as well as (hopefully!) providing a useful code snippet that you can adapt to your environment.
Before starting, ensure that you have installed the CRM Rest Builder Managed Solution within your CRM development environment. This tool allows you to quickly generate code snippets in JScript that perform web service calls into CRM/D365E and is a massive help in a number of different ways. A big shout out and thanks to Jason Lattimer for the work he has done on this.
- To begin, you need a FetchXML query that returns the data you need. This can be written manually or generated automatically via Advanced Find. In this example, we are going to use the following snippet that queries the Case entity, that will return 7 sample data records from CRM:
<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
<entity name="incident">
<attribute name="title" />
<attribute name="ticketnumber" />
<attribute name="createdon" />
<attribute name="incidentid" />
<attribute name="caseorigincode" />
<attribute name="customerid" />
<order attribute="ticketnumber" descending="false" />
<filter type="and">
<condition attribute="prioritycode" operator="eq" value="1" />
</filter>
</entity>
</fetch>
- Next, we need to generate the URL that will be used to query the Web API endpoint. There are two challenges here - the first being that the FetchXML query needs to be included in the URL and that it needs to be encoded so that it is a valid URL. The start of the URL is fairly straightforward to put together - it’s just your CRM organisation URL, in the following format:
https://<Organisation Name>.<Region Code>.dynamics.com
So if your organisation name is crmchap and your CRM tenant is based in the EMEA region, then the URL would be as follows:
https://crmchap.crm4.dynamics.com
The rest of the URL can be obtained from the CRM Rest Builder. Open the Managed Solution, navigating to the Configuration page. It will look something like this:
Update the page so that the Action is set to Predefined Query. Ensure that the Primary Entity is set to Incident (always something you have to remember when working with the Case entity 😉) and then copy and paste the FetchXML query into the box below. The window should look similar to the below once ready:
Press Create Request to put together the code snippet. On Line 2, you will see the following piece of code:
req.open("GET", Xrm.Page.context.getClientUrl() + "/api/data/v8.0/incidents?fetchXml=%3Cfetch%20version%3D%221.0%22%20output-format%3D%22xml-platform%22%20mapping%3D%22logical%22%20distinct%3D%22false%22%3E%3Centity%20name%3D%22incident%22%3E%3Cattribute%20name%3D%22title%22%20%2F%3E%3Cattribute%20name%3D%22ticketnumber%22%20%2F%3E%3Cattribute%20name%3D%22createdon%22%20%2F%3E%3Cattribute%20name%3D%22incidentid%22%20%2F%3E%3Cattribute%20name%3D%22caseorigincode%22%20%2F%3E%3Cattribute%20name%3D%22description%22%20%2F%3E%3Cattribute%20name%3D%22customerid%22%20%2F%3E%3Corder%20attribute%3D%22ticketnumber%22%20descending%3D%22false%22%20%2F%3E%3Cfilter%20type%3D%22and%22%3E%3Ccondition%20attribute%3D%22prioritycode%22%20operator%3D%22eq%22%20value%3D%221%22%20%2F%3E%3C%2Ffilter%3E%3C%2Fentity%3E%3C%2Ffetch%3E"
The bit we are interested in is the string value after the Xrm.Page.context.getClientUrl() function, which will need to be appended to our CRM URL. So based on the above, our URL to use with PowerBI would be as follows:
A bit of a mouthful I agree!
- Now that we have the URL, we can connect up to CRM within PowerBI. Create or Open a new PBIX file and select Get Data -> Web:
- On the From Web window, copy and paste the URL we’ve built and press OK. You will be prompted to log into your CRM organisation; select Organisational account and log in as you would normally via the Office 365 login page. Once logged in, the data will be retrieved and the Query Editor will open, displaying something similar to the below:
- Some additional work is required in order to get our data into a standard, tabular format. In addition, the data at the moment is returning the underlying Option Set and Lookup values from the Incident entity, as opposed to the Display Name; not good from a reporting point of view:
We, therefore, need to modify the underlying PowerQuery in order to achieve the following:
- Include a line onto the Web Service request to return the formatted values for entity fields, where applicable, and only display these values in our results.
- Parse and convert the returned data into a line per CRM/D365E record.
- As an additional example, for presentation purposes, we also need to rename _customerid_value field to Customer Name
Right click on Query1 and select Advanced Editor to open the underlying Power Query text. Delete everything here and then copy and paste the following into the window:
let
//Get the CRM data, including the Formatted Values as part of the returned data
Source = Json.Document(Web.Contents("https://crmchap.crm4.dynamics.com/api/data/v8.0/incidents?fetchXml=%3Cfetch%20version%3D%221.0%22%20output-format%3D%22xml-platform%22%20mapping%3D%22logical%22%20distinct%3D%22false%22%3E%3Centity%20name%3D%22incident%22%3E%3Cattribute%20name%3D%22title%22%20%2F%3E%3Cattribute%20name%3D%22ticketnumber%22%20%2F%3E%3Cattribute%20name%3D%22createdon%22%20%2F%3E%3Cattribute%20name%3D%22incidentid%22%20%2F%3E%3Cattribute%20name%3D%22caseorigincode%22%20%2F%3E%3Cattribute%20name%3D%22customerid%22%20%2F%3E%3Corder%20attribute%3D%22ticketnumber%22%20descending%3D%22false%22%20%2F%3E%3Cfilter%20type%3D%22and%22%3E%3Ccondition%20attribute%3D%22prioritycode%22%20operator%3D%22eq%22%20value%3D%221%22%20%2F%3E%3C%2Ffilter%3E%3C%2Fentity%3E%3C%2Ffetch%3E", [Headers=[#"Prefer"="odata.include-annotations=""OData.Community.Display.V1.FormattedValue"""]])),
//Get the underlying list of records returned
values = Source[value],
//Create a new table with one column, populated with the values list of records
#"Table from List" = Table.FromList(values, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
//Query will error if no results, therefore use an if statement to build an empty table with the correct column headers
Expand = if List.IsEmpty(values) then #table({"title", "ticketnumber", "createdon", "incidentid", "caseorigincode", "_customerid_value@OData.Community.Display.V1.FormattedValue"}, {"title", "ticketnumber", "createdon", "incidentid", "caseorigincode", "_customerid_value@OData.Community.Display.V1.FormattedValue"}) else Table.ExpandRecordColumn(#"Table from List", "Column1", {"title", "ticketnumber", "createdon", "incidentid", "caseorigincode", "_customerid_value@OData.Community.Display.V1.FormattedValue"}, {"title", "ticketnumber", "createdon", "incidentid", "caseorigincode", "_customerid_value@OData.Community.Display.V1.FormattedValue"}),
//For some reason, if there are no results, then the empty table contains error records - so need to specifically remove them
#"Removed Errors" = Table.RemoveRowsWithErrors(Expand),
//Finally, rename the _customerid_value field
#"Renamed Columns" = Table.RenameColumns(#"Removed Errors",{{"_customerid_value@OData.Community.Display.V1.FormattedValue", "Customer Name"}})
in
#"Renamed Columns"
The comments should hopefully explain what the code is doing, but to summarise: the PowerQuery is parsing the JSON data into a tabular format, using the returned data to build column headers, and then renaming the _customerid_value field to match our requirements. There is also a logic statement in there to check if we actually have any data; and if not, then build an empty table (since JSON does not return anything that we can use if 0 records are returned).
With our updated PowerQuery, our result set should look similar to the below:
Nice, presentable, filtered and exactly what we need to start building our PowerBI report! 😀
Conclusions or Wot I Think
Whilst I’ll be the first to admit the above example is rather code-heavy and would require significant tinkering to suit specific scenarios, I would argue that the approach demonstrated adheres most closely to some of the common rules when it comes to querying data for reports:
- Only return the columns you need
- Filter your data to reduce the number of results returned
- Ensure that your report-side columns are giving presentable database column values
This approach is the only one adheres most closely to the above and, therefore, I feel that the extra effort is warranted; particularly when it means that those who are more familiar with CRM can stick to the tools they know best. As the default Dynamics 365 connector uses the Organization Data service, I would imagine that eventually this will be updated to use the new Web API instead. I hope that when this happens, we can begin to achieve all of the above via the PowerBI interface, as opposed to resorting to code.