Setting up the built in OLAP cube for Project Server 2007

  • Article

Technorati Tags: Project Server 2007 Reporting,Project Server 2007,OLAP,SQL Server Analysis Services,Business Intelligence,Reporting,This should be easier

So, you’re setting up your Project Server installation, and you want to take advantage of the OLAP Data Analysis View you’ve been hearing so much about. Ok, this should be easy, just go to your PWA site, click on Server Settings, then “Build Settings” under the “Cube” heading. You enter your Analysis Services server, the cube name you wish to use, are impressed by the scheduling engine, and click “Save & Build Now”, sit back, and wait for the cube to process.

But wait, what’s this? If you’re using Analysis Services 2005 or 2008, you just got stopped cold in your tracks with the following error:

Analysis Services session failed with the following error: Failed to connect to the Analysis Services server ms-nrcvm. Error: The connection string to repository needs to be specified in the 9.0 server properties (see <DSO>...</DSO> section in msmdsrv.ini file from Analysis Services 9.0).

Turns out that Project Server 2007’s cube expects SQL Server Analysis Services 2000, not SSAS 2005. Therefore, you need to configure DSO to provide backward compatibility if you’re going to use SSAS 2005+ with Project Server 2007

First, you’ll need to set up a DSO metadata database. The quickest (and recommended) method for this is to copy the repository database from a SQL Server Analysis Services 2000 server set up to use SQL Server as its metabase repository onto your SQL Server 2005+ server. Unfortunately (ok, fortunately), many of us no longer have access to a SSAS 2000 box. Luckily, the structure of the metabase is extremely simple. If you need to create one manually, create a database on your SQL Server RDBMS server. I call mine AS2000_Repository, just so it’s obvious what it is.

Then, open a query window and select this new DB as your current database and execute the following script

CREATE TABLE [dbo].[OlapObjects]( [ID] [varchar](36) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [ParentID] [varchar](36) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [ObjectName] [nvarchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [ClassType] [int] NOT NULL, [ObjectDefinition] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [LastUpdated] [datetime] NULL, [Changed] [bit] NULL, [Version] [int] NULL,CONSTRAINT [idx_ID] PRIMARY KEY NONCLUSTERED ( [ID] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY],CONSTRAINT [idx_ParentClassName] UNIQUE CLUSTERED ( [ParentID] ASC, [ClassType] ASC, [ObjectName] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

CREATE TABLE [dbo].[Server]( [ObjectDefinition] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Next, we’ll need to add the repository string into the SSAS configuration. To do this, open SQL Server Management Studio (SSMS), and open a connection to your SSAS server. Then, in object explorer, right click on properties for your SSAS server.

image image image

 

Then, click on “Show Advanced (All) Properties”, and navigate to DSO\RepositoryConnectionString, and click on the ellipses (…) and enter the connection string to get to the AS2000_Repository database we just created. Of course, you may need to set up different security parameters than are shown in the screenshot. Of course, test your connection before proceeding.

imageimage

Go back to the PWA web site, navigate to Server Settings –> “Build Settings”, and try rebuilding your cube. It should work now!

 

Thx to Kermit Llaurador, who walked me through this the first time, way back when.