Nucleus research reports a 241% ROI using Big Data to enable larger, more complex analytics

LINK

Adding a 4th V to BIG Data - Veracity

I talked a week or so ago about IBM’s 3 V’s of Big Data. Maybe it is time to add a 4^th V, for Veracity.

Veracity deals with uncertain or imprecise data. In traditional data warehouses there was always the assumption that the data is certain, clean, and precise. That is why so much time was spent on ETL/ELT, Master Data Management, Data Lineage, Identity Insight/Assertion, etc.

However, when we start talking about social media data like Tweets, Facebook posts, etc. how much faith can or should we put in the data. Sure, this data can be used as a count toward your sentiment, but you would not count it toward your total sales and report on that.

Two of the now 4 V’s of Big Data are actually working against the Veracity of the data. Both Variety and Velocity hinder the ability to cleanse the data before analyzing it and making decisions.

Due to the sheer velocity of some data (like stock trades, or machine/sensor generated events), you cannot spend the time to “cleanse” it and get rid of the uncertainty, so you must process it as is - understanding the uncertainty in the data. And as you bring multi-structured data together, determining the origin of the data, and fields that correlate becomes nearly impossible.

When we talk Big Data, I think we need to define trusted data differently than we have in the past. I believe that the definition of trusted data depends on the way you are using the data and applying it to your business. The “trust” you have in the data will also influence the value of the data, and the impact of the decisions you make based on that data.

A discussion of data models for analytics

Some of our competitors recommend that you use 3rd Normal Form (3NF) for their data structures, as they believe that is the optimal architecture for the ad hoc queries that form the basis for decision support and analytical processing of today. While 3NF can save storage space, it makes queries harder to write, and slower to execute. A big down side of 3NF for data warehousing is that it causes the database to join tables for most queries. “Joins” can be a performance pitfall because they force large volumes of data to be moved around the system. To speed up these queries, DBAs using these other databases create and maintain aggregates and/or indexes across tables. In fact, some tables can have 3, 4, 5 or even more aggregates/ indexes if they are joined to tables using different columns. It is important to realize that these aggregates/indexes require knowledge of the queries, reports and analytics that are going to be run within the system, now and in the future.

Think of it this way—if you are heading out for a Sunday drive, and have no destination in mind, how can you use a navigation system to give you directions?

Another issue that many of our competitors’ customers report is that they need to create a duplicate copy of their data in a dimensional model in order to meet their desired response times. In many cases this dimensional model will contain many star schemas, as well as a number of copies of the fact tables containing different levels of data. The issue with this approach is that the application developer and the business users must be fully aware of what data these new tables and aggregates really contain. If they aren’t aware, they can inadvertently make a business decision based on an old snapshot of the data, or a small, skewed sample of the data. In addition, if a user mistakenly picks the wrong “table,” that query might take 10 or even 100 times longer to run that it would if they completely understood the model.

Because IBM Netezza appliances do not need indexes or pre-computed aggregates to perform well, there is no need to tune the data model for IBM Netezza appliances. In fact, many customers simply copy the data model directly from their old data mart/warehouse or from their transactional system into the IBM Netezza appliance and leave it as-is. They then simply load a single copy of the data and start running their queries.

Why Analytics is like Wine Tasting

I mentioned yesterday that I was in the Finger Lakes last week. Well, we stopped by some wineries while we were there and it is always interesting to read the tasting notes on the back of the bottle, or on the tasting sheet that the winery gives to you.

I was thinking last night about this and how great it would be if all of the data we, as an organization, create or consume came with its own “tasting notes”.  Just imagine a new set of transactions arriving from your OLTP systems, and they come with a tag that says something like “This data shows a correlation between the purchase of steaks and seasoning salt.” Wouldn’t that make the job of the data scientist / data analyst so much easier?

