A new Project Online Content Pack for Power BI
Published Mar 06 2019 02:37 PM 45.1K Views
Brass Contributor
First published on TECHNET on Oct 26, 2017
*** Update 11/3 - non-English installation?  scroll down to Raphael's comment where you will find some tips on getting this working ***

We are pleased to announce the release of a new Project Online Content Pack for Power BI. Starting October 26 2017, Project Online users will be able to download the new content pack directly from AppSource:

https://app.powerbi.com/groups/me/getdata/services/project-online

Comprising of 13 visually rich report pages, the new content pack is designed to support the management needs of project managers, portfolio managers and resource managers, by retrieving planning and execution data from Project Online and arranging the data to support timely decision making.

The new content pack report includes reports in three key perspectives: Individual Projects, Portfolio of projects and the organization’s resources. Each perspective encompasses the following detailed report pages.

Individual Projects This perspective includes 2 report pages presenting information of a single project at a time:

· Project Status – A summary of a selected project including work and cost, over time, upcoming milestones and overall status.


· Project Risks and Issues – Issues and risks for a selected project.


Portfolio of Projects This perspective includes 6 report pages offering multiple analysis angles on portfolio of projects:

· Portfolio Dashboard At a glance summary of all your projects, including completion status and performance KPIs.


· Portfolio Timeline – An interactive timeline of projects in the portfolio, their planned start and finish dates as well as completion data.

· Portfolio Costs – A detailed report of the portfolio costs, with details on cost status for specific projects.


· Portfolio Milestones – Key milestones in the portfolio and their statuses.

· Portfolio Risks – Aggregated risks across the portfolio and their impact on the portfolio.

· Portfolio Issues – Aggregated issues across the portfolio and the causes for those issues.

Resource This perspective includes 5 report pages:

· Resource Availability - Availability and demand for resources over time, presented through interactive heatmap and charts.


· Resource Overview – A view of all the resources in the portfolio and their detailed information.

· Resource Assignments – Deeper view into how resources are invested across the different projects in the portfolio, with information on their progress in current assignments.

· Resource Details – A summary of a selected resource including availability, assignments and work over time.

· Resource Demand Forecast – Forecast of demand for resource in upcoming time periods, based on current plans and predictive analytics.

How to set up the Content Pack

Go to this Power BI site to learn how to set up the Content Pack.

FAQs

Where can I find the new Content Pack?

The new Content Pack is available for download, directly from Power BI through ‘Get Data’.

The Content Pack is also available on AppSource and a PBIT file is shared through GitHub .

Will the new content pack replace the previous Project Online Content Pack?

Yes, the new content pack replaces the previous Project Online Content Pack. The new Content Pack offers a larger number of reports and leverages latest Power BI capabilities, for a richer analysis experience.
60 Comments
Copper Contributor

This content pack for Project Online is terrific. I do have one question:

 

I received a request to add an overall filter that only shows Projects that are active, not those that are 100% complete. It seems that the "edit" functions are disabled. Is that the case - customizing to fit our needs is not possible?

 

Thanks in advance - and nice work in putting this together!

Hello,

If you download the Power BI template file from GitHub you can then open that in Power BI Desktop and edit this as required. Here is a link: https://github.com/OfficeDev/Project-Power-BI-Content-Packs/blob/master/Microsoft%20Project%20Online...

***Update - link seems to have moved to here: https://github.com/OfficeDev/Project-Power-BI-Templates/blob/master/Project%20Online/Microsoft%20Pro... ****

Paul

Copper Contributor

Paul,

 

Thank you for the reply and link, it was exactly what I needed. I've downloaded the file, opened it in BI Desktop and I can edit as needed.

 

If I can ask one more question:

On the Portfolio Dashboard there are three stoplight KPIs for Cost Health, Work Health and Schedule health. Our PMO is asking what the thresholds are that would affect the colors of the stoplights. I've dug around the properties of those and could not determine the answer. Can you tell me how these are configured so I can provide that answer?

 

Thank you!

 

@Pandra 

Those are calculated based default fields, for example the Cost Health field just replaces the string value of the CKPI field with the correct image:PowerBICostHealth.jpg

 

