Extracting MSP_TIMEPHASED_DATA via ODBC connection to SQL Server

[WGHayes]

Our requirement is to extract the timephased data from the
MSP_TIMEPHASED_DATA table stored in SQL Server. After reviewing the
ProjDB.HTM project database tables information and examining the actual
storage or data in this table it looks like an near impossible job to
extract/interpret the data for a DAY by DAY view. Has anyone done this?

I have already developed a method to extract the timephased data using the
OLEDB provider for MS Project which presents task, assignment and resource
views of data at all time levels (i.e. minute, hour, day, etc..) This works
really well and we have developed a solution that meets our requirements
perfectly from this data source. However, this method requires the MS
Project be installed on the Workstation/PC (i.e. the OLEDB provider is
installed by MS Project). The problem with this is that ALL users(clients)
or our application software will require MS Project to be installed to be
able to view this data. Whilst it is NOT MANDATORY to have MS project
installed to run our software there will be certain aspects of our software
which cannot be used unless MS Project is installed.

Assistance, comments or advise most welcome.

Thanks,
WGH.

 

[Rod Gill]

Hi,

Firstly I agree that extracting timephased data from the database is a pain,
especially to make it accurate (to do this I'm sure you need to take account
of the calendar table as well.

Yes the oledb interface is easy.

Possible solution: write an application to read daily data from each project
into a separate database every night. Data won't be right up to date, but
you could provide a utility to re-read individual projects.

Your app then reads data straight out of your other database in whatever
format is easiest for you.

--
For VBA posts, please use the public.project.developer group.
For any version of Project use public.project
For any version of Project Server use public. project.server

Rod Gill
Project MVP
For Microsoft Project companion projects, best practices and Project VBA
development services
visit www.projectlearning.com/

 

[WGHayes]

Rod,

Thanks for the advice, however in my circumstance that approach is not
workable. I actually need the data to be dynamically available. (i.e. cant
accept overnight or even hourly refresh of data).

You noted that it was a 'pain' to extract the timephased data and make it
'accurate' but is it possible? Have you done this or is this approach a
'black hole'?

Thanks,
WGH.

 

[Rod Gill]

Hi,

I haven't ever finished extracting from the database because I felt the
calendar data needed to be taken into account, so gave up on it and used
other solutions. Either creating a copy of the data or using oledb from .mpp
files, or when using Project Server: using the MSP_VIEW tables.

Why is hourly refresh no good, especially if the schedule is only updated
once a week or less?

--
For VBA posts, please use the public.project.developer group.
For any version of Project use public.project
For any version of Project Server use public. project.server

Rod Gill
Project MVP
For Microsoft Project companion projects, best practices and Project VBA
development services
visit www.projectlearning.com/

 

[WGHayes]

Rod,

Thanks for the feedback.

The reason we would 'ideally' like the data to be available 'on-demand' is
that we have a timesheet portion to our application which allows the user to
enter ACTUAL time, FORECAST time and to also view PLAN time on a day by day
basis. Having the timephased data immediately available would be ideal as
employees entering actual/forecast time can also see the plan items and
furthermore perform various Actual V Plan V Forecast analysis.

I am more the willing to attack this problem from another angle if I cannot
interpret the MSP_TIMEHPHASED_DATA table data directly however, ideally, if
this could be done the It would be the best outcome.

WGH.

 

[Rod Gill]

For a timesheet, do you actually need to extract timephased data? You could
just offer the total work to do for the assignment, have the user enter
actual hours against days and edit the remaining work. You then need to
enter new timephased records to represent the actual work. Note that the
rest of the schedule won't update until it's opened in Project and
re-calculated.

--
For VBA posts, please use the public.project.developer group.
For any version of Project use public.project
For any version of Project Server use public. project.server

Rod Gill
Project MVP
For Microsoft Project companion projects, best practices and Project VBA
development services
visit www.projectlearning.com/