Minimal Post-Installation Steps for Analysis Services

installDuring installation of Analysis Services, most of the properties are configured with a default value. These properties can be reconfigured any time after installation from SQL Server Management Studio. For most properties the default values works just fine. However, there are few properties that are best reconfigured immediately after installation to prevent down-time in production environment.

At a minimum, these four properties MUST be reconfigured BEFORE any cubes are deployed. Failure to reconfigure these values could result in cube processing failure due to insufficient disk space over time.

Property Description Default Recommended
DataDir Folder where the cube files are created. \Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data Any Storage Folder
LogDir Folder where the log files are created. \Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Log Any Storage Folder
BackupDir Folder where the cube backup is created. \Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Backup Any Storage Folder
TempDir Folder where the temp files during cube processing is created. Empty. If Empty, same as DataDir. Any Storage Folder

The account running Analysis Services must be granted same permission to the target folder as the default folders.

Setting Property Values from SQL Server Management Studio

1. From the SQL Server Management Studio, connect to the instance of Analysis Services. Right-click properties to bring up the Properties Page.

Screen Capture 1 - Analysis Services Properties
Screen Capture 1 - Analysis Services Properties

2. On the Properties page, tick the Show Advanced (All) Properties. Key-in new values for the DataDir, BackupDir, LogDir and TempDir properties. Notice that these changes will be effective only after Analysis Service is restarted.

Screen Capture 2 - DataDir, BackupDir
Screen Capture 2 - DataDir, BackupDir
Screen Capture 3 - LogDir
Screen Capture 3 - LogDir
Screen Capture 4 - TempDir
Screen Capture 4 – TempDir

3. Restart Analysis Services from SQL Server Configuration Manager.

What-if I Need to Reconfigure in an Operational Environment?

It is Ideal to reconfigure these properties immediately after Analysis Service is installed and before any cube has been deployed. If however, there is an existing operational environment which still has default values for these parameters, suggest following these steps to reconfigure the parameters.

1. First Modify the BackupDir and TempDir. Restart Analysis Services. At this stage leave the DataDir and LogDir untouched. Take note of the existing values for all the four properties.

2. Backup all the Analysis Service Database(s). This will create the backup in the new BackupDir.

3. Now change the DataDir and Log Dir. Restart Analysis Services. You will notice that the existing Analysis Service Database(s) no longer appears in Management Studio. This is because now the DataDir has changed.

4. Restore the Analysis Services Database(s) from the backup. This will create the cube files in the new DataDir.

5. Verify that the restores are successful by browsing the cube. Delete the default physical folders. If the restore is not successful, hit the Restore Default button on the properties page and start all-over again.

Benny Austin

2 thoughts on “Minimal Post-Installation Steps for Analysis Services

  1. That post was written with SQL Server 2005 in mind. In SQL Server 2008 – Data Dir, Log File Dir, Temp dir and Backup dir can now be specified as a step BEFORE installation of Analysis Services – http://wp.me/pxNuz-ep

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s