Let’s create and access a simple but effective Resource Demand Dashboard using:
- Project Online Engagements data;
- Power Query
- Office Apps
- Pivot Tables in Excel
- Power BI
First of all, you must have a Project Online account and some Engagements data.
- Engagement is the new feature in Project Online to support a full Resource Management capability.
I’m talking about a model and giving a full demo in the video below.
Let’s get started then!
- Make sure you have Resource Engagements data
- Open Excel
- I’m using the Excel 2016, which contains Power Query built in
- In case you are using Excel 2013, you just need to download the Power Query here: Microsoft Power Query for Excel
- Create a blank workbook
- Access the tab Data (Power Query in Excel 2013)
- Create a New Query from Other Sources
- Then select From OData Feed
- Access your Project Online OData feed by typing the following URL:
- in the Navigator window
- select the table: Engagements
- Click on the arrow next to Load
- Select Load To…
- In the Load To window
- Select Only Create Connection
- Check Add this data to the Data Model
- Click Load
- You will see the Workbook Queries panel
- Create your own layout, but remember the Excel Online limitations.
- Select any cell
- Click on Insert à Pivot Table
- In the Create PivotTable windows select Use this Workbook´s Data Model
- Select Existing Worksheet
- Click OK
- Drag and drop the fields below to the following areas:
à VALUES (Sum)
- Access the Insert tab and click on People Graph
- People Graph Is a cool Office App, which will allow us visualize this data better
- In the People Graph’s Data settings, click on Select your data
- Select our Pivot Table area, like below
- Click on Create
- Repeat the steps below and create a new Pivot Table
- Replace ProjectName by ResourceName (Role/RBS/Skills)
- I’m using only TOP 10 most demanded Roles, but it’s up to you
- You can also add some slicers to make your analysis even better
- Feel free to add as many slicers as you want!
- You can also be creative and try different layouts!
- Upload your file to SharePoint Online or OneDrive for Business
- You will have a great report with full Excel Online functionalities
- Below you can see when we are using the slicers
- I want to check all the demand only for one specific project
- Import your file to Power BI
- Open the report directly in Power BI
- If you have the Power BI Pro license you can schedule data refresh hourly
- But even with the FREE version of Power BI you can schedule data refresh daily
Hope you like!