Skip to main content

Vertipaq Engine in SSAS Tabular Database

If you can remember my last blog post, SSAS tabular has two architectures to store data either DirectQuery or In-memory mode. Today in this blog post I’m going to discuss more detail about in-memory or Vertipaq engine.

In reality, SSAS tabular database engine can get requests from the client either MDX or DAX query.  Inside the Tabular database it contains two layers of calculation engines,

Formula Engine: Whenever a new request coming from the client despite its DAX/MDX, from Analysis services it parse and transforming them in query plans and finally execute them by formula engine.

Storage Engine: To perform calculations , from formula engine it performs one or more requests to the storage engine where the data is stored, which could be either in-memory (Vertipaq) engine or external relational engine (directQuery) depending on the mode you are using to build the model. In this post I'm mainly focus on Vertipaq Engine. It has a copy of data read from the data source when you perform a refresh. 


Vertipaq Engine

Vertipaq/storage is a in-memory columnar database engine which receives requests in internal binary format. In this storage all the data in the model resides in-memory or RAM. Inside the Vertipaq engine the data is stored in columnar format which is a special structure which holds the data in column format where as traditional SQL database engines store data row format. This optimized for vertical scanning and does not have additional structure to optimize queries such as indexes in relational database engine. Data here is compressed in memory using algorithms to allow quick scans. This will reduce the scan time and the memory required to store data. 

Vertipaq Engine != Tabular In-memory Engine

Technically, vertipaq engine is the storage engine which store data, do I/O compressed data, apply filters, joining tables and do basic aggregations and its a one part of the whole tabular in-memory database engine. More complex calculations are performed by formula engine after received intermediate result from Vertipaq engine.

Further, when performing calculations, formula engine executes queries in a single thread for each queries but perform parallel processing based on the different users requests whereas storage engine use multiple cores(minimum 16M rows per table).

Inside the Vertipaq Engine

Storage engine or Vertipaq engine processing based on few algorithms.


Each value in a column mapped into 32 Bit integer value. This mapping is done by two ways. Either hash encoding or value encoding. Value encoding uses dynamic mathematical calculation to convert the real value into 32 bit integer value. Hash encoding uses always a new value to store when converting. This 32 bit value compressed before it store in the columnar structure. Using RLE or run-length encoding sort these values before store then similar values in columns will get a higher level of compression.

Will discuss how the data compression is working in more detail level in my next blog post.

Labels:

Comments

Post a Comment

Popular posts from this blog

Run T-SQL Script Files Using Command Line

Most of the time when we need to execute a SQL script or statement, then we go for SQL Server Management Studio to execute our T-SQL scripts. But the same result we can achieve using Command Prompt. This is very simple way to do that just a matter of write a command and pass few arguments. You can see the process status like as in when you execute a query in Management studio. Use-case : There may be having some situations you won't be able to execute T-SQL script using Management Studio. Most of the time when doing data population. In my case, I had a situation I needed to populate a Database without using backup/restore. Once I generated script with all the schemas and related data it took around 400+Mb sql file.  SQL Server Management Studio will not allow you to run the script unless you execute the query in high-end box. Because, when the file loaded to the memory it will too hard to cater our requirement. You may probably get  memory ...
Post a Comment
Read more

How to Create a Date Table in Few Steps Using Power BI

Calendar Dimension or Calendar Table is one of the crucial table in a Power BI model. I never have seen any data warehouse data model which does not have a Calendar table so far in my development. Because, when it comes to data warehousing or dimension modeling you may storing various business processes or events as Facts. So those events anyway occur in particular date or time. So simply there wouldn’t be a Power BI data model without a date table. In this post, I’m going to share with you how to create a date table within few steps. Actually, there are two DAX measures which we can use to create a date table. Calendar(DAX) and CalendarAuto(Auto). Calendar(DAX) = You can pass start date and End date as parameters you need to create date table. CalendarAuto(DAX) = You can use this function without passing any parameter. Then it will generate the dates based on your data model dates.  You can simply copy the below DAX code and paste in your Power BI Desktop DAX e...
7 comments
Read more

Value Encoding in Vertipaq Engine

If you can remember my last blog post regarding Vertipaq engine inside SSAS Tabular , I’ve discussed three algorithms which are in use when process the model. Processing in the sense perform data loading from the relational source and load into the tabular structure. In here data compression is taken place in order to save the memory footprint. It is really important because all the data we had in the data warehouse or source relational database after processed the model load into the memory. So by the compression save the huge amount of memory space and it will utilize your hardware optimum way while faster scans because the data model is smaller than the original. These are the steps taking place when we process the tabular model from SSDT or via SQL Server Management Studio.  Read the data from the source database and transform into columnar structure or vertipaq data structure while data encoding and compression occurs. Creating of dictionaries and indexes for eac...
Post a Comment
Read more