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.