Stored project data in SQL database

[Nigel]

Hello,

All the project data gets stored in the Project Server SQL
database. I would like to go and extract and/or query
some of this data. Although I am familiar with SQL
databases and queries I don't know how the project info is
stored in the database. Without knowing which table names
and what-not to look at, I don't know where to start
looking for the data I wish to extract/query.

Is there a good reference guide somewhere that gives a
breakdown of how the project data is stored in the SQL
database ?

For example, I have custom defined non-project time
categories defined that people are using to report, well,
non-project time. I want to extract that info from the
database and analyze it various different ways (you know,
create graphs and whatnot of where we are spending out
time). However because I don't know the table or field
names that this info is stored under I don't know where to
start looking.

Thanks for any pointers on this,

Nigel

 

[Jason-W]

Hi,

There are two database references included with Project.
Both are located in:

C:\Program Files\Microsoft Office\OFFICE11\1033\

The files are called PJOLEDB.HTM and PJDB.HTM. If they
aren't in that directory, try looking on the CD that
Project came on.

You may also want to download the MSP 2003 SDK from MSDN,
which also has some information.
http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/pjsdk/html/pjsdkoverview.asp

HTH,
Jason

 

[Nigel]

Thanks Jason, that info is most useful.

I have played around with this a little but I seem to be
missing something at the 'internal workings' level.

I've trying to extract the stored non-project time
recorded by users on the server.

I can extract the categories of non-working time from the
database easily -> select * from
MSP_WEB_NONWORKING_CATEGORIES

However when I look at the stored non-working records
using -> select * from MSP_WEB_NONWORKING, I get nothing
returned, indicating that there isn't any data stored in
the DB.

However, when I log onto the server as a user and look at
the stored non-working time I can see time entered against
various non-working time categories.

When this time is entered an 'Update' cannot be done,
presumably because there is no project to update back to,
so a 'save' is done instead. The save stores the non-
working time but not in the database it would seem.

After writing all that I guess my question is, is the time
stored in the databse and I'm just looking in the wrong
place, or if it isn't stored in the DB where then is it
stored.

Thanks to anyone with useful advice.

Nigel

 

[ITG_Mike]

We do a lot of back-end reporting and all of the data you are looking for is in the DB. However, getting at it is not a simple matter. I will outline how we do it and you can decide if it is worth the effort

First, we have a view of the MSP_WEB_WORK table (where the work is stored). In this view, we filter the work to include just submissions by resources (as opposed to planned work). We further filter to only see time a user has submitted as long as the manager has not rejected the submission. I created this view and it actually uses the MSP_WEB_WORK, MSP_WEB_ASSIGNMENTS, MSP_WEB_TRANSACTION, and MSP_WEB_PROJECTS tables to extract the necessary information

Then, using this view as the source, I have a procedure that processes this data to create a new table with "accepted work by day" we do this processing because the WEB_WORK table does not have an entry per day, but instead groups days together when the hours per day are the same. My process creates a table with hours worked per day

Finally, using this new table as the source, we can query for hours per resource per project

Like I said, there is not a really simple process

Here is some SQL of just the initial slice of the WEB_WORK data

SELECT TOP 100 PERCENT dbo.MSP_WEB_PROJECTS.PROJ_NAME, dbo.MSP_WEB_RESOURCES.RES_NAME, dbo.MSP_WEB_ASSIGNMENTS.TASK_NAME,
dbo.MSP_WEB_WORK.WWORK_VALUE, dbo.MSP_WEB_WORK.WWORK_VALUE / 60000. AS Hours, dbo.MSP_WEB_WORK.WWORK_START,
dbo.MSP_WEB_WORK.WWORK_FINISH, dbo.MSP_WEB_WORK.WWORK_TYPE, dbo.MSP_WEB_WORK.WWORK_UPDATE_STATU
FROM dbo.MSP_WEB_WORK INNER JOI
dbo.MSP_WEB_ASSIGNMENTS ON dbo.MSP_WEB_WORK.WASSN_ID = dbo.MSP_WEB_ASSIGNMENTS.WASSN_ID AND
dbo.MSP_WEB_WORK.WRES_ID = dbo.MSP_WEB_ASSIGNMENTS.WRES_ID INNER JOI
dbo.MSP_WEB_PROJECTS ON dbo.MSP_WEB_ASSIGNMENTS.WPROJ_ID = dbo.MSP_WEB_PROJECTS.WPROJ_ID INNER JOI
dbo.MSP_WEB_RESOURCES ON dbo.MSP_WEB_WORK.WRES_ID = dbo.MSP_WEB_RESOURCES.WRES_I
WHERE (dbo.MSP_WEB_WORK.WWORK_TYPE = 1) AND (dbo.MSP_WEB_WORK.WWORK_UPDATE_STATUS = 0) AND
(dbo.MSP_WEB_PROJECTS.PROJ_NAME LIKE N'PROJECTNAME%'
ORDER BY dbo.MSP_WEB_PROJECTS.PROJ_NAME, dbo.MSP_WEB_ASSIGNMENTS.TASK_NAME, dbo.MSP_WEB_WORK.WWORK_STAR

Be sure and change PROJECTNAME% in the where clause to match your admin project name. Like I said, this is only the very beginning of our process, but hopefully it helps

Mik

 

[Nigel]

Thanks Mike.

I'm more interested at getting to the reported non-working
time. Although your information is useful and interesting
it only addresses project data in project schedules. The
info I'm after is the non-project time that you create on
the server under Admin/Customize MPWA/Nonproject time
categories, and report time against under Tasks/Nonproject
time.

I can get to the *project* info I want quite easily by
exporting data from the actual project schedule, after of
course I have updated the schedule with all the reported
time data/actuals/status reports.

However, I can't seem to find the nonproject time info in
the server database, and I can't export it from a schedule
because it's not in a schedule, rather its defined in the
server cfg itself.

Anyway, thanks again for the info. If you happen to know
where the nonproject time info is stored I'd be very
interested to hear about it.

Thanks again,

Nigel

-----Original Message-----
We do a lot of back-end reporting and all of the data you

are looking for is in the DB. However, getting at it is
not a simple matter. I will outline how we do it and you
can decide if it is worth the effort.

First, we have a view of the MSP_WEB_WORK table (where

the work is stored). In this view, we filter the work to
include just submissions by resources (as opposed to
planned work). We further filter to only see time a user
has submitted as long as the manager has not rejected the
submission. I created this view and it actually uses the
MSP_WEB_WORK, MSP_WEB_ASSIGNMENTS, MSP_WEB_TRANSACTION,
and MSP_WEB_PROJECTS tables to extract the necessary
information.

Then, using this view as the source, I have a procedure

that processes this data to create a new table
with "accepted work by day" we do this processing because
the WEB_WORK table does not have an entry per day, but
instead groups days together when the hours per day are
the same. My process creates a table with hours worked
per day.

Finally, using this new table as the source, we can query

for hours per resource per project.

Like I said, there is not a really simple process.

Here is some SQL of just the initial slice of the WEB_WORK data:

SELECT TOP 100 PERCENT dbo.MSP_WEB_PROJECTS.PROJ_NAME,
dbo.MSP_WEB_RESOURCES.RES_NAME,
dbo.MSP_WEB_ASSIGNMENTS.TASK_NAME,
dbo.MSP_WEB_WORK.WWORK_VALUE,

dbo.MSP_WEB_WORK.WWORK_VALUE / 60000. AS Hours,
dbo.MSP_WEB_WORK.WWORK_START,

dbo.MSP_WEB_WORK.WWORK_FINISH, dbo.MSP_WEB_WORK.WWORK_TYPE,
dbo.MSP_WEB_WORK.WWORK_UPDATE_STATUS
FROM dbo.MSP_WEB_WORK INNER JOIN
dbo.MSP_WEB_ASSIGNMENTS ON

dbo.MSP_WEB_WORK.WASSN_ID =
dbo.MSP_WEB_ASSIGNMENTS.WASSN_ID AND

dbo.MSP_WEB_WORK.WRES_ID =

dbo.MSP_WEB_ASSIGNMENTS.WRES_ID INNER JOIN

dbo.MSP_WEB_PROJECTS ON

dbo.MSP_WEB_ASSIGNMENTS.WPROJ_ID =
dbo.MSP_WEB_PROJECTS.WPROJ_ID INNER JOIN

dbo.MSP_WEB_RESOURCES ON

dbo.MSP_WEB_WORK.WRES_ID = dbo.MSP_WEB_RESOURCES.WRES_ID

WHERE (dbo.MSP_WEB_WORK.WWORK_TYPE = 1) AND

(dbo.MSP_WEB_WORK.WWORK_UPDATE_STATUS = 0) AND

(dbo.MSP_WEB_PROJECTS.PROJ_NAME LIKE N'PROJECTNAME%')
ORDER BY dbo.MSP_WEB_PROJECTS.PROJ_NAME, dbo.MSP_WEB_ASSIGNMENTS.TASK_NAME,
dbo.MSP_WEB_WORK.WWORK_START

Be sure and change PROJECTNAME% in the where clause to

match your admin project name. Like I said, this is only
the very beginning of our process, but hopefully it helps.

 

[ITG_Mike]

I think we have a version difference. In PS03, the non-Project time is stored in an administrative project. This can be accessed like any other project and that is the report process I provided

Perhaps if you specify what version of Project Server you are running, someone else can provide a solution for your version

 

[Nigel]

Mike,

Thanks for that info. I am running Sefver 2002. Does
anyone know if the nonproject time is stored differently
in that version ?

I run a script on MSP_WEB_NONWORKING and get no output
even though there is nonproject time stored.

I also run a script that lists all projects stored in the
database and don't see an admin project.

Can anyone offer some insight as to where the info is
stored in Server 2002 ?

Thanks,

Nigel

-----Original Message-----
I think we have a version difference. In PS03, the non-

Project time is stored in an administrative project. This
can be accessed like any other project and that is the
report process I provided.

Perhaps if you specify what version of Project Server you

are running, someone else can provide a solution for your
version.

 

[Ernie]

Nigel-
I also am using server 2002; the "non-project" time is
different from "non-working" time. I am trying to find a
way to extract the non-project time myself; as far as I
can tell "availability" is calculated every time, not
stored. So, the answer to your question depends on how
you developed your custom way to record "non-project"
time. I am interested in knowing the answer to that one
myself.