Welcome to the new Project – getting started Part 2 #MSProject #PPM #Office365 #PowerPlatform #ProjectMangement #ModernWorkManagement #CDS #PMOT #PMO

October 30, 2019 at 4:11 pm | Posted in Administration, App, Functionality, Information | Leave a comment
Tags: , , , , , , ,

Following on from yesterday’s blog post: https://pwmather.wordpress.com/2019/10/29/welcome-to-the-new-project-getting-started-part-1-msproject-ppm-office365-powerplatform-projectmangement-modernworkmanagement-cds-pmot-pmo/ walking through the new Project, here is part 2. In the post we will look at the backend to the new Project and look at two Power BI report packs I released yesterday. Links were in the part 1 but for completeness here they are again:

Power BI report for Project:  https://gallery.technet.microsoft.com/Power-BI-Report-Pack-4506f183

Power BI report to combine Project and Project Online data (1 PWA instance): https://gallery.technet.microsoft.com/and-Online-Power-BI-Report-d1fbca1b

Project is built on the Power Platform’s Common Data Service for Apps (CDS), the data is stored in Entities in the CDS – the same as Roadmap – lots of links about Roadmap here: https://pwmather.wordpress.com/2019/03/01/project-roadmap-cds-app-overview-ppm-projectmanagement-msproject-projectonline-office365-powerplatfom-dynamics365/ Let’s take a look at the solutions deployed for Project, to do this I’ve accessed the Dynamics 365 Admin Center from the Office 365 Admin centers which loads this:

D365Admin

Project is deployed to the default organisation, on this tenant I only have 1 organisation – click the Open arrow on the Default org. This loads the PowerApps interface, from here click the Settings cog > Advanced Settings:

PApps

This takes you to the Dynamics 365 Settings:

D356Admin

Click the Settings > Solutions:

D365Settings

This then loads the solutions deployed to this instance:

D365Solutions

You can also see the solutions from the familiar PowerApps interface:

PAppsSolutions

Sticking with the Dynamics 365 Admin interface for the moment, let’s have a quick look at the msdyn_ProjectServiceCore solution:

ProjectSolInfo

We can then view the components that form this solution, I wont go into them all but here are a few. Looking at the Entities we can see the Entities used in this solution, here I’ve drilled down to the fields in the Project entity:

ProjEntity

I will go into more details on the fields via the PowerApps interface. Looking at the Model-driven Apps we can see Project:

ModelApp

Now lets switch to the PowerApps interface (make.powerapps.com) and view some of the entity fields. Click Data > Entities from the left navigation menu:

PAppsEntities

Change the view from Default to Managed in the top right corner and scroll down to Project:

ProjectEntity

Click Project to view the Project entity details:

ProjEntityDetails

Here we can see the fields, relationships, rules, views etc. We can also view the current data stored in that entity:

PEntityData

The current main entities used by Project are:

  • Bookable Resource
  • Project
  • Project Bucket
  • Project Task
  • Project Task Dependency
  • Project Team Member
  • Resource Assignment
  • User

There are others such as Replay Log Section, Work template etc. but those above are where the core data is stored that you see in the UI. Some of these entities are new for Project but some have just been extended or updated to add new components, for example, adding new views or fields to existing entities to support Project. More on the entities later on when we explore the Power BI report pack I created as that is a good way to visualise the data model and the relationships between the entities. Let’s click Apps from the left navigation menu, select Project and click edit:

ModelDrivenProject

This then loads the app designer displaying the new Project app – this is what you see when you are in the Dynamics interface that we looked at yesterday. This shows the components that make up the model-driven Project app – more on this in future blogs posts.

Now lets go back to looking at the entities / data model for Project, for this I will use the Power BI template I published yesterday https://gallery.technet.microsoft.com/Power-BI-Report-Pack-4506f183 Once downloaded and opened in Power BI desktop you will need to provide the CDSUrl for the parameter. There are several ways to get the correct URL for your default CDS instance. Your Office 365 Administrator can access the PowerApps Admin Center, click Environments, click the default environment which loads this:

CDSEnvDetails

Then click See all:

CDSURL

Copy the Environment URL. Or you can get it from the browser when accessing project.microsoft.com. When accessing that page, open the browser dev tools and access the Network tab then reload the page, look for GetModProdCdsEndpoint then click the Response tab and find the cdsUrl property value:

CDSURLDEVTOOLS

Once you have the correct CDS URL, enter that into the CDSUrl parameter input – minus the trailing slash:

CDSURLPowerBI