The CKPI field just calculates of default fields such as Project Baseline Cost, Project Baseline Finish, Project Cost:

PowerBICKPI.jpg

The Work one use the work fields and schedule uses the duration fields instead of the cost fields.

 

You can easily change these / add your own.

 

Hope that helps

 

Paul

Copper Contributor

It does help, thank you.

 

The screenshots you provided also help a lot. For example, the code behind the Work Health one tells me that if the baseline is at least 20% over baseline cost, indicate that with a specific color. (And so on for other baseline info)

 

If I look deeper into the others it will tell a similar story of the logic behind which color is displayed. 

 

Super helpful and greatly appreciated - thanks again!

Copper Contributor

Hello Paul,

Need to ask you a question: The Project Status tab seems to lose the data after scheduled refreshes:

 

Power BI refresh issue.PNG

 

I have tried to refresh it manually and each time the Project Status tab shows blanks across the board. All other tabs work and refresh fine.

 

Any idea why, or suggestions to resolve this?

 

Thank you in advance...

 

@Pandra - What have you changed in this report page? Does the underlying data connection show the raw data in Power BI?

Copper Contributor

Paul, thank you for the reply.

This one turned out to be a user error - the report in the dashboard is actually working fine.

Thanks....

 

Copper Contributor

I am facing issue with Project filter available on Risk and issue report 

when I select a project from slicer both the grids ( risk and issues)!goes blank 

if I choose project name from risk or issues table and create a slicer then it’s able to filter respective individual grid!

anybody facing similar issues or know solutions? Please help

@Jayant440 hello, on the default Microsoft Report pack this works fine for me. The only reason I can think of is that the relationships have been changed in your report? Here are the defaults for Issues and Risks:

IssuesandRisks.png

Copper Contributor

Hi @Paul Mather Thanks for your reply , I haven't changed anything in the report, I also checked the relationships are intact, only thing I changed is the url to connect to our PWA instance, not sure then why its not working for me! anything that I can provide you which will help you to figure out whats the issue? Thanks.

Copper Contributor

@Paul Matherits working, I think I was doing something wrong, Thanks for your reply.

Copper Contributor

Hi @Paul Mather 

 

I am new to using Project Online & Power BI. I have just connected our PWA Instance to the MS Project Power BI Content pack. In the Resource Availability report, in the Availability Heatmap (see photo), I can see the time allocated to each resource in hours - is there a way to add a formula or change this view to view this by days e.g. if it shows me 16 hours i would like to be able to see 2 days as we measure availability & capacity in our organization by days. I will appreciate the help. Thanks.

 

Availability heatmap.PNG

Hi @faithcsoy ,

You can change the visual to day but clicking the visual to show the visualisation properties then add the Time field from TimeSet and remove the Year-Month field. You can see this below - I also selected the Time field and clicked Modelling then used the Format options to remove the time so it was just the date:

PWAPowerBIResDays.png

Hope that helps

Paul

Copper Contributor

Thank you @Paul Mather ,

 

Using the steps above I was able to understand what fields I needed to change. However to get the view I wanted, I had to create a new measure with a value of "8" to represent 8 hours per day and divided all the Availability field values in the Heatmap by that value, then I used conditional formatting to format the colors as per my requirements. Please see below for the results. Thanks for the assistance.

Availability heatmap 2.PNG

 

Regards,

Faith.

Copper Contributor

Hi - I downloaded the PWA content pack template file from github and receiving the following error when opening the file in PowerBI desktop.  Is this a known issue and is there an available workaround?

 

"Unable to open template"

 

"[Filename] is corrupt or an invalid report file"

 

pwa pbit error.png

 
Copper Contributor

Looking for help with the Resource Details page. Not sure where the data is coming from and I'm new to BI

 

REesource details page.png

Hello @Vicki Little ,

If you click the visual such as the "Availability, Demand and Capacity over time" visual, you will see the data sets used by that visual will show a yellow circle with a tick next to it. To see the queries used for that data set, click Edit Queries to load the query editor. With a query selected in the Power Query editor, click the Advanced Editor button on the Home tab. You will then see the source table name used from the ProjectData reporting API.

Paul

Copper Contributor

Thanks Paul

