Project - OLAP Database error after SP1 install

Asked By R2

16-Jan-08 09:34 PM

My organization installed SP1 a few days ago and now when we attempt to
rebuild the cube, it stalls at the 'OLAP Database' point.  I have heard that
customers have experienced this issue.  Help!
--
Thanks,
R23

18-Jan-08 08:13 AM

Hi!

I have a customer that has experienced an OLAP database error after SP1 was
installed. The following information sums up what I have found out about the
problem.

PWA Cube building log:
===== Initiating cube build process =====
[1/18/2008 11:04 AM] Cube build request message has been added to the
Project Server queue
===== Verifying and running pre-build server event handler =====
[1/18/2008 11:04 AM] Verifying and running pre-build server event handler
===== Determining database and cube structure =====
[1/18/2008 11:04 AM] Cube build initialization started
[1/18/2008 11:04 AM] OLAP database and cube structure was determined
successfully
===== Building database and cubes =====
[1/18/2008 11:04 AM] Cube build session started
[1/18/2008 11:04 AM] Analysis Services session started
[1/18/2008 11:05 AM] Analysis Services session completed successfully
===== Verifying and running post-build server event handler =====
[1/18/2008 11:05 AM] Verifying and running post-build server event handler
===== Processing OLAP database =====
[1/18/2008 11:05 AM] Process OLAP database session started
[1/18/2008 11:05 AM] Analysis Services session started
[1/18/2008 11:05 AM] Analysis Services session failed with the following
error: Failed to process the Analysis Services database <DB_NAME> on the
measure group with the ID of 'EPM Timesheet', Name of 'EPM Timesheet' cannot
be processed because it contains MG dimension with the ID of 'Task Is
Overallocated', Name of 'Task Is Overallocated' with different granularity
attribute than its source object.

===== Process Completed =====
[1/18/2008 11:06 AM] Failed to build the OLAP cubes. Error: Analysis
Services session failed with the following error: Failed to process the
Analysis Services database <DB_NAME> on the <ASSERVER_NAME> server. Error:
Errors in the OLAP storage engine: The linked measure group with the ID of
'EPM Timesheet', Name of 'EPM Timesheet' cannot be processed because it
contains MG dimension with the ID of 'Task Is Overallocated', Name of 'Task
Is Overallocated' with different granularity attribute than its source object.

In the application log on the application server two error messages occur.
One of them is the general error thrown every time a queue job fails, the
other one is related to the actual problem, and has the following specifics:
Event Id: 7691
Source: Office SharePoint Server
Category: Project Server Analysis Cube Building
User: N/A
Description: Exactly the same as the error message from the PWA cube
building log.

On the database server no event log errors are generated during the build
process.

The system is set up as follows:
Database server: SQL Server 2005, Analysis Services 2005
Application server: Project Server 2007 SP1

Analysis Services Repository placed in an Access database.

Is this the same error you are seeing?
Has anyone else encountered this error and/or knows what could be causing
the problem?

PS: I have tried creating a new cube database, but the results are the same.

Regards,
Sindre

07-Feb-08 05:27 AM

s
he
=3D=3D=3D=3D
=3D=3D=3D=3D

ed
ot



k
ect.

s:
e.
d that

Sindre

Strip out the custom elements of the cube configuration and rebuild.
Then add back the custom configuration and rebuild.

regards

Mike

21-Jan-08 10:38 AM

Mike

Thanks for your reply. I have already tried stripping the cube of all custom
content though, and it still produced the same error. I also tried changing
the repository from the access database to a sql server database and creating
an all new cube database in AS, but neither solved the problem.

Do you have any other ideas on how to solve this?

-Sindre

07-Feb-08 05:28 AM

om
g
ing
1 was
ut the
=3D=3D=3D=3D=3D
ler
=3D
=3D=3D=3D=3D=3D
dler
ng
he
linked
cannot
rity
e
ror:
D of
t
'Task
object.
cur.
the
ifics:
ild
ing
same.
to
heard that

Sindre

It seems to me there is some corruption/problem in your data.  Try
processing the cube database manually and see whether the error
reporting in Analysis Services points you to the problem.

regards

Mike

07-Feb-08 05:28 AM

Hello,

It seems we are running into the exact same problem (database is a SQL
cluster).
Problem is we are just installing Project Server 2007 + SP1 + some
customization (mainly adding custom fileds)
So even with a brand new installation and an empty database we have
this error message :

