Dynamics 365 Project Service Automation Analytics: Extending PSA’s reporting with Power BI Solution Templates
Antti Pajunen, Innofactor Oyj
Lähde: Innofactor Oyj
I recently tried out the Power BI Solution Template for PSA in a case where a customer wanted to get an easy overview of their project business and resourcing loads. The Power BI Solution Template for Project Service Automation includes three different reports for Practice Management, Resource Management and Account Management. The template itself is free of charge, however implementing the template requires an Azure subscription with Azure SQL server and database as well as Azure Key Vault.
The point of this blog post isn’t to go into price details and more information on the costs can be found from the Cost Estimator. For Dynamics 365 the Data Export Service solution can be installed from AppSource and it is free on charge.
To use the Data Export Service the Microsoft Dynamics 365 (online) and Azure Key Vault services must operate under the same tenant and within the same Microsoft Azure Active Directory. The Azure SQL Database service can be in the same or a different tenant from the Microsoft Dynamics 365 service. (Source: Microsoft TechNet.)
Installation
Once you have an Azure subscription the first step is to install the DES solution from AppSource. The installation takes few minutes and pretty much requires nothing more than clicking “Next” a couple of times. The process is very straightforward so I decided not to cover that in detail in this post.
The Power BI Solution Templates can be used without DES, however DES allows you to monitor the health of the service. It also allows you to add more entities and relationships to your data model. A big thanks to CRM Chart Guy Ulrik B. Carlsson for pointing out the advantages of installing the Data Export Service solution in Dynamics 365.
After installing the DES solution, the implementation of the Solution Template begins with the “Get it Now” button on AppSource. Your web browser will be redirected to a wizard style installer that provisions an Azure SQL server, database and Key Vault. The whole process is extremely simple and takes under 20 minutes when the database is under a gigabyte.
1. Getting Started
The Getting Started page displays the requirements for using the Solution Template.
2. Dynamics 365 Login
The second step is setting up a connection to Dynamics 365. Choose the target Dynamics 365 Organization and the Azure subscription that is in the same tenant as the Dynamics 365 instance. You can set an Azure Resource Group Name or leave it at the default name.
3. Login to the Key vault
The third step is setting up a connection to Azure Key Vault. The wizard handles everything so you don’t need to have Key Vault ready in your Azure subscription.
4. Target
The fourth step is setting up a connection to Azure SQL. You can either create a new Azure SQL Instance or use an existing one. Remember to write down the credentials if you create a new instance! The wizard handles the creation of the Azure SQL Instance and all you need to do is provide the following details:
- Server Name
- SQL Username
- Password
- Confirm the password
- Choose the Server Sku. This is optional and you can change this later in your Azure portal. I recommend leaving this at the default of S2.
- Server Locations. This is optional however you may want to have the SQL in the same region with your Dynamics 365.
5. Summary
The fifth step is verifying the details and proceeding with the Solution Template.
6. Progress
The sixth and final step is verifying everything went as planned and downloading three .pbix files by clicking on “Download Report”. The sixth step takes approximately 5-15 minutes. You can close the browser window when the wizard finishes and everything is green across the board.
Azure side of things
You can find the newly created Azure SQL server, database and Key Vault in Azure portal. I got an authentication error of some sort on my first two tries in step five of the installations process and got through the provisioning on my third attempt. This seems to have created three different Key Vaults in my Azure subscription, however so far this has had no impact on using the Solution Template.
You can scale the Azure SQL up or down in your Azure portal. There is a great article about the performance differences between different DTUs by CRM Chart Guy Ulrik B. Carlsson. The article can be found here. In my case I downgraded the Azure SQL to S0, which is more than enough for small scale trial purposes.
Power BI side of things
When the Solution Template is ready, the next step is to open up the .pbix file(s) on your Power BI Desktop and connect to Azure SQL. When one of the reports is open, click on Edit Queries and Data source settings. This is where you manage the settings for data sources that you have connected to. Under Change Source you will set the details of the Azure SQL server and database.
After setting the server details you need to set the credentials. Click on Edit Permissions followed by Edit under the Credentials. Be sure to provide your credentials under Database option.
I chose Privacy Level as None as I was using the template with a trial environment. It’s good to know the differences between the different Privacy Levels. There’s a good article on Power BI Documentation about the differences. This completes the setup on Power BI’s side and hitting refresh should show you data on the visualizations.
Possible solutions to correct empty visualizations
If some of the visualizations in the “Resource Management” report don’t show data, the likely reason is that a default “Resource Role” has not been set for resources in Dynamics 365. It is also important to set a “Target Utilization” for each resource to get a graphical representation of the utilization for all resources in Dynamics 365’s “Resource Utilization” section.
I hope this brief instruction allows you to take more out of Project Service Automation and make better business decisions with powerful insights provided by Dynamics 365 Project Service Automation Analytics. Remember to also check the Sales Management for Dynamics 365 Power BI solution template.
Disclaimer:
All my blog posts reflect my personal opinions and findings unless otherwise stated.