Search code examples
multithreadingssasssas-tabularcpu-cores

What does it mean that SSAS tabular formula engine is single threaded?


We are building a SSAS tabular cubes and trying to determine what hardware is suitable.

I imagine that it means if you have 1 core then 1 thread is using the formula engine. If you have 4 cores then 4 core can, in parallel, access the formula engine.

Our goal is to determine number of cores as we have many users.


Solution

  • The underlying engine for SSAS Tabular, can be split into two functions, the storage engine and the formula engine. When queried, it basically gets the data from the storage engine, and passes it to the formula engine to run the DAX queries. You are right, in that the formula engine is single threaded, but the storage engine is multi threaded. So the storage engine will quickly get the data, then the formula engine will do the calculations on it. But multiple users queries, will result in multiple single threaded queries, and not the same thread being used, so it will in affect parallel process the queries.

    If you look at the spec of the recommended size of an instance is around 8 to 16 cores, as most queries will not be running at the same time and the storage engine will be running as well as the calculation one at the same time, SSAS will quickly return data and only take seconds to process. The most import part will be the RAM limits that you have to store, and run queries.

    If you look at Power BI Premium you'll see 4 cores, with 25GB of ram, which can take 1200-1500 report interactions per hour. With Azure Analysis Services get a measure of QPU's which equals about 1 virtual core to 25 QPU's. For S2 that is the same a 4 virtual cores.

    For reference you should check out the following blog posts on the vertipaq engine and the SQLBI post here, by Marco Russo, one of the leaders in SSAS Tabular and DAX, on the considerations of sizing your server.