Hey guys!
How are you?

In this post, I will show you how to move/migrate Analysis Services cubes to another drive or directory. This is often used when you need to move the “DataDir”, “BackupDir” or “LogDir” directories to another location or another drive, a very common need in everyday life and which I have had to do a few times for clients here at Power Tuning, generally in cases where the disk where the cubes are stored (DataDir), which by default is the C: disk, ends up becoming full.

To move Analysis Services cubes to another drive or directory, open SQL Server Management Studio (ssms.exe).

On the Object Explorer screen, click on “Connect” and then on “Analysis Services”

Connect to the Analysis Services instance that you want to move data to.

IMPORTANT: Remember that a server can have more than one Analysis Services instance, so you can connect using just the server name or the “server_name\instance_name” format. This is configured during SQL Server/Analysis Services installation.

On the Object Explorer screen, click on the Analysis Services instance, right-click and select the “Properties” option

On this properties screen, which has just appeared to you, perform the following actions:

  • In the left menu, click on the “General” tab
  • Check the “Show Advanced (All) Properties” option at the bottom of this screen
  • Memorize the current path where the Analysis Services data files (DataDir) are located or copy it to a notepad so you don't forget it.
    The default path is “C:\Program Files\Microsoft SQL Server\MSAS.\OLAP\Data\”
    Example: C:\Program Files\Microsoft SQL Server\MSAS16.SQL2022\OLAP\Data
  • In the “DataDir” property, change the current path to the new path where you want to move the files (don't move the files yet)
  • You now need to change the value of the “AllowedBrowsingFolders” property.
    It is formed by the values ​​of the properties “BackupDir”, “LogDir” and “DataDir”, in that order, concatenated using “|” as a separator.
    You can add the new path to the end, including the “|” separator. and the new file path or replace the previous value with the new value, paying attention not to change the wrong path.

NOTICE: The “AllowedBrowsingFolders” property is used to specify which folders you can browse when saving files, opening files, and browsing files in the Analysis Services Sync, Backup, and Restore screens.

The SSAS service user must have read and write permissions on all directories listed in the values ​​of this property.

Observation: This step is not mandatory, because if you automate all maintenance of your Analysis Services using the command line, this change does not even need to be made, as it will not affect its use in any way.

After changing the values ​​of the “DataDir” and “AllowedBrowsingFolders” fields, click the “OK” button to confirm the changes.

Open “SQL Server Configuration Manager”

IMPORTANT: If you are not finding the “SQL Server Configuration Manager” application, read the article SQL Server Configuration Manager disappeared? Find out how to recover the shortcut.

Click on the “SQL Server Services” menu, in the left corner, select the SQL Server Analysis Services service that is running the instance in which you will move the cubes, right-click and select the “Stop” option to stop the service.

Now that the service is stopped, move the files from the old directory to the new directory.

After moving the files, start the Analysis Services service again.

Now go back to the Analysis Services properties to confirm that the “DataDir” property value is pointing to the new path and check that the cubes are working correctly.

Migration of cubes to the new disk or directory completed successfully.

I hope you liked this tip and see you next time!