Cascade Skyline - with Microsoft Logo and Project Support header - author Brian Smith

Microsoft Office Project Server 2007 and SQL Server 2005 Analysis Services - Building Cubes

Microsoft Office Project Server 2007 and SQL Server 2005 Analysis Services - Building Cubes

  • Comments 57

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:

Leave a Comment
  • Please add 3 and 7 and type the answer here:
  • Post
  • Thanks a bunch for posting this, actually this is the most useful information on Internet that i've found about this issue, I have only 1 question, the owner of the process ProjectServerOLAPCubeGenerator.exe is NETWORK SERVICE. Are there any problems, if I give permissions to this user? (beside a bad design, hehe)

  • Hi Alejandro,

    I must admit to a strong preference to named service accounts to handle specific functions - but apart from "bad design" as long as the account has the right permissions set and can communicate with any other servers they need to then it will work.

    Thanks for your feedback.  I'm trying to post information that people will find useful.

    Brian.

  • Great information about SSAS 2005. I've a question and a little problem.

    The question is if I need to buy the Enterprise Edition of SQL Server 2005 for enabling portfolio analyzer views on the internet/extranet (I think in SQL 2000 it was needed, isn't it?)

    The problem I've is creating calculated members from PWA, it always show me an error saying that the member has a bad name (or it also exists). I've tried to create a memeber with a simple MDX expression (the one shown in the Project Server SDK of Availability = [Capacity]-[Work]). Tried with another ones and have the same problem. Maybe I forgot to configure somethung in Analysis Services?

    Thanks

  • Hi Brian,

    I have Analysis Services running under SQLAdmin account, and SSP administrator is SSPAdmin. In order to enable cube building, I had to make both SQLAdmin and SSPAdmin accounts as administrators within Analysis Services. Also I had give SQLAdmin, SSPAdmin, and PS administrator permissions to read/write access to the DSO9 share. Only this setup allows successful cube building on my server. Do you possibly have any idea why my setup requires so many accounts to be added to various places unlike your description of the process? I would greatly appreciate if you could enlighten me on this matter.  

    Thanks, Liliya

  • Hi Lillya,

    The only permission that seems un-necessary is the PS admnistrator on the DS09 share.  I would expect the SQLAdmin to need to be in the OLAP administrator and have permission on DS09, and the SSPAdmin likewise.  Let me know if I am missing something here and if you think other permissions shouldn;t be needed.

    Best regards,

    Brian.

  • Hi Brian,

    Thank you for your reply.

    This is really strange but in addition to the SSPAdmin and SQLAdmin (AS service logon account) I do need to add Project Server instance administrator to the share. Otherwise I am getting the following error when building the cube:

    Failed to build the OLAP cubes. Error: Analysis Services session failed with the following error: Failed to delete the Olap database: PWA_AnalysisServices. Error: Your permissions on the server computer do not allow you to administer this Analysis server. Error:

    I didn't find any explanations why this happens. But if I add PS instance administrator to the Share the error goes away.

    Thank you,

    Liliya

  • I haven't seen this one.  I would check for any configuration settings such as adding extra dimensions, measures or MDX statements.  

  • Liliya, try to set the DSO\RemoteLocksDirectory value on the analysis server to \\yourserver\MSOlapRepository$\.  This was the only thing that worked for us after trying to permission everything every which way.

  • For info:

    After reading through this and many many other articles, the only way I could get the cube to build was to make the service account a local admin on both the PWA and SQL servers.

  • Hi John, thanks for this feedback.  Although that probably confers the right permissions you should be able to get the cube to builds as long as the service account for the SSP is an admin in Analysis Services, the service account for Analysis Services has datareader permissions for the reporting database and the repository is available read/write for both.  Making the user an admin is more permissions than it really needs.

    Brian.

  • Hi Brian,

    can I extend PS2007 database in Analysis Services, creating more two cubes ?

    i.e: PS2007 14 cubes and I want to add more 2 cubes with information about fields with formula and information from other databases (HR and SAP).

    Is it possible ? Or I must have to build another database to put the two cubes ?

    Thanks,

    Cláudio Hidetoshi

    Rio de Janeiro - Brazil

  • Hi Cláudio,

    As we always re-build the entire cube you would probably need to also re build/link to your other data. I think Analysis Management Objects would be the tool to use - see Christophe's posting http://blogs.msdn.com/chrisfie/archive/2007/08/06/customizing-analysis-services-cube-using-analysis-management-objects-amo.aspx for details.

    Best regards,

    Brian.

  • Hi Brian,

    I am having trouble building a cube in Project server 2007. I get the 7682 Event viewer error in the event log.

    I have configured an OLAP respository using a SQL Server 2005 database (called Analysis Services Repository) as per option 2 in the technet arcticle "requirements for using SQL Server 2005 Analysis Services with Project Server 2007 cude building services". The installation of WSS I am working with has NETWORK SERVICE as the SSP service account. I have followed your instructions "SSPAdmin needs to be admin in analysis services" but I'm not sure what I have to do here. I have tried setting the user mapping for the NETWORK SERVICE account to a number of db_ roles but to no avail. I have also tried adding a server role for the account, and restarted the analysis service after each change.

    Finally I added the NETWORK SERVICE account to the administrators group for the server. The cube built sucessfully after this.

    Could you tell me which server role and or database role I have to grant the NETWORK SERVICE account for the repository database or any other database if I totally missing the point?

    Cheers

    Dave..

  • Hi Dave,

    I think the piece you are missing is to open SQL Management Studio, Connect to Analysis Services, Right Click the server name and go to Properties, then click the option in the left pane for Security and add your user there.  It is a bit vague and not as straight forward as adding to groups was for SQL Server 2000 AS.

    Brian.

  • Hi Brian,

    thanks for the swift reply the I have already added the NETWORK SERVICE account to the Security page of the Analysis Services properties in SQL Management Studio. Any other tips?

    Cheers

    Dave..

Page 1 of 4 (57 items) 1234