Baseline Info by Day in Project Server

[TroyS]

Does anyone know where/how this info is stored in Project Server?

I'm looking to extract Baseline Hours and Baseline Costs down to the
Project- Resource -Task level on a daily basis and i'm not seeing where/how
this info is extracted from Project server or how it is stored in the
database.

the reason for obtaining this information is for building a custom olap cube
outside of what portfolio analyzer

thx in advance.........

 

[Ed Morrison]

TroyS,
Assignment baseline information is stored in the msp_assignments table. It
is not stored in a timephased way. In my opinion, having timephased
baseline information is inaccurate, as one change to a tasks start date will
significantly change the daily differences between the currently
planned/actuals and the baseline work.

If you are intent on using this information in your OLAP cube, an easier and
more accurate way is to create a new version called .Baseline. Save to this
version when you are saving a baseline. You now have all information
baselined, not just the handful of fields that are saved with the baseline.
This version is automatically available to the Portfolio Analyzer.

For more information on timephased baseline information, see the thread
titled "Resource Usage" starting with the reply from Wil.

 

[TroyS]

Ed,
thx for the quick response.
as an fyi, i am using a version of the sp you reference in the thread on
'Resource Usage' from Wil. I can get timephased data for scheduled and
actuals from the timesheet table MSP WEB WORK. However, scheduled work isn't
necessarily baseline work ....unless the schedule never changes from
baseline, but we all know that does not hold true.

So i was looking at a way to get Baseline data similar to what comes in
Portfolio Analyzer in the std cube. We are customizing a cube outside of
portfolio analyzer to add additional information such as billing rates from
financials, task names, etc. I just want to be able to grab baseline work as
well in addition to the scheduled work and actual work from MSP WEB WORK.

Since the std portfolio analyzer olap cube has baseline info in it already,
i would then have to utilize 2 cubes (std portfolio analyzer and a custom
cube with additional info) or extend the existing std portfolio analyzer
cube using the Microsoft SDK, however, Microsoft will NOT support anything
in the olap extension sdk for project server and so i don't want to go down
that route (....i've already been to great dissatisfaction from Microsoft
Tech Support)

So, i'm wondering how the std portfolio analyzer olap cube builds this data
of Actual, Scheduled and Baseline unless it uses the objects and methods to
extract this info from the project schedules themselves.

thx.

 

[Ed Morrison]

TroyS,
If you are looking for the baseline assignment information on a daily basis,
you can not accurately get this information. This is because when you
create a baseline, it only saves the following fields for an assignment:
start, finish, work, cost, & cost per use. It does not split the task due
to either resource availability or the calendar. On the other hand, the
msp_web_work table does split the task.

So if a resource is scheduled to work 4 hours on Thursday, 4 hours on
Friday, and 5 hours on Monday, the msp_assignments table will have one entry
for the assignment and the assignment's baseline information. The
msp_web_work table will have two entries, one for Thursday and Friday
totaling 8 hours and one for 5 hours on Monday, but no baseline information.
This is why we use the msp_web_work table to get our timephased information.
The best we can do with the baseline information in the msp_assignments
table and assume that the assignment's baseline work is equal every day
(which may not be accurate), after we take into account the proper
calendars.

I believe that Microsoft left out the ability to timephase the baseline
because I think this information is of little value. Very few tasks start
on the original baseline date, sometime the start early, and sometimes they
start late. If a task starts one day early, a resource will show actual
work on a day that they were baselined as having none. On a report, this
looks bad, but in reality, it is very good!

If you or your management insist on having this report, the best way is to
have a complete copy of the plan saved when you want to create the baseline.
The easiest way to do this using Project Server is to create a new archive
version. (In PWA, Admin - Manage enterprise features - Versions.) You can
call this new version .Baseline. This new "project plan" is loaded into the
OLAP cube, so it is available for the Portfolio Analyzer views. No OLAP
extension necessary! If you don't want these .Baseline plans to appear in
Project Center views, you can filter them out.

I hope this helps.
--
Ed Morrison
msProjectExperts
"We wrote the books on Project Server"
http://www.msprojectexperts.com
FAQ - http://www.projectserverexperts.com

 

[Brian K - Project MVP]

Does anyone know where/how this info is stored in Project Server?

I'm looking to extract Baseline Hours and Baseline Costs down to the
Project- Resource -Task level on a daily basis and i'm not seeing
where/how this info is extracted from Project server or how it is stored
in the database.

the reason for obtaining this information is for building a custom olap
cube outside of what portfolio analyzer

thx in advance.........

Im no expert on the guts of an OLAP cube but you might look at the
existing, default cube and see how they get Baseline Work and Baseline
Cost. It's structure might give you some clues.

 

[Jan De Messemaeker]

Hi,

Let's be clear on this.
Project provides the timephased info on baselines, maybe Server doesn't, but
Project definitely does.
HTH

 

[TroyS]

Brian,
I'm not sure 'where' the olap cube gets it's baseline info down to the day
level from. Whether it's from the project schedule datafile itself or some
other table. I've not been able to determine that since it all seems to
happen 'behind' the scenes. I'll keep looking though....thx