Archive
Exposing the SSAS file system with the Metadata Sniffer
Very recently I encountered a production issue and to assist me in the root cause analysis I needed information/clues that are not exposed by any SSAS interfaces so I created the metadata sniffer.
SSAS Free drive space command
Today i was reviewing some traces and saw 2 commands i had never noticed before which had been issued by SSMS, these are shown below.
SystemGetLogicalDrives
SystemGetAllowedFolders
The commands can be executed in a MDX query session.
SystemGetLogicalDrives returns the free space in MB for the disk drives that are contained within the server property “AllowedBrowsingFolders”, see below for an example.
I have checked and the commands work in all versions of SSAS back to 2005 and anyone can run them.
Personally i think this is really useful and here a few reasons why.
- Cut sysadmins out of the loop when maintaining space (this is a good thing
). - If your managing a remote dedicated SSAS server and have no access to the OS its a quick way to check space just like SQL Servers xp_fixeddrives & fn_fixeddrives()
- You might use it when collecting space stats or alerting.
- SystemGetAllowedFolders gives you the directories configured in “AllowedBrowsingFolders”.
- I hope you find this useful! I passed the info onto one of the Dev teams i work with and they certainly will make good use of it.
SSAS Multithreaded sync with Windows 2008 R2
We have been happily running some of our systems on Wndows 2003 and have had an upgrade to W2K8 R2 on the list for quite some time. The upgrade has now completed and we can start taking advantage of some of the new features which is the reason for this post.
For a long time we have used the sample Robocopy script from the SQLCat team to synchronize some of our larger SSAS databases. If your wondering what i mean by large, around 5 TB with a good few thousand partitions.
The script works like a dream but at this scale you soon find that the copy process starts to chug when it hits the fact data as this fantastic multi threaded script now becomes single threaded again as the bulk of the data being transferred is fact data.
Working with SSAS traces and the dreaded Textdata in SSMS
For good or bad i regularly work with SSAS traces that i have saved to a table and many years ago i was regularly frustrated by the fact that the contents of the textdata column containing the query is always truncated at 65535 characters.
You may think 65535 characters should cover most queries but once Excel gets to work it generates some huge queries!
Fortunately i came across a solution by MVP Adam Machanic that uses a very nifty trick to convert the output to XML and this has the benefit that XML column data is presented as hyperlinks in grid view!
SSAS Deployment essentials
Over the recent years i have enjoyed the privilege of working on a number of different SSAS deployments. Some are huge, some are complex and some of them are huge and complex and most interestingly they all behave differently.
What i want to share today is what i consider to be essential for an SSAS installation. This covers what i expect to see installed to compliment SSAS, configuration settings to be changed and the literature that should be at your finger tips.
Andrew Calvett's SQL Server Blog 