Formula engine and storage engine in SSAS

In SSAS, to improve query performance, it’s important to understand what happens inside analysis services when a query is run.  Analysis Services is made up of two engines:

Formula Engine (FE) – It is single-threaded.  It processes the MDX queries, determines what data is needed to answer them, then requests that data from the Storage Engine, and then performs all calculations needed for the query.  It does most of the analysis work and tries to keep cells in memory.  Fast clock speeds are the best way to improve a Formula Engine performance.

Storage Engine (SE) – It is multi-threaded.  It handles all reading and writing of data: it fetches the data requested by the Formula Engine when a query is run and aggregates it to the required granularity.  If cells are not in memory, it is the Storage Engine which gets the data from disk.  A performance goal is to minimize Storage Engine use and keep data in memory for the Formula Engine.  Try to use faster storage (SSD) or more disk drives for quicker responses to Storage Engine requests.

So in summary, when you run an MDX query, that query goes first to the Formula Engine where it is parsed; the Formula Engine then requests all of the raw data needed to answer the query from the Storage Engine, performs any calculations on that data that are necessary, and then returns the results in a cellset back to the user.

There are numerous opportunities for performance tuning at all stages of this process, as I will discuss in my next blog post.

More info:

Storage Engine Cache Aggregation and its Implications for Dimension Design

“Multi-threading” the Sql Server Analysis Services Formula Engine – I

TROUBLESHOOTING MDX QUERIES

About James Serra

James is SQL Server MVP and a independent consultant with the title of Business Intelligence/Data Warehouse/Master Data Management Architect and Developer, specializing in the Microsoft SQL Server BI stack.
This entry was posted in SQLServerPedia Syndication, SSAS. Bookmark the permalink.

5 Responses to Formula engine and storage engine in SSAS

  1. Pingback: Performance tips for SSAS | James Serra's Blog

  2. Ravi says:

    This is what i call as sweet & short discription. Thank you !!

  3. khaled says:

    Thanks for the clear description

  4. Pingback: “Multi-threading” the Sql Server Analysis Services Formula Engine – I #ssas #mdx « Rui Quintino Blog

  5. Pingback: “Multi-threading” the Sql Server Analysis Services Formula Engine II-a parallel query msmdpump proxy | Rui Quintino Blog

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>