Configuring a SQL 2005 Analysis Repository for DSO9 instead of an Access MDB

The standard procedure to configure (KB 921116) the Cube Building Services (Analysis Services 2005) for Project Server is to copy the Msmdrep.mdb file on a network share.

¾  Get a backup of a SQL 2000 AS Repository database (you can use the migration wizard within AS 2000 to create one) or download the one below.

¾  Create a database in SQL Server 2005 and call it AS2000_Repository.

¾  Restore the database backup found in the link below to the AS2000_Repository database

¾  Locate the msmdsrv.ini file in C:\Program Files\Microsoft SQL Server\MSSQL.#\OLAP\Config (NOTE: MSSQL.# is the folder in which SQL Analysis Services 2005 files are installed)

¾  Modify the <DSO> section of the msmdsrv.ini to (NOTE: Each XML item should be on a single line):

<RemoteRepositoryConnectionString>Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Data Source=SERVERNAME;Initial Catalog=AS2000_Repository;</RemoteRepositoryConnectionString>
<RepositoryConnectionString>Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Data Source=SERVERNAME;Initial Catalog=AS2000_Repository;</RepositoryConnectionString>

That’s it!

Comments (3)

  1. En partant d’un serveur from scratch il faut vraiment le vouloir pour créer un Cube. Pré-requis : Installer

  2. Mrs D says:

    thank you so much christophe you saved my life.

    I cannot understand a second how such a major feature as the setup of the OLAP cube cannot work without having to dig for a file on the NET

  3. peterhall says:

    Thank you for my life as well Chris!

    3 days of searching and I was ready to uninstall MS SQL 2005 from my Projects Server 2003SP3/WSS 2.0 SP2 – Portal Server 2.0 SP2 Server.

    Cube built successfully!  La belle que voilà…