Click Load and sign in when prompted. To access all data, the account used here will need Read access at the business unit level to the Project CDS entities used plus the other default roles a normal user is assigned to. If the user account doesn’t have the correct access the connections will fail. You could create a new “project report access” role in the Dynamics 365 instance where Project is deployed if needed. To do that, access the Dynamics 365 admin center from the Office 365 Admin center using the Global Admin account. Open the default Dynamics 365 instance (this is where Project is deployed to) then click the Settings Cog > Advanced Settings. Then click Settings > Security > Security Roles > New. Give the new role Read access at the business unit level to the 8 entities used in the report:

    • Bookable Resource
    • Project
    • Project Bucket
    • Project Task
    • Project Task Dependency
    • Project Team Member
    • Resource Assignment
    • User

Then access the user account that will be used for the report from the Dynamics Security admin in the Users page then assigned the new role to this account using the Manage Roles option. Other roles and role assignments are as per the default settings. I covered this for the Roadmap report pack I released at the start of the year: https://pwmather.wordpress.com/2019/01/30/project-roadmap-powerbi-report-pack-ppm-projectmanagement-msproject-projectonline-office365-powerplatform-dynamics365/ – for Project the role needs access to different entities. Once the data has loaded in the Power BI report, click on the Model button on the left navigation menu to view the data model:

DataModel

Click the Manage Relationships button to view the relationship table:

RelationshipTable

Here you can see how the different entities / tables are related.

I also published a report yesterday that combines the Project (CDS) data with the Project Online data (1 PWA instance) https://gallery.technet.microsoft.com/and-Online-Power-BI-Report-d1fbca1b This report uses the slightly less CDS entities:

  • Bookable Resource
  • Project
  • Project Task
  • Resource Assignment
  • User

It also uses the following tables in the Project Reporting API:

  • Assignments
  • Projects
  • Resources
  • Tasks

This reporting combines the following tables:

  • Assignments and Resource Assignments
  • Projects and Project
  • Project Task and Tasks
  • Resources and Bookable Resource
  • Resources (Filtered to users) and Users

I will create separate blogs post dedicated to these Power BI Reports in the next week or two but download them and see what you think, hopefully they either give you want you need for reporting or help you build the reports you do need.

Look out for more blog posts and videos on Project in the future!

Welcome to the new Project – getting started Part 1 #MSProject #PPM #Office365 #PowerPlatform #ProjectMangement #ModernWorkManagement #CDS #PMOT #PMO

October 29, 2019 at 5:08 pm | Posted in App, Functionality, Information | 1 Comment
Tags: , , , , , , ,

As you might have seen, the new Project started rolling out a week just over a week ago but the official release is today, here is the blog post from Microsoft: https://www.microsoft.com/en-us/microsoft-365/blog/2019/10/29/new-microsoft-project-rolls-out-worldwide/ If you didn’t see the earlier Microsoft blog post announcement about the start of the rollout it can be found here: https://techcommunity.microsoft.com/t5/Project-Blog/The-new-Project-is-rolling-out/ba-p/909721 In the blog post there are a few links that are worth spending time reading both as Project partner and a Microsoft Project user.

In this blog post we will take a look at the new Project.

How do I access the new Project?

Once the new Project has reached your tenant, creating new projects is via the Project Home, the same place new Roadmaps are created: https://project.microsoft.com/

ProjHome

You can also access this via the Dynamics interface as seen later on in this post.

Clicking the + New blank project button will launch the page:

BlankProj

Building the project schedule

Straightaway you you can start creating tasks on the grid. The first thing to do is give the project a name – click the “Untitled project” and the project name panel will launch:

ProjName

Here you can give your project a name, change the project owner and project start date if required. Click the “Add new task” row and you can enter the the first task:

ProjTask

Enter the tasks as required:

ProjTasks

Hover over the task and you you will see an i icon and an ellipsis, clicking the ellipsis launches the task callout with actions for the task – you can also right click on the task to see the task callout:

TaskCallout

Clicking the i icon or the Open details on the task callout launches the task details panel:

TaskDetails

Build the schedule as needed, here is my simple example:

Tasks

At this point there are no dates associated with the tasks. I will now add some durations as this will add start and finish dates to the tasks:

TaskDurations

TaskDate

So we now have some tasks, durations and dates, what about the other information like task links, % complete etc.? These columns can be added by clicking the + Add column button:

Column

Add in the columns required – notice that Successor and Predecessor have been renamed. Here I have added in the columns I want:

ColumnsAdded

Columns can be reordered by dragging the column to the required position, column widths can also be adjusted as needed too. The columns added here and the ordering is persistent for this project for all users who have access to it. Let’s add some task links. There are several way to do this, from the Task Details panel using the Add dependency button:

AddDepButton

