Wednesday, June 8, 2016

Reporting from Project Server 2016 - multiple sites and userviews

Just a quickie...
I've been interested in how MS have handled the "multiple PWA sites in a Content DB" thing since I read that this was their new approach.  Most of my reporting is via SSRS so i am reliant (still... in 2016) on DB queries rather than OData feeds (tsk) and this "querying a PWA DB with more than one PWA site in it is unsupported" quote was worrying me.

So it looks like what is happening is this.

When you create the first PWA site in a Content DB it hard-codes the SiteID into the _Userview view design elements.  This means that your first PWA Site is the default.  All the data for subsequent sites are still held in the tables against separate SiteID's but you cannot utilise the OOTB _Userview components (see below)

SELECT        ProjectFields....
FROM            pjrep.MSP_TVF_EpmProject('FF19B767-CA6D-4C4C-B123-C0B5AE5354D6') AS MSP_EpmProject 
                      pjrep.MSP_TVF_EpmInternalProjectHierarchies('FF19B767-CA6D-4C4C-B123-C0B5AE5354D6') AS MSP_EpmInternalProjectHierarchies              ON    MSP_EpmInternalProjectHierarchies.ChildProjectUID = MSP_EpmProject.ProjectUID 
                      pjrep.MSP_TVF_EpmTask('FF19B767-CA6D-4C4C-B123-C0B5AE5354D6') AS MSP_EpmTask 
             ON    MSP_EpmTask.ProjectUID = MSP_EpmProject.ProjectUID 
             AND  MSP_EpmTask.TaskIsProjectSummary = 1

now you can see lots of references to pjrep.MSP_TVF....  these are Table Functions that allow the Site ID to be passed and perform a SELECT against the core table.

The above views have the initial SIteID hard coded so (** WARNING:  NOT SUPPORTED APPARENTLY **) if you want to leverage _UserViews you will have to make your own using the appropriate SiteID references for the Table functions.

Or alternatively just use the Custom Field joins from the UserView view design in your own select statement to get just the fields required and avoid the userviews (ideal but hey... we can all be lazy sometimes).

So here's your lesson:  MANAGE YOUR CONTENT DATABASES!  Only allow one PWA site to be stored in the ContentDB otherwise it is just pain and anguish (hot topics for an England fan given its nearly the start of Euro2016)

Friday, June 3, 2016

Issue provisioning PWA on Root (HNSC or Site) in #projectserver2016

Following on from my previous 2016 PWA Setup post, I am encountering an issue with PWA on the root of a HNSC with PWA in ProjectServer Permission Mode.


 I want to create a pwa site on a HNSC at,  I don’t PWA as a pathed web under the HNSC (‘/pwa’) and I would like to avoid using a Host Header on a separate webapp with PWA provisioned at ‘/’ if poss as its horrible and ikkie


  1. Create a web app on server name http://fe1
  2. Create a root Site Collection on the web app
  3. Lock Content DB down
  4. Create new Content DB for the PWA site
  5. Create a HNSC called pointing to the above web app using the PWA#0 template etc

    New-spsite "" -hostheaderwebapplication "http://fe1" -name "PWA" -Description "Project Web App" -owneralias "Domain\user" -language 1033 -template "PWA#0"
  6. Lock the content db down
  7. Enable PWASite features and all that jazz
  8. Switch to Project Server permission mode
  9. Add a user to a PWA User Group to trigger the sync

At this point adding resources returns this in the ULS

An unexpected exception occurred while processing queue messages of type PSPermissionSynchronizePWASite, Exception: System.ArgumentException: Trying to get name of an SPWeb at the root web app is not allowed. 
SPweb?  huh?  I created an SPSite surely!

And this in the queue
 GeneralQueueJobFailed (26000) - PSPermissionSynchronizePWASite.PSPermissionSynchronizePWASiteMessage. Details: id='26000' 
