Customising the Dynamics 365 Sales Power BI Template Apps

Suppose an organisation uses Dynamics 365 Sales and wants to dip its toe into the proverbial Power BI pool. In that case, there’s a good chance that they will first take a look at the following template apps that are available via AppSource:

• Sales Analytics for Dynamics 365 Sales

• Process Analytics for Dynamics 365

These apps provide a quick way to start working with your Dynamics 365 Sales data within Power BI and showcase some of the product’s core capabilities. However, imagine that our business has strayed a little too far away from native functionality within the application, perhaps via extensive customisation. In this situation, you could find these apps challenging to work with or wonder why your data is not surfacing correctly. This is one of the critical drawbacks of simply installing these apps straightaway into our environment. As a result, their potential utility extends only for demonstrations and deployments that adhere very rigidly to how Dynamics 365 Sales works natively. Although we talk a lot about this being our end goal when deploying the solution, I’ve yet to see a situation where a business is 100% using the app as intended. 😉

So what happens if we are super duper keen on using the Sales Analytics or Process Analytics reports and have a need to customise them ourselves? Unfortunately, we cannot extract the underlying PBIX files from the installed apps within Power BI Online. Instead, what we can do is consult the following Microsoft Docs article and download these ourselves to work with:

• Sales Analytics for Dynamics 365 Sales
• Process Analytics for Dynamics 365

Once downloaded, we are free to customise these as we see fit, perhaps by including any additional columns/tables we are using or by adding different visualisation types that may address our requirements. There are a couple of things to note when working with these template files:

• The files all use the OData connector targeting the Microsoft Dataverse Web API instead of the legacy or revamped Dataverse connector. The OData connector won’t support Direct Query capability or other functionality we expect from the alternate connectors; you would need to make the manual modifications to switch over to these if required.
• If you’re wondering why no data loads when you open the file for the first time, make sure you go into the Power Query Editor and supply the correct URL for your environment via the D365_Sales_URL parameter:

From there, select one of the tables (such as Account) to provide your login details, if needed.

• All connections made into your Dynamics 365 environment are done via the version 8.2 Web API endpoint instead of the current 9.2 version. My recommendation would be to migrate towards the latest version, if possible.

Other than that, the files should be pretty straightforward for anyone with experience working with Power BI Desktop to pick up and customise further. I was both surprised and thankful that Microsoft has made these templates available in this manner. This will come in handy in the future, for more complex demos or for where we need to make a minor modification to satisfy a customer requirement. 😁 Here’s hoping these templates are updated in the future to migrate towards the modern Dataverse connector and setup to leverage Direct Query instead. 🤞