We also went to one winery in particular, and noted that the wine maker did not have any tasting notes, and only described his wine as “Like your favorite pair of slippers” or something like that. After talking about this for a while, we found that we actually liked this approach better. Rather than tasting and searching for what the wine maker told us he or she tasted, we were able to develop our own impression, and detect tastes on our own. Without being directed to a particular smell or taste, we used our own nose and palette to decide what we tasted, and what we liked. In the end we bought more bottles from this winery than we did from any of the other wineries that we visited.

You might be scratching your head and wonder where I am going here, so let me explain. I believe that analytics needs to be more like the second case above. You should not start with any preconceived notions on your data based on what others tell you. Analyze the data, detect correlations/patterns/trends on your own, and then check the tasting notes if you want to.  

 The goal of analytics should be to find NEW information that you can act upon, not simply find the same thing that someone else already found.

IBM's Big Data Platform - Saving One Life at a Time

Having been working on parts of IBM’s Big Data platform for the past year or more, I am continually impressed with the value that IBM brings to our clients.

When we talk Big Data at IBM, we talk about the three V’s: Variety, Velocity, and Volume.

Volume is pretty simple. We all understand that we’re going from the terabytes to petabytes and into a zettabytes world. I think most of us understand today just how much data is out there now and what’s coming over the next few years.

The variety aspect is something kind of new to us in the data warehousing world, and it means that analytics are no longer just be for structured data, and on top of that, analytics on structured data doesn’t have to be in a traditional database any longer. The Big Data era is characterized by the absolute need and desire to explore and analyze all of the data that organization produce. Because most of the data we produce today is unstructured, we need to fold in unstructured data analytics as well as structured.

If you look at a Facebook post or a Tweet, they may come in a structured format (JSON), but the true value, and the part we need to analyze, is in the unstructured part. And that unstructured part is the text of your tweet or your Facebook status/post.

Finally, there’s velocity. We at IBM consider velocity as being how fast the data arrives at the enterprise, and of course, it’s going to lead to the question, and how long does it take you to analyze it and act on it?

It is important to keep in mind that a Big Data problem could involve only one of these characteristics, or all of them. And in fact, most of our clients see that a closed loop mechanism, normally involving more than one of our Big Data solutions, is the best way to tackle their problem.

The neonatal ward at a well known Hospital is a prime example of this. Hospital equipment issues an alert when a vital sign goes out of range – prompting the hospital staff to take action immediate.  However many life threatening conditions take hours or days to reach critical levels, delaying possible life saving treatments. Often signs that something is wrong begin to appear long before the situation becomes serious enough to trigger an alert, and even a skilled nurse or physician might not be able to spot and interpret these trends in time to avoid serious complications. Complicating this is the fact that many of these warning indicators are hard to detect and it’s next to impossible to understand their interaction and implications until a threshold has been breached.

For example, nosocomial infection, a life threatening illness contracted in hospitals. Research has shown that signs of this infection can appear 12-24 hours before overt trouble/distress is spotted and normal ranges exceeded. Making things more complex, in a baby where this infection has set in, heart rates stay completely normal (i.e. it doesn’t rise and fall throughout the day like it does for a healthy baby). In addition, the pulse also stays within acceptable limits. The information needed to detect the information is present, it is very subtle and hard to detect. In a neonatal ward, the ability to absorb and reflect upon all of the data being presented is beyond human capacity, there is just too much data.

By analyzing historical data, and developing correlations and understanding of the indicators of this and other heath conditions, the Doctors and researchers were able to develop a set of rules (or set of conditions) that indicate a patient is suffering from a specific malady, like nosocomial infection. The monitors (which can produce 1,000+ reading per second) feed their reading into IBM’s InfoShpere Streams where it is checked on the fly. The data is checked against healthy ranges, and also against other values for the past 72 hours, and if there are any rules that are breached, an alert is generated. For example, if a child’s heart rate has not changed for the past 4 hours and their temperature is above 99 degrees, then that is a good indicator that they may be suffering from nosocomial infection.

And as the researchers continue to study more and more historical data in their data warehouse and Hadoop clusters, when they detect more correlations, they can dynamically update the rules that are being checked on the real time streaming data.

