One of the most important aspects of a resource manager / project manager is to monitor the assignments for each of their resources so that they can effectively balance their team’s workloads. There might be resources that are overallocated or underallocated. The key is to efficiently manage those resources by viewing their workloads and availability in Microsoft Project and making adjustments.
MS Project Online combined with MS Power BIÂ is a great online solution for powerful and effective project and resource management.
These are some of the views that a resource manager / project manager looks for:
- Future availability of resources
- Resource utilization over last week or last month
- Resource current assignment/ projects
Here I will provide you with an overview on how you can connect to Project Online from Power BI and create reports that help in effective project and resource management.
To connect to your resource data follow these steps:
- Open your Power BI Desktop.
- On the home screen, click on Get Data.
- Choose OData feed and click on Connect.
- Then enter the URL for your OData feed in the URL box, and click on OK.
Suppose the address for your Project Web App(PWA) site resembles https://.sharepoint.com/sites/pwa, then the address you’ll enter in the URL box for your OData Feed is https://.sharepoint.com/sites/pwa/_api/Projectdata.
You will be prompted by Power BI Desktop to authenticate with your Office 365 account. Select your organizational account and then input your credentials.
After connecting, choose the tables you would like to connect to and build your query.
Choose the appropriate tables from your Project Online for creating Resource Availability Report. For example: take the tables that consist of all the resource data, their roles in your organization, their projects and assignment data, etc.
After choosing the required tables, you will need to create the possible relationships between tables.
While working on the relationships you need to answer these questions:
- How much work is allotted to a resource on each day?
- What are the active tasks a resource needs to work on?
- What are the future tasks allotted to any particular resource?
- Which resource is available to work on any project?
- How much work is assigned to a resource?
- Which resource is available to work on any project?
- … and many more.
In the Resource Report, you may group the resources by role so when you click on a particular Role, all the resources of that role gets displayed and you may select relevant resources to look into for the details.
The below dashboard displays the Resource availability report for next 2 months:
You can also drill down into resources.
As shown in the below image, if we click on any resource name, we can easily check on what projects is the resource working on. Also, the active, future & overdue tasks for that resource can be identified. This helps in understanding the kind of complexity level task assigned to the resource.
Also, the availability of resources for next 2 months can be seen in this report. You can change the duration easily by filtering the date as per the requirement.
As shown in the below image, you can see all the available resources for the coming two months and their allocation on various projects. This duration can be changed to look for the information at a certain duration.
This report can be a great tool for resource managers to understand the resource allocation and availability at a glance, saving them time invested in navigating to each individual project in Project Online.
Similarly, to track the team wise, role wise or department wise resource utilization, all resources can be grouped at a desired level and then their submitted timesheet data can be leveraged to get the utilization.
The below report gives the information about resources who are grouped role wise.
So, we have seen how these interactive dashboards and reports can be useful to check for the availability and utilization of resources. The above reports are generic and can be created for any Project Online Tenant users and of course can be further customized based on specific needs.
To better understand project and resource management, you can watch the recording on  Project and Portfolio Management using MS Project Online
