SQL Knowledge Bank - Azure

  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 Partit...

  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 ...

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 H...

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 ...

Introduction Before moving to Spark RDD concept, which is the base line of Spark, we need to understand the concept of Hadoop Mapredue. RDD is the improvement of Hadoop Mapreduce to get 100 outputs in memory. In this blog post we are going to discuss about Hadoop Mapredue to understand the concept only. Hope it will be interesting.   What is MapReduce MapReduce is a software framework and programming module to handle huge data. Hadoop is capable to run MapReduce programs which are written in different language like Java, Ruby, Python and C++. How MapReduce Works As the name specified, MapReduce is a combination of Mapping and Reducing.   We can divide the MapReduc into following Sections. ·          Input Splits ·          Mapping ·          Shuffling ·          Reducing   Before exami...

Introduction File Handling is a very important portion for any application. In Python, we have several function to Create, Read, Update and Delete file. In this blog post, we are trying to discuss about Python File handling. Hope it will be interesting. Open Function This is the key function to working with file. The Open function takes two parameters. One is File name and second is mode. "r"   -  Read   - Default value. Opens a file for reading, error if the file does not exist "a"  -  Append  - Opens a file for appending, creates the file if it does not exist "w"  -  Write  - Opens a file for writing, creates the file if it does not exist "x"  -  Create  - Creates the specified file, returns an error if the file exists In addition of that, we can specify that the file can be treated as Binary or as Text file. "t"  -  Text  - Default value. Text mode "b"  -  Binary  - Binary mode (e.g. images) Exa...