Unable to build OLAP cube(in Project Server 2007) after migrating to SQL 2008 R2 Cluster

Issue:

Project Server 2007 environment migrated from SQL Server 2005 to SQL Server 2008 R2 . Evertything worked fine except OLAP cube building process. It failed to recognize SQL Server Analysis Server that caused failure in building OLAP cubes.

Solution:

We performed the following two actions to fixed this issue

1.   Changed the client provider from 10 (SQL OLEDB 9.0)to regular SQL Native Client in making DSO connection.

2.   Added client SQL alias to each node of the SQL Cluster that SharePoint is using to connect to database tier and then cube gets process just fine.