How to update Analysis Views (OLAP) after an EPM 2003 to EPM 2007 migration?

  • Article

After migrating your database from Project Server 2003 to Project Server 2007, or after migrating from one environment to another (Production to Development for instance), a quick and easy way to update the Data Analysis Views is to issue the following T-SQL command in the REPORTING database:

USE ProjectServer_Published

GO

-- Check Analysis Views that needs to be updated

SELECT WVIEW_ASServerName,WVIEW_ASDatabase,WVIEW_ASCubeName,WVIEW_ASServerExtranetAddress,*

FROM MSP_WEB_VIEW_REPORTS

WHERE WVIEW_ASServerName IS NOT NULL

AND WVIEW_FILTER IS NULL

-- Update Analysis Server Name

UPDATE MSP_WEB_VIEW_REPORTS

      SET WVIEW_ASServerName='MYASSERVERNAME\INSTANCENAME'

      WHERE WVIEW_ASServerName IS NOT NULL

      AND WVIEW_FILTER IS NULL

Test this query on a test environment prior to applying it on a Production system!