SSAS for DBAs

As part of our series on DW / BI for the DBA, I’m going to spend some time today on SQL Server Analysis Services (SSAS). My assumptions are that (a) you are aware that SQL Server uses Analysis Services as an engine and development platform for multi-dimensional analysis and (b) that you are a database administrator so you will not have the option to modify an application and have to work with the tools that a DBA has at their disposal to maintain and tune SSAS.

SSAS LOVES RAM

Just to restate, I am assuming that as a DBA, you are not going to be able to modify cube partitions or aggregations and are somewhat at the mercy of the application or developers that they have the stats and traces that they need (hopefully you’ve provided this information!) to make the proper adjustments to the cube to maximize cube storage and query access. That being said, let’s keep it simple. Keep in mind that SSAS is a different server process and engine separate from the relational database. The SSAS formula engine will want to cache results so that cell values are reused rather than recomputed. Therefore, 64-bit hardware is recommended for your SSAS infrastructure and monitor your SSAS instances memory usage with these perfmon counters (make sure they are SSAS, not MSSQL): MSAS2008:MemoryMemory Usage Kb, MSAS2008:MemoryMemory Limit Low Kb, MSAS2008:MemoryMemory Limit High Kb.

SCALE-OUT with SCALABLE SHARED DATABASE

One of the most common uses for the scale-out SQL Server architecture using a shared database with multiple instances against a read-only disk volume that I have seen is with SSAS. I have typically witnessed this approach with implementations of SSAS against very large data warehouses with needs that include a large number of concurrent users and concurrent queries.

Using the read-only database technique allows you to implement the sample architecture that I pasted below from the SQLCAT team. I want you to be aware of this option, though, because it provides the best scalability for maximum growth in your environment. From a DBA’s perspective, something that you’ll need to learn is how to refresh the read-only database. The database is on a read-only disk volume, so you need to snapshot the SSAS database and use that to update the shared data.

One more thing that I would add in this architecture to take note of for a DBA is that the actual cube processing takes place on a separate instance as opposed to the scale-out “query” server instances, where most of the bottlenecks occur.