AX DB Restore Scripts #2 – SSAS Servers

Recently I started a series of posts on DB restore scripts for Dynamics AX by providing a list of data entities in your AX database that may need to be updated when restoring from one environment to another, in particular a production DB to a development or QA environment.

Today I will be drilling into how to reset the SSAS (SQL Server analysis services) configurations via DB scripts.

In the Dynamics AX 2012 client these configurations are located under System Administration -> Setup -> Business Intelligence -> Analysis Services -> Analysis Servers.

Depending on the configuration of your source system (e.g. Production) you may have multiple servers setup here and typically your destination would only have a single server (Development) therefore your script would need to do the following:

1. Remove all but one of the SSAS configurations
2. Update the server details of the remaining configuration

The following SQL code will accomplish the above. I have parameterised the SQL for easier reuse or adjustment.

Declare @SSASSERVERNAME varchar(20) = '[YOURSERVERNAME]\AX'
Declare @SSASDESCRIPTION varchar(30) = 'Dev SSAS'; -- Description of the server configuration

delete from BIAnalysisServer where RecId not in (select min(recId) from BIAnalysisServer)
update BIAnalysisServer set 
    SERVERNAME=@SSASSERVERNAME, 
    DESCRIPTION=@SSASDESCRIPTION,
	ISDEFAULT = 1
WHERE SERVERNAME <> @SSASSERVERNAME -- Optional where clause if you want to see the "affected rows

Note: These changes assume everything is setup correctly on the SSAS server itself.