Configure OLAP cubes for Project Web App
Summary: Set up OLAP cubes for Project Web App in Project Server Subscription Edition, Project Server 2019, and Project Server 2016.
Applies to: Project Server Subscription Edition, Project Server 2019, Project Server 2016
This article describes how to configure a SQL Server Analysis Services cube for Project Web App. You must follow the procedures in this article for each instance of Project Web App that you create.
In this article:
Before you begin
Before you begin:
You need an instance of SQL Server Analysis Services where you can build an OLAP cube You also need the name of the account that is running the SQL Server Analysis Services service for one of the procedures in this article.
Ensure that the SharePoint system account is an OLAP Administrator on that instance of SQL Server Analysis Services.
You must have installed the following versions of SQL Server Analysis Management Objects (AMO) on each Application and Front-end role servers in the respective farms:
Project Server 2016 Project Server 2019 Project Server Subscription Edition SQL Server 2014 Analysis Management Objects (AMO) SQL Server 2016 Analysis Management Objects (AMO) SQL Server Analysis Services AMO is usually already installed with SQL Server 2019 and Analysis Services 2019. If not already installed, download the SQL Server 2019 Analysis Management Objects (AMO).
Configure the Analysis Services service account
To build OLAP cubes, the account running the Analysis Services service must have read access to the Project Web App in the SharePoint Content database. This access is granted using the PSDataAccess database role in SQL Server.
The first step is to create a SQL Server login for the account running the SQL Server Analysis Services service.
Use the following procedure to create a login for the SQL Server Analysis Services service account.
Note
If a login already exists for the SQL Server Analysis Services service account, you can skip this procedure.
To create a login for the Analysis Services service account
In SQL Server Management Studio, connect to the instance of the database engine where your Project Web App is located.
In Object Explorer, expand Security.
Right-click Logins, and then click New Login.
In the Login name box, type the name of the Active Directory account that is running the Analysis Services service.
Click OK.
Once the login has been created, you must grant the login access to the SharePoint content database where your Project Web App site is located. Use the following procedure to configure database access.
To grant database access to the Analysis Services service account
In SQL Server Management Studio, connect to the database engine.
In Object Explorer, expand Security.
Double-click the login for the Analysis Services service.
In the Select a page section, click User Mapping.
Select the Map check box for the SharePoint content database where your Project Web App site is located, and then, in the Database role membership for: <database> section, select the PSDataAccess check box.
Click OK.
The next step is to build an OLAP cube.
Build an OLAP cube in Project Web App
In order to configure the needed permissions in SQL Server Analysis Services, the OLAP cube must be created. Even if you do not plan to use the cube right away, you will need to build one now in order to configure the needed user access requirements.
Use the following procedure to build an OLAP cube.
To build an OLAP cube
In Central Administration, under Application Management, click Manage service applications.
Click the Project Server service application.
Hover over the instance of Project Web App for which you want to build a cube, click the arrow that appears, and then click Manage.
On the Project Web App settings page, under Queue and Database Administration, click OLAP Database Management.
On the OLAP Database Management page, in the OLAP Database Name column, click the DatabaseName link.
On the OLAP Database Build Settings page:
In the Analysis Services Server box, type the name of the instance of Analysis Services where you want to build the cube.
In the Analysis Services Database to be created box, type a name for the OLAP database.
Click Save.
On the OLAP Database Management page, select the row in the table for the cube that you just configured, and then click Build Now.
Monitor the Status field on the OLAP Database Management page until the status is Build Success!
After you have built the cube, you can grant users access to it.
Grant access to the Project Web App OLAP cube
To grant users access to the cube, you must add the following groups and accounts to the default ProjectServerViewOlapDataRole role in the cube:
If you're accessing the cube using Windows authentication, such as through Excel, your user account must be a member of the ProjectServerViewOlapDataRole role in the cube. Consider using Active Directory Directory Services groups to manage user access to the cube. If you're using Active Directory synchronization with Project Server, those groups may contain the users to whom you want to grant cube access.
If you're accessing the cube using Secure Store, such as when using Excel, the credentials of the Secure Store target application must be a member of the ProjectServerViewOlapDataRole role in the cube.
Use the following procedure to grant users access to the OLAP cube.
To grant user access to the OLAP cube
In SQL Server Management Studio, connect to Analysis Services.
In Object Explorer, expand Databases.
Expand the cube that you just created.
Double-click the ProjectServerViewOlapDataRole role.
In the left pane, select the Membership page.
On the Membership page, click Add.
On the Select Users or Groups dialog box, click Object Types.
On the Object Types dialog box, select the Groups check box, and then click OK.
On the Select Users or Groups dialog box, type the name of the Report Authors Active Directory group and the name of the data access account for the ProjectServerApplication Secure Store target application. Also type the name of any additional users or groups to which you want to grant cube access.
Click OK.
Click OK.