Click it and you can select a task:

AddTaskDep

Now the Task Details panel updates to the show the linked task with the finish date of the linked and a label to show the task is “Driving”, Task1 in this case is driving Task2:

TaskDepDetails

The Grid has also updated to show the task IDs for the task links in the Dependents (after) and Depends on (before) columns:

GridUpdateDeps

You can also create task links by directly clicking in the Dependents or Depends on cells and select the task/s from the list:

DepColsCreateDeps

Or type the task ID or task name in the cell:

CreateDepsByTyping

The other way I want to show you is the slickest way by far, click the Timeline button next to the project name. This loads the interactive Gantt chart:

Gantt

As you hover over a task bar on the Gantt you see controls appear:

GanttHover

Here I can drag the end of one task to the start of another to create the task link:

DraggingDep

The Gantt will then update to move the tasks as required:

TaskLinked

I have now created my simple project task schedule:

TasksSchedule

Assigning Resources

Who used to get fed up having to build the team to be able to assign an enterprise resource? Well not anymore! Hover over the Assign to cell next to the task and click the + person icon:

AddResource

This launches the control to add a resource:

AddResourceControl

Now simply start typing the name of the resource to search the tenant directory and click the resource to add them:

ResSearch

Clicking the user will launch a modal pop up:

Groups

As you can see from the pop up, I firstly need to connect my new project to an Office 365 Group. I can either create a new Office 365 Group or link to an existing one. For this project I will create a new Office 365 Group. Clicking the proposed group name loads the Office 365 Group menu – update as required:

GroupDetails

Then click Create and assign to assign the resource. When adding other resources to the project, you will see another modal pop up:

AddMemberPopup

This is just to warn you that the user will be added to the group and have access to the project data, groups files and Microsoft Teams etc. – more on this in the Project Access Model. One point to note, you can no longer assign anyone to a summary task:

SummaryTaskRes

This was bad practice anyway but it’s great Project now prevents this!

You might also want to plan and manage your plan in an Kanban type task board, this is covered using the Board view, this is accessed by clicking the Board button next to the Project Name:

BoardView

There are different view options available on the menu here:

BoardViewOptions

The default view is the “Group by Progress” view which has 3 fixed columns for Not Started, In Progress and Completed – more on this view when we cover task progress. Switching to the Bucket view, new buckets can be added:

BucketView

Click the “Add Bucket” button to create a new bucket. Task cards can then be dragged into the new bucket/s:

BucketTasksMoved

Switching to Group by Finish Date and you see the Task group in Next Week and Future:

BoardFinDate

Just note, if you drag a task from the Next Week column into the Future column, this will update the task’s and any linked dependent task’s Finish dates to the future. Notice on all of the Board views, Summary Tasks are filtered out.

Project / Task Progress

There are several ways to progress a task in Project. Starting with the Grid view, you can click in the % complete cell and type:

TaskProgress

Click out of the cell and you will see the progress:

TaskProgress1

You can open the Task Details panel and type the progress in the % Complete input:

TaskDetailProgress

In the Board view using the “Group by Progress” view, you can drag a task card to “In Progress” and this will update the task % complete to 50% or drag a task card to the Completed and this will update the task % complete to 100%:

TaskBoardProgressUpdate

You can also click the empty circles you see next to the Task names on all views, when you hover over these you see a tick appear inside:

Tick

Clicking the empty circle marks the task as complete, the same feature that exists in Microsoft To-Do:

Ticked

Project Saving and Editing

The project auto saves so no need to worry about saving and the concept of publishing no longer exists – how cool is that! Need to undo / redo something, just use Ctrl + z to undo and Ctrl + y to redo.

The new Project also supports co-authoring so multiple users can edit the project at the same time!

Project Access Model

The security for the new Project is built on the Office 365 Groups model – just like Project Roadmap. When you assign a user to a task, they are added to the Office 365 Group. All users in that Office 365 Group have edit access to the project. You can check group access very easily via the Group Members button in the top right corner:

GroupMembers

It is a very simple access model, you either can access and edit the project by being in the Office 365 Group or not access to the project at all.

Access via Dynamics 365

As mentioned at the start of this post, you can create new projects via the Project Home but also via the Dynamics interface. Access the Dynamics home https://home.dynamics.com and click the Project app, this will load the Project app:

D365Project

From here you can create new projects or edit / view existing projects, see below my test project opened in the Dynamics interface:

Summary View:

D365Summary

Tasks View:

D365TaskView

I can edit the project directly here or for a better experience, click the “Open in Project” button to edit in the full page experience this post has covered. I will cover more of the Dynamics interface in future posts.

Extensibility Options

