#ProjectServer 2019 missing data in #OLAP cube? #SSAS #Reporting #Excel

November 29, 2019 at 10:56 pm | Posted in Administration, Configuration, Fixes, Functionality, Information, Installation, Issue | Leave a comment
Tags: , , , ,

Just a quick post and one related to Project Server on-premises which I haven’t done for a long time! A colleague escalated an issue to me where by data was missing in the OLAP cube in a new Project Server 2019 deployment. The symptoms where that data appeared to be missing from the Portfolio Analyzer cube. For example, adding Project List into the pivot table didn’t display any projects but when looking at the values in the Project List dimension from the Pivot Table fields menu you could see the projects:


Adding the Project List to the Pivot table, no Project Names included:


If another cube was used such as the Project Non Timephased, the Projects displayed on the pivot table as expected. If the built in measures where removed from the OLAP cube configuration and the cube rebuilt, the Project List then displayed fine on any OLAP cube. The fix is a simple one, it relates to a the Reporting Timephased Data change that on new installs this is set to Never which is the same behaviour for new PWA site collections in Project Online. Change the Reporting setting to another option such as daily:


Then publish the projects, once the projects are published, ensure the built in measure are enabled as required in the OLAP cube configuration if you have changed these and set the OLAP cube to build. Once processed the OLAP cubes will all work as expected.

#ProjectOnline Supporting Projects and Programs Part 3 #PPM #MSProject #Office365 #PMOT #PMO #SharePoint #PowerBI

October 1, 2018 at 6:27 pm | Posted in Administration, Configuration, Customisation, Functionality, Information, Reporting | Comments Off on #ProjectOnline Supporting Projects and Programs Part 3 #PPM #MSProject #Office365 #PMOT #PMO #SharePoint #PowerBI
Tags: , , , , , , ,

In part 3 of this mini series of blog posts we will look at a basic report example to support projects and programs making use of the configuration changes in part 1 and 2. For those of you that missed part 1, see the post here: https://pwmather.wordpress.com/2018/09/19/projectonline-supporting-projects-and-programs-part-1-ppm-msproject-office365-pmot-pmo/ and part 2 here: https://pwmather.wordpress.com/2018/09/21/projectonline-supporting-projects-and-programs-part-2-ppm-msproject-office365-pmot-pmo-sharepoint/

Now that we have done some very simple configuration changes in PWA and the Project Sites and then populated some example test data in the PWA instance we can look at example reports. We won’t cover creating these reports from start to end as this isn’t the purpose of the post, it is purely to highlight how to make use of the configuration changes to give to the program level reporting. These reports are also not engaging or showing casing Power BI, so you will want to create much better looking reports as these are just used to show examples of the data!

Firstly, lets look at the Project Center so you get an idea of the Project data I have in this test instance:


Notice I have two projects tagged and 1_Program projects but one in each program. These are the projects that will provide the data in the first page of my Program report:


The slicer is using the Program custom field:


To limit the data on this page, I have added page filter using the Project Plan Type field and filtered to “1_Program” projects:


So this page shows data for the project tagged with “1_Program” in the Project Plan Type field and in this case, the project tagged with “IT Transformation” which in my data set is the “IT Change Program” project. I don’t have much data on this page but this is just to show the data is for the program level project.

The next two pages show similar details for the program, one shows the details and the other shows some charts (just to add some colour!) but they both work the same way in filtering data that is only relevant at the program level:



On these pages there are no page level filters set, the tasks, risks and issues visualisations all have a filter applied to only display tasks, risks or issues that are requiring attention at the program level. On the tasks visuals we are using the task level “Escalation Level” field and filtering to only include tasks tagged with “1_Program”:


On the risks and issues visuals, we do the the same but use the “Category” field and filter to only include risks or issues tagged with “1_Program”:


This provides quick access to data relevant to the program. As we can see, these are very simple examples but the concept can be applied to larger datasets with more fields and data but the first page / report example will only work providing you one have 1 project plan per “program” value tagged with “1_Program” in the “Project Plan Type” Project level field.

That’s it for this short series – I hope that you found it useful!

#ProjectOnline Supporting Projects and Programs Part 2 #PPM #MSProject #Office365 #PMOT #PMO #SharePoint

September 21, 2018 at 9:00 pm | Posted in Administration, Configuration, Customisation, Functionality, Information, Reporting | 1 Comment
Tags: , , , , , , ,

In part 2 of this mini series of blog posts we will look at the configuration on the Project Sites to support projects and programs. For those of you that missed part 1, see the post here: https://pwmather.wordpress.com/2018/09/19/projectonline-supporting-projects-and-programs-part-1-ppm-msproject-office365-pmot-pmo/ 

As the Project Site are SharePoint sites, this also has many configuration options but this needs to be considered careful based on your reporting requirements. Whilst all of the data in SharePoint is accessible for reporting not all data on the Issues and Risks lists is available in the Project Online OData Reporting API. Only the data from default list columns Microsoft include on the Issues and Risks are included in the Project Online OData Reporting API. Other data from custom columns on the lists is accessible but only via the SharePoint list REST APIs but this can be tricky to report on for a cross project report. Here is an example for accessing this data in Power BI reports: https://pwmather.wordpress.com/2016/01/05/want-to-query-cross-project-site-sharepoint-lists-in-projectonline-projectserver-powerbi-powerquery-bi-office365-excel-ppm/ As we want to keep this as simple as possible, we will ensure the data we need in synchronised to the Project Online OData API. The Category column on the Issues and Risks lists is the ideal default column to use for our requirements. By default this contains the following values:

