There are sometimes some cool things you can do with a language that is not really it’s primary focus. T-SQL has functionality in it to work with the filesystem, send mail, handle message queuing, etc, ,but its usually a combination of technologies and these statements that is the best solution.

MDX is no exception. There are some interesting pieces of functionality that allow MDX to be used to work with the file system on your server. This is useful for things like backups, etc if you’re a SSAS Admin, but not an admin on the server.

There are several functions that enable this in MDX:

SystemGetLogicalDrives - This function returns a list of all the drives on your Analysis Services Server and their current free space information.

SystemGetSubDirs - This will return the list of subdirectories under a specified path. This will only work if those directories are listed on the AllowedBrosingFoldersProperty on the SSAS Server (See Next Blog Post for where to find this)

SystemGetFiles - This extension will return all the files in a given directory, again, only working on folders in AllowedBrowsingFolders.

For Example this location is automatically included

SystemGetFiles ‘C:\Program Files\Microsoft SQL Server\MSAS10.MSSQLSERVER\OLAP\Backup\’, but

SystemGetFiles ‘C:\Backups\OLAP’

will return an empty result set since I have not added it to the Allowed Browsing Folders.

SystemGetFileExists – This function returns 1 or 0 based on whether a file exists or not. For example

SystemGetFileExists ‘C:\Program Files\Microsoft SQL Server\MSAS10.MSSQLSERVER\OLAP\Backup\AdamsDemoBackup.abf’ will return 0 since that file does not current exist on my drive

Happy playing! Remember to post questions on the BIDN forums !