Indexes do NOT make a warehouse agile

I took a few days off to visit the Finger Lakes in New York (you really should go there if you like hiking and/or wine) and came back to an overflowing email inbox. Some of these emails were from clients that have been receiving more correspondence from our competitors with claims of their superiority over IBM Netezza.

One of these claims was that their solution is more nimble and able to handle broader workloads because they have indexes and aggregates. This one made me think for a few minutes, but I still feel that the Netezza approach where there is no need for indexes is still a far better solution.

While an index or aggregate can be used to improve/optimize performance for one or more queries, the upfront table, aggregate, and index design/build phase will cause other systems to take MANY TIMES longer to get up and running efficiently than IBM Netezza appliances. In fact, some of our competitors’ customers have openly talked about months long implementation cycles, while IBM Netezza customers talk about being up and running in 24 hours…

Instead of days or weeks of planning their data models, IBM Netezza appliance customers simply copy their table definitions from their existing tables, load the data, and start running their queries/reports/analytics. There is absolutely no need to create any indexes (and then have to choose between up to 19 different types of indexes) or aggregates. Where other data warehouse solutions require weeks of planning, IBM Netezza appliances are designed to deliver results almost immediately.

Today’s data warehouse technologies made it possible to collect and merge very large amounts of data. Systems that require indexes are fine for creating historical reports because you simply run the same report over and over again. But today’s business users need answers promptly. The answer to one question will determine the next question that they are going to ask, and that answer the next, and so on. This thought process is known as “train of thought” analysis, and this can lead to competitive advantages required in the economy of today and tomorrow.

Outside of IBM Netezza data warehouse appliances, standard operating procedure is for users to extract a small sample of data, move it out of the data warehouse to another server, and then run the analytics against that sample.  This is required because the systems cannot support these ad-hoc analytic reports without completely exhausting the system resources, and impacting all other users. Even if these other systems had the right indexes all of the time, they would still need to move massive amounts of data into memory before processing it. This has led to users with other data warehouse solutions sampling their data and copying a small sample to a dedicated analytics server.

The small sample size allows the analysis to complete in a reasonable amount of time, but by restricting this analysis to a small subset of the data, it becomes harder to spot (and act on) the trends found within it.  We discussed this above with the baseball example, but it applies to everyone. Credit cards are another example, with credit card numbers and identities being stolen every day, the card companies need to detect these misuses immediately in order to limit their liability and prevent loss. While there are quick “indicators” of fraud, like a new credit card being used at a pay phone at an airport for the first time, most indicators come from correlating more than one transaction. For example, a card cannot be used in Kansas and then in Orlando 7 minutes later, unless one of the transactions is a web transaction or the card number was manually entered because it was taken over the phone. So, if a card was physically swiped in Kansas and then in Orlando less than a few hours later, the account must have been compromised. Now, if the fraud detection application only looked at every 10, or 100, transactions, they would miss at least one, if not both, of these transactions most of the time.

Why should compression only work for read-only data?

A number of our competitors make bold compression claims, like “we provide 4X, or even 10X+ compression”. What they do not tell you is that you have to choose between multiple different compression levels, and/or compression libraries/types. They also do not mention that you cannot use their compression on tables that have inserts, updates, and/or deletes occurring. Nor do they mention the overhead that you can expect to see if you turn on their compression.

Let’s look at these three points in a little more detail.

In today’s world of reduced budgets, one of the easiest ways to save money with a data warehouse is to reduce the amount of disk space required to store the data. To this end, nearly all data warehouse solutions offer some form of database compression. In order to use compression with many of our competitors the table must be an Append-only table. 

Append-only tables have the following limitations:

            •Cannot UPDATE rows in an append-only table

•Cannot DELETE rows from an append-only table

•Cannot ALTER TABLE...ADD COLUMN to an append-only table

•Cannot add indexes to an append-only table.

