System jobs (Settings > System Jobs) are used by the CRM platform for many different operations including workflows, plugins, bulk record deletions, imports to name but a few. Should system jobs fail or take too long to execute, a big impact can be felt by the end user.

It is best practice for the CRM Administrator to monitor the volume and state of system jobs regularly but CRM doesn’t make it easy to do this because the system job entity cannot have charts added. Furthermore, charts & views are limited to render only 5000 records and it is quite common to have tens/hundreds of thousands of system jobs at any point in time. The issue is compounded in CRMOnline due to the inability to access the SQL database to investigate further.

The following walk through shows how we can leverage PowerBI to report on large volumes of system jobs and embed in a CRM Dashboard.

I’m not going to provide a step-by-step guide on how to build a PowerBI report as there are many blogs on how to do this but what I will show is how you can take the report I built (download the PowerBI Designer .pbix file here) and connect that to your CRMOnline organisation.

The first step is to use the PowerBI Option-Set Assistant that is part of the XrmToolbox solution. Check out our other blog on how to use this.

Run the PowerBI Option-Set assistant against the system job entity, select ‘Status Reason’ and ‘System Job Type’ then click ‘Create records for selected option sets’. If this is the first time you have run this, it will prompt you that a new entity will be created in which to store the records. This allows us to categorise system jobs in PowerBI because a limitation of CRM when using PowerBI to interrogate it is statuses and option-sets do not show friendly names, just the integer values. As a result, we can use this tool to generate corresponding records for each status reason and option-set value in a separate table, then join our system job table to them.


 

Once done, open the PowerBI pbix file and update the connection details on each of the 3 queries.

To do this click the ‘Edit Queries’ button then right click on each of the 3 queries and click ‘advanced editor’. Update the <ORGNAME> with your CRMOrganisation name.

 

You will then be prompted for credentials so be sure to select ‘Organizational account’ then Sign In.


 

Once signed in, close & apply. The PowerBI report will then load the system job data from your CRM organisation. Click ‘Enable Custom visuals’ as this allows the custom time slicer to render.

The first page of the report shows system job volume by status and the average delay in execution over time. There are also filters on time and system job type.


The second page of the report gives a more detailed breakdown of the system job names, average duration of each and count. Filters are again available for the system job type and execution time. 

Now we have the PowerBI report, it can be easily added to a CRM dashboard. Publish the PowerBI report and then navigate to https://app.powerbi.com and click on the CRMOnline System Job Report. Click ‘File > Publish to web’.

Click ‘Create embed code’


 

Click ‘Publish’

Copy the html string but amend the ‘width’ to 1800 and height to 700. For users running full HD resolution, I find this fits perfectly once the CRM navigation is taken in to consideration.

Next, in CRM create a dashboard (it doesn’t matter which layout as we shall be removing all but 1 component) then click ‘Web Resource’ on the component.


 

Click the Web Resource lookup then add new. Select ‘HTML’ as the type and give it a suitable name. Click ‘Text Editor’ then ‘Source’ and paste the html that was copied previously.

Click OK then increase the height and width of the component so it fills the entire dashboard.


 

Save, close then publish your dashboard.


 

The PowerBI report will then be available and interactive inside CRM.