===== Initiating cube build process =====
[1/22/2008 6:22 PM] Cube build request message has been added to the
Project Server queue
===== Verifying and running pre-build server event handler =====
[1/22/2008 6:23 PM] Verifying and running pre-build server event
handler
===== Determining database and cube structure =====
[1/22/2008 6:23 PM] Cube build initialization started
[1/22/2008 6:23 PM] OLAP database and cube structure was determined
successfully
===== Building database and cubes =====
[1/22/2008 6:23 PM] Cube build session started
[1/22/2008 6:23 PM] Analysis Services session started
[1/22/2008 6:23 PM] Analysis Services session completed successfully
===== Verifying and running post-build server event handler =====
[1/22/2008 6:23 PM] Verifying and running post-build server event
handler
===== Processing OLAP database =====
[1/22/2008 6:23 PM] Process OLAP database session started
[1/22/2008 6:23 PM] Analysis Services session started
[1/22/2008 6:23 PM] Analysis Services session failed with the
following error: Failed to process the Analysis Services database
ProjectServer_OLAP on the 10.100.200.71 server. Error: Errors in the
OLAP storage engine: The linked measure group with the ID of 'EPM
Timesheet', Name of 'EPM Timesheet' cannot be processed because it
contains MG dimension with the ID of 'Task Is Overallocated', Name of
'Task Is Overallocated' with different granularity attribute than its
source object.

===== Process Completed =====
[1/22/2008 6:23 PM] Failed to build the OLAP cubes. Error: Analysis
Services session failed with the following error: Failed to process
the Analysis Services database ProjectServer_OLAP on the 10.100.200.71
server. Error: Errors in the OLAP storage engine: The linked measure
group with the ID of 'EPM Timesheet', Name of 'EPM Timesheet' cannot
be processed because it contains MG dimension with the ID of 'Task Is
Overallocated', Name of 'Task Is Overallocated' with different
granularity attribute than its source object.

Hope that Microsoft or someone will find a fix or a workaround.
We've already try renaming the cube, cleaning the repository db ...
nothing solve this issue.

Thank you
Dje

07-Feb-08 05:29 AM

=3D=3D=3D=3D
=3D=3D=3D=3D

Dje