These limitations are because of the way these vendors have implemented their ‘database compression’. While Netezza has built in algorithms specifically designed for database usage, others uses a compression library that “compresses” the data file as it is being written to disk, and then uses the same library to un-compress the file as it reads it from disk. For anyone that has used a tool like gzip, WinZip, pkzip, WinRar, etc. you all know how slow these tools are, and how much CPU cycles they use. This is the same consideration and overhead you will have with these other vendors if you use their compression. In fact this overhead can be so bad that some customers who have presented at our competitors’ conferences have talked about tests where a single query running on a table with compression used up over 95% of the CPU, while the same query against the same data in a table that was not compressed used less than 5% of the CPU.

On top of the performance impact, there is also the DBA overhead. With one competitor the DBA has to choose between 3 types of compression (i.e. compression libraries) and 9 different levels of compression, each of which work better for different data types. That is 27 different combinations/permutations that the DBA has to choose between, for each table.

With IBM Netezza compression is always on, there is no “better algorithm” for different tables, and because of the way the Netezza architecture works, when you get 4X compression with your data in Netezza, you see an associated 4X improvement in performance, for all types of workloads, not just reads. 

Addressing more crazy competitor claims

Among some of the other “claims” that our competitor made about Netezza is that is can only load at the rate of 2TB/hr. First off, this is false. The current generation of the Netezza platform can load at over 5TB/hr. But, the real question I ask is "Does this really matter after you have your system up and running?”

After the initial loading of the database from the existing system(s), very few companies load more than a couple hundred GB to a couple TB per day, and most do not even approach these daily or even monthly load volumes. Even Netezza’s biggest customers who have PetaByte sized systems do not find Netezza’s load speed to be an issue.

Now let’s look at the claims this competitor is making in more detail, and peel back the layers of the onion.  This competitor claims that they can load at double the Netezza’s load speed of 5TB/hr. But they leave out a number of important factors when they make this claim.

What about compression?

Netezza can load at a rate of 5TB/hr and compress the data at the same time. This competitor can only load at their claimed compression rate if compression is not used. So, if you want to compress the data, how fast can you really load on their platform?  They use a library based compression algorithm that uses CPU cycles to basically “zip” the data pages as they are written to disk, using significant CPU cycles in the system that cannot then be used to format the data into pages, build indexes, etc.

What about partitioned tables?

This competitor needs tables to be partitioned in order to provide good performance, but in order to load a table this competitor has to have a “rule” for each data partition, and then each row that is being loaded must be compared to the rules to know which partition it should be loaded into. If the row should be loaded into one of the first couple of ranges, then there is little extra processing, but all of the latest data will have to be checked against many rules, slowing down the processing of these rows, and definitely slowing down the load process.

What about indexes?

This competitor admits in their manuals that they also need indexes in order to perform well. But, each index incrementally slows down load performance.

Netezza does not need indexes to perform well, so does not suffer from decreased load speed because of indexes, or table partitioning

What about pre-processing?

Netezza can load at the same 5TB/hr rate with no pre-processing of the input data file. This same competitor can only load at their claimed faster rate if their appliance includes an option “integration” or ETL module where the servers pre-process the data and then send it to the data modules to be loaded. Without the integration module, the load file would need to be placed on the shared file system (accessible from all modules in their appliance) and then the load speed is really only 2TB/hr based on published validation reports of their architecture and procedures. And again, this 2TB/hr is without compression, or table partitioning.

Uncovering the truth about more outrageous competitor claims

Another of the claims this competitor made is that Netezza cannot handle the data ingest from a point of sale (POS) type system because so it could not handle the data being sent from the hundreds or thousands of POS systems at the same time. They go on to claim that Netezza cannot handle more than a specific, ridiculously small, number of concurrent writes to try to give credence to this argument.

