Monday, January 8, 2018

LAUNCHED - OData Improvements to Include Timestamps for Project Entities


Last year, I was excited about a roadmap feature that had been updated on the Office 365 Roadmap. I talked about it here.

Well, here is an even more exciting update as it has been released the 4th of January. I haven't seen any official blogs yet so I had a bit of an investigation to understand what changed.
Overall, Microsoft has kindly provided the modified information to the following tables;


DataSet
Added Field
Comment
Assignment Baseline Timephased
AssignmentBaselineModifiedDate
Date of the modified baseline (date it has been published)
Assignment Timephased
AssignmentModifiedDate
Date the Assignment has been last modified
Engagement Timephased
EngagementModifiedDate
Date the Engagement has been last modified
Engagements
EngagementModifiedDate
Date the Engagement has been last modified
Project Baselines
ProjectBaselineModifiedDate
Publish date of the last modified baselines
Resource Demand Timephased
ResourceDemandModifiedDate
Date of the Resource Demande last modified
Resource Timephased
ResourceModifiedDate
Date of the Resource last modified
Task Baselines
TaskBaselineModifiedDate
Date of the modified baseline (date it has been published)
Task Baselines Timephased
TaskBaselineModifiedDate
Date of the modified baseline (date it has been published)
Task Timephased
TaskModifiedDate
Date of the task last modified
TimesheetLine Actual
TimesheetLineModifiedDate
Date of the timesheet line last modified

That is really great addition. For example, as Guillaume was stating in his comment from the previous blog, we can now track changes in the baselines and validate when a baseline has been defined.
Therefore, we can easily add a bit of smart in the queries to download only if a data has been modified.
Here is a quick example of a Power Bi report using the Baseline Modification Data to highlight tasks baselined after the project has been baselined.

One thing I am not so happy about is that I was expecting to have a trending capability with some historical data stored somewhere. I guess I was too demanding. The new fields are providing with a last modified date at a lower level than the Project level but are not recording the historical values of the fields before they changed. Something that Microsoft is hopefully considering soon. you can vote here: https://microsoftproject.uservoice.com/forums/218133-microsoft-project/suggestions/15300603-implement-versioning-in-project-online.

Tell me what you think about that added information and what usage you may do of it!

Share this article :

Thursday, December 21, 2017

Seasonal greetings to you!

Hi all, 
I hope that your year has been enjoyable and full of interesting challenges. 

I wish you a joyful Christmas period and a successful and happy upcoming year.


Share this article :

Wednesday, December 20, 2017

OData Improvements to Include Timestamps for Project Entities


A quick article to share an exciting news for the Project Online community
While having a look at the roadmap for Project Online, I discovered a very interesting feature that a lot of people will be happy to see released and wanted to share with you.
Indeed, it looks like Microsoft is working on a capability to do trending on several elements of Project Online including Project, Engagement and resources data. 
I would be really interested to know what the etc. will be but it is a really good start.

Let's keep us posted!
Share this article :

Friday, December 15, 2017

PowerBI report pack V2 for Project Online: add the weekly timescale

I'm not a developer guy, thus I'm quite happy with PowerBI. I can do nice reports and have the feeling that I actually developed something. Microsoft provided a report pack updated (v2) which has been blog quite all over the web. But what I found (and my customers too) is that the monthly timescale for the capacity planning was too high level and they need to bring a more granular look to the resource capacity planning.

Of course, we all know that since a couple of weeks, you can choose the reporting granularity. But if like me you are not willing to update and migrate all your reports impacted by this change, you might just want to keep your original reports and add to them the weekly dimension.

So here is what they would like to have. Note the weekly timescale.

Nice isn't it? So how to get there starting from the report pack:

In the query editor, edit the TIMESET table, then the "removed other columns" step to add the week number.


Since this is a one-digit column, for the ordering to be done correctly (1, 2, 3...) and not (1, 10, 2, 3...), you need to force the week number to be 2 digits. Create a custom column with the following formula: 
Week = Text.PadStart(Text.From([TimeWeekOfTheYear]),2,"0")

Insert a new field extracting the 10 first characters of the date, then the 4 last characters to extract the year:

Finally concatenate the year and the week number doing a merged new column:

Apply the changes, and now you are good to use this new weekly time dimension in any of the visuals.



Share this article :

Wednesday, December 13, 2017

How to archive completed projects?

This is quite a common request from organizations. How to archive completed projects? Especially from organizations having a large amount of mid-sized projects. After a couple of years using Project, you can have up to 300 projects visible in the project center and in MS Project. Moreover you don't have anymore (that's a shame, I know...) the back-up and restore feature in Project Online, which allowed you in Project Server keeping one or many versions of a project. So you could delete the draft/publish versions, still having access to the archived versions from the archived DB.

That is to said : there is a way to archive properly completed projects. The easy but not ideal way is to simply filter out completed projects from the Project Center views, adding filters to the view definition. The issue of this way of doing if that the projects are still visible in MS Project/File/Open.

The best practice is to create a security category associated to a security group. Basically, you put all completed projects in the category where you deny all permissions. And you associate this category to a group where you add all non-admin users. So far so good. This process has been shared since almost 10 years across the Project community, so much that it has also been officially blogged by Microsoft here for Project Server 2010.

So why would I rewrite it? Because the procedure doesn't work anymore as-is. Don't know exactly when this happened, but Microsoft might have updated a setting in the back-office security model. Here is the trick: in the category settings, you need to check all views. I'll just detail these steps but keep in mind that all the steps from MS article (unpublish, project site) are still applicable.

SECURITY GROUP
You first create a security group for non-admin users. Put in it all users who should NOT see the archived projects. Of course, don't add yourself or any admin. 
That's it for the group.

SECURITY CATEGORY
Create a category, adding all completed projects. Do not check any of the options under the available/selected projects.

Add all the views to the category, that's the new thing:

Then add the archive group to the category. Click on the group once added and deny all permissions of the group. 

Et voilà!

Note that this is the only occasion where I use the DENY permission which is very strong. For example, if a user is a member of groups A and B associated with categories A and B, if a permission is granted on category A but not granted (meaning neither allowed or denied), the user will have the permission. While if the permission is granted on category A but denied on category B, the user will NOT have the permission. This is why by default, we never recommand to use the deny option, but rather not check any of the options.


Share this article :