For more details, check the ULS logs on machine 6ce47b31-e9bf-41c3-ae1d-0ebdf5c14dc6 for entries with JobUID 2112a0fb-fa27-e611-80bb-00155d940402.
ComputerName='6ce47b31-e9bf-41c3-ae1d-0ebdf5c14dc6' GroupType='PSPermissionSynchronizePWASite' MessageType='PSPermissionSynchronizePWASiteMessage'
For more details, check the ULS logs on machine 6ce47b31-e9bf-41c3-ae1d-0ebdf5c14dc6 for entries with JobUID 2112a0fb-fa27-e611-80bb-00155d940402.

So what does work so far and what doesn't....

  1. new-SPSite on an explicit managed path ('/pwa') or under a wildcard path '/pwasites/pwa1' - OK
  2. new-SPSite on Root of a Web App with hostheader as '/'  - FAIL (expected as it seems to be SPSite on Root that the issue is
 So it seems that PWA cannot yet be on root anywhere.... YIKES!

Wednesday, April 27, 2016

First look: #projectserver2016 installation first looks

** update 3 **
seems so far that creation of Root PWA sites doesn't work (sync issues in Project Permission mode).  I've written a post recently about this

Also _userviews are back... but only apply to the first Site in any Content DB.  See post 8 June 2016

** update 2 **
user sync issue is still happening on PWA.  not sure why as of yet....

** update **
I encountered a failure in the user synchronisation job in PWA which i think was atributed to creating the PWA HNSC site prior to creating the web app root site. whoops 
** end **

Project Server 2016 RTM first looks...........

Once you've installed SQL and SharePoint there are a few surprises for you under the hood of Project Server

Getting going

It is probably worth noting that the Project service is deemed "front end" in the miniroles for SP2016.  This is inline with my latter thinking on SP2013 and a welcome addition.  However i have gone Custom for my limited installation just for flexibility.


to enable PWA functionality you must enable the Project Server licence via powershell (under UAC)
Enable-ProjectServerLicence -key "key string"

Creating your PWA instance
So hear you seem to have two choices.  The approach is to create a site using the template PWA#0 (so you get your menu items) and then enable the Project Web App feature sets.   

1) Create a PWA site as a Site Collection on a pre-created Web App 
2) Create PWA as a HNSC on a pre-created web app

Basically any site can be a PWA (which is interesting and to be explored i think).

I am currently testing HNSC approach as follows:

Firstly create a kerberos auth web application and setup your SPNs

Then create HNSC (where fe1 is the webapp name)

New-spsite "http://pwa" -hostheaderwebapplication "http://fe1" -name "PWA" -Description "Project Web App" -owneralias "Domain\user" -language 1033 -template "PWA#0"

Enable PWA features on the newly created site 

Enable-SPFeature -Identity 697c6b49-3dff-4981-9394-0a62632120ec -url http://pwa
(Note:  This must be done using an admin account with Powershell under UAC or certainly had to for me.  Trying to do this via the Manage Site Collection Features in Site Settings always returned an unexpected error relating to ULS:  Failed to get [Admin] access level connection string because current user is not an administrator on the farm

Set-spprojectpermissionmode -url http://pwa -mode ProjectServer

** update ** I found it a lot easier to use the following command for enabling the PWA features Enable-SPFeature PWASite -Url xxxxxxxxxxxxxxxx Also, make sure that your root web application site has been created prior to creating your pwa site as there is a risk of failure of the user synchronisation job An unexpected exception occured while processing queue messages of type PSPermissionSynchronizePWASite, Exception: System.ArgumentException: Trying to get name of an SPWeb at the root web app is not allowed.

** END **
some things to consider

1) Firstly the Content Database for the Web App being used will be converted to a ContentDB+PWADB schema when creating the PWA Site.  i guess you could get around this by locking down and creating secondary contentDBs just for PWA data. This is next on my tests as this could be worthwhile when looking at performance separation on DB IO (too early to tell)

