SQL server 2005 Analysis Services?

[Nana]

Is there anyone out there with experience using SQL 2005 analysis
services with Project server 2003? I am stuck at the point of
configuring DSO on the Project Server to connect to analysis services
on the database server in order to create the OLAP Cube. I get the
following error:

Component: MSP Resource Availablity Refresh and OLAP Cube Creation
Component (ProjOLAP)
File: PROJOLAPProcess
Line: 1
Description: <Description><![CDATA[DSO.Server.Connect failed with error
message 'ActiveX component can't create object' Error Number :
'429]]></Description>

I have two machines with Sever 2003 SE (project server 2003SP2 + WSS
2.0SP2a + Anaysis ServiceSP1) and Sever 2003 EE (SQL Server 2005SP1 +
Analysis ServicesSP1).

Any direction will be appriciated

 

[Gary L. Chefetz [MVP]]

Nana:

Has this ever worked for you, or is this a new problem? Did it work before
you put SP1 on SQL2005?

 

[Nana]

Gary

I just installed it in this enviroment so no, it never worked for us.
Our DBA recommened SP1 and installed it before i installed project
server so there is no way i could know if it worked without SP1.

 

[Gary L. Chefetz [MVP]]

Nana:

There have been a couple posts in recent weeks by people having the same
problem after applying SQL 2005 SP1. Of course you should know that SQL 2005
is not supported with PS2003. The information on this possible issue is
anecdotal at best, so use it accordingly.

 

[Boris Bazant]

KB 917401 has some additional info that suggests that PS2003 database can be
hosted on SQL Server 2005. SQL Analysis Services 2005 is the piece that is
not compatible with PS2003 according to this article.
I guess the only solution is to have SQL 2000 Analysis Services running on
one server and SQL 2005 on another.

Boris Bazant

 

[Andrew Lavinsky]

That's consistent w/ what I have head from Microsoft.

-A

 

[PJC]

The reason why AS2005 does not work with PS2003 is that it does not use a
repository. You should be able to create a repository manually by following
the steps below:-


If running Analysis Services 2005 (Service Pack 1 is required), you need to
setup a repository for DSO to write to. If you have migrated the Analysis
Services 2000 repository to the Analysis Services 2005 server, no additional
steps are required.

The instructions below detail how to migrate the repository or create a new
one:

1. On the server with Analysis Services 2005 installed, go to \Program
Files\Microsoft SQL Server\MSSQL.2\OLAP

2. Create a directory called ‘DSO9’
3. Share it as MSOLAPREPOSITORY$

4. Give the SQLServer2005MSOLAPUser$<MACHINENAME>MSSQLSERVER administrative
access to the share

5. Copy the file msmdrep.mdb into the DSO9 directory previously created
This file can be found in the Analysis Services 2000, and by copying it to
this directory you will be migrating it to Analysis Services 2005. If you
don’t have an Analysis Services 2000 server, you should create a new (empty)
..mdb file and add it to the DSO9 directory (you should use the name above or
edit the <DSO> section below).

6. Navigate to the \Program Files\Microsoft SQL Server\MSSQL.2\Config
directory

7. Edit msmdsrv.ini file and replace DSO section with the following (make
sure to fill out the appropriate <MACHINENAME>):

<DSO>
<RemoteRepositoryConnectionString>Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=\\<MACHINENAME>\MSOLAPRepository$\msmdrep.mdb;Persist Security
Info=False</RemoteRepositoryConnectionString>
<RepositoryConnectionString>Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\Program
Files\Microsoft SQL Server\MSSQL.2\OLAP\DSO9\msmdrep.mdb;Persist Security
Info=False</RepositoryConnectionString>
<RemoteLocksDirectory>\\<MACHINENAME>\MSOLAPRepository$</RemoteLocksDirectory>
<LocksDirectory>C:\Program Files\Microsoft SQL
Server\MSSQL.2\OLAP\DSO9</LocksDirectory>
</DSO>

Ensure that each tab is in one entire line (not wrapped) on the file.

For more information about creating a repository, read the appropriate
Analysis Services documentation:
• http://msdn2.microsoft.com/en-us/library/ms403364.aspx
• http://msdn2.microsoft.com/en-us/library/ms403370.aspx

Is there anyone out there with experience using SQL 2005 analysis
services with Project server 2003? I am stuck at the point of
configuring DSO on the Project Server to connect to analysis services
on the database server in order to create the OLAP Cube. I get the
following error:

Component: MSP Resource Availablity Refresh and OLAP Cube Creation
Component (ProjOLAP)
File: PROJOLAPProcess
Line: 1
Description: <Description><![CDATA[DSO.Server.Connect failed with error
message 'ActiveX component can't create object' Error Number :
'429]]></Description>

I have two machines with Sever 2003 SE (project server 2003SP2 + WSS
2.0SP2a + Anaysis ServiceSP1) and Sever 2003 EE (SQL Server 2005SP1 +
Analysis ServicesSP1).

Any direction will be appriciated