Vertipaq Engine – Data Compression

Introduction

Microsoft introduces a very powerful engine for fast query named Vertipaq. Which is used in the Power BI and SSAS Tabular model? Here we are not going to discuss about Power BI or SSIS Tabular model. Our main concern is to identify how the Vertipaq Engine works and how it provide Fastest Query.

The Vertipaq Engine stores the table in a columnar format and established relation between columns. In this blog post we are going to discuss about Data Compression mechanism and try to explain it.

 

Case Study

We have a table which is imported into Vertipaq engine.
Table Name: Order_Details

 


 

How Vertipaq works on this Table

Vertipaq Compress Text in a Table

Text takes lot of space in data base. So Vertipaq need to compress it. It maintain another table with distinct Text value and Id number like this. It is also called Dictionary/ Hash compression.

 

Product_ID_Dictionary

     


    

 

Value Encoding

Here Vertipaq tries to convert Value into One Digit where as possible. TO understand this we are taking Quantity columns for an example.

 


 

From Quantity columns its tries the Minimum value. In our case it is 10.

Now its takes every value from Quantity and try to minus minimum value to make it single digit where as possible.

 


 

If, we need to get the Original Value, we have to add the Minimum value, i.e. + 10

 


 

 

Run Length Encoding

 

Now look at the Product_ID columns. Now we have to find that how many times the Product ID is repeated.

 


 

(Product_ID, Value Repetes)

(0, 2)
(1, 2)
(2, 1)
(3, 1)

 

Looks like this:

 


 

Hope you like that.

Comments

  1. Sukamal16 August 2020 at 09:38

    It is very much informative and as well as interesting also. As per my understanding I need little bit more elaborate discussion in perspective of quey execution to fetch record...

    Thanks

    ReplyDelete

Post a Comment

Popular Posts

Working with Spark – Spark RDD

Image
Introduction In my previous blog post, we are discussing about Hadoop Map Reduce. Now in this blog post we are going to discuss about Spark Fundamentals concept like RDD. RDD stands for  Resilient Distributed  Dateset , these are the elements that run and operate on multiple nodes to do parallel processing on a cluster.  Hope it will be interesting. What is RDD According to Apache Spark documentation -  " Spark revolves around the concept of a resilient distributed dataset (RDD), which is a fault-tolerant collection of elements that can be operated on in parallel. There are two ways to create RDDs: parallelizing an existing collection in your driver program, or referencing a dataset in an external storage system, such as a shared filesystem, HDFS, HBase, or any data source offering a Hadoop InputFormat ". RDDs are immutable elements, which means once we create an RDD we cannot change it. RDDs are fault tolerant as well, hence in case of any failure, they ...
Read more

Activity Dependencies with Logical AND in ADF

Image
Introduction In this article we are going to discuss about Activity Dependencies at ADF Control flow. As a SSIS developer we all have Experience about Precedence Constrains and how useful it is when building SSIS control flow. In ADF we have the similar concept with some limitation. Depends on this, we have to design our control flow in ADF. Hope it will be interesting.   Precedence Constraints in SSIS Before moving further, we have to review our SSIS Precedence Constraints.   Here we have: 1.       Constraint – Success, Failure, Completion 2.       Expression 3.       Logical AND / OR   It is really flexible.   Activity Dependency in ADF     In ADF Activity Dependencies provides only Logical AND not any Logical OR. It has Add Activity on: 1.       Success 2.       Failure 3.    ...
Read more

Working with Python – Module

Image
Introduction In this blog post, we are going to discuss about Python Module. Before that, we have to understand what the module is. Module is a code Library. Remember, when we are at C or C++, we are using #Include statement at beginning of the code to call specific code library. How to Create a Module If we need to create a module, we just save our code with  .py   extension. def  greeting(name):    print ( "Hello, "  + name)   Suppose, we have the above code and we are just saving the code in the name of  mymodule.py Calling Module   import   mymodule mymodule.greeting( "Mayuree" )   Hello, Mayuree   If we are looking at the calling procedure. mymodule . greeting ( "Mayuree" ) mymodule  – Module Name greeting  – Function Name   Can we assign Variable in Module   Module can have function that we already demonstrate. However, it can also have Variables. Example: ...
Read more