Archives of the TeradataForum

Message Posted: Fri, 12 May 2006 @ 12:32:43 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

In simple terms, Teradata is a database that is optimised to support data warehouse (DWH) activities.

DB2 is a database that is optimised to support OLTP activities.

So what is the difference between OLTP and DWH?

Again in simple terms OLTP is about servicing lots and lots of simple queries generated by a large number of users. Example: support an ATM network.

DWH is about taking all of the information and performing analysis on it. Analysis could be anything. An example is scanning transactions over a period of time for all customers looking for interesting patterns. An example of an interesting pattern might be a customer who has been receiving regular monthly pay checks that suddenly stops. Combine this with the information that the customer has home loan. The implication is they might not be able to service their obligations and it can be dealt with proactively. Other patterns might indicate fraudulent activity.

Can you do this work on DB2? Perhaps if we look at it from the point of view of can we write the same query on the DB2 system. The answer is probably you can. However, if you run this query on the DB2 system, what will be the impact on the OLTP users as DB2 tries to run this query? Basically their response times will blow out to unacceptable levels. Sure you could run the DWH style queries at off peak times, but if you doing anything worth while, you need to be working off of a system where you can get the information when you need it.

For example if someone is defrauding your company, do you want to know when it is happening or can you wait until the weekend when the system is free? And how do you justify running the fraud query (which hardly ever gets many hits) when there are other "more important" queries competing for the window. And what happens if the queries don't complete in time for the peak operation - they will be killed that's what will happen. This setup will only constrain the type of work you can do.

The answer is you wouldn't - if you were serious - so you set up a separate Data Warehouse. So do you set up another DB2 system or pick one that is designed for DWH. Most OLTP systems are optimised to perform OLTP because they perform certain functions very well. However, the things that OLTP do well typically don't help DWH very much. Some OLTP systems for example constrain the number of tables that can be joined, how well they scan the entire content of huge tables and so on. These are OK for OLTP and reporting, but not for DWH analytics.

On a scalability perspective, Teradata's linear scalability is achieved due to its "loosely coupled" MPP architecture. If you need it to do more work, just add more nodes. If you double the size of the system it will do roughly double the work. The reason this is true is because Teradata works like checkout lines in a retailer or bank. If you double the number of operators, you can double the number of customers served per hour. I don't believe this can be claimed for systems optimised for OLTP doing DWH work.

I hope this overview gives you some insight, obviously there is a lot more to it than one can cover in an email message.

If nothing else maybe this analogy will help. Motorcycles, family sedans and busses are all motor vehicles. As such they do similar things (eg move people and luggage), but a motorcycle is optimised to certain requirements which are not the same as bus's requirements or family sedan's and vice-versa. The same if true for databases!

I hope this helps

Glenn Mc

	<Prev	Next>		<<First	<Prev	Next>	Last>>