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):
<DSO>
<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>
<RemoteLocksDirectory/>
<LocksDirectory/>
</DSO>
That's it!