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
LEFT OUTER JOIN
pjrep.MSP_TVF_EpmInternalProjectHierarchies('FF19B767-CA6D-4C4C-B123-C0B5AE5354D6') AS MSP_EpmInternalProjectHierarchies ON MSP_EpmInternalProjectHierarchies.ChildProjectUID = MSP_EpmProject.ProjectUID
LEFT OUTER JOIN
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)
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
LEFT OUTER JOIN
pjrep.MSP_TVF_EpmInternalProjectHierarchies('FF19B767-CA6D-4C4C-B123-C0B5AE5354D6') AS MSP_EpmInternalProjectHierarchies ON MSP_EpmInternalProjectHierarchies.ChildProjectUID = MSP_EpmProject.ProjectUID
LEFT OUTER JOIN
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)
Comments
Post a Comment