SSAS Performance tips – Lessons learned
I’ve been researching a few performance issues lately on an Analysis Services 2005 instance with 20 (almost) identical databases on it, each weighing roughly 2.5GB and containing 6-7 cubes with approximately 400 partitions in each cube. As you can imagine, this makes up quite an impressive number of files stored on disk. SSAS stores everything on files, and unlike its relational relative (no pun intended), which will store hundreds of GB of data on a fairly small number of large files (I know this is an over simplification of how one should plan for data file allocation on SQL Server but its good enough and true enough in most cases for the sake of this post), SSAS stores a single database as described above in hundreds and hundreds of files. The number of files required is determined by the number of partitions, dimensions, cubes and aggregations and since we have lots and lots of those elements we will eventually have to deal with a heavily congested file system structure.
Tags: management, performance