Using MDX to browse the file system
One very obscure feature of Analysis Services that I’ve only ever seen documented in the books “Microsoft SQL Server Analysis Services 2005” and its successor “Microsoft SQL Server 2008 Analysis Services Unleashed” (both highly recommended for advanced SSAS users, by the way) is the fact that you can use MDX to browse the file system of the server that Analysis Services is running on, with some restrictions. Full details on this can be found on P804 of the 2008 book but it neglects to give any real examples of how to do it, so since it took me a few minutes to work out the correct syntax to use I thought it was worth a blog post.
There are four MDX extensions you can use, which can be executed from SQL Management Studio just like any other MDX statement.
- SystemGetLogicalDrives returns a list of the drives on your AS box, with the amount of free space.
- SystemGetSubdirs returns a list of directories under a specified path. This only actually works on directories that you have listed on the AllowedBrowsingFolders server property for Analysis Services (which you can find in SQL Management Studio by right-clicking on your instance name in the Object Explorer and clicking Properties) and their parents, so on my machine the following query returns only the Program Files directory:
- SystemGetFiles returns all the files in a given directory, again only working for the directories listed in AllowedBrowsingFolders. For example on my machine:
SystemGetFiles ‘C:\Program Files\Microsoft SQL Server\MSAS10.MSSQLSERVER\OLAP\Backup\’
returns all the files in my backup directory because that’s automatically included in AllowedBrowsingFolders, whereas
returns an empty result set.
- SystemGetFileExists returns whether a file exists or not in a directory you’re allowed to browse. So, for example:
SystemGetFileExists ‘C:\Program Files\Microsoft SQL Server\MSAS10.MSSQLSERVER\OLAP\Backup\MyBackupFile.abf’
will return 1 if the file MyBackupFile.abf exists, or 0 if it doesn’t.
Clearly this functionality is quite useful if you’re automating things like backups and you don’t have administrative rights to the server file system, although you need to be an Analysis Services administrator to do this.