What does a DBA have to know about SSAS?
A quick overview of SSAS for DBAs
So, you're a SQL Server DBA and you've just inherited some cubes out of the blue to manage. A quick crash course on SSAS administration seems to be in order.
From an administrative point of view, SSAS is a fairly straightforward, if resource hungry application. It's way simpler than a DBMS platform, although different in several ways. Also, there are a few administrative tasks such as usage based optimisation specific to SSAS that you might have to get your head around.
Back up your configuration file
All config data lives in a file called msdmsrv.ini. It's an XML file. If you tinker with the config through SSMS (Connect to the server, right click on the server, select properties) you can set options that will break the server on start up. Take a copy of msmdsrv.ini before you play with anything.
Important parameters
Memory: SSAS is a biblical memory hog. It likes 64 bit builds if possible, and lots of memory. The parameters 'Memory\LowMemoryLimit' and 'Memory\HighMemoryLimit' control memory usage policies. LowMemoryLimit is not a minimum memory allocation. It is a threshold where SSAS considers the system is low on memory and starts flushing stuff out of its caches. HighMemoryLimit is the absolute maximum it will use.
Note that SSAS stores its data in files (lots of files - it doesn't have any mechanism equivalent to filegroups), so it makes a lot of use of O/S file system caching for those files. Note that the default for these limits is about 65% and 80% of the machine's memory respectively, so if you want the OLAP server to coexist with a SQL Server instance you will need to turn those down so it isn't fighting over memory with the database server.
Directories: Five parameters of interest cover this: DataDir, AllowedBrowsingFolders, BackupDir, LogDir and TempDir. DataDir and AllowedBrowsingFolders are the most important.
AllowedBrowsingFolders affects the list of folders that the OLAP server will put its data files in. Anything with a user interface (e.g. the deployment wizard) will restrict your options to the list in AllowedBrowsingFolders. The value is a pipe ('|') delimited list of directories.
DataDir is the default path for files. If you intend to partition the cube over multiple volumes you will need to configure AllowedBrowsingFolders appropriately.
LogDir is where the server places various log files including flight recorder and query logs. The flight recorder log is used for troubleshooting and the OLAP query log is used for usage based optimisation (more on this later).
TempDir is a location for temporary files that SSAS creates while processing. If you are processing large data volumes and having performance issues, you may benefit from shifting this off onto another volume from the data.
BackupDir is what it says on the tin.
Miscellaneous: A few miscellaneous parameters may also be of interest. A couple of sets that you may have to tweak are:
DefaultMaxDrillthroughRows: This limits the size of drillthrough row sets. You may need to fiddle this to allow more.
Threads/Timeouts: You may need to adjust these. I've never had to bother.
Those are the basics. You may need to tweak the others for specific reasons, but you can do your homework on that.
A reference guide for the SSAS server properties can be found here.
Operations
Deployment: You can compile a project in BIDS and get a set of files that can be deployed with the deployment wizard. You may need to adjust file paths for partitions and a few other things.
Programmed and batched admin tasks: Commands are issued to SSAS through a web service API called XML/A. Microsoft provides an interactive tool for issuing MDX and XML/A connands. If you have to embed MDX in an XML/A command, watch out for the need to use XML escapes such as &. This is not an issue with the MDX editor and query tool in SSMS.
Offline jobs can be done through various SSIS cube processing tasks, a command line utility called ascmd.exe or a .Net API called AMO. You can also get various powershell tools and suchlike. ascmd.exe takes an XML/A file and posts it to the server. If you have to frig the file programatically you might be better off working with little .Net tools rather than trying to manipulate XML files from a .cmd script.
The operations guide goes into this in more detail.
Security
Security on SSAS is fairly simplistic. It has a global 'Server' role that has administrative permissions across the whole system. Unfortunately you need 'Server' to create databases, so it's quite likely you will need to grant it to developers on any development OLAP servers you have.
Other security can only be applied to individual cube schemas. You can grant permissions to read, process, drillthrough, writeback and suchlike on individual items to roles within a schema. Roles on OLAP schemas can be defined within BIDS and are deployed with the cube. AD groups or users can be assigned to those roles through SSMS.
An example of how to programatically manage role membership can be seen here.
Usage-based optimisation
As a DBA you might well get involved in this one, but first a little backgrounder about the physical storage. SSAS works by calculating and persisting pre-built aggregates along with the base data. If a query can be satisfied by hitting an aggregate the OLAP server will use this in preference to the base data, as the aggregate will involve much less I/O and therefore it will be quicker to retrieve the data.
However, you have to work out which aggregates to calculate (i.e. what combinations of dimension attributes to generate rollups for). BIDS has a tool that will take a guess at this and generate some for you. Some tools, such as BIDS helper will also allow you to manually edit the aggregates.
Usage based optimisation works by taking a log of the actual queries issued to the server, and then using that log to work out a set of aggregates that would be optimal for those queries. As a DBA, you may set up OLAP query logging to capture this data, and then run the optimisation on the cube. A blurb about setting up the query log can be found here.
The tool for doing this is called the 'Usage based optimisation wizard.' This lives in SSMS, and can be found by opening the partition int the explorer and selecting 'Usage Based Optimisation' off the right-click menu.
The performance guide goes into tuning in more detail.
MDX
MDX looks a bit like SQL but works very differently. A treatise on 'MDX for SQL programmers' is an entire topic in its own right. I suggest reading through some of the tutorials on it and/or getting a book on it. Plus, the friendly folks here at dba.se can help with questions if you have any.
MDX doesn't have any concept of filtering rows. The language has a whole lot of set operations to work out what to display on the various axes of the query1, plus 'SELECT'. You can use with statements that look a bit like CTEs to define measures and sets.
Some introductory MDX programming resources can be found here and here (this one is quite old and very long winded). There are also a few good books on the subject; this SO question has quite a bit of fan-out to SSAS resources.
1 Although it does have non-empty operators that restrict the results to combinations that actually have data. Most MDX queries consist of defining what slices to show on the axes, and non-empty operators will be necessary to avoid queries returning combinatoric level quantities of empty cells.
You might get some of the answers regarding SSAS administration from this lengthy white paper SQL Server 2008 R2 Analysis Services Operations Guide. This is how the introduction begins:
In this guide you will find information on how to test and run Microsoft SQL Server Analysis Services in SQL Server 2005, SQL Server 2008, and SQL Server 2008 R2 in a production environment. The focus of this guide is how you can test, monitor, diagnose, and remove production issues on even the largest scaled cubes. This paper also provides guidance on how to configure the server for best possible performance.
Clearly it is targeted to DBA/admins. If you also develop applications using SSAS you can also take a look at the Analysis Services 2008 Performance Guide Whitepaper