I have not seen this exact message, however it is possible to generate
a similar error ("Errors in the OLAP Storage Engine).  You do this by
deleting a value in a lookup table which is in use on a published
plan.
In your system I would create another instance of PWA, publish a 1
task plan with an assignment, enough data to create a cube.  Do not
create any custom fields or calendars (fiscal, time periods).  The
cube should build successfully.  If it doesn't there is something
fundamentally wrong with your installation.  If it does then it points
to a problem with your data.  Manuallly processing the cube in
Analysis Services will highlight the problem.

Mike

23-Jan-08 07:46 AM

Thanks again for your input, Mike. I have now done the following:
-  Processed all cubes and dimensions in the OLAP database from Management
Studio.
- Added the custom fields back into the cube configuration and initiated
build process.
- Processed all cubes in the OLAP database again from Management Studio.

The results are the same both with and without custom fields; the cube named
MSP_Project_Timesheet fails for the measure EPM Timesheet. (The error message
is the exact same as in the PWA OLAP build screen.) The EPM Timesheet cube
itself though, which only contains the EPM Timesheet measure, is processed
successfully.
To me, it seems that it must then be the consolidation between the EPM
Timesheet measure and the two other measures in the MSP_Project_Timesheet
cube that is the problem. I have read that Analysis Services 2005 SP2 do not
allow measure groups of different granularity to be mapped, could this be the
problem source?

Any further input would be greatly appreciated.

Regards,
Sindre

07-Feb-08 05:30 AM

Hello Mike,

Ok, I've made a lot of tests regarding this issue.

With the existing PWA site:
- With custom fields, project/assignment -> Cube Building Failed
- Remove everything (custom fields, projects/assignments) -> Cube
Building Failed

Creating a new PWA site:
- Empty site -> Cube Building Failed
- With one projects/assignments -> Cube Building Failed

Reinstalling from scratch the Project Server 2007 server but without
SP1, then creating a PWA site:
- Empty site -> Cube Building Succeed
- With custom fields, projects/assignments  -> Cube Building Succeed
BUT - after installing WSS SP1 and Project Server 2007 SP1 -> Cube
Building Failed still with a "Errors in the OLAP storage engine"

One very interesting point is to compare the Dimension Usage for the
definition is completely changed (would say corrupted) and it does not
work anymore.

For me the only solution is to re-install without SP1 and wait for a
fix from Microsoft :(

Regards,
Dje

07-Feb-08 05:31 AM

=3D=3D=3D=3D=3D
=3D
=3D=3D=3D=3D=3D



Dje

I have upgraded several PS2007 systems to SP1 and have not hit this
problem, These systems are using SQL/AS 2005 SP1 and SP2 (and in one
case AS2000 SP4), without a problem (see SP2 note in Sindre's mail). A
final thought - I have noticed when deleting a cube database the
metadata in the repository relating to that cube is not removed, a
related issue is that if you configure a calculated field it seems
impossible to remove it from the cube even after taking it out of your
config settings.
So you may have done this already, but try changing your cube settings
to a brand new cube database name, and see if that builds cleanly.

regards

Mike

regards

Mike

15-Feb-08 11:39 PM

a SQL
me
ave
he
dler =3D=3D=3D=3D=3D

=3D=3D
d
ully
ndler =3D=3D=3D=3D=3D
t
e
the
M

me of
n its
sis
ess
200.71
sure
ot
sk Is
..
rate
is by

ot

points


ot



ng
he


Sindre

Have you tried processing the cube database from within SQL Server
Management Studio?  When it fails it often provides a more specific
error message identifying the problem data.

regards

Mike

15-Feb-08 05:26 AM

Mike,

I have tried processing the cubes from Management Studio, and I have been
able to narrow the problem down to one specific cube, MSP_Project_Timesheet.
As far as I know this is a consolidated cube consisting of three other cubes:
EPM Timesheet, Resource Timephased and Assignment Timephased. All these cubes
can be processed successfully on their own, but when the
MSP_Project_Timesheet cube is processed the EPM Timesheet measure group
fails. The error message is the same in Management Studio as in PWA.

-Sindre

18-Feb-08 10:59 PM

e is a SQL
+ some
we have
to the
handler =3D=3D=3D=3D=3D
vent
=3D=3D=3D
mined
essfully
t handler =3D=3D=3D=3D=3D
event
e
abase
s in the
'EPM
e it
, Name of
than its
nalysis
process
100.200.71
measure
cannot
'Task Is
nt
nd.
db ....
generate
o this by
hed
a 1
Do not
=A0The
ing
it points

hout
ed
e

the
es not
or a
s
ne
). A
ur
gs


ng
lds,
e
t
ilding
lt the
Any
t.
es:
bes

Hi Sindre

I suspect it is a corruption in the reporting database -   maybe a
localisation issue - are you using english language version? Below for
comparison is contents of EPM Timesheet cube (sp1 standard).

regards

EPM Timesheet
Measures
EPM Timesheet
Actual Overtime Work Billable
Actual Overtime Work Non Billable
Actual Work Billable
Actual Work Non Billable
Planned Work
Fiscal Time
Fiscal Time
Members
All Time
Not Set
QtrNot Set
Not Set
Fiscal Year
Fiscal Quarter
Fiscal Period
Fiscal Day
Project List
Project List
Members
All Project List
fred
New plan
Project List Level 02
Member Properties
Actual Duration
Actual Finish Date
Actual Start Date
Calendar Duration
Currency
Duration
Finish Date
Fixed Cost
Percent Complete
Percent Work Complete
Project List
Project Manager
Project Type
Resource Plan Date
Resource Plan Type
Start Date
Status Date
fred
New plan
Resource List
Resource List
Members
All Resource List
Resource List
Member Properties
Code
Cost Center
Earliest Available From
Email Address
Group
Latest Available To
Material Label
@Consultancy
farmadmin
Generic Local Resource
Unassigned Resource
Unknown Resource
Task List
Task List
Members
All Task List
fred
New plan
Task List Level 02
Member Properties
Created Date
Duration
Duration Variance
Finish Date
Finish Variance
Modified Date
Percent Completed
Percent Work Completed
Start Date
Start Variance
Task Index
Task List
fred
New plan
Task List Level 03
Time
Time
Members
Year
Quarter
Month
Week
Day
Timesheet Class
Timesheet Class
Members
All Timesheet Class
Administrative
Sick time
Standard
Vacation
Timesheet Class
Member Properties
Administrative
Sick time
Standard
Vacation
Timesheet Period Status
Timesheet Period Status
Members
All Timesheet Period Status
Closed
Opened
Timesheet Period Status
Member Properties
Closed
Opened
Timesheet Status
Timesheet Status
Members
All Timesheet Status
Acceptable
Approved
In Progress
Rejected
Submitted
Timesheet Status
Member Properties
Acceptable
Approved
In Progress
Rejected
Submitted

19-Mar-08 09:30 PM

ase is a SQL
1 + some
e we have
=3D
d to the
nt handler =3D=3D=3D=3D=3D
event
=3D=3D=3D=3D
ermined
=3D
ccessfully
ent handler =3D=3D=3D=3D=3D
r event
the
atabase
ors in the
of 'EPM
use it
d', Name of
te than its
Analysis
o process
0.100.200.71
ed measure
' cannot
of 'Task Is
rent
ound.
y db ....
o generate
do this by
ished
sh a 1
=A0Do not
=A0The
thing
en it points
in
e
ithout
ceed
ube
e"
or the
does not
or a
his
one
il). A


your
ings

om
nd
ting
ields,
the
ect
P

o
building
uilt the
e. Any
n
eet.
ubes:
ubes

Sorry for the delay in getting back to you.

The corruption is normally around the OLAP Objects table.  Did you go
into the systems database in SQL and delete the OLAP object table?
That is probably where your corruption lies.  Let me know.

11-Apr-08 03:11 AM

Hi All,

Was this issue resolved as I am having the exact same problem after
application of Project Server 2007 SP1

05-May-08 08:13 AM

Hi!

We were able to solve this problem at our customer in the end. We used the
following process to solve the problem:

- Install SQL Server 2005 SP2 Cummulative Update 6. (This hotfix must be
requested from Microsoft.)
- Reinstall/Update SQL Server Native Client, SQL Server 2005 Backward
Compatibility Components and SQL Server 2005 Management Objects Collection,
all part of Feature Pack for SQL Server 2005 - february 2007, on the
application server.
- Delete OLAP Repository database.
- Delete Cube database.
- Create new OLAP Repository database (with new name), set user rights and
DSO connections.
- Delete all content in OLAP\Data folder, and in the specified locks
directory.
- Remove all custom fields from cube configuration.
- Back up and restore custom fields to rebuild Reporting database.
- Set up Cube database to be created with new name and run cube building.

If building succeeds:
- Add custom fields to cube configuration.
- Run cube building.

If building succeeds second time:
- Connect all existing Data Analysis views to new Cube database.

As you can see most of these steps were suggested earlier in this thread,
and it was the first two steps that did the trick. We don't know which one of
them actually solved the problem, as they both were applied between the last
failed and first successful cube build. The SQL hotfix probably isn't common
in most PS2007 installations where the cube building works fine though, so we
think the SQL feature pack update is most likely the solution.

Regards,
Sindre

09-May-08 04:53 AM

,

of
st
on
we
ote:
(database is a SQL
7 + SP1 + some
atabase we have
=3D=3D=3D
n added to the
er event handler =3D=3D=3D=3D=3D
server event
e =3D=3D=3D=3D=3D
as determined
=3D=3D=3D
d
ted successfully
ver event handler =3D=3D=3D=3D=3D
server event
=3D=3D
arted
d
with the
ices database
r: Errors in the
he ID of 'EPM
d because it
located', Name of
ttribute than its
Error: Analysis
iled to process
the 10.100.200.71
e linked measure
esheet' cannot
he ID of 'Task Is
different
workaround.
ository db ....
ible to generate
=A0You do this by
a published
publish a 1
ube. =A0Do not
ods). =A0The
s something
oes then it points
hecubein
iled
->Cube
but without
ng Succeed
P1 ->Cube
engine"
sage for the
cube
nd it does not
wait for a
hit this
and in one
e's mail). A
the
ved, a
seems
ut of your
besettings
eanly.
lds from
rver
elds and
Reporting

Hi Gordon

As Sindre identifies the SQL SP2 hotfix has a big impact on some cube
and data analysis problems.

regards

Mike