In my opinion, this point shows a lack of knowledge of real data warehousing and analytics. IBM Netezza has a large number of retail customers who feed their POS data into Netezza all the time. But, this data normally always comes into the data center in some non-database type of communication protocol. The data is then extracted from these POS feeds, and has meta data management and identity assertion algorithms applied against it, because the data may be coming from many different stores, even different “named” stores where the same item may have different SKUs. Only then is the cleansed data loaded into the warehouse, it is not loaded directly from the hundreds/thousands of POS applications/registers.

IBM Netezza absolutely supports trickle feed and real/near-real time updates from this type of data stream process, as well as direct replication from other relational databases.

And, if you are looking for the ultimate in real time reporting and analytics on your POS data, IBM has the system for you. The IBM DB2 Analytics Accelerator is an IBM Netezza system connected directly to a DB2 for z/OS system using the zEnterprise connection. In this configuration, the transactional applications still run against the tried and true DB2 for z/OS system, and reporting/analytic queries get transparently routed through the DB2 for z/OS system to the Netezza system, to offload the processing and resource usage, and ultimately run much faster. DB2 for z/OS systems run many of the world’s largest scale OLTP applications, and this brings them the power of real time analytics without the need to create extra indexes, aggregates, etc. in their DB2 for z/OS system which are needed to allow the reports/analytics to run quickly enough, but also have a detrimental effect on the transactional performance.

FUD Competitors are Spreading on Netezza

Recently I was made aware of some FUD (fear, uncertainty, and doubt) that a competitor has been sending to our current and prospective clients. This FUD contained a number of gross inaccuracies, as well as some “points” that really made me scratch my head and wonder how much this competitor really understands data warehousing and analytics.

This competitor claimed that Netezza scanned the entire table for all SQL / Analytic operations.

This claim is absolutely not true. While Netezza does not have indexes that the DBA must create and maintain, it does automatically build and maintain zone maps for all tables in the database. These zone maps contain the minimum and maximum value for all columns in every extent within each table. So, before the query starts reading from disk, it looks at the predicates in the query, and compares them to the zone maps to determine which table extents can be skipped and which need to be read.

For example, if you want to calculate the total sales of Red Ford Mustangs in June 2011, Netezza can skip any table extent that does not have data for June 2011. So, for a database with 7 years of sales history, it can skip any extent that has a maximum that is less, or a minimum that is greater than, June 2011. This eliminates 98% or more of the I/O required.

Our competitor claims that their range partitioning would automatically eliminate processing on all of the months other than June 2011 and is a better solution. While their range partitioning will eliminate the I/O like Netezza, there is a whole bunch of effort that partitioning brings to their solution that they do not talk about. In their solution you create a table, and then you create "inheritance children", one per partition. So for a table with 7 years of data, that is 84 monthly partitions, and 84 tables (the base table plus the 83 inheritance children). That might not seem too bad, but there's more. If you have a primary key, foreign key, index, constraint, or permission on the table, you need to apply it to the table and each of its inheritance children, it is not a global operation. So, for these 84 tables with 3 user groups with different permissions, a primary key, a constraint, and one non-unique index, that would be 84 * (3 + 1 + 1 + 1) or 504 DDL statements to set this up, and to maintain over time.

And on top of that, their bulk loader is not partition aware, so you need to write a rule for each table/inheritance child, adding 84 more DDL statements to the list.

In Netezza you write one statement to create the table, vs. 588 DDL statements for the exact same table and data in this competitor.

I’ll respond to some more of the claims this competitor has been making over my next few posts.

Argumentative Colleagues and IBM Netezza reducing runtime from 7 hours to 8 seconds....

I had an “interesting discussion” with someone during my IBM Netezza Futures presentation last week at the IDUG (International DB2 User’s Group) conference. At the end of the presentation, one of my IBM colleagues was trying to argue that Netezza’s streaming architecture must not be able to handle multi-table joins because “it does all of its work in the FPGA”.  I tried to explain that he was incorrect, and the way that Netezza does joins so efficiently, but my colleague didn’t seem to listen, and asked the same thing again.

Although some people reading my post might think that I am writing this to try to explain to my IBM colleagues how they should act in customer events, I am not. 

