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 FilesMicrosoft SQL ServerMSSQL.XOLAPDSO9. (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 Server90SharedASConfig 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:-


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:

Comments (57)

  1. Alejandro says:

    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)

  2. 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.


  3. JCano says:

    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?


  4. Liliya.kantor says:

    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

  5. 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,


  6. Liliya.kantor says:

    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,


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

  8. Dave808K says:

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

  9. John Smith says:

    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.

  10. 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.


  11. Claudio Hidetoshi says:

    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 ?


    Cláudio Hidetoshi

    Rio de Janeiro – Brazil

  12. 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 for details.

    Best regards,


  13. Dave says:

    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?



  14. 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.


  15. david_s says:

    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?



  16. Hi Dave,

    If you have restarted AS, then I would check the logs for other clues, and try SQL Profiler to see where the process is breaking down.  Making sure all components are at the same relase level is also important.

    Best regards,


  17. david_s says:

    Hi Brian,

    I have removed the NETWORK SERVICE account from the administrators group and now I get this error.

    [21/01/2008 11:48 AM] Analysis Services session failed with the following error: Failed to connect to the Analysis Services server VSPROJECTOFFICESERVERS. Error: A connection cannot be made to redirector. Ensure that ‘SQL Browser’ service is running.

    ===== Process Completed =====

    [21/01/2008 11:48 AM] Failed to build the OLAP cubes. Error: Analysis Services session failed with the following error: Failed to connect to the Analysis Services server VSPROJECTOFFICESERVERS. Error: A connection cannot be made to redirector. Ensure that ‘SQL Browser’ service is running.

    After some more googling I think the problem is related to using a local machine account. This article also describes a smiliar problem.

    If I try to use the SQL Profiler to connect to the analysis service I get the same error.

    What do you think?



  18. Hi Dave,

    You are using a named instance of Analysis Services and this works on a random port.  The SQL Browser service is used for other connections to discover what port is used.  I assume that removing that account from the administrators group has either stopped SQL Browser from working – or it just cannot do its job.  I would check which of these is true and either give network service the permissions it needs – or assign a different account to run SQL Browser.

    Best regards,


  19. Scott says:


    You mentioned above that when viewing or building cubes in PWA that the client machine is talking directly to Analysis Services via the OLEDB 9.0 component.  Does this mean that each end user needs permissions on Analysis Server?  I’m seeing a situation where the end users can only successfully connect if they are added to the "ProjectServerViewOlapDataRole" for the OLAP cube or are added at the Analysis Server Properties level.  


  20. Yes Scott, users will need permissions on the cube.  The "properties" level shouldn’t be needed as this is more of an admin permission.


  21. Scott says:

    Thanks Brian.  So are the users supposed to be added manually?  I tried adding a group manually, but then when I re-built the cube the group had been removed and replaced with the users listed explicitly.

  22. Project users should get added – but if you need other users outside of your PWA users to see the data then these will need to be added manually – and yes the cube rebuild will push them out again.  I think Analysis Management Objects can be coded to add these back after a cube build.  I think Christope posted on this (see links on the left of my blog)

  23. David says:

    Hi Brain

    I am using a 2005 SQL server with project server 2007, they are both on the same server but when i try to build a cube it gives me the error of:

    26/03/2008 14:01] Failed to build the OLAP cubes. Error: Analysis Services session failed with the following error: Failed to connect to the Analysis Services server projectserver. Error: Automation error

    The system cannot find the file specified.

    I have done and checked all the info that you have given but to no luck, can you please help me to get this working?

  24. Daan says:

    I have the same problem as David.

  25. David says:

    HI Brain

    I think the file that it is looking for is one called a ‘00010004.bsd’ do you have any idea what that is?

  26. Daan says:

    Hi David,

    According to MS we had to uninstall the SQL feature packs we had and reinstall them again. That fixed the problem for us.

  27. David says:

    Hi Daan

    Did you have SP1 installed on your SQL server?  And does the uninstalling of the SQL feature packs affect SQL database or project server at all?

    Thanks for your help in this…

  28. David says:

    Hi Daan

    What does the SQL feature pack consist of?

    Microsoft SQL Server 2005 Books online

    Microsoft SQL Server native Client

    Microsoft SQL Server Support files

    Microsoft SQL Server VSS writer

    MSXML 6.0 Parser


    Are these all the programs that i would have to uninstall and reinstall again?


  29. Rick says:


    I have MS Project Server 2007 no SP on a single server with MS Windows Server 2003 patched to current revs and SQL 2005 patched to current revs.  I used all local accounts on the server.  

    I can get the cube to build and  I created a sample view and I can see it on the server itself.  

    I cannot see the cube off of the server.  I installed msxml6.msi, owc10.exe, SQLServer2005_ASOLEDB9.msi on the client.  No luck.

    When I go to ‘Reporting’ in PWA to look at the cube, it gives the following error “The query could not be processed.  The data provider didn’t supply any further error information.”


  30. Hi Rick,

    Other things you could check are that the required ports are open, you have IE set to access data across domains, and one other check would be trying to connect to the cube with Excel.  Also any client components should be at the same level as the server components – with the applicable patches.

    I hope this helps,

    Best regards,


  31. Rick says:

    Followup: regarding can’t see OLAP off of physical server…

    I had IE set to access data across domains.

    Ports.  I thought about the ports but didn’t have a way to test it and the Network guys are never to quick to answer 🙂  Thanks for the tip on trying an ODBC (MS Query) connection to the server.  It failed.  Utterly.  So, it’s ports.

    I’ll see what I can do there.  Thanks!

  32. dmax says:

    I have the same error:

    "Failed to build the OLAP cubes. Error: Analysis Services session failed with the following error: Failed to connect to the Analysis Services server <servername> Error: A connection cannot be made. Ensure that the server is running. "

    We have a named instance of SQL 2005, and the database engine files are installed on the D: drive, where there is enough space for Project and SharePoint to put their databases.  This is also where the Analysis Services folders ended up.

    I’ve gone through many attempts.  I’ve tried downloading the msmdrep.mdb file, and placing it in D:MSSQL2005MSSQL.2OLAPDSO9.  I changed the D:MSSQL2005MSSQL.2OLAPConfigmsmdsrv.ini file and the Analysis Services DSO properties to point to the msmdrep.mdb file.  Nothing seems to work.

    I just put the msmdsrv.ini file back the way it was, and reset all of the DSO properties back to their defaults.  Still not working.

    Is there anything else I can do?

  33. Hi DMax,

    Do you have the SQL Browser running?  If you are using named instances then the browser allows the ports to be identified.  Also obviously these ports need to be open.  I would suggest not using the mdb file but configuring the repository in SQL Server as per the TechNet article – option 2.  This makes it easier to troubleshoot why a conneciton isn’t happening.

    Best regards,


  34. DMax says:

    The SQL Browser is running.  My supervisor says the ports should be open, because we are behind the firewall.

    I’ve followed the TechNet article Option 2 step-by-step.  I ended up changing the DSO/RemoteRepositoryConnectionString again, because it was set to Jet instead of SQL Native Client.  I also found that Office Web Components was missing from my computer, so I installed that.  All of the other settings were correct.

    I still get:

    Failed to build the OLAP cubes. Error: Analysis Services session failed with the following error: Failed to connect to the Analysis Services server <server name>. Error: A connection cannot be made. Ensure that the server is running.

    Is there anything else I can try?

  35. DMax says:

    We also found that when trying to synchronize with Active Directory, we can search and add Users, but not Groups.  Could this problem be related?  Could there be a problem communicating through the OLAP group SQLServer2005MSOLAPUser$<server name>$<instance name>?

  36. DMax says:


    Previously, I could not type the server name and instance name in the Cube Build Settings, because when I typed it did nothing.  I found a way to type <server name><instance name> in notepad, and copy and paste it into the field.  This time, it accepted it.

    I have run the Cube Build, but it still fails.  However, it gives me more information now.

    "Failed to build the OLAP cubes. Error: Analysis Services session failed with the following error: Failed to connect to the Analysis Services server <server name><instance name>. Error: Your permissions on the server computer do not allow you to administer this Analysis server. "

    I have tried adding my ProjectSSPAdmin, SharePoint_Config, SQL2005Admin, and my account to both the Analysis Server users, and I’ve even added them as Administrators on the machine.  My account is a member of the Domain Administrators group anyway.  Still, I get the error above.

    What else can I try?

  37. CBO says:

    DMax, I have the exact same error. Let me know if you figure it out. Another variable in my set up is that the Analysis Services/SQL server is 64-bit and my Project Server application server is 32-bit. I’m trying to verify that the 32-bit components can communicate to the 64-bit Analysis Services.

  38. Hi DMax and CBO,

    Adding the SSP administrator as an administrator on the AS server is usually the step that resolves this problem – but a restart of AS is also required to recognise the change in permissions.  I  have used 32 to 64 without issues – but you do need to have all components at the same release level.

    Best regards,


  39. Dave says:

    Hi Brian

    I Have built a cube and I am able to view the information that I need to in Data Analysis, however when someone else tries to view it they get an error message saying:

    The quer could not be processed:

         o Cannot connect to the server ‘projectserver’.the server is either not started or too busy.

    I have tried adjusting the end users internet settings but that does not work.  I am confused as to why this is happening, the end users have been given the right permissions, but still cannot view it, but i and some others can?

    What would you advise?



  40. Hi Dave,

    I’d suspect either permissions or firewall issues.  Are these users on a different part of the network?  Are the users that are working actually administrators as far as Analysis Services is concerned?  I’d check the permissions within AS and the cbe to see who has access.

    Best regards,


  41. David says:

    Hi Brian

    Thanks for the quick reply, but with regards to the end users being administrators as far as AS is concerned, they are not but i dont think that is the actual problem, as when i log on to PWA on their local PC and try to view the cube in data Analysis it comes up with the same error, and i am wondering if there is something that i have left out or have not thought of.

    The end users are in the Group Project Managers, and i have enabled the Group Project Managers to view Data Analysis, so in theory thy should be able to see it.



  42. Hi Dave,

    Have you also added the specific view you have created in Manage Views to the category?  Enabling the users gets them the feature, but security can still block or enable specific views.

    However, the error looks more like a lower level problem such as a firewall blocking the connection, or DNS unable to find the server.  Can you use one of these PCs and connect succesfully?

    Best regards,


  43. And another quick test Dave – can the users connect with Excel to Analysis Services?  If not then the issue is outside of any PWA settings.


  44. David says:

    Hi Brian

    thanks for these, i will try them and let you know, how would i use Excel to connect to Analysis Services to see this?

    thanks for the help.


  45. Hi Dave,

    In Excel 2007 under the Data tab on the left hand side you can Get External Data.  Click From other sources and one of them is Analysis Services.  Just follow the Wizard, using the same server name and choose the AS database from the drop down – then you have the various cubes available.  In 2003 it is slightly different – but still available under the data sources option.

    Best regards,


  46. David says:

    Hi Brian

    I Think I might have solved this problem, for some reason end users with anything other than office 2007 cannot view the data analysis through pwa, it will only allow them to view it if they have office 2007 installed, i have tested this theory with a user that has 2003 installed, he could not access it on his local PC but logged onto a PC that has 2007, it works.

    Thanks for all your help though.



  47. Bela says:

    Hi Brian,

    When I log on to PWA use local network all function works fine. But when I log on  from internet and try to view the cube in Data Analysis it comes up an error: “Either a connection cannot be made to the ‘projectserver’ server, or Analysis Services is not running on the computer specified”. There aren’t any error with the other PWA functions (timetable, etc), they are OK.

    The client computer and the user are the same (a laptop with XP SP2, Office 2007 and IE7). I thought the problem is near firewall setup (ISA2006), so I made new roles to allow traffic on 2382, 2383, 1035 (it comes from ASConfig) ports, but the error is the same.

    Best regards,


  48. Hi Bela,

    You may need to configure Analysis Services to run over http (if you haven’t already) – take a look at Steve Haden’s blog at and see if this helps.

    Bes regards,


  49. Kresimir says:


    i had a same problem regarding error ""Failed to build the OLAP cubes. Error: Analysis Services session failed with the following error: Failed to connect to the Analysis Services server <server name><instance name>. Error: Your permissions on the server computer do not allow you to administer this Analysis server."

    I have used Systernal tool "filemon" and have come up with the interesting result.

    The SSP account was trying to write to the root of C: and tried to create cube file.

    After i have granted the SSP account write permissions to the root of C$ everything the cubes were built with no porblem

    I k ow this is not secure solution.. but.. i was in a time jam andd this helped…

    Hope this helps and best regards,


  50. Thanks for sharing Kresimir,

    Not sure why that account is wanting to write there but good catch.  Perhaps the default location for AS cube building is pointing to that location?

    Thanks again, and best regards,


  51. MCart says:

    For client machines accessing the OLAP Cube without an Office 2007 component installed, that are receiving the "The server is either not started or too busy" error can install the following Analysis Services component, to gain interactive access to the cube:

    Microsoft SQL Server 2005 Analysis Services 9.0 OLE DB Provider

    It’s about halfway down the page.

  52. Ray says:

    How can I upgrade my PS 2007 on 32 bits to a PS 64x, I need to change my app, becuase my MOSS is on 64, my Server is 64 but my PS is on 32 because when i take this deploy dosen’t exist 64 media fron PS2007.


  53. Hi Ray,

    Project Server 2007 is available in x64 – although I think trial versions are limited to 32 bit.  What media do you have?

    Best regards,


  54. Sébastien P. says:


     Do you try to run a Project Server 2007 in 64 bit and SQL Server 2005 32 bit without any problem?

    Thnaks in advance

  55. Hi Sébastien, that should give you no problems – but if I was investing in x64 I’d probably put the money into the SQL Server first to give the best performance returns. For 2010 you will need x64 for the Projec Server and SQL.

    Best regards,


  56. Ashley says:

    Thank you so very much Kresimir.

    Your suggestion to check the filesystem using Filemon solved the exact problem that you had for me too. For some reason the OLAP cube process needs to create a useless file called MSOLAP.Upd on the C: drive, and so giving the SSP user permission to create/write/read files in the C: drive fixes everything. Here’s some keywords for those doing the searches so they don’t have to spend 4 hours pulling their hair out like I did!

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

    Project Server 2007 Analysis Services Cube Generation Build