Online Transaction Processing Database

Understanding OLTP Databases

Most businesses use (OLTP) databases to gather and store the records generated by their daily operations. Typically, (OLTP) databases execute transactions, meaning that they add, update, or delete groups of records at the same time. For example, the database for a grocery store inserts and updates information about prices, purchases, and costs of goods and freight, and it usually does so at lightning speed. After all, you don’t want your customers to wait in line while your inventory system updates its stock and pricing tables.

However, the design that allows OLTP databases to record transactions quickly and accurately also makes it hard to analyze their data for several reasons. First, OLTP databases contain a large number of tables, sometimes hundreds. Those tables often have multiple relationships with other tables in the database. That complexity can make it hard to understand the database and know where to look for data.

The following figure depicts some of the tables and relations that exist in the Northwind sample database provided by Microsoft® SQL Server™ 2000:

Northwind Database Schema

Northwind Database Schema

Sebagian besar organisasi bisnis menggunakan (OLTP) database untuk mengumpulkan dan menyimpan catatan transaksi operasionalnya sehari-hari. Biasanya, (OLTP) database melakukan transaksi, seperti menambah, memperbarui, atau menghapus record transaksi. Sebagai contoh, database untuk toko retail melakukan update informasi harga, pembelian, dan biaya barang dan angkutan namun transaksi ini harus dilakukan dalam waktu singkat tanpa menutup penjualan/toko. Anda sebagai pemilik usaha tentunya tidak ingin pelanggan anda untuk menunggu dalam antrean sementara sistem inventaris Anda harus memperbaharui tabel harga dan persediaan.

Desain OLTP database memungkinkan untuk mencatat transaksi dengan cepat dan akurat, namun sulit untuk menganalisis data transaksinya karena beberapa alasan. Pertama, database OLTP mengandung sejumlah besar tabel, kadang-kadang ratusan. Tabel-tabel tersebut kadangkala memiliki hubungan dengan tabel-tabel lain dalam database. Kompleksitas relasi antar tabel membuatnya sulit untuk memahami database dan mengetahui tabel mana yang dapat digunakan untuk mencari data.

Gambar berikut menggambarkan beberapa tabel dan hubungan yang ada dalam database Northwind di Microsoft ® SQL Server ™ 2000:

Figure 1. Part of the Northwind database schema

Gambar 1. Salah satu bagian dari Skema Database Northwind

Second, if you try to extract (OLAP) data from an OLTP database, you usually need to create and run stored procedures—groups of SQL statements compiled into a single execution plan. Stored procedures can take hours to run, and they can slow the down the production database, something you don’t want to do with a live system. (Remember the whole “customers waiting in line” thing? You don’t want that to happen.)

Kedua, jika anda mencoba untuk mengekstrak (OLAP) data dari database OLTP, anda biasanya perlu untuk membuat dan menjalankan store procedures-beberapa pernyataan SQL yang dikompilasi ke dalam satu modul eksekusi. Store procedures dapat mengambil jam untuk menjalankan, dan mereka dapat memperlambat turun database produksi, sesuatu yang Anda tidak ingin lakukan dengan sistem hidup. (Ingat seluruh “pelanggan menunggu dalam antrean” hal Anda tidak ingin itu terjadi.?)Third, during normal operations, OLTP databases constantly update their data. Trying to analyze changing data is, well, like trying to analyze changing data. You will always have a hard time obtaining an accurate result, assuming you can obtain one at all.

Finally, OLTP databases usually store individual records. For example:

On April 2, John Smith bought a case of apples from Jane Doe for $5.00.

That type of storage poses a problem for analysts because they use summarized data—totals and subtotals—to help answer business intelligence questions. Individual records don’t help them at all.

In other words, you need a system that extracts data from your OLTP database, aggregates it into totals and subtotals, and then displays the resulting data in a way that allows you to spot past successes and failures, and to identify potential future successes and failures.

The solution to that problem is called an Online Analytical Processing (OLAP) database.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s