Copper Contributor

I tried connecting the app to my organization's PWA site but I get an error about it timing out. PowerBI experts tell me it's a setting on the PWA site. Any recommendations?

 

pic.png

Hi @data_di_rector 

The 120 minutes is a Power BI limit as documented here: https://docs.microsoft.com/en-us/power-bi/refresh-data#data-refresh

Power BI Premium Capacity allows up to 5 hours if needed but I think you could trim down / optimise your queries / data sets, as 2 hours is a long time!

There is no setting in PWA for this, as the Power BI message suggests, I would review your datasets in Power BI. Are you you using the Microsoft Project Power BI report pack or is this a custom / updated report pack?

Paul

Copper Contributor

@Paul MatherI'm using the Microsoft Project Power BI report pack. I would have imagined that the app would be able to handle our organization's PWA data - 300 schedules & 500 resources.

Hi @data_di_rector thanks for that. The data set you describe above doesn't seem that large on the surface unless those 300 schedules are all huge as in 20 years duration with 1000's of tasks in each with many resources assigned to each task etc. Are you able to open the Power BI report in the Power BI Desktop and refresh it there locally - how long does that take?

Paul

Copper Contributor

@Paul Mather Most schedules are under 3 years. Average task count is 100, with the largest schedules having 1500. 

 

I tried grabbing the pbit file from GitHub but that still takes hours and hours. I haven't been able to refresh it successfully yet. Yesterday I got an error after 6 hours. (Unfortunately didn't capture what it was.) AssignmentTimephasedDataSet was up to 4.5G by the time it failed. 

Hi @data_di_rector ,

It sounds like you have a pretty large dataset if the AssignmentTimephasedDataSet got up to 4.5GB when it failed. I think you will need to review and update the dataset query to add a date filter to potentially limit the data returned:

 

TimebydayFilter.png

 

 

 

 

 

 

 

 

 

Paul

Copper Contributor

@Paul Mather  I took a look and found we have one row of data per project, task, resource, and time that the resource submitted their timecard. And that turns into a lot of data. I'll be able to trim it down now that I know where the issue is. 

 

Thanks so much for your help on this! I'm attending training on practical applications for this app and having it connected to our org's data will make the training so much more useful!

Hi @data_di_rector 

I hope you get it resolved soon, Project Online can generate pretty large volumes of data as you have seen!

Paul

Iron Contributor

Thanks for this information, it is quite helpful. Does this MS Project Online version works with Power BI Pro as well?

Regards,

Lewis

Hello @Lewis-H 

Yes it does work with the Power BI service.

Paul

Copper Contributor

Hello,

 

I've just connected Project Web App and I found that the number of tasks that I see in MS Project online doesn't coincide with the number of tasks in Power BI.

After a short investigation, I saw that some tasks are located in the "Tasks" table and some are in "TaskBaselines" table, while in MS Project those are the same tasks and are shown in one table.

 

I don't have access to Query to append those tables, so maybe you know a short way to fix it?

 

Thanks,

Oleg

 

Copper Contributor

We have had the same issue as Oleg.  Would love to know the answer to this.

Copper Contributor

Trying to figure out if this is possible to use with Power BI Desktop. The link posted by Paul Mather MVP is broken.

Copper Contributor

You may check here how to connect MS Project online to Power BI Desktop

https://docs.microsoft.com/en-us/power-bi/desktop-project-online-connect-to-data

Brass Contributor

Does anyone know perhaps where "Schedule Health" and "Cost Health" is calculated or queried

 

 

Hello @khyron,

An earlier comment on this thread gives the details for the Cost Health - the others follow the same pattern just with different fields: https://techcommunity.microsoft.com/t5/project-support-blog/a-new-project-online-content-pack-for-po...

Paul

Brass Contributor

Sorry @Paul Mather  - let me a bit more specific - I can't actually find the formulas for how they're calculated as you've shown. I open PowerBI Desktop and loaded the PBIT files - and I can create the dashboards and reports - but how do I actually get into those specific formulas to show how it's donUntitled.pnge?

Hello @khyron 

Are you editing the Power BI Report in Power BI Desktop?

Paul

Brass Contributor

Yes I am

