Project Server 2007: OLAP Cube Building works every other time

So third posting of the day, and it had to be OLAP.  Thanks to Jon for this one, and also to my readers Kresimir, Tom and Pete.  Pete posted these steps which worked for him in overcoming build issues:

1. Setup DSO9 directory in \Program Files\Microsoft SQL Server\MSSQL.x\OLAP

2. Share the folder as MSOLAPRepository$

3. Give your SSP account full control of this share

4. Edit \Program Files\Microsoft SQL Server\MSSQL.x\OLAP\Config\MSMdsrv.ini - add the following in the DSO section near the end:

<RemoteLocksDirectory>\\SERVERNAME\MSOLAPRepository$</RemoteLocksDirectory>

<LocksDirectory>C:\Program Files\Microsoft SQL Server\MSSQL.x\OLAP\DSO9</LocksDirectory>

(where MSSQL.x is the directory that contains the OLAP folder)

5. Restart Analysis Services.

Similarly both Jon and Kresimir noticed through Process Monitor that the cube generator was trying to access the C:\ drive of the server.   Perhaps certain permissions hide the fact that this is happening, and it looks like the correct fix is to follow the steps outlined above.

In Pete’s case this was to overcome an error:

"Your permissions on the server computer do not allow you to administer this Analysis server."

and for Jon we were seeing every other cube build fail (Matt in the UK had this same symptom too).  It appears that the cube build works, but the subsequent build cannot delete the repository settings to re-write the new ones.  The same workaround as described above resolve this too.  The full error in this case had a specific sentance in front:

"Analysis Services session failed with the following error: Failed to delete the Olap database: xxxx. Error: Your permissions on the server computer do not allow you to administer this Analysis server.”

I am assuming I have always avoided this error by having the SSP admin also as a box admin – but I appreciate this isn’t always desirable outside of a test system.