The best part of this discussion was when my colleague asked the same question a second time, one of our customers stood up and said “What Dwaine said about the performance of IBM Netezza is exactly right”. The customer then went on to say how impressed his company is with the proof of concept (PoC) they just completed with the IBM DB2 Analytics Accelerator. I will explain the IBM DB2 Analytics Accelerator soon, but what the customer went on to say is that they have queries that were taking 7 hours on their DB2 for z/OS system (and using MIPS all that time) that are now running on the IBM DB2 Analytics Accelerator in 8 seconds and using basically no MIPS. I could not have paid for a better ending to my session.

The IBM DB2 Analytics Accelerator is an IBM Netezza 1000 system piggy backing along side a DB2 for z/OS system. The DB2 for z/OS optimizer will offload large, complex queries from the DB2 system onto the IBM DB2 Analytics Accelerator, much like a materialized view or materialize query table, but keep the short transactional queries running on DB2 for z/OS. 

So with absolutely no change to their application, data loading procedures, or any extra tuning, this customer was able to run their analytics over 3,000 times faster. That’s the power of the IBM Netezza appliance and the IBM DB2 Analytics Accelerator.

And, it would still be nice if my IBM colleagues learned how to act at customer events. 

Increasing Profits with the "Best Offer"

My colleague James Kobielus has been talking about “next best” action recently[1][2], and I also talked about this in my post on the importance of agility for analytic applications[3]. I have been talking to a number of customers recently who want to take this beyond next best, to the best offer.

In my blog I talked about a person who had made a number of purchases, and the retailer was trying to influence the customers next purchase by providing them with a coupon for an item that they might be interested in based on their purchasing history. But, how much more could a company make if they could influence their customers’ purchases before they are made, pushing them to more profitable items during the ordering process.

Think of a distributor of auto parts as an example. As the body shops and garages are placing orders, wither online or over the phone, as they enter an item’s description or SKU, what if you could offer a different part, that has the same specifications and same look and feel, but it costs less, and has a higher profit margin for you. Well, that would be a win-win situation, the garage gets a less expensive part that they can sell to the customer for less (like this would really happen, right?) but the distributor also makes a higher profit.

This take more than just a data warehouse / analytic application. And this type of example is where IBM is uniquely positioned to help you out. You cannot build this “system” with just a data warehouse.

You will need an analytics warehouse that can determine the most profitable items. You may need IBM InfoSphere BigInsights to examine your parts catalogs to find which parts have the same specifications and can be substituted for each other. You will probably need a rules storage mechanism that will record the compatible items from BigInsights and also from your internal sales team who have the deep knowledge of the items they sell. Then you need to have both your online ordering system and your internal order entry system must be intelligent enough to pop-up an offer whenever it sees one of the item numbers or SKUs that have been identified as "replaceable".

While you might be able to call a customer back if you can save them thousands of dollars or more, normally these offers need to be made as the order is being entered/taken, not afterwards.

Bring the power if the IBM Big Data family and our other Information Management and WebSphere solutions to your business, and drive more profit.

---

[1] http://thinking.netezza.com/blog/next-best-action-healthcare-save-improve-lives-through-applied-analytics

[2] http://thinking.netezza.com/blog/next-best-action-smarter-planet-think-global-optimize-local

[3] http://dsnowondb2.blogspot.com/2012/05/importance-of-agility-for-analytic.html

Thoughts on "in memory" data warehousing / analytics

“In Memory” seems to be the latest buzz term in the database and analytics arena. If in memory was able to live up to the hype it would be a great thing, but there is still a lot of hype involved.

In memory database vendors claim that “Reporting requirements have a large impact on I/O time and network resources when dealing with large volumes of data. In order to minimize this, a company needs optimized file and database access methods. In-memory overcomes this process by shifting all the data and workload into memory.” [1]

But this is not the only way to eliminate the I/O bottleneck that can plague traditional databases when they are used for data warehousing and analytics. IBM Netezza solves the I/O bottleneck by pushing the work down to the I/O channel, where it acts on the data as it comes off disk, and only sends the data that matches the query across the server inter-connect.