As the new Project is built on the Power Platform Common Data Service for Apps (CDS), you have the power of the Power Platform to extend / build on top of Project using PowerApps, Flow and Power BI. I will have many posts in the future for extending / adding features for the new Project, maybe even one or two later this week / early next!

Reporting

All of the data is stored in entities in the CDS so reporting is very simple from tools like Power BI. I do have a Power BI report pack for the new Project, this can be downloaded here: https://gallery.technet.microsoft.com/Power-BI-Report-Pack-4506f183 In part 2 of this getting started series I will cover the Power BI Report pack. Here are some screenshots of the reports:

Report1

Report2

Report3

You can also add Projects from the new Project (I wonder how long I will keep calling it that!) into Roadmap.

I have also created a Power BI report pack that displays both Project (CDS) and Project Online data in the same reports to help with the transition where you might be using both applications side by side, this can be downloaded here: https://gallery.technet.microsoft.com/and-Online-Power-BI-Report-d1fbca1b The reports look very similar the the Project reports above.

Summary

Well the wait is finally over, the new Project is here, go give it a try! This post covers most of the end user features, in part 2 I will cover the backend. Project Online will still be a part of the Microsoft PPM offering so I will still continue to include blog posts / videos for Project Online. I have also published an intro video here: https://youtu.be/4OeegM8ScMk

#Project Roadmap #PowerBI report pack with #AzureBoards data #PPM #ProjectManagement #MSProject #Office365 #PowerPlatform #Dynamics365 #CDS #Odata #AzureDevOps

March 16, 2019 at 5:38 pm | Posted in Administration, App, Configuration, Customisation, Functionality, Information, Reporting | 1 Comment
Tags: , , , , , , , , , , ,

This post follows on from my last post where I posted about using Azure DevOps Azure Boards in Project Roadmap, in case that you missed it here is the link: https://pwmather.wordpress.com/2019/03/15/project-roadmap-azuredevops-azureboards-ppm-projectmanagement-msproject-projectonline-office365-powerplatform-cds/

In this post we will cover combining Azure Board data into the Roadmap Power BI report pack I released. Here is the blog on the default Roadmap Report pack if you haven’t seen that yet: https://pwmather.wordpress.com/2019/01/30/project-roadmap-powerbi-report-pack-ppm-projectmanagement-msproject-projectonline-office365-powerplatform-dynamics365/

image

I covered a similar topic the other week but for combining Project Online data here: https://pwmather.wordpress.com/2019/03/08/project-roadmap-powerbi-report-pack-with-projectonline-data-ppm-projectmanagement-msproject-office365-powerplatform-dynamics365-cds-odata/

With the Power BI Roadmap report set up and loading data from your Roadmap service which includes linked items from Azure Boards, we will now edit that Power BI report to bring in Azure Boards data. Firstly click Get Data > Odata Feed and enter the Azure DevOpps OData API URL like below:

https://analytics.dev.azure.com/organizationName/_odata/v1.0/

image

For details on the Azure DevOps OData API in Power BI, see this article: https://docs.microsoft.com/en-us/azure/devops/report/powerbi/access-analytics-power-bi?view=azure-devops

Click OK and sign in as required. In the Navigator window select Projects and WorkItems plus other tables as required:

image

Click Edit to load the Power Query editor. Edit the queries as needed, such as removing columns, remaining columns etc. but ensure you leave the ProjectId and WorkItemId columns in Projects and WorkItems queries as these are required to join the Azure Boards data with the Roadmap data. Once finished you should have at least 9 queries like below:

image

Click Close and Apply in the Power Query editor. Set up the relationships between the Projects table and RoadmapRowLinks and WorkItems table and RoadmapItemLinks:

image

Now update the Roadmap Detail page in the report as needed, as seen below outlined in red, I have included some project and work item level data from my linked Azure Boards Projects and Work Items:

image

It’s that simple!

#Project Roadmap #PowerBI report pack with #ProjectOnline data #PPM #ProjectManagement #MSProject #Office365 #PowerPlatform #Dynamics365 #CDS #Odata

March 8, 2019 at 11:04 pm | Posted in Add-on, Administration, App, Configuration, Functionality, Information, Reporting | 2 Comments
Tags: , , , , , , , , ,

As mentioned in previous posts, I said I will post on how to bring in Project Online data with the Roadmap service data in a Power BI Report. We will start off with the Power BI Roadmap report pack I published recently. If you missed it, it can be downloaded from the post below:

https://pwmather.wordpress.com/2019/01/30/project-roadmap-powerbi-report-pack-ppm-projectmanagement-msproject-projectonline-office365-powerplatform-dynamics365/