2) MS state "it is not supported to directly query the Reporting schema (pjrep) when more than one PWA instance links to the same database.  Therefore the secondary/tertiary DB option may be more interesting

this is where it's got interesting.  basically a wholesale re-engineering job has been done here. On first looks
1) some of the views are gone - anyone used to using MSP_EPMProject_Userview needs to think again!  The list of views is "curious"
** Update:  the userviews are present and correct.  However they only apply to the first SiteID created.  See recent post 08 June 2016 **

2) Multiple PWA instances are separated by SIteID in the database - remember MS doesn't support more than one PWA site in the db and direct querying (assuming odata is not included here)
3) on the IO front, I am not sure what the impact of serious reporting against the DB will potentially have on SharePoint web performance.  It is usable that this is negated via caching of SPSites 

4) good old creaky MSP_EPMAssignmentByDay is still there!  However we now have TimeByDay table also for referenceing Fiscal Periods in the reporting schema which is nice :)

5) due to the change in schema, most direct-query reports will need rewriting!  stick that in your upgrade budget and smoke it

next stop BI and SP Social and Search setup

Friday, February 26, 2016

Project Server SQL tips

This post is going to ignore Project Online as I am still a little underwhelmed by the reporting capabilities of the Excel+ODATA Power-shennanigans.  Mostly I spend my BI days in SQL Reporting Services for our on-premise customers, and even our larger Project online customers are moving back to local BI storage and SQL RS.

Anyway, rant over.  

So SSRS has been my friend for some years now and I continue to find new and interesting ways to make it do the things i want.  However the real fun is down in the TSQL area.  Here is a little selection of things I've found that may help you optimise or improve your report outputs.

Large datasets and the AssignmentByDay data

We all know that the MSP_EPMAssignmentbyDay table is a beast and is the main limiting factor when we look at time by period reporting.  Yes it would be great if MS did an automatic aggregation to weekly periods as part of the publish job but that is never gonna happen.  However it is possible to get some performance out of this monster table.

First things first.  get your indexes in order!  

Second things second.  NEVER USE THE VIEW.  

The MSP_EPMAssignmentbyDay_Userview is where madness lives.  The view loops through a join to the MSP_EPMAssignementBaselineByDay table and aggregates the Baseline0-10 values by day - even if you dont use more than one baseline.  Therefore you are executing a huge amount of SQL processing time just to get the values out.

Suggestion:  If you need both PLAN and BASELINE0 values, just use the tables in your join, ensuring you select the correct BaselineNumber when joining to the AssignmentUID

SELECT             foo
FROM               MSP_EPMAssignment A
LEFT JOIN       MSP_EPMAssignmentByDay AD 
    ON                 A.AssignmentUID = AD.AssignmentUID
LEFT JOIN       MSP_EPMAssignmentBaselineByDay BD
    ON                 A.AssignmentUID = BD.AssignmentUID   as BD.BaselineNumber = 0

Then you can extract your Baseline data and Planned data by day from the source tables without the overhead of the other baseline queries that are held in the view.


Lets face it most people don't need by day information except when looking at near-field operational level reports such as who's doing what next week.  These reports tend to have a limited date range and therefore the data set is reduced anyway, normally by adding a WHERE into the JOIN

                     SELECT foo 
                     FROM MSP_EPMAssignmentByDay 
                     WHERE TimeByDay >= @DateFrom 
                           AND TimeByDay <= @DateTo
                  ) AD on ..................

So summing up to Week or Month is by far and away the best method.  Doing this early can sometimes reap rewards too (see optimisation).

So just to make sure we know how to turn days into months, here's a sample :

SELECT                 SUM(ad.AssignmentWork) as WorkByMonth
                               ,DATEADD(Month,DATEDIFF(Month,0,ad.TimeByDay),0) as MonthPeriodStart
FROM                    foo ad

