Not all Published Projects Show in OLAP Cube in PS 2007

[Ken T]

Running Project Server 2007 SP1 and Post Sp1 + latest hotfixes

We have noticed that not all of the published projects that are visible in
PWA are showing up in the cube even though they have some published data and
we are using earliest project start date and latest project finish date, and
are not filtering the views in the cube by any other criteria. Its just a
smple view with the Project list as a dimension and Work and Actual Work as
measures.

is anyone else seeing a difference between project list in PWA and the
project list in the cube (when all usual requirements are met like should be
published, have a task assigned, etc)

Is there an MDX query that returns the project list from the cube with
total hours of work for each project regardless of level (ie want to include
all projects in levels 02, 03, and 04).

Thanks,
Ken

 

[Marc Soester [MVP]]

Ken, do you get any error messages in the Queue or the event log?
Thanks
Marc

 

[Ken T]

Mark,

There are not any indications in either log that projects are missing.
Manual reconciliations have shown that some projects have not been in the
cube in the past - we are trying to identify that list on an ongoing basis to
determine the reasons why. I think its likely that if projects are missing
there are valid data reasons for that, but without the reconciliation it
can't be proven definitively.

We can reconcile manually - We export the cube project view to Excel
(projects level 02-04) and that creates the first list. Then we query the
reports database MSP_EPM_Projects table for a list of published projects.
After that we reimport the Excel list into a temp table and compare the two.

I forgot to add ithe DB server is SQL 2005 SP2 + a cumuative hotfix rollup
to put the version of SQL Sever at 9.0.3175

Ken

 

[Marc Soester [MVP]]

hmmmm very odd Ken, I can only imagine that some data corruption takes place.
I am stabbing in the dark here but here are a couple of points I would check:

1. Are the projects that are missing in the cube really published?
2. Are the projects that are missing maybe archived?
3. Check the Proejct Name and ensure that no special caracters are in the
name ( which can prefent the cube from building)
4. Once a project is identified not beeing in the cube, export the proejct
and reimport it to see if the corruption is resolved, then rebuild the cube
and see if the project is present now.

These are the imediate things I would check. Let me know how you progress.
Sorry that I cant be of more help mate
Marc


--
Marc Soester [MVP]
National Manager: EPM
http://marcsoester.blogspot.com

 

[Ken T]

Hi Marc,
Thanks for the suggestions. Sorry for the tardiness of my response , but I
wanted to verify the answers to your questions before I replied.
1. Yes
2. No
3. None that we are aware of.
4. We have verified that they are clean and do not have any corruption.

We have created a report in SSRS that we run to help us identify those
projects that are not in the cube, but should be.
So at least we can automate the process partly.
Thanks,
Ken

 

[Marc Soester [MVP]]

Sorry Ken, I wish I could help you, but I have not come accross this issue
before. Maybe worth logging a Microsoft support call. They can be fairly
quick with their response.
I cross my fingers that you get it worked out soon
Marc
--
Marc Soester [MVP]
National Manager: EPM
http://marcsoester.blogspot.com