SharePoint (and Project Server) Shenanigans

I am a Project Server and SharePoint consultant but my main focus currently is around Project Server. I have been working with Project Server for nearly five years since 2007 for a Microsoft Gold Certified Partner in the UK, I have also been awared with the Microsoft Community Contributor Award 2011. I am also a certified Prince2 Practitioner. This article has been cross posted from pwmather.wordpress.com (original article)

This post follows on from part 1: https://pwmather.wordpress.com/2019/01/01/project-roadmap-cds-app-overview-ppm-projectmanagement-msproject-projectonline-office365-powerplatfom-dynamics365-part-1/ In part 1 we had a quick overview of the solutions used in the CDS app for the Roadmap service. In this post we will look at the fields used by the Roadmap service and take a look at some of the data in the Portfolio Service CDS database. As you know, this new feature is known as Roadmap to the end users, but the backend is known as the Portfolio Service, hence both names being used here.

Firstly we will have a look at some of the entities and field definitions from the Portfolio Service solution PowerApps admin interface that we briefly accessed at the end of part 1:

As you can see from the screen shot above, there are 6 entities deployed in the Portfolio Service solution. Each of these entities have various other artefacts such as views, keys, fields etc. For this post, we will just look at the fields. We wont explore all of the entities or all of the fields in each entity, but we will look at some of the key entities and fields used by the Roadmap service.

Firstly we will look at the Roadmap entity in the PowerApps portal:

You can scroll along the grid to see the properties such as description etc. This entity contains the roadmap details – the roadmap properties and rows added to the roadmap. Some of the key fields here for us as end users for reporting etc. are:

• msdyn_name – this field will contain the Roadmap name and the row name
• msdyn_type – this is used to determine the type – either a Roadmap which is type 0 or a row which is type 1
• msdyn_roadmapid – this is the roadmap / row GUID
• msdyn_parentroadmapid – this is contains the roadmap GUID for the rows added to a roadmap
• msdyn_groupaadid – this is the associated Office 365 group ID

The next entity is the Roadmap Item:

This entity contains the roadmap item details – items added to rows on the roadmap and items added directly to the roadmap like key dates. Some key fields are:

• msdyn_name – this field will contain item or key date name
• msdyn_duedate – this field will contain the due date for row items
• msdyn_startdate – this field will contain the start date for row items
• msdyn_status – this is the status for the item such as At Risk, On Track etc.
• msdyn_type – this is used to determine the type – either a key date which is type 0 or a phase which is type 1
• msdyn_roadmapid – this is the roadmap / row GUID
• msdyn_roadmapitemid – this contains the roadmap item GUID

The next entity is the Roadmap Item Link:

This entity contains the roadmap item link details – items added to rows on the roadmap that are linked to external projects (Project Online or Azure Boards currently). Some key fields are:

• msdyn_externalname – this field will contain the external task name
• msdyn_externalduedate – this field will contain the external due date for row items
• msdyn_externalprojecttaskid – this is the external task GUID from the external project
• msdyn_externalstartdate – this field will contain the external start date for row items
• msdyn_externaltype – this is used to determine the type – either a key date which is type 0 or a phase which is type 1
• msdyn_externalurl – this is the link to the external project
• msdyn_roadmapid – this is the roadmap row GUID
• msdyn_roadmapitemid – this contains the roadmap item GUID
• msdyn_roadmapitemlinkid – this contains the roadmap item link GUID

The final entity that we will look at is the Roadmap Row Link:

This entity contains the roadmap row link details – rows on the roadmap that are linked to external projects (Project Online or Azure Boards currently). Some key fields are:

• msdyn_externalname – this field will contain the external project name
• msdyn_externalprojectid – this is the project GUID from the external project
• msdyn_externalurl – this is the link to the external project
• msdyn_refreshenddate – this is the finish time the external project last synchronised with the CDS data via the Flow
• msdyn_refreshstartdate – this is the start time the external project last synchronised with the CDS data via the Flow
• msdyn_roadmapid – this is the roadmap row GUID
• msdyn_roadmaprowlinkid – this contains the roadmap row link GUID

Lets explore the data in the Roadmap entity. There are several ways in which we can do this, in code using the Organization Service or the Web API: https://docs.microsoft.com/en-us/powerapps/developer/common-data-service/work-with-data-cds or there is a Connector for Power BI: https://powerapps.microsoft.com/en-us/blog/cds-for-apps-powerbi/ For this I will use Power Query in Power BI but also show you how to access the Web API too. To access the Roadmap data in Power BI you will need the server URL. There are at least two ways (probably more) to get the correct server URL for the CDS Roadmap uses. Firstly via the Office 365 Admin Center using the steps below:

Access the Office 365 Admin Center > Admin Centers > Dynamics 365:

Then click open on the correct instance and you will see the server URL in the URL bar:

The other way, if you do not have access to the Admin Center is by using the browser dev tools. Access Project Home > Press F12 / open the dev tools > Access a Roadmap > In the Network tab, find the network call GetCdsEndpoint then check the Response, you will see a cdsurl property:

Once you have the server URL you can either use the Power BI connector or the Web API. The the simplest way to demo viewing this data using the Web API is via the Browser. In this example. Add the following to the end of the server URL: /api/data/v9.1 – you can use a different version, at the time of writing 9.1 was the latest on my tenant. Going to the root Web API URL will return the JSON for all of the entities available. Append the entity that you want to access to the end of the URL and you will see all of the data available in the entity:

As the Web API implements the OData protocol you can use the standard query options such as $select or $filter. Here I’m just selecting the name and type from the roadmaps entity:

https://org05724544.crm11.dynamics.com/api/data/v9.1/msdyn_roadmaps()?$select=msdyn_name,msdyn_type

Here I’m filtering for roadmaps only as the roadmaps entity contains roadmaps and rows:

https://org05724544.crm11.dynamics.com/api/data/v9.1/msdyn_roadmaps()?$filter=msdyn_type eq 0

To easily make sense of the data you need to format the JSON. Power BI is easier to visualise the data.

In Power BI click Get Data > More > Online Services > Common Data Service for App (Beta):

Select it then press connect. Accept the warning about being a preview connector (something to be aware of!). Now enter the server URL:

Now you can see all of the tables / entities that are in this CDS service, for the purpose of this blog post I will just expand Entities then select Roadmap, Roadmap Item, Roadmap Item Link and Roadmap Row Link:

Then click Edit to load these in the query editor. I’ve removed some of the default fields to clean up the data. Here is one of my roadmaps from the msdyn_roadmap table via Power Query in Power BI:

Here is a row from my roadmap:

Here is a key date added to my roadmap from the msdyn_roadmapitem table:

Here is a task (known as a phase in Roadmap) added to a row in the roadmap:

Here is a milestone (known as a key date in Roadmap) added to a row in the roadmap:

Here is an item link for a task linked to a row in Roadmap from msdyn_roadmapitemlink table:

Here is an row link for a project linked to a row in Roadmap from msdyn_roadmaprowlink table:

In the final part of this mini series, we will look at following on from this and creating a simple report in Power BI for the Roadmap data and bring in some Project Online data.