Hello @khyron 

Did you get the file from here and try with that in Power BI Desktop: https://github.com/OfficeDev/Project-Power-BI-Templates/blob/master/Project%20Online/Microsoft%20Pro...

Paul

Brass Contributor

Sorry Paul, my bad. I did have it but I connected Power BI to the App and not the PBIT.

 

Looks fine - so I can manipulate the fomula and generate my own dashboard then?

Hello @khyron  - yes you can do that in the Power BI Desktop using the downloaded template file or creating your own Power BI report for Project Online - either will work.

Paul

Brass Contributor

Thanks got it working - is there anyway I can pull other data from project server like SPI/CPI etc?

Hello @khyron ,

Yes - these are available in the Tasks endpoint. Edit the Task query in Power Query - from Power BI Desktop with the Project Online Power BI Report template file open, click Transform data. This will launch Power Query. Click on Tasks then on the right hand side in the Applied Steps panel double click the "Removed Other Columns" step to launch the "Choose Columns" window - add the columns as needed. You will see TaskCPI and TaskSPI in the list. Click OK then click Close and Apply. You will now be able to add the columns into the report.

Paul

Copper Contributor

Can you provide more instructions on how to load the Project online pack into the power BIA desktop in order to reduce the size of the data to be polled? I see this discussed as a solution when you get the air that I am getting but I do not see how to do it

Copper Contributor

I am a bit confused as to the licensing needed to edit the Template pack. This is what I understand :

 

  • Out of the box you need a Project 1 Plan licence to install/use the reports
  • To edit and customize them you need a Project 3 or 5  Plan license

 

Here is where I am confused : once I have published it to the web after modifying it, do my colleagues also need a Project 3 licence to view the reports. They already have access to the data with Project Plan 1

Copper Contributor

I have been able to add the custom project and the task fields I created in Project Online by going into Removed Other Columns i Power BI and adding them. However, I would like to do the same thing for the custom issues fields I created. In Power BI, when I try to go to Removed Other Columns for the issues table, I do not see my custom columns like I did for the tasks table.  Is there something special I need to do to add them?

 

How I created the custom issues columns:

I created the custom site columns at the project online project center Site Settings, Site columns. Then I went to the project site (template) and under Site Contents I right clicked and chose Settings for Issues. I selected add from existing site columns, and added those columns. I used this project as a project template to create the other projects. All of the custom Issues fields are there in the project sites I created from the site template.

 

Is there another step I need to perform for the custom issues columns to show in Power BI Desktop Pro?

 

Thanks,

Mark

hello @makins83 ,

The custom columns added to the Risks / Issues lists are not added to the ProjectData Reporting API in Project Server / Project Online, you will need to access these columns using the SharePoint APIs. I blogged about an approach a few years ago: https://pwmather.wordpress.com/2016/01/05/want-to-query-cross-project-site-sharepoint-lists-in-proje...

Alternatively you can push the SharePoint data to another source like Azure SQL Database then connect Power BI to the database, I blogged an example here: https://pwmather.wordpress.com/2020/04/07/want-to-report-on-projectonline-project-site-list-data-exa...

Paul

Copper Contributor

Does anyone know why my cost/work over time graphs aren't showing any data? I have 7 projects linked via MS Project Desktop tool and Project Web App/Online. The PowerBI Project Content Pack app is configured and showing most data but this is one area that isn't showing correctly:

 

Michael_Murray_0-1596225496332.png

 

 

Another area that isn't showing data is the Risks/Issues section, but from what I gather/read online this information is only feed from SharePoint lists/external data, not necessarily from an existing Microsoft Project Desktop MPP file that's been uploaded/sync'd via project web app?

 

Michael_Murray_1-1596225570434.png

 

Hello @Michael_Murray ,

For the cost and work over time data, please can you check that you have the Reporting Timephased data enabled in PWA - see this post: https://pwmather.wordpress.com/2018/05/14/projectonline-time-phased-data-rollup-for-odata-reporting-...

For the Risks / Issues, these will be list items from the default Issues and Risks lists that PWA uses in the Project Site template.

Paul

Version history
Last update:
‎Mar 06 2019 02:37 PM
Updated by: