Timephased Data & Bowling Scores

[Debbie O.]

I have not found this problem on any of the newsgroups...
I am looking for an explanation as to how Project saves
timephased data into a SQL database:

I am using MS Project Pro 2002. I have turned on "Expand
Timephased Data in the Database" and am saving the
projects to a SQL server that contains all of the tables
from the MS Project 2002 SQL Schema. I use the
td_assn_script.sql script to extract timephased data.
When I run the script, the data shows up differently than
what is displayed in the Project itself.

When I look at the contents of table MSP_TIMEPHASED_DATA,
I see values that are not saved according to how it was
entered in the project. For example, I have a task that
shows the following Actuals values:
Mon 2/17 6.08h
Tue 2/18 6.85h
Wed 2/19 6.85h
Thu 2/20 6.85h
Fri 2/21 6.85h

When I look at the table, I see the following for the
TD_Values columns:
TD_VALUE1 - 410665.9062
TD_VALUE2 - 410665.9062
TD_VALUE3 - 410665.9062
TD_VALUE4 - 410665.9062
TD_VALUE5 - 363952.6593
TD_VALUE6 - null
TD_VALUE7 - 46713.24683

When I run td_assn_script.sql, I get the following data:
ASSN_UID CONTOUR_TYPE
PERIOD_START PERIOD_UNIT
VALUE
VALUE_UNIT
88 Assignment Actual Work 2003-02-17 08:00:00.000
hour 1.6666666666666666E-2 hours
88 Assignment Baseline Cost 2003-02-17
08:00:00.000 day 525.0
dollars
88 Assignment Baseline Work 2003-02-17
08:00:00.000 week 35.0
hours
88 Assignment Actual Work 2003-02-17 08:54:36.000 day
6.8444317695666665 hours
88 Assignment Baseline Cost 2003-02-18
08:00:00.000 day 525.0
dollars
88 Assignment Actual Work 2003-02-18 08:54:36.000 day
6.8444317695666665 hours
88 Assignment Baseline Cost 2003-02-19
08:00:00.000 day 525.0
dollars
88 Assignment Actual Work 2003-02-19 08:54:36.000 day
6.8444317695666665 hours
88 Assignment Baseline Cost 2003-02-20
08:00:00.000 day 525.0
dollars
88 Assignment Actual Work 2003-02-20 08:54:36.000 day
6.8444317695666665 hours
88 Assignment Baseline Cost 2003-02-21
08:00:00.000 day 525.0
dollars
88 Assignment Actual Work 2003-02-21 08:54:36.000 day
6.0658776557833338 hours

You will notice that the Actual Work for 2003-02-21
belongs in 2003-02-17. Why does this happen? Why does
the data get saved differently than how it was entered
and/or captured in Project? It looks like it is doing
what I like to call the "Bowling Score Methodology"!

I have spreadsheets & screen prints to show this.

Thanks for your help,
Debbie

 

[Debbie O]

I was afraid you were going to say that!

So how can I extract the data in a fairly accurate way for
earn vs. burn reporting?

We want to get to the day level by week.

Thanks,
Debbie

 

[Gary Chefetz [MVP]]

Debbie:

Start with the database reference tools:

http://www.microsoft.com/technet/tr...roject/project2002/reskit/toolbox/tbdbref.asp

Then, check this out:

http://www.microsoft.com/downloads/...8a-4e40-4bdd-b881-efeeb0db8d75&DisplayLang=en

--
Gary Chefetz [MVP]
http://www.msprojectexperts.com
"We wrote the book on Project Server"

*** Remember to look for line breaks in links posted to the news group, use
cut and paste for these.

 

[Debbie O.]

Gary, I have been using these two sites religiously for
finding information.

The sp_pjacutalsbyweek.sql script does the same thing that
the td_assn_script.sql script does. The problem is how
the data is stored in MSP_TIMEPHASED_DATA.

If I could figure out the patterns within this chaos, then
I can accurately work with the data.

If one uses the Analyze Timescaled Data in Excel, it pulls
the data directly from the .mpp file. If that same file
gets saved to the server, the data is stored differently.

I would like to use the data from the server, because it
is more granular and I don't need to open every project
file to get the data I need. The ATD in Excel, summarizes
the data for all the resources working on a particular
task.

TIA,
Debbie