Vertipaq Engine – Column & Segment Elimination

 Introduction

As we are discussing about the power of Vertipaq Engine, in this post we are going to discuss about another beautiful technique that Vertipaq Engine maintain to make our query faster and that is Column Elimination and Segment Elimination.  

Hope it will be interesting.

Case Study

We have a table which is imported into Vertipaq engine.

Table Name: Order_Details


 

Column Elimination

As Vertipaq Engine stores data as a columner fashion so it’s looks like.


 

Now we need to fetch data for Customer ‘ABC Company’ and want to know the Total Quntity the specified company purchased. For that we need only columns named Customer and Qty. All others columns are simply eliminated.


It actually limit the data by eleminating the columns and called columns elemenation.

Segment Elimination

This is done by horizontal Partitioning.


 

For Power Pivot it takes 1 million per partition and for SSAS Tabular it takes 8 million per Partition.

Now how it works.

 

Suppose we have query to fetch number of Quantity sold for customer ‘ABC Company’ within the period of 1/10/219 to 8/12/2019. In this situation it just simply eliminated one segment that have higher range of data.


 

Hope you like that.

Comments

  1. SunTec Business Solutions23 December 2021 at 11:25

    pricing management software for banking
    Personalize products, offers, pricing and loyalty programs; prevent revenue leakage and ensure regulatory compliance with a billing solution.

    ReplyDelete

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

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

Azure DataBricks Accessing Data Lake (Using Access Key)

Image
  Introduction: In this Blog post we are going to discuss about accessing Azure Data Lake Gen2.   How can we Access the Azure Data Lake Gen2: Access can be done by Ø   Using Storage Access key Ø   Using Shared access signature (SAS token) Ø   Using Service Principal Ø   Using Azure Active directory authentication pass-through Ø   Using unity catalog   In this post we are going to discuss about accessing Azure Data Lake Gent 2 by using Access key. Authenticate Data Lake with Access Key: ·          Each storage account comes with 2 access keys ·          Each access key is 512 bits ·          Access key gives full access of storage account ·          Conceder it as super user ·          Key can be rotated (re-generated)   Access Key Spark configuration: Here we take myschool as Azure Data Lake gen2 and there is a container within this data lake named bronze . The bronze containers have a csv file named school.csv   spark.conf.set(     "
Read more