Back to the Basics - OLAP vs OLTP

In the last article we looked at Third Normal Form. In this article we look at a de-normalized data structure for Business Intelligence data analysis called OLAP.

OLAP vs. OLTP

In the 1980's a new method of structuring data for analysis emerged called On-Line Analytical Processing (OLAP). It was in contrast to the prevailing method for structuring data, which was On-Line Transactional Processing (OLTP).

OLTP was designed to accomplish certain purposes with regard to databases, namely:

• efficient data storage
• fast performance when writing data (either creating or updating records)
• rapid query response time for returning individual records

OLTP accomplishes these purposes by strictly adhering to Third Normal Form (3NF). No data element is stored twice anywhere in the database, which ensures efficient storage and writing performance.

The majority of database systems in the world are OLTP. Think of your bank's system that tracks your chequing account transactions or your grocery store's Point-of-Sale system at the check-out counter that tallies your scanned products. Transactions still rule the world and OLTP systems will always have a prominent place in it.

However, for all its strengths, OLTP has a major weakness -- it can be slow when trying to aggregate large amounts of transaction data. For instance, my bank system is very fast at processing a cash withdrawal at an ATM or showing my last 10 transactions for the month. However, if an analyst at the bank wants to know the average deposit amount across all personal chequing accounts in eastern Canada in 2014, that may take a while. In fact, it may slow down the entire system while the query crunches through the millions of records necessary to find the answer.

This is why OLAP was created. It had one purpose only:

• fast performance for aggregation queries

Note there is nothing mentioned about efficient data storage or data writing performance. OLAP only cares about data aggregation performance. That single purpose is tremendously freeing because all other concerns become secondary. If storing the same data element in 3 places in the database will improve aggregation query performance, then we can do it. If it slows down data update performance, that's OK. If finding an individual record takes longer than in an OLTP system, who cares?

The main consequence of OLAP's singular purpose is that it does NOT follow 3NF. OLAP is a non-normalized data structure. Some Database Administrators (DBA's) are so used to using 3NF that they try to force normalization into OLAP, but that defeats the purpose of OLAP. Normalization slows queries because it introduces additional table joins, and joins are the enemy of fast query performance.

In the next article we will compare the data models that support OLTP and OLAP.

When Data Mining Makes You Smart, Should You Play Dumb?

If your data mining tools are successful at giving you business intelligence about your customers, should you act like you have that intelligence?  Or should you play dumb?

I started thinking about this after reading an article in this weekend's NY Times called "How Companies Learn Your Secrets" by Charles Duhigg.

The article describes how Target stores have had success at mining their customer data to figure out which customers are likely expecting a baby.  In fact, they even go as far as predicting the due date.  It turns out people change their buying habits during periods of major change in their lives, and having a baby is about the biggest life change that comes around.  However, if you wait until after the baby is born, you are already too late.  The changes in buying habits have already occurred.  Target tried to influence those buying habits during the second trimester of a pregnancy.

And they were good at it.  Too good in fact.  When women got coupon books with coupons for pregnancy and baby needs, they got spooked.  Their response was, "How did Target know I was expecting when I haven't told them?"  They wouldn't use the coupons.

So Target dumbed things down a bit.  Instead of sending customized flyers with just baby products, they added random coupons for things the expectant mother would not be interested in, such as a lawn mower.  When the woman thought she was getting the same flyer as every other house on the block, she would gladly use the baby coupons.

It seems like business intelligence is becoming more like its namesake, military intelligence.  When you know something about your adversary, it is only helpful to you if they don't know that you know it!  When the British broke the German Enigma code, they obviously wanted to use it in their war efforts, but they were also careful not to demonstrate their knowledge to the Germans.  That meant sometimes you didn't use that intelligence.  The most famous instance (still debated about its truthfulness) was when Churchill knew Coventry was to be bombed based on Enigma intercepts, but did not take defensive measures which would tip off the Germans.

Now that we are achieving some success at business intelligence, we have a new decision to make:  When do we choose to NOT use our knowledge about our customers in order to keep them buying from us?