(1) Category1
(2) Category2
(3) Category3

We will update these values for the Category columns to match the lookup table values we created for the Project Plan Type and Escalation Level PWA custom fields:



This is done on each list, for example access the Risks list, click the List tab then List Settings. Scroll down the page to the columns and click the Category column and update the values. Repeat for the Issues list then repeat for the other project sites. You need to be careful updating some of the default Issues and Risks columns as you can break the synchronisation processes to the Project Online reporting schema which the OData Reporting API uses. If you do break this sync, you will see queue errors in the PWA Manage Queue page. Changing just the choice values as I have will be fine and not cause sync issues but fully test changes to ensure the data syncs as expected with no queue errors. As the Issues and Risks use a list content type, these change need to be made in the site template so new project sites get new values and manually or via code in the existing project sites but that is beyond the scope of this post but here is a post that might help get you started: https://pwmather.wordpress.com/2016/07/08/access-projectonline-project-sites-using-powershell-and-sharepoint-csom-office365/ or https://pwmather.wordpress.com/2016/05/04/projectonline-projectserver-project-site-provisioning-using-office365-pnp-remote-provisioning-sharepoint-powershell/ When updating existing project site lists, you will need to consider existing data on those lists as they might be using values you are wanting to remove.

Now our project sites have the correct Category values for Issues and Risks, we can tagged the items as needed as seen below on an example project:





You could also update the Risks and Issues view to and views that filter to just Program or Project or group by Category etc. Now the project sites are updated, when Issues and Risks are created these can be tagged with the correct category to make these visible in Program level reports.

In the final part of this blog post series we will look at using this data in example Power BI reports.

#ProjectOnline Supporting Projects and Programs Part 1 #PPM #MSProject #Office365 #PMOT #PMO

September 19, 2018 at 7:57 pm | Posted in Administration, Configuration, Customisation, Functionality, Information, Reporting | 2 Comments
Tags: , , , , , , ,

Microsoft’s Office 365 PPM tool, known as Project Online is a very flexible tool in that it is fully configurable to support your organisations PPM requirements. An intro to some of the configuration options can be found in my getting started guide I wrote a few years back: https://pwmather.wordpress.com/2014/07/22/getting-started-with-projectonline-round-up-ps2013-office365-project-ppm-sharepointonline-pm-sp2013/ 

In this mini series of blog posts we will look at an option for supporting a simple project hierarchy of projects and programmes – known as programs across the pond. Due to the flexibility Project Online offers, there are several ways this can be done – there is no right or wrong way. The right way is the way that works for your organisation. In this example we will use custom fields to support projects and programmes, these will be at the project level, task level and also the issues and risks lists. But you could do this with Enterprise Project Types (EPTs) with different project site templates and custom fields but for the purpose of this blog post we will just use the fields and all projects are under that same EPT. In this series of posts we will look at the minimum required PWA configuration, the SharePoint configuration and then finish off with some simple example reports making use of the configuration changes we implement.

Firstly we will look at the PWA custom fields then the Project Site columns. In PWA navigate to PWA Settings > Enterprise Custom Fields and Lookup Tables. I created a new lookup table to hold the following values to determine the level, I called this Project Plan Type:


I created another lookup table called Program to list the programs used in the organisation:


As you can see, I just created two test / example program values just for the purpose of this blog post. Next I created two project level custom fields, one call Program linking to the Program lookup table and one called Project Plan Type linking to the Project Plan Type lookup table:


These are used to tag the projects with the correct project type and associate the projects to the correct program.

I also created a task level field called Escalation Level and linked that to the Project Plan Type lookup table:


This task level field is used to escalate / highlight tasks or milestones from the project plans up to the program level if needed.

These are the only fields I need to add to support my simple project / program scenario.

Next up I will configure a Project Center view to support my projects and programs, in PWA Settings navigate to Manage Views and create the new view/s as required. In this example, I copied the default Summary view, called it Programs. I then edited this new Programs view to include the two new project level fields – Program and Project Plan Type. I then added a grouping to group by Program then by Project Plan Type and sort by Project Plan Type:


Which results in – these are just test projects for the purpose of this blog post:


This view enables us to easily see the project and program data as well as aggregate the data to the summary grouping rows where applicable.

I then updated the Task Summary Project view to include the new Escalation Level field so that this new field can be used in PWA. It could also be added to an Enterprise Global view so that it was available by default in a Project Desktop client view/s. The updated view can be seen here:


Next, ensure the two new Project level fields are present on a Project Detail Page (PDP) so that users can set the values as needed.


We are now able to capture the schedule data required to support this simple scenario for projects and programs. The details for each project are managed as normal in the “2_Project” type projects, any tasks or milestones that need escalating to the program would be tagged correctly and viewed in reports. Program level activities are managed in the “1_Program” type project, all of the program level summary details such a Status Summary as seen on the PDP image above are added to the program project. In the next post we will look at how we can support this on the Issues and Risks lists on the Project Sites.

Blog at WordPress.com.
Entries and comments feeds.