Vertipaq Engine – Columnar Database

 Introduction

 In our previous post, we are discussing about Vertipaq Data compression one of the best feature of Vertipaq engine by Microsoft. In this post we are trying to discuss about Vertipaq Columnar Database. The ultimate feature of Vertipaq engine is to speed up the query performance.

Hope it will be interesting.

 

What the Special in Vertipaq Engine

Vertipaq is an in-memory columnar database. Being in-memory means that all of the data handled by a model reside in RAM.

 

How Database works with a Table

To understand the columnar database we have to understand, how data is retrieved from a table in our traditional database like MS SQL Server.

Let’s take an example of a table.

Table Name: tbl_OrderDetails


The data in a Database table stores in 8 KB page named data page. The 8 data page combined together to from an Extinct.

When we search for a specified product to understand how many Quantity has been sold the entire data page with all the columns comes into memory and search the desired Product one by one. So the load of the memory (RAM) is huge for long table. Please remind that we need only columns named ProductName and Qty only.  Rest of the columns is not needed to get the answer of how many quantity is sold for a specified product. It is just unnecessary down load into the memory and holding the space and hence for long table the performance is slow.


How the Vertipaq Engine works

The In memory storage of Vertipaq is different than the traditional DB storage. It stores the data in to columnar fission.


To complete the above query it takes only two columns named ProductName and Qty. It search the desired product from ProductName columns and sum the value of Quantity columns.



 

 Hope you like that.

Comments

Post a Comment

Popular Posts

Vertipaq Engine – Data Compression

Image
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 u
Read more

Implementing LIKE Operator in ADF

Image
Introduction SQL WHERE clause we often use LIKE operator with wild card character (% or _). But in ADF we can’t use it directly.  We can use heard coded LIKE and wildcard character in Control flow Lookup Activity, but we need to automate the process. Hope it will be interesting.   Case Study We have a Flat file named DeveloperList.txt and a AZURE SQL Table object named [dbo].[WorkCommesta]     Now we need to search each [ DevName ] from DeveloperList.txt to our [dbo].[WorkComments].[comments] table using wild card and LIKE operator. It just likes SQL command:   Select * From Workcommensts WHERE comments LIKE '%Joydeep%'   Stored Procedure     ADF Control Flow     Outer Lookup Activity     ForEach Activity     Inner Lookup Activity (Under ForEach)     Set Variable Activity (Under ForEach)   Stored Procedure Activity (Under ForEach)         Output     Hope you like it.
Read more

Azure Data Bricks Architecture Part-1

Image
  Introduction:   We decide to provide several blog posts for learning Azure DataBricks step by step. This blog post is useful for beginners who want to start Azure DataBricks.   Azure DataBricks High Level Diagram:   Azure DataBricks is an analytics platform optimized for the Microsoft Azure cloud services platform. It provides a collaborative environment with Apache Spark-based analytics that enables big data processing, real-time analytics, and machine learning tasks. Here are some key features of Azure DataBricks: ·          Optimized Apache Spark Environment: It allows you to set up an Apache Spark environment quickly, with autoscaling and auto-termination to optimize costs. ·          Collaborative Workspace: It supports multiple languages like Python, Scala, R, Java, and SQL, and integrates with tools like GitHub and Azure DevOps for version control. ·          Machine Learning Capabilities: Azure DataBricks integrates with Azure Machine Learning to provide au
Read more