image

With the Power BI Roadmap report set up and loading data from your Roadmap service, we will now edit that Power BI report to bring in Project Online data. Firstly click Get Data > Odata Feed and enter the Project Online Reporting API URL like below:

image 

Click OK and sign in as required. In the Navigator window select Projects and Tasks plus other tables as required:

image

Click Edit to load the Power Query editor. Edit the queries as needed, such as removing columns, remaining columns etc. but ensure you leave the ProjectId and TaskId columns in Projects and Tasks queries as these are required to join the Project Online data with the Roadmap data. Once finished you should have at least 9 queries like below:

image

Click Close and Apply in the Power Query editor. Set up the relationships between the Projects table and RoadmapRowLinks and Tasks table and RoadmapItemLinks:

image

Now update the Roadmap Detail page in the report as needed, as seen below outlined in red, I have included some project and task level data from my linked Project Online Projects and Tasks:

image

It’s that simple, take a look and see what you think.

#Project Roadmap #PowerBI report pack #PPM #ProjectManagement #MSProject #ProjectOnline #Office365 #PowerPlatform #Dynamics365

January 30, 2019 at 12:19 am | Posted in Administration, Functionality, Information, Reporting | 3 Comments
Tags: , , , , , , , ,

I have released a solution starter report pack for Project Roadmap, this follows on from a mini series of blog posts on the Roadmap backend CDS database / app. The final post in that series can be found here: https://pwmather.wordpress.com/2019/01/22/project-roadmap-cds-app-overview-ppm-projectmanagement-msproject-projectonline-office365-powerplatform-dynamics365-powerbi-part-3/

As mentioned in that post, I would release the Power BI report pack I created. This report pack can be downloaded from the link below:

https://gallery.technet.microsoft.com/Roadmap-Power-BI-Report-8eaae91e

This report pack consists of 3 reports for Project Roadmap, these reports can be seen below:

Roadmap Summary page:

image

Roadmap Detail page:

image

Roadmap Sync Admin page:

image

Once downloaded, the report pack CDS data source will need to be updated to point to your target Project Roadmap environment. To do this you will need the Power BI desktop tool which is a free download here: https://powerbi.microsoft.com/en-us/desktop

Open the downloaded PWMatherRoadmapReport.pbit template file in Power BI Desktop and follow the steps below to point the CDS data source to your Project Roadmap environment:

  • When prompted, enter the correct CDS URL for the Project Roadmap environment:

image

image

    • I created a new role in the Dynamics 365 instance where Roadmap is deployed – access the Dynamics 365 admin center from the Office 365 Admin center using the Global Admin account. Open the default Dynamics 365 instance (this is where Roadmap is deployed to) then click Settings > Security > Security Roles > New. I gave the new role Read access at the business unit level to the 4 Roadmap entities used in the report:

image

    • I then accessed the user account from the Dynamics Security admin in the Users page then assigned the new role to this account using the Manage Roles option. Other roles and role assignments are as per the default settings:

image

    • Now the report will be able to access and load the data.
  • The report will update with the data from your Project Roadmap environment – this might take a few minutes.
  • Save the report.
  • Publish the report to the Power BI service and distribute / share as required.

Your Office 365 administrator / Dynamics 365 administrator will be able to help you out with the correct user account to use as they will probably have a preferred approach to granting access that might be different to the way I have done it here. Or they might want to set up this report, publish to Power BI and give you access via the Power BI service.

This does use the Power BI Common Data Service for Apps (Beta) connector – so this connector could change when released and the report might require some re-work.

I hope you like it and find it useful.

#Project Roadmap #CDS #App Overview #PPM #ProjectManagement #MSProject #ProjectOnline #Office365 #PowerPlatform #Dynamics365 #PowerBI Part 3

January 22, 2019 at 7:39 pm | Posted in Administration, Configuration, Functionality, Information, Reporting | 2 Comments
Tags: , , , , , , , ,

This post follows on from part 2: https://pwmather.wordpress.com/2019/01/07/project-roadmap-cds-app-overview-ppm-projectmanagement-msproject-projectonline-office365-powerplatform-dynamics365-part-2/ In Part 2 we reviewed the fields and looks looked at the data used by the Roadmap application. In this post we will continue with the Power BI report that was started in part 2. I have made some changes following on from the last post if you have been following and creating the Power BI report. The queries I have can be seen below:

image

Here are the query connection details:

  • Roadmaps queries msdyn_roadmaps but filter to only return roadmaps
  • RoadmapItems queries msdyn_roadmapitems but filtered to only return roadmap items
  • RoadmapRows queries msdyn_roadmaps but filter to only return rows
  • RoadmapRowItems queries msdyn_roadmapitems but filtered to only return row items
  • RoadmapItemLinks queries msdyn_roadmapitemlinks
  • RoadmapRowLinks queries msdyn_roadmaprowlinks

