none
in memory queries execution target (storage or formula engine)

    Question

  • Hi eb

    It is mentioned in performance guides for ssas tabular 2012 models that queries may be pushed down

    to the xVelocity Storage engine - hence being performed much faster then in the (in memory)

    formula engine.

    Is there some  "hint" or other techique to convince ssas to go for the storage engine in specific queries

    or this is totally a black box - depending on the need to decompresse etc.??

    If the later is the case - are there any guide lines when data would be decopressed hece sending query

    to the formula engine (as they say in those guides)?

    TIA!

    Rea

    Sunday, September 08, 2013 8:13 AM

Answers

  • Hi Rea,

    As we know, SQL Server Analysis Services 2012 introduce xVelocity in-memory analytics engine (VertiPaq) is an Analysis Services engine that services tabular model databases. The xVelocity engine uses in-memory storage and performs calculations that aggregate and manipulate data at the time it is requested.

    The Tabular models can be queried by using both MDX and DAX queries. The following diagram explains the underlying query processing architecture of Analysis Services when running in Tabular mode:

    The Storage Engine 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. The formula engine commonly runs several VertiPaq storage engine scans, materializes the results in memory, joins the results together, and applies further calculations.

    For more information, please see:
    Formula engine and storage engine in SSAS: http://www.jamesserra.com/archive/2011/10/formula-engine-and-storage-engine-in-ssas/

    Best Regards,


    Elvis Long
    TechNet Community Support

    Tuesday, September 10, 2013 5:16 AM
  • There is no "USE STORAGE_ENGINE" hint or anything like that. The query optimizier will do it's best to push as much work down to the storage engine as it can. However certain types of logic need to be done in the Formula engine.

    This whitepaper http://msdn.microsoft.com/en-us/library/dn393915.aspx goes through the steps of how to identify performance issues and tune them in a tabular model/query.


    http://darren.gosbell.com - please mark correct answers

    Tuesday, September 10, 2013 7:44 AM

All replies

  • Hi Rea,

    As we know, SQL Server Analysis Services 2012 introduce xVelocity in-memory analytics engine (VertiPaq) is an Analysis Services engine that services tabular model databases. The xVelocity engine uses in-memory storage and performs calculations that aggregate and manipulate data at the time it is requested.

    The Tabular models can be queried by using both MDX and DAX queries. The following diagram explains the underlying query processing architecture of Analysis Services when running in Tabular mode:

    The Storage Engine 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. The formula engine commonly runs several VertiPaq storage engine scans, materializes the results in memory, joins the results together, and applies further calculations.

    For more information, please see:
    Formula engine and storage engine in SSAS: http://www.jamesserra.com/archive/2011/10/formula-engine-and-storage-engine-in-ssas/

    Best Regards,


    Elvis Long
    TechNet Community Support

    Tuesday, September 10, 2013 5:16 AM
  • There is no "USE STORAGE_ENGINE" hint or anything like that. The query optimizier will do it's best to push as much work down to the storage engine as it can. However certain types of logic need to be done in the Formula engine.

    This whitepaper http://msdn.microsoft.com/en-us/library/dn393915.aspx goes through the steps of how to identify performance issues and tune them in a tabular model/query.


    http://darren.gosbell.com - please mark correct answers

    Tuesday, September 10, 2013 7:44 AM