Because IBM Netezza operates on the data as it streams off of the disks, it is not plagued data not fitting in memory. In an in-memory database, the entire database must fit in memory, and any temporary tables, scratch tables, overflowed sorts, etc. must also fit entirely within the memory the system has been configured to use.  As data volume grow, and the “warehouse” becomes more and more visible, there will be more users, and as the number of users increases, the chance that 2, 3, or even 20 or more people will be running queries at the same time, and may be sorting or ordering data using temporary tables. Or, a new user may point and click their way around Business Objects and build a huge query that joins 5 or 10 tables. All of these will cause large temporary tables to be created, all at once, potentially filling up the memory on the system.

So, what happens if an in memory databases uses up all of the system memory (RAM) on a server? Well according to an SAP employee, the system will crash.[2]  Does that seem like a good solution to you?

Because of issues like this, most in memory databases are used to run reports and/or analytics on a sample of the data, to restrict the data size to something manageable and affordable.  And, as I explained a couple weeks ago, using data sampling for analytics is a bad idea, that will severely limit, if not completely eliminate the value you can derive from your data. Data that you store (some where else, since it cannot fit in memory) and do not analyze is a liability, only when you analyze your data does it become an asset.  IBM Netezza ensures that all of your data is a business asset that you can derive the most value from.

There are a number of other points that I want to make on “in memory” databases, but I’ll talk about them in my upcoming posts. 

---

[1] In Memory Analytuics, Retrieved 5/14/2012 from http://www.yellowfinbi.com/Document.i4?DocumentId=104879

[2] SAP Community Network, retrieved 5/14/2012 from http://forums.sdn.sap.com/thread.jspa?threadID=2125871

Come see me at IDUG next week in Denver

I will be presenting IBM Netezza futures next week at the IDUG North America conference.  Drop by and say Hi....  Visit the IDUG Site for more info. 

New Paper - Teradata and IBM Netezza Compared

Check out this paper comparing Teradata and IBM Netezza.

Click here to download the paper

It's Time to End Node Rage

Check out this paper and the videos to see why IBM Netezza appliance help "End Node Rage"

End Node Rage

Performance in an Ad-Hoc Environment

I start off my Introduction to Netezza (or Netezza 101) presentations with the question “Would you still use Google if it took 7 days and 3 people o get your answer”, and most people scratch their head at first wondering why I ask this. Let me explain.

In my opinion, Google is a lot like today’s analytic tools/applications. When you use Google, you give it some search terms, and see what it comes up with. If you are lucky, the information you want is on the first page, but maybe it is on page two or three. Or maybe the search results are not what you were looking for, so you change the search terms completely, or add/remove some terms and search again. Eventually you find what you are looking for.

Today’s train of thought analytics is a lot like Google. You never know what someone is going to ask. You don't know where the result of one query will lead, or what the results will ultimately be. Unlike the report that gets the total sales for the past month, there is no “map” to guide train of thought analytics. IBM Netezza provides the unique ability to move beyond just reporting, and allows users to uncover the answers they are looking for.

IBM Netezza is the best solution for train of thought analytics for a number of reasons:

1. IBM Netezza does NOT require indexes to perform optimally 
2. IBM Netezza allows analytics across all of your data, not just a subset (I talked about this in an earlier post[1])

In my opinion a database that relies on indexes (or even worse, 19 different types of indexes) and aggregates to perform well is ill suited for train of though analytics. Now, these databases can run without indexes, as long as you take a very small sample of the data to run against… But then you run into incorrect and/or poor results that will not help drive more value, and may in fact hurt the bottom line.

Let’s use an example to show why a system that needs indexes is not suited for train of thought, or any ad-hoc, analytics. Given the following tables:

create table car_sales(

            delivery_date date,

make  varchar(45),

            model  varchar(45),

            model_year char(4),

            color varchar(20),

            VIN  varchar(45),

            selling_dealer varchar(45))

create table dealer_sales(

            VIN  varchar(45),

            cost decimal (10,2)

sales_price decimal(10,2),

            sales_person varchar(45))

create table sales_people(

            sales_person varchar(45),

dealer varchar(45)

region varchar(10))

If you want the total of all sales then you need only access the dealer_sales table. There are two things that will make this query faster, a materialized view or an index that just contains only the price column in the dealer_sales table. In this case the rows in table dealer_sales are 102 bytes wide. If you created an index on the price column, the index would be roughly 1/8^th of the width of the table, so the query should run roughly 8 times faster.  After seeing this query run a few times, a good DBA will create this index, but it is too late for the people who already ran their analytic queries.

Now, what if the regional sales manager wants to track the total sales by region at any given time. In order to calculate this you need to join the dealer_sales and car_sales tables, and you probably would want to create a join index (or an aggregate depending on which database you are using) to allow that to be done efficiently. The only column in these tables that is common across both tables is the VIN column, and that is not one of the columns that you are even referencing in the query (price and region, and probably date as well so he or she can look at the current day’s sales, as well as the current week, month, year to date, etc.).

If I were a regional manager I would also want to do some optimization of the delivery of new cars to the dealers where they are most likely to sell at the highest profit margin. Now, there are a number of ways to do this, and each has an increasing potential to drive more sales, if the current trends continue:

1)     Compute the average net profit (sales_price – cost) for every region and ship more cars to that region than to other regions

·       This would require at least two join indexes, and likely other secondary indexes on the sales_price and cost columns in dealer_sales.

2)     Compute the average net profit for each make (i.e. Ford, Chevy, Dodge, Acura, etc.), and ship more of that make of car to the dealers for that make in that region than to other regions

·       This would require the same indexes as above, but also an index on the make column in the car_sales table.

3)     Compute the average net profit for each make and model, and ship more of that model of car to the dealers for that make in that region than to other regions

·       This would require the same indexes as above, but also an index on the make and model columns in the car_sales table.

4)     But, maybe different regions also have a preference for color, like in the north eastern US where white does not show the dirt from the sand and salt on the roads in the winter. If we combine the color preference with the make and model preference from above, then we can tailor the inventory of each dealer to their customer set.

·       The color preference query would require the same indexes as above, but also an index on the color column in the car_sales table.

5)     Taking this even further, let’s look at inventory optimization. What if we can eliminate the need to keep cars on the lot for months before they sell. The historical sales might indicate that in Maine there is a three week period at the beginning of May when their sales are three times their normal rate for a two week period. Well, then I would want to make sure I have enough cars on the lot there (optimized by make, model and color of course) to meet this need, but I do not want to keep this much inventory all year long as it ties up a lot of money.

·       This would require more tuning of the data model with an index on the delivery_date column in the car_sales table.

Now, you can say that after all of this, you have all of the indexes and/or aggregates you might ever need. But that is only true for these queries. What about the local dealership manager who wants to see which salesman has sold the most cars in the past month, or sold the car with the highest average profit margin.

This is train of thought analytics, where one answer drives the next question. It is like going for a drive with no destination in mind. You cannot use MapQuest or Google Maps, since you do not know where you are going.

The revolutionary design of the IBM Netezza data warehouse provides exceptional performance without the need for indexes. The IBM Netezza architecture is based on a fundamental computer science principle: when operating on large data sets, do not move data unless absolutely necessary. IBM Netezza data warehouse appliances fully exploit this principle by utilizing commodity components called field programmable gate arrays (FPGAs) to filter out extraneous data as early in the data stream as possible, and as fast as data streams off the disk. This process of data elimination close to the data source removes I/O bottlenecks and frees up downstream components (CPU, memory and network) from processing superfluous data, and is therefore expected to have a significant multiplier effect on system performance.

---

[1] http://dsnowondb2.blogspot.com/2012/05/importance-of-agility-for-analytic.html