Microsoft Office Project Server 2007 and SQL Server 2005 Analysis Services - Building Cubes
Following on from my permissions piece with Project Server I will extend this logic to the service accounts and permissions to get a successful cube build. I'll start with an explanation of what goes on when building cubes which should help any troubleshooting you do.
When you click on Build Cube then this kicks of a sequence of events starting with the save of any new or changed data in the cube settings - such as the server or cube name. Next a job will be placed on the Project Server queue requesting a cube build. This job will be picked off the queue and processed by the Microsoft.Office.Project.Server.Queuing.exe process, which will spawn the ProjectServerOLAPCubeGenerator.exe process. Both of these processes will be running under the identity of the admin account of the SSP - in my last posting this is the SSPAdmin. So this user needs to be an admin within Analysis Services so it can communicate through DSO to Analysis Services. This permission is added through a SQL Management Studio connection to Analysis Services by right-clicking the instance name and then selecting Properties, selecting the Security tab and then adding the user (a restart of the Analysis Services service at this point will also unsure the running instance is aware of the permission change) . This process also needs to access the repository of meta data used to define the cubes. This repository is detailed in KB 921116 (as are some other pre-requisites for multi server environments) and is in a share on the Analysis Services server called MSOLAPRepository$. If you have a single server then the share will not be used - instead the direct directory location of C:\Program Files\Microsoft SQL Server\MSSQL.X\OLAP\DSO9. (The X will be a number relating to the installation of analysis services). Therefore SSPAdmin, or your equivalent service account will need read and write access to this directory - and if you are in a multi server environment then also read/write access via the share.
The next activity in the cube building process is the Analysis Services executable - MSMDSRV.exe - actually building the cube based on the instructions given by the ProjectServerOLAPCubeGenerator.exe process. This executable runs under the identity of the account running the SQL Server Analysis Services (MSSQLSERVER) (or named instance) service. I'll refer to this account as ASAdmin So this account needs to be able to read the reporting database of the Project Server instance, which is in effect the staging tables for the cubes. Adding a login to SQL Server for ASAdmin with datareader role on ProjectServer_Reporting (or whatever reporting database name you are using) achieves this. That should be all you need to get a cube building.
So basically the SSPAdmin needs to be an admin in Analysis Services with read/write access to the repository. ASAdmin needs datareader access to the reporting database.
Also remember - when building a cube your application server is talking to/from your SQL Server Analysis Services server - when viewing or building views in Project Web Access your client PC is talking directly to your Analysis Services server (and each client needs the ASOLEDB 9.0 components). Make sure any firewalls allow for this traffic.
The default instance of Analysis Services will normally be listening on port 2383. If you have named instances then the SQL Browser service will need to be running on the server to tell give clients a port for the named instance. The SQL Browser is normally on port 2382.
Named instances of Analysis Services will have other dynamically allocated ports. These can be discovered by looking in the configuration file for SQL Server Browser. Open the msmdredir.ini file located at %Program files%\Microsoft SQL Server\90\Shared\ASConfig and look at the <Instances> section in it. On 64 bit machines this may be in the Program Files (x86) directory.
Here is an example:-
<Instances>
<Instance>
<Name>AS2005</Name>
<Port>1259</Port>
</Instance>
</Instances>
and would mean your AS2005 instance is listening on port 1259.
For my next post, rather than cluttering this one, I will show a variety of the errors from both ULS logs and Event logs that can appear if the above settings are not in place.
Technorati Tags: Project Server 2007