Project Server SQL SSAS OLAP Cube-making Demystified

Microsoft Project Server gives you the ability to build online analytical processing or OLAP databases from the Project Server reporting tables. It also allows you to create multi-dimensional data cubes that can be used in reporting. The information presented in the cubes adds time-phased data to the standard reporting. This is a powerful aspect of Microsoft Project Server that let you to perform complex analysis of your Project Server data and to serve it up to individual groups within your organization that require specific data for the projects they care about.

But there’s a bit of confusion on various blogs and the Microsoft support forums regarding how to build OLAP cubes. There are variations in how SQL Server, SQL Server Analysis Services and Project Server OLAP Cube building are accomplished under different conditions in both Project Server 2010 and Project Server 2013 using the available SQL Server versions of 2008 R2, 2012 and 2014. Also complicating things are the SQL Server, SharePoint, and Project Server best practices for creating SQL Server aliases.

A simple explanation of the process will help to illustrate the relevant points of communications among the various products.

For the Project Web App (PWA) OLAP Build action, the products are Project Server, SQL Server Analysis Services (SSAS) and SQL Server (synonymous with SQL DBMS).

In basic terms (with many details omitted and an order that may not be exact), the process goes like this:

1. Project Server triggers the OLAP Build processing (either manually or via the timer job).

2. Project Server gathers details from PWA settings for items such as security, enterprise custom fields, OLAP database details (including the SSAS Server), the OLAP DB name and the PWA instance (from which is derived the SQL Server holding the “reporting” database and the name of that database).

3. The process determines if SQL Analysis Management Objects is available on the current Project Server computer to perform the build.

4. The process creates the XML Analysis (XMLA) job and sends it to the SSAS server specified in the PWA OLAP Build page for processing. An implicit permission check is done here because the SSAS server will reject the job if permissions aren’t right.

5. SSAS receives the XMLA job and begins processing the commands. SSAS creates a Data Source entry pointing to the SQL Server “reporting” database.

6. SSAS opens a channel using the SQL Server Native Client library to the SQL Server (DBMS) and database as specified in the Data Source.

7. The SSAS XMLA job directs SSAS to read the “reporting” database specified in the data source and pull in the desired information.

8. SSAS then assembles the OLAP database with the various cubes, dimensions and measures as specified both by PWA OLAP database configuration and the Microsoft-supplied OLAP build script.

9. When the OLAP database is completed, the PWA Project BI Center is updated with two new folders.

a. The Data Connections Library now has a new folder with the identifier of “SSAS Server name — OLAP Database name,” whose contents are the ODC connection files pointing to the cubes in the OLAP database just created.
b. The Templates Library also contains a new folder using the same name and whose contents are the Excel report templates pointing by way of the ODC files to the cubes in the OLAP database just created.
c. There is one ODC and Excel Report template pair for each OLAP Cube created (14 of them in total).

OK, if all of that goes perfectly, you’ll have your new OLAP database and the associated sample Excel files to create reports against the Project Server OLAP Cubes.

Now for the explanation from the SQL Server perspective.

Software and Settings

In this section we’re concerned with the SQL Server settings and libraries that permit all this communication. Specifically, we’re going to need the following items installed, configured and permissions set up correctly:

• *SQL Server Alias (best practice)
• SQL Server (DBMS)
• SQL Server Analysis Services (SSAS)
• Account permissions in SSAS and SQL Server (DBMS) to permit all this activity
• Project Server (where the Project Server SharePoint services are running)
• SQL Server Analysis Management Objects library (SQLSERVER2008_ASAMO10.msi)
• SQL Server Native Client library (2008: sqlncli.msi; 2008 R2: sqlncli_amd64.msi)
• PWA OLAP Build configuration page (where we’re going to specify the SSAS Server and OLAP database names)

The SQL library versions of Analysis Management Objects and Native Client are most important. The version of SQL Server (DBMS) and SQL SSAS are not as important. Project Server communicates over the version 10 libraries no matter which version of SQL Server DBMS or SSAS is being used. I prefer this formula:

• For Project Server 2010 use the SQL Server 2008 library version, since Project Server 2010 code uses this library version: Primary Version 10.00.nnnn
• For Project Server 2013 use the SQL Server 2008 R2 library version, since Project Server 2013 code uses this library version: Primary Version 10.50.nnnn

I freely admit that version 10 is the requirement for both Project Server 2010 and 2013 as confirmed by Brian Smith on his blog.

You can find these libraries on their respective SQL Server Feature Pack download pages. I recommend you take the latest service pack version also. As long as the primary version is 10, any SP version should be fine.

• Microsoft SQL Server 2008 Service Pack 3 Feature Pack
• Microsoft SQL Server 2008 R2 SP3 Feature Pack

Notes about Required Configuration

For this portion we’ll presume three server roles:

• Project Server
• SQL SSAS Server
• SQL DBMS Server

It doesn’t matter if these server roles are all on the same computer or separated. The required setup is the same.

• Project Server uses the SQL Server Analysis Management Objects libraries when communicating to the SSAS server. Therefore, this library must be found on each server where the Project Server Services are running (as Project Server is initiating the OLAP build).
• SSAS uses the SQL Native Client libraries when communicating to SQL Server (DBMS). Therefore, this library must be found on the SSAS server in order to make contact with the SQL Server (DBMS).
• The Project Server services service account (by default, the Farm Account) is required to be in the OLAP administrator’s configuration. This permits Project Server to send the XMLA job to SSAS and to have enough rights to create the data source and the database.
• The SSAS service account must have read access to the Project Server “Reporting” database so that it may extract the reporting data to include in the OLAP database being created. This requires that the SSAS Service account must be a login in SQL Server (DBMS) with a mapping to the PWA database with a minimum of read access.
• The SQL SSAS Server must be able to “find” the SQL Server by the same name that was used to provision the PWA instance. This could be either the SQL Alias or the raw SQL Server name. If a SQL Server (DBMS) Alias was used, then the SSAS server must have a client alias of the same configuration. You must set up both the 32-bit and 64-bit client aliases. And, of course, you should have set up both 32- and 64-bit SQL Server Aliases as well.
• There are a few other expectations:
  • SSAS and SQL Server need to be the same versions (but this may not be a requirement).
  • Clearly the accounts must be in trusted domains.
  • That SQL standard ports aren’t blocked.

Here’s the output you’ll get when you have a successful OLAP Build:

I hope this demystifies the requirements for OLAP Cube building in Project Server! Happy cubing!

Cropped “Jello Cubes 8-3-09- 3” image courtesy of Steven Depolo through Creative Commons CC BY 2.0 license.