DWgeek.com

Netezza Skew and How to avoid it

You will hear a lot about "Netezza Skew" if you are developing data warehouse on Netezza, Redshift, Teradata, hive or Impala database. The performance of the system is directly linked to uniform distribution of the user data across all of the data slices in the system. When you create a table and then load the data into the system, the rows of the table should be distributed uniformly among all the data slices. If some data slices have more rows of a table than others this scenarios is called skew.…

Comments Off

July 6, 2016

Netezza

Importance of right Netezza Distribution key

This post is all about how data is distributed (Netezza distribution key) in Netezza server. Feel free to make comments or suggestions to improve it, or pass it on if you like. Let’s first understand how NPS stores the data on disk drives. Each Snippet Processor in the Snippet Processing Unit (SPU) has a dedicated hard drive has its separate CPU, FPGA, separate RAM memory, hard disks and the data on stored on drive is called a data slice. Read: Changing Netezza Table Distribution Key Cluster Based Tables (CBT) in…

Comments Off

July 6, 2016

Netezza

Working with Netezza Zone Maps and Best Practices

Before going to Netezza zone maps first let’s understand extent. An extent is smallest unit of storage on the disk. In fact it is smallest disk allocation on storage to store the data. Netezza would maintain the maximum and minimum value of the column that is stored in an extent (each extent is of 3MB in size) What are Netezza Zone maps? Netezza Zone maps is an internal mapping structure to the extent (smallest unit of storage) that takes advantage of internal ordering of the data. These are automatically generated internal…

1 Comment

July 6, 2016

Netezza

A Guide to load data into Netezza Database

Netezza data load is transferring data into Netezza appliance. There are several ways to transfer the data into appliance. Netezza Data Load using External tables These are tables stored as flat files on the host or client systems and not in the Netezza appliance database. Create table on top of the flat file that needs to be loaded to Netezza tables, then use that external table to load target table in Netezza appliance. As a result data will be copied to the target table. Flat Files An external table allows…

Comments Off

July 6, 2016

Netezza

How Netezza FPGA Works on Netezza SQL Query ?

The Netezza FPGA is a the heart of Netezza’s patented streaming architecture and is made of a result of framework of FPGA Accelerated streaming technology (FAST) engine. In this post we will see how FPGA works in Netezza SQL. In the following subsequent paragraphs we will see how FPGA works when we execute Netezza SQL query on host machine. Read: Netezza FPGA - FAST Framework in Netezza The performance multiplying effect of the FPGA FAST engine: Control, Parse, Visibility, Project and Restrict is shown with help of small example of processing SQL query…

2 Comments

July 5, 2016

Netezza

Netezza FPGA – FAST Framework in Netezza

Most of the companies around the globe using Netezza data warehouse appliance to perform their near real-time, complex analytics and data warehouse needs. Netezza FPGA is important part of NPS system. NPS performance is made of a result of framework of Netezza FPGA Accelerated streaming technology (FAST) engine. This engine is just like a turbo-charger to an already powerful engine. This FAST engine what makes the Netezza a powerful server. Read: Working with Netezza Zone Maps nzload command and its Usage nzsql Command and its Usage Netezza has introduced a FAST…

Comments Off

July 5, 2016

Data Mining

9 Laws Everyone In The Data Mining Should Use

DATA MINING is a powerful new technology with a great potential to help companies focus on more important information by extracting the hidden predictive information from large database in their data warehouses. There are some 9 data mining laws that miner should follow when performing mining on particular data sets. Data mining provides two types of results: Business Insights Predictive models, makes predictions automatically. It includes a various methods that include, clustering, classification and market basket analysis, etc. Read: 9 Laws Everyone In The Data Mining Should Use Mining Frequent itemsets - Apriori Algorithm…

Comments Off

July 5, 2016

Netezza

nzload Command and its Usage in Netezza

nzload is bulk copy command available in Netezza. This is a command that provides an easy method for using external tables and getting data into the Netezza appliance. Read: guide to load data into Netezza Working with Netezza Zone Maps Commonly used Netezza Basic Commands Netezza COPY Command Syntax and Examples How the nzload Command Works? The nzload command is a SQL CLI client tool that allows you to load data from the local or a remote client, on all the supported client platforms (Linux/windows). The nzload command processes command-line…

4 Comments

July 5, 2016

Data Warehouse

Slowly Changing Dimensions (SCD) in Data Warehouse

Slowly changing dimensions or SCD are dimensions that changes slowly over time, rather than regular bases. In data warehouse environment, there may be a requirement to keep track of the change in dimension values and are used to report historical data at any given point of time. We can implement slowly changing dimensions (SCD) using various approaches, such as; Type 0: Always retains originalType 1 : Keeps latest data, old data is overwrittenType 2 : Keeps the history of old data by adding new rowType 3 : Adds new attribute to store changed valueType 4 : Uses…

Comments Off

July 3, 2016

Data Warehouse

Design Slowly Changing Dimension Type 2 in SQL Cont

This is a continuation of the Design Slowly Changing Dimension (SCD) Type2 in SQL Step 4: Copy only updated records from LOAD table These are records which are updated in this load cycle. Since updated records are already in the LOAD table, you have to compare the LOAD and TGT records on Patient ID or SK column and copy the record which is updated. You should also keep in mind to set EFF_FROM_DT and flag to indicate active record. The updated records will be active from the moment we load them so EFF_FR_DT…

2 Comments

July 3, 2016