Analysis Services - Caching MDX Calcs to Disk
One of my clients has a pretty complex cube. Actually it's one of the most complex cubes I've ever worked on. It came to life just as Microsoft release SQL Server 2005 and designers crammed in everything they could pretty much using all of the new shiny functionality available. The only saving grace is that the data volumes are small. Anyway the result was a performance nightmare. It's since been redesigned however there are still a handful of statistical set based calculations that pose a performance challange.
To solve this challenge I re-wrote the MDX to tweak every last bit of performance from the query engine. We re-organised the server and software architecture. I then built a cache warming job. On warming the cache we were still faced with the same problem; for somes reason the engine would not cache the calcs and queries still ran at unacceptable lengths (2 minutes or so for a small data set).
Tags: mdx, performance