This MonthPeriodStart will return  YYYY-MM-01 00:00:00 type values which work great for reporting outputs as you can then fiddle with the date format output to display JAN-00 or similar.

Note:  It is always useful to aggregate early using a pre-select and join later (see below)


Pre-selecting from the AssignmentsbyDay

Sticking the ByDay table in a big long nasty TSQL JOIN can really bear down on your performance.  Therefore it can be useful to grab this data early, stick it in memory and then reference it.  Normal approaches can be #temptables, @tablevariables or Common Table Expressions (CTE).  I have a fondness for the CTE just because it is so useful in so many ways, especially its recursive nature (more to follow).

So to use the CTE as the pre-select you could do something like this:

DECLARE @DateFrom Date = 'datevalue'
DECLARE @DateTo     Date = 'datevalue'

; WITH Assn AS 
               (     SELECT foo 
                     FROM MSP_EPMAssignmentByDay 
                     WHERE TimeByDay >= @DateFrom 
                           AND TimeByDay <= @DateTo

SELECT        Assn.Foo
FROM           Assn -- cte name
INNER JOIN   on more tables as per normal

If we compare this approach with two standard SELECT models we see the difference in the estimated execution plan performance (on a small dataset)

DECLARE @DateFrom Date = '2015-01-01'
DECLARE @DateTo     Date = '2016-01-01'

--Option 1 CTE pre-select

; WITH Assn AS 
               (     SELECT * 
                     FROM MSP_EPMAssignmentByDay 
                     WHERE TimeByDay >= @DateFrom 
                       AND TimeByDay <= @DateTo
FROM MSP_EpmAssignment A
inner join         Assn  
                   on         assn.assignmentuid = a.assignmentuid 

--Option 2 Subquery for assignment by day data                     

FROM MSP_EPMAssignmentByDay ad 
WHERE TimeByDay >= @DateFrom 
                        AND TimeByDay <= @DateTo
) ad
inner join        MSP_EpmAssignment A 
           on        ad.assignmentuid = a.assignmentuid 

--Option 3 Standard inline query                     

FROM MSP_EPMAssignmentByDay ad
inner join MSP_EpmAssignment A 
on ad.assignmentuid = a.assignmentuid 
WHERE TimeByDay >= @DateFrom 
        AND TimeByDay <= @DateTo

(Also - spot the missing index!)

So we see that each in-line SQL execution costs 36% overall whereas the CTE pre-select costs just 29% which is a small but not insignificant win!

interestingly moving the WHERE clause into a subquery didn't really get us any joy.

Just for completeness, the temp table approach cost than all the other query methods (with this small dataset) although I would expect some gains when larger datasets are involved.  I have never seen it match CTE performance though but mileage may vary of course!

What happened to my months - filling in the gaps

When using [for example] a Matrix in SSRS or an Excel Pivot, if you are reviewing data from the AssignmentsByDay data and there are some periods where there are no data then you will lose months in the output.  This could be especially problematic if you are doing Missing Timesheet reports or Resource Capacity reporting where the fact that work/actual work may not exist in a period is actually the point of the report so missing it out is not valid.

So how to fix.  This is something I have been playing with recently and I am 100% not saying this is a perfect method but it's working right now.

So what we need is a fixed set of periods between @datefrom and @dateto 

Firstly I use a recursive CTE to build these weekly periods:

DECLARE @ViewFrom DATE = '2015-10-26'
DECLARE @MinDate date =@ViewFrom
DECLARE @MaxDate date =DATEADD(WEEK,27,@ViewFrom)

;WITH ctedaterange
     AS (
        SELECT        [Dates]=@MinDate
                UNION ALL
            -- recursion
                SELECT        DateAdd(WEEK,1,[dates])
                FROM           ctedaterange
                WHERE        [dates] < @MaxDate

SELECT * FROM ctedaterange

This will build us a set of 27 weekly periods between 2015-10-26 onward 

Next we want to use this CTE and create a set of rows by Resource and "Dates" so that each resource has a date period associated. We do this with a horrible JOIN as follows


This will build us a set of 27 weekly periods between 2015-10-26 onward for each resource!

Once you have this you have a number of options to begin to join that data created to your ByDay planned data.  I find joining on a combination of ResourceUID and time periods fields works well.  Alternatively you can create a UNION on this data to your by period query. Either way you end up with a full set of periods even when no data is planned in.  

I am not going to go into examples here as this goes way too deep for this blog post.  Sorry ;o)

Parent Child relationships in Task structures

A question I've come across quite often over the last year is how would you join a Task with AssignmentWork on it to another task further up the task tree (but not directly the parent summary task).  This is a specific need seem by those that are performing integration to external systems such as SAP PS, specifically aggregating to a SAP WBS code entered into a summary tasks in a plan somewhere up the task tree (as SAP PS is at a higher level than detailed operational planning).

This is easy when the summary task is just one level up (via the TaskParentUID field).  However what happens when the aggregation point is "somewhere above the Task Assignment" level.

To do this we need to JOIN the Task with the Assignment with an summary level field somewhere above it.  So in this example we are going to JOIN Task to a LEVEL 3 Task.


FROM             MSP_EPMTask tv

( SELECT TaskUID,TaskName, TaskOutlineNumber, ProjectUID,TaskWBS
WHERE TaskOutlineLevel = '3'            -- this gets the L3 task parents
                        )       L3

ON           CONVERT(NVARCHAR(100), L3.TaskOutlineNumber)+'.' 
                        = LEFT(tv.TaskOutlineNumber,LEN(CONVERT(NVARCHAR(100),L3.TaskOutlineNumber)+'.'))

AND L3.projectuid = tv.ProjectUID

So what we are doing here is using the TaskOutlineNumber ( from the TaskAssignment level and the L3 Summary level (1.3.1.) and joining on the 1.3.1. value.

So this works for a fixed value for the summary but you can also do a similar join approach to join onto the first, second, lowest summary above the Task Assignment that matches certain criteria], for example:  Find the first summary task above the Task Assignment where a SAP WBS code is entered.

To do this you need to use a RANK/PARTITION in the Summary JOIN

What RANK allows you to do is associate a numeric ranking to your SQL results table.  The Partition element allows you to Rank within a subset of the rows returned.  In this scenario the Partition is the Task Assignment UID as we want to find all the parents of that task and then select the one we want based on RANK.

Now again i am not gonna give you the complete view of this as it really depends on your data/query/requirements but here's a sample.

SELECT data.*
                 SELECT   RANK() 
                 OVER      (
                                    PARTITION BY tv.TASKUID 
                                    ORDER BY       L3.TaskIndex DESC
                                  ) r
                                  , l3.taskname as SummaryName
                                  , tv.TaskName
                FROM        (      SELECT * 
                                          FROM    msp_EPMTASK 
                                          WHERE TaskIsSummary = 1 
                                                  and SAPWBS IS NOT NULL 
                                    )  L3 
                LEFT JOIN (     SELECT * 
                                         FROM    msp_EPMTASK 
                                         WHERE TaskIsSummary = 0 
                                    )   tv
               ON        CONVERT(NVARCHAR(100), L3.TaskOutlineNumber)+'.' 
                                    = LEFT(tv.TaskOutlineNumber,   
    AND       L3.projectuid = tv.ProjectUID
) data

Now what this would return is a record for each summary above the task WHERE SAPWBS IS NOT NULL.  What we want is the first one (i.e. the lowest level task) where SAP WBS IS NOT NULL so one record per bottom level task.

To do this we add in a filter on the RANK alias r

WHERE data.r = 1 

and tadaa we have a table that shows the bottom level task and the lowest summary task where WBS Code is set.  Now if you include the TaskUIDs etc in the subselects you can join out to wherever you need from here.