Retrieving Custom List Data from All Project Online Sites with Power BI

Posted By Posted by: Peter Williams on January 22, 2018

Background

Microsoft Project Online is a great platform to manage a programme/portfolio of projects. Project Online is an app that leverages SharePoint Online; it is effectively a very complex “app” for SharePoint Online.

Project Online encourages project collaboration via the use of a dedicated SharePoint site for each project. Typically, you can expect to find lists (aka registers) for things like managing Risks, Issues, Assumptions, Decisions, Benefits, etc. on each project SharePoint site.

If there are 100 projects, you may have 100 separate locations (project sites) of where Issues, for example, are stored.

For Risks and Issues, Project Online contains a special solution which synchronizes pre-defined columns from these list to the “project database”, which allows for easy retrieval of this data, across all projects, using the Project oData Service. However, if you were to add new custom columns to the Risk and Issues lists, then you won’t find that information available using the Project oData Service. Also, any other new custom lists, such as Decisions, Benefits, etc. are not available via the Project oData service.

Objective

With all this valuable information being stored in many different project sites, there is now a real challenge to have a reporting solution that can retrieve this information.

This blog discusses how to use Power BI (or Power Query Editor within Excel for that matter) to retrieve information from custom lists across the portfolio of projects in Project Online, without the need to employ a custom solution that firstly retrieves data from Project Online and stores it in a custom SQL database.

I have seen many reporting solutions for Project Online that first requires the download of data to an on-premise database using tools such as SSIS. While this is a viable and powerful solution (e.g. snapshotting becomes plausible), it incurs extra cost and often isn’t in line with the desire to “migrate to the cloud” philosophy.

Considerations

For the Power BI reporting solution to be robust, the following scenarios need to be handled gracefully so as not to cause the solution to fail:

  1. One or more project sites doesn’t contain a list that has been configured to be queried
  2. One or more specified columns are not present on a list that has been configured to be queried

Also, the solution should allow for relatively easy maintenance. Ideally, it should be relatively obvious on how to add a new column to a list or add a new target list.

Approach

Firstly, I must thank Reza Rad for providing some insights into the power of Power Query Editor or (M Functions). He was a guest speaker at the Sydney PUG (Power BI User Group) last November. He provided a quick demo of his blog http://radacad.com/fetch-files-andor-folders-with-filtering-and-masking-power-query during one of his presentations and I quickly realized that this technique surely could be adapted to fetch data from Project Online.

Power Query Editor can be found in both Excel and Power BI Desktop app.

I developed the solution using the Power BI Desktop app and as a base, I used the Microsoft Project Online Power BI Pack released last in 2017.

The following extra components were added to what is already found in the Microsoft Project Online Power BI Pack.

  1. RequiredColumnsOnTargetList custom table.
    This is a configuration table. It details:

    1. The columns that should be returned from each specified list. This is detailed in the [TargetColumnOnList] column.
    2. Whether the target column is a Person field. Person fields return a record instead of a name when querying list data. Extra code was required to drill into a person record to extract the name (as opposed to their phone number, department, etc)
    3. If the column should be renamed.
    4. Which list the column is related to.
      A screenshot of the table and the sample query code is shown below.


let

Source = Table.FromRecords

(

{

// Issues list columns

[TargetColumnOnList = “Title”, IsPeopleField = false, RenameColumnTo = “”, ListName = “Issues”],

[TargetColumnOnList = “Id”, IsPeopleField = false, RenameColumnTo = “”, ListName = “Issues”],

[TargetColumnOnList = “GUID”, IsPeopleField = false, RenameColumnTo = “Issue Id”, ListName = “Issues”],

[TargetColumnOnList = “AssignedTo”, IsPeopleField = true, RenameColumnTo = “”, ListName = “Issues”],

}

)

in

Source

  1. GetProjectSiteListData custom function.
    This function retrieves list data for a target list on a singular project site. This function requires the following parameters: ProjectWorkspaceInternalURL, ProjectName, ProjectId and the name of target list. These parameters are added as columns to the table returned.Error handling is performed by checking if each target column exists on the target list. If not, an empty table is returned.
  2. GetSpecifiedListDataFromAllprojectSites custom function.
    This function requires just one parameter; the name of a project site list.
    This function calls on the Project oData service to return a list of projects that have a project site. It retrieves the ProjectWorkspaceInternalUrl, ProjectName and ProjectId.; these are the parameters that are fed to the GetProjectSiteListData custom function.This list of projects is then iterated through and calls on the GetProjectSiteListData custom function to return all list items across all projects. The data is then combined to form one table.Each column is then parsed to determine if it is of the person data type. If so, the name is returned from the person record. This blog https://community.powerbi.com/t5/Desktop/Iterate-over-each-cell-in-a-table/td-p/102889 helped greatly in figuring out how to do this.
  3. Queries that call on the GetSpecifiedDataFromAllProject E.g. AllProjectSiteDecisions.
    A sample of this query is shown below. This simple query will return all items from a list called Benefits found across all project sites.

let

Source = GetSpecifiedListDataFromAllProjectSites(“Benefits”)

in

Source

 

The Result

Final Consideration

It should be noted that execution query time is slow compared to a Project oData Service query. This is expected, as the solution developed here is essentially performing a separate query for each project site/list combination.
For a test environment that had 20 projects, it took about a minute for the query to execute to return all Issue items across all 20 project sites. It isn’t hard to imagine, therefore, that if there were 100+ projects and 5 or more lists on each project site, that the query time for the report could take 15+ minutes. Therefore, I recommend configuring a scheduled refresh when of a dataset like this as opposed to relying upon a live query.

If you are interested in finding out more about this modified version of Microsoft’s Project Online Power BI Reporting Pack, please contact us.


Blog Posted In Blog Posted In: Blog, How to, Project Online
Blog Posted In