OLAP Cubes: Compatibility between Analysis Services 2005 and 2008

Question

I'm working with Analysis Services and I have run into a compatibility issue. Here is my situation. I have 3 machines:

• Server2005: This machine has SQL Server, Analysis Services and Visual Studio, all in 2005 versions.
• Client2008: This machine has Visual Studio 2008.
• Client2005: This machine has Visual Studio 2005

What I'd like to do:

• Develop cubes working on Client2008 and deploying on Server2005.

The problem:

• I can read data and deploy cubes. However, when I try to "process" a cube I get an error message: Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of 'xxxxx', Name of 'xxxxx'. Impersonation information is set to "Use service account". I don't know why I can use the connection to read data, I can deploy the cube (a new AnalysisServices db is created in Server2005) but I can't process data.

Tests that have worked:

• I can deploy and process cubes locally on Client2008 with SQL Server 2008.
• I can deploy and process cubes locally on Server2005.
• I can deploy and process cubes on Server2005 from Client2005.
• Here comes the strangest of all: I develop a cube using Client2005. I copy the visual project to Client2008 and update the project to use it with VS2008 and... it works! However if I develop exactly the same cube with VS2008 from the beginning I get the previous error. I have compared the .ds files generated with VS2005 and VS2008 and have not found any significant differences.

I'm clueless. Any help is appreciated, thank you!

Answer 1

The impersonation settings have changed slightly. The best solution is to ensure that your SQL Server database gives the Analysis Services service account read permissions.

You may also need to change the data source connection impersonation information:

Impersonation Information tab" />

Answer 2

Thanks for you answer James!

I'm not sure about your suggestions. How should I give permissions? I've tried finding some user related to A.S. to no avail. Anyways, it should already have permissions, because I can read data and deploy cubes. Maybe I must give some other kind of permissions? I'm really a newbie with all this, sorry.

About your 2nd suggestion, I had already tried every option on impersonation without success.

Anyways, thanks again for your time. I'll keep trying!

Answer 3

When I talk about giving the service account read permissions, I mean that the Analysis Services service runs under a different identity to yours so even though you can read the data the service can not.

It will either be SYSTEM, NETWORK SERVICE or a specific user account - type services.msc at a run prompt to get a list of services...

You need to give this account the relevant read permissions on your SQL database.

Answer 4

The same problem here... it looks like projects have benn build in VS2008 cann't be deployed to SQL Server Analysis Services 2005...

Answer 5

We had the same problem and just replaced the SQL Native 10.0 data source with a OLEDB SQL Server data source.

Pedro Gomes.

Good Luck

Answer 6

Which Provider is your Data Source using?

If you are going to deploy on SQL Server 2005, you need to make sure that the Provider is "SQLNCLI.1" instead of "SQLNCLI10.1".

In your Analysis Project, double-click on the Data Source and click "Edit" below Connection string. There is a drop-down list at the top where you can select the correct provider. You need to have the SQL Server Native Client 9.0 installed on your Client2008 (download here, look for "Microsoft SQL Server Native Client").

Alternatively, it should work if you install SQL Server Native Client 10.0 on your 2005 server (download here, look for "Microsoft® SQL Server® 2008 R2 Native Client").