Project Server 2007, Analysis Services2005 and Building a Cube

By far and away the most irritating long term issue I have had is in regards to building a cube with Project 2007 and Analysis Services 2005.  I was unable to find any documentation on what I had to do to get this working and had a client who really needed it working yesterday.  After weeks of puzzling over it I have finally worked through some problems and gotten it going!In retrospect it was really simple, but some basic knowledge was holding me back.  Essentially the problem was that AS2005 did not have the correct repository setup, and the entire answer was in this link : http://support.microsoft.com/default.aspx?scid=kb;EN-US;921116In particular the following step : On the computer that is running SQL Server 2005 Analysis Services, follow these steps:

Note This includes SQL Server 2005 Analysis Services that are running remotely in a 3-tier configuration.

1. Create a subfolder that is named DSO9 in the following folder: C:Program FilesMicrosoft Sql ServerMssql.NumberOLAPNote Mssql.Number is the folder in which the SQL Server 2005 Analysis Services files are stored.
2. Copy the Msmdrep.mdb file from a computer that is running a fully patched SQL Server 2000 Analysis Services to the DSO9 folder on the computer that is running SQL Server 2005 Analysis Services.
3. Share the DSO9 folder, and then specify MSOLAPRepository$ as the share name for the shared folder.
4. Assign Read access and Write access for the shared folder. Do this for the Local Administrators group or for any account that Project Server 2003 will use to connect to Analysis Services for processing.
5. The account that accesses Analysis Services from Project Server 2003 must be granted the appropriate permissions on the Analysis Services instance.

Open the Msmdsrv.ini file, and then replace the contents of the <DSO> tag with the following code:
<DSO>

<RemoteRepositoryConnectionString>Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\ComputerNameMSOLAPRepository$msmdrep.mdb;Persist Security Info=False</RemoteRepositoryConnectionString>
<RepositoryConnectionString>Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program FilesMicrosoft SQL ServerMSSQL.NumberOLAPDSO9msmdrep.mdb;Persist Security Info=False</RepositoryConnectionString>
<RemoteLocksDirectory>\ComputerNameMSOLAPRepositorymce_markerlt;/RemoteLocksDirectory>
<LocksDirectory>C:Program FilesMicrosoft SQL ServerMSSQL.NumberOLAPDSO9</LocksDirectory>
</DSO>

6. Note The Msmdsrv.ini file is located in the PathConfig folder. Make sure that you replace ComputerName in the code with the name of the computer that is running SQL Server 2005 Analysis Services.
7. The first time that you save the Msmdsrv.ini file, SQL Server 2005 Analysis Services encrypts the “ConnectionString” information. After this occurs, you cannot read the information when you open the file again. Therefore, you should keep a copy of the code in case you need it in the future.
8. Stop and then restart the SQL Server 2005 Analysis Services service.
9. Create a subfolder that is named DSO9 in the following folder: C:Program FilesMicrosoft Sql ServerMssql.NumberOLAPNote Mssql.Number is the folder in which the SQL Server 2005 Analysis Services files are stored.
10. Copy the Msmdrep.mdb file from a computer that is running a fully patched SQL Server 2000 Analysis Services to the DSO9 folder on the computer that is running SQL Server 2005 Analysis Services.
11. Share the DSO9 folder, and then specify MSOLAPRepository$ as the share name for the shared folder.
12. Assign Read access and Write access for the shared folder. Do this for the Local Administrators group or for any account that Project Server 2003 will use to connect to Analysis Services for processing.The account that accesses Analysis Services from Project Server 2003 must be granted the appropriate permissions on the Analysis Services instance.

Open the Msmdsrv.ini file, and then replace the contents of the <DSO> tag with the following code:
<DSO>

<RemoteRepositoryConnectionString>Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\ComputerNameMSOLAPRepository$msmdrep.mdb;Persist Security Info=False</RemoteRepositoryConnectionString>
<RepositoryConnectionString>Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program FilesMicrosoft SQL ServerMSSQL.NumberOLAPDSO9msmdrep.mdb;Persist Security Info=False</RepositoryConnectionString>
<RemoteLocksDirectory>\ComputerNameMSOLAPRepositorymce_markerlt;/RemoteLocksDirectory>
<LocksDirectory>C:Program FilesMicrosoft SQL ServerMSSQL.NumberOLAPDSO9</LocksDirectory>
</DSO>
Note The Msmdsrv.ini file is located in the PathConfig folder. Make sure that you replace ComputerName in the code with the name of the computer that is running SQL Server 2005 Analysis Services.The first time that you save the Msmdsrv.ini file, SQL Server 2005 Analysis Services encrypts the "ConnectionString" information. After this occurs, you cannot read the information when you open the file again. Therefore, you should keep a copy of the code in case you need it in the future.

13. Stop and then restart the SQL Server 2005 Analysis Services service.

So why did I ignore this for so long?  Purely because the file mentioned there does not exist in AS2005!  It is a legacy file from AS2000.  I do not know why it was not included, and I?m sure there are better ways to create a repository, but this method definitely works.

You can download a blank fresh copy of mdmdrep.mdb from http://www.msexperts.org/downloads/msmdrep.zip, follow these steps, and make sure that you give the SSP users and the SQL database appropriate permission to be able to access C:Program FilesMicrosoft SQL ServerMSSQL.NumberOLAPDSO9. Please also ensure that you replace the sections in bold in the config file with the appropriate values, or you will continue to get errors.

I really wish I had found and tried this earlier.  Don?t ignore this because msmdrep.dll doesn’t exist, or it looks like it is unrelated due to the AS2000 references, it will definitely help.