I have also used the Power Query editor options to remove fields I do not need, renamed fields etc. but that is standard Power BI functionality.

Then the following relationships have been set up between these tables:

image

Or the visual view:

image

Now this Power BI file is ready to start creating the reports. Here are some screen shots of example reports:

image

image

I will be releasing the Power BI file to download later this week that can be used as a solution starter for your Project Roadmap reporting. I will then create a blog post on how you can bring in other data from Project Online into the Power BI file.

#ProjectOnline #PPM #PowerBI Project Compliance Report Pack #BI #Reporting #PowerQuery #DAX #Office365

October 22, 2018 at 9:29 pm | Posted in Add-on, Administration, Functionality, Information, Reporting | 3 Comments
Tags: , , , , ,

This is a supporting blog post for a new Project Online Power BI Report Pack that I have published. This report pack provides examples for a project compliance / audit type check to ensure your projects follow certain planning standards. This follows on from the previous report packs that I published: https://pwmather.wordpress.com/2017/10/31/projectonline-ppm-powerbi-report-pack-v2-bi-reporting-powerquery-dax-office365/ This new report pack follows the same theme / styling. The compliance report pack can be downloaded from the Microsoft Gallery, the link to download the report is here: https://gallery.technet.microsoft.com/Online-Power-BI-Compliance-b45b657c

The report pack consists of two reports, a summary report for project level checks and a detailed report for tasks, risks and issues checks. These can be seen below:

Summary Page:

image

Project Details (Select a Project from the filter):

image

Same report but with a different project selected:

image

These reports only use default intrinsic fields so it should work for all Project Online deployments.

Once downloaded, the report pack data sources will need to be updated to point to your target Project Online PWA instance. To do this you will need the Power BI desktop tool installed. This can be downloaded here: https://powerbi.microsoft.com/en-us/desktop

Open the downloaded PWMatherProjectOnlinePowerBIAuditComplianceReportPack.pbit template file in Power BI Desktop and follow the steps below to point the data sources to your Project Online PWA instance:

  • In the parameter window that opens, enter the full Project Online PWA URL without the /default.asp – such as https://tenant.sharepoint.com/sites/pwa
  • Click Load
  • The data will now start to load and you will be prompted to connect
  • On the OData feed window, click Organizational account and click Sign in and enter credentials as required
  • Click Connect
  • On the Privacy levels window set the privacy as required
  • Click Save
  • The data will load – this may take a few minutes depending on the dataset size in Project Online
  • Access the Project Details page and select a project from the project filter
  • Save the report

Please note, some of the steps above might not be seen if you have connected to the Project Online instance from Power BI Desktop previously. This file can either be emailed around to colleagues with details on how to update the credentials to their own or what would be better is to create a Power BI app workspace and give users access: https://docs.microsoft.com/en-us/power-bi/service-create-workspaces

The checks in this pack are just examples and might not be applicable to your organisation but it will give you a good starting point it you do not have any compliance / assurance type reports today.

I will plan to update this in the future, so feel free to add comments for any suggested project compliance checks, provided they are generic enough and possible using only intrinsic fields, I will look to add these in a later release.

I hope you like it Smile

Reporting on #ProjectOnline Resource Cost Rate Tables #Office365 #PPM #PowerBI #Excel #PowerQuery #MSProject

August 10, 2018 at 4:18 pm | Posted in Administration, Configuration, Customisation, Functionality, Information, Reporting | 1 Comment
Tags: , , , , , , ,

The resource cost rate table details are not available in the Project Online / Project Server OData Reporting API (_api/ProjectData) but they are accessible using OData but from the CSOM REST API (_api/ProjectServer). In this blog post, I will walkthrough getting this data into an example Power BI report. It wont look pretty, that’s not the idea of this post!

To get this data you need to use the _api/ProjectServer API as seen below in the example for cost rate table A:

{PWAURL}/_api/ProjectServer/EnterpriseResources(‘{RESGUID}’)/CostRateTables(‘A’)/CostRates

Which gives the detail:

SNAGHTML5adc642

To get all of the resources different cost rate A details, you would need to dynamically pass in the RESGUID. In the steps below we look at doing this in Power Query so this would work for either Power BI or Excel but for the purpose of the blog post, I’m using Power BI.

In Power BI, create a new OData connection using the Get Data > OData option. Use the following URL:

{PWAURL}/_api/ProjectServer/EnterpriseResources(‘{RESGUID}’)/CostRateTables(‘A’)/CostRates

Update with the correct PWA URL and a valid resource GUID from that PWA instance. Edit the data so it loads the Power Query Editor:

image

I renamed this to fn_getResCostRateA as this will become a function. Open the advanced editor:

SNAGHTML581e6c0

The code needs to be updated to:

SNAGHTML5817fb9

Click done and you will see the following:

image

No need to do anything with the parameter or buttons. Now we need to add another data source in for the resource metadata. Add a new new OData data source in from the Power Query Editor window and use the following URL:

{PWAURL}/_api/ProjectServer/EnterpriseResources?$Select=Id,Name&$Filter=ResourceType ne 3

Update with the correct PWA URL. This will get the list of resource GUIDs to pass into the function and also the resource name to be used in the report. I renamed the connection to Resource Details – Cost Rate Table A:

image

Once you have edited the query as required a new custom column needs to be added to invoke the function created earlier. Click the Add Column tab then click Custom Column. Give the column a name such as GetCostRateADetails then enter the following: fn_getResCostRateA([Id]) as seen below:

image

When clicking OK, this might take a while depending on how many resources you have as this will invoke the function for each project and call the REST API, passing in the Id for that row and bring back the cost rate A table records. Once completed you will see the tables as below in the new custom column:

image

Now the column needs to be expanded, click the double arrow in the custom column heading and expand the cost rate fields:

image

Click OK and the data will refresh / load then display the data for the cost rate fields:

image

Notice for those resources with multiple cost rate table entries there are multiple rows per resource. These are just resources from the Microsoft Project Online demo content with updated cost rate entries.

That’s it, now load into Power BI and create the report – a basic table example below:

image

For other cost rate tables, repeat the process but replace the A for the other cost rate tables such as:

{PWAURL}/_api/ProjectServer/EnterpriseResources(‘{RESGUID}’)/CostRateTables(‘B’)/CostRates

This dynamic function process is the same process I’ve used and detailed before in previous blog posts for Power Query such as this one: https://pwmather.wordpress.com/2018/01/03/projectonline-powerbi-report-include-html-formatting-ppm-pmot-powerquery-odata-rest-part-2/

#ProjectOnline time phased data rollup for #OData reporting note #PPM #PMOT #BI

May 14, 2018 at 11:06 am | Posted in Administration, Configuration, Functionality, Information, Reporting | Comments Off on #ProjectOnline time phased data rollup for #OData reporting note #PPM #PMOT #BI
Tags: , , ,

Just a quick post to highlight a feature in Project Online when changing the rollup of timephased reporting data in Project Online as posted here:

https://pwmather.wordpress.com/2017/11/17/projectonline-time-phased-data-rollup-for-odata-reporting-ppm-pmot-bi-excel-powerbi/

As per the Microsoft support article below:

https://support.office.com/en-us/article/Configure-rollup-of-timephased-reporting-data-in-Project-Online-da8487fe-899e-4510-a264-e2ebc948928c

This mentions only the following endpoints in relation to this change:

image

You will also find that the ResourceDemandTimephasedDataSet endpoint is also impacted by this reporting setting if your projects are set to calculate the resource utilisation from the Project Plan / Project Plan Until. For example, if you have the timephased data setting set to Never as seen below and your projects resource utilisation is set to the Project Plan, the resource demand for those projects will not appear in the ResourceDemandTimephasedDataSet endpoint.

image

Just something to be aware of.

#ProjectOnline #PowerBI Report – Include #HTML formatting #PPM #PMOT #PowerQuery #OData #REST Part 3

January 16, 2018 at 8:18 pm | Posted in Configuration, Customisation, Functionality, Information, Reporting | 4 Comments
Tags: , , , , , , ,

Following on from my 2nd post in this mini series on reporting including HTML formatting in Power BI, in this post we will look at a couple more options that will refresh in the Power BI App Service. If you missed the previous posts, the links are below:

Part 1: https://pwmather.wordpress.com/2018/01/01/projectonline-powerbi-report-include-html-formatting-ppm-pmot-powerquery-odata-rest-part-1/

Part 2: https://pwmather.wordpress.com/2018/01/03/projectonline-powerbi-report-include-html-formatting-ppm-pmot-powerquery-odata-rest-part-2/

The options we will look at in this post require a process to get the Project HTML data into a source that can be queried from Power BI with one call. Firstly I will demonstrate a simple PowerShell script that will get the data and write this to a SharePoint list on the PWA site. This is process is very similar to a Project Online snapshot solution starter script I published back in August 2016: https://pwmather.wordpress.com/2016/08/26/projectonline-data-capture-snapshot-capability-with-powershell-sharepoint-office365-ppm-bi/ Once you have a script running to capture the data on the defined scheduled you will see something similar to the screen shot seen below:

image

Here you can see my process has run twice, once back in August when I first wrote this script and just now when I ran it again. As this is based on sales demo data, you can see in the two expanded examples the data has not changed but in a real world usage I’d like to think the data would have changed / been updated! Having the data in one list enables a SharePoint OData call from Power BI, as I have included the ProjectId in the data on the list, this can easily be joined with the data from the main Project OData Reporting API. As this data is in a SharePoint list you might need to consider the user permissions / access to the list. If this was running on a schedule, either from a Windows Scheduled task if on-prem or maybe a scheduled Azure Function if you wanted to make use of Azure PaaS, set the schedule to run before the reports were due allowing time for this process to complete. I won’t cover the PowerShell script in detail here as I will create a dedicated post for that in a week or two, but I will highlight the changes required if you were to start with the OData snapshot example.

  • The first API called was updated in this example to change the select query to just return the ProjectId:

image

  • After the while statement, the script will start a foreach loop and set the ProjectId to a variable:

image

  • Then the REST URL is constructed and the ProjectId is passed in. The select query includes the Project Name, Project ID and the Multiline custom fields that I want to include. I then make the various REST calls in a try / catch block, firstly to get the data:

image

  • Then to write the data to the SharePoint list:

image

Once that runs successfully with an account that has full access to all projects and edit access the the SharePoint list, your target list will contain all of the projects along with the selected fields. As mentioned, I will post this full script in a week or two once I get a chance to tidy a few bits up in the code sample but hopefully the screenshots of the changes along with the snapshot example PowerShell script, there will be enough pointers to get started. Now the data is in a single source, it is very simple to use in Power BI.

In Power BI Desktop add a new OData feed, in the URL field enter the SharePoint list REST URL for the source list, for example the REST URL I used is: https://tenant.sharepoint.com/sites/PWA/_api/web/lists/getbytitle(‘ProjectMutliLineFields’)/Items    where ProjectMutliLineFields is the name of my SharePoint list. Edit the query to launch the Power BI query editor. In this example, my source SharePoint list contains duplicate projects but in my report I want to only see the latest. The steps below will transform the data so that the report only has the latest version for each project record. Rename the query to IDandDate then remove all columns except for the ProjectId and Created columns:

image

Now group by ProjectId and get the Max Created value, I called this column “Latest”:

image

That will give you a list of unique Project IDs using the latest record. Now add a 2nd OData feed and use the same SharePoint list REST URL as in the previous step. Remove columns that are not required, I removed all expect for Title, ProjectId, Created and the multiline fields. Then rename the columns to meaningful names if required:

image

This query will currently contain the duplicate project records based on my example list, next I will merge this query with the IDandDate query using the ProjectId column and the Created/Latest column:

image

Hold down the Ctrl key to select more than one column per table for the merge.

This will add the new column into the table:

image

Click the double arrow on the column heading to expand the column then select the aggregate radio button. On the dropdown menu next to Latest select Maximum:

image

This will show a date value for the latest records, where a null is displayed, there is a duplicate record with a later date:

image

Filter out the null records from the Max of Latest date column and that is it. For the purpose of this blog post, I also added a 3rd query to the Project OData API to show data from the two sources. Close and Apply the data then ensure the relationships are correct, I also set the IDandDate query to be hidden in the report view:

image

Then design your report as needed making use of the same HTML Viewer custom visual:

image

As you can see, this is just a simple example like the others just to highlight the HTML formatting being rendered in Power BI.

Another option without having to write and maintain any custom code or write the data to a SharePoint list does make use of a 3rd party tool that extracts the Project Online data into an Azure SQL database as the data changes in Project Online. This particular tool is developed by the Product Dev team I lead at CPS and is called DataStore. This product is part of our edison365 product suite but is available on its own. This isn’t sales pitch so I won’t go into details here but I just wanted to give another option as some people prefer no code solutions. There are also other software vendors that do similar products for Project Online but I’m not sure if they include the multiline project level fields with the HTML. So using this tool or similar (check they include the HTML fields), you can get all of your Project Online data into an Azure SQL database, as mentioned, the DataStore tool will also include the HTML data as displayed below in the example SQL query below:

image

Power BI can get data from the Azure SQL Server and this data will also refresh in the Power BI App Service.

Feel free to contact me if you have any queries or questions but hopefully that gives you some ideas on including the HTML formatting in your Project Online reports using Power BI!

Next Page »

Create a free website or blog at WordPress.com.
Entries and comments feeds.