OLTP Technology: Data Warehousing

1. INTRODUCTION

Data Warehousing is a set of decision support technologies, which allows executives, managers, and analysts to make informed decisions, thereby better and faster. It provides basic planning tools for businessman and his workers organize, understand and use their data to make accurate decisions. Data Warehouse is a database used for analysis and to make reports in a business. It is known to be the database that is maintained individually from the company’s operational database. The information stored in the data warehouse is uploaded from the operational systems. Data Warehouses unite variety of application systems. Data warehousing technologies are successfully used in various industries and companies, as follows:

• Manufacturing

As a result a data warehouse does not require transactions processing, recovery, or consensus control mechanisms. It requires only two operations in data accessing: primary loading of data and accessing of data.

The data warehouse supports an online analytical processing that is the OLAP technology, the requirements of which are quite different from those of the on-line transaction processing that is the OLTP technology which is inturn application traditionally supported by the operational databases.

Online transaction processing covers almost all daily operations of a company or industry such as purchases, record-keeping, built-ups, banking, registrations and many more. An on-line transaction processing system is customer oriented and is used for transaction and query processing. It makes the current data that usually are too detailed to be easily used for decision making which concentrates mainly on the current data of a company or industry, without referring to data of other companies. The access patterns of on-line transaction processing systems consist mainly of short transactions. These systems require concurrency control and healing

MULTIDIMENSIONAL DATA MODEL

Multidimensional data model is the base for data warehouse and OLAP tools. This model views data in the form of a data cube. A data cube allows data to be modeled and viewed in multiple dimensions. A company can keep records with respect dimensions which are perspectives or entities. A table is associated with each dimension, called a dimension table, which tells about the dimension in detail.

It is usually structured around a subject. Fact table represents this subject. The names of the facts, or measures, as well as keys to each of the related dimension tables is contained in this fact table Source: An Overview of Data Warehousing and OLAP Technology, Surajit Chaudhuri (March 1997)

Business analysts use multidimensional data model grew out of the view of business data popularized by programs like spreadsheet. Rotating is one of the popular operations that are supported by the multidimensional spreadsheet. Rotate which is also called pivot, is a visualization procedure that rotates the data axes in view in order to provide an different representation of the data. Other operations are roll-up, drill-down, slice and dice. The roll-up operation performs the aggregation on a data cube, either by climbing up the concept hierarchy for a dimension or by dimension reduction. Drill-down is the reverse of the roll-up. It moves from less detailed data to more detailed data. A selection on one dimension of the cube is operated by slice. A selection