OLTP & OLAP

OLTP (online transaction processing) is a class of software programs capable of supporting transaction-oriented applications on the Internet.

OLTP systems are used for order entry, financial transactions, customer relationship management (CRM) and retail sales. Such systems have a large number of users who conduct short transactions. Database queries are usually simple, require sub-second response times and return relatively few records.

An important attribute of an OLTP system is its ability to maintain concurrency. To avoid single points of failure, OLTP systems are often decentralized.

In OLTP database there is detailed and current data, and schema used to store transactional databases is the entity model (usually 3NF).

OLAP (On-line Analytical Processing) is characterized by relatively low volume of transactions. Queries are often very complex and involve aggregations. For OLAP systems a response time is an effectiveness measure.

OLAP applications are widely used by Data Mining techniques.

In OLAP database there is aggregated, historical data, stored in multi-dimensional schemas (usually star schema).

Types of OLAP

Online Analytical Processing Server (OLAP) is based on the multidimensional data model. It allows managers and analysts to get an insight of the information through fast, consistent, and interactive access to information. Various types of OLAP servers are:

• ·         Relational OLAP (ROLAP)
• ·         Multidimensional OLAP (MOLAP)
• ·         Hybrid OLAP (HOLAP)
• ·         Specialized SQL Servers

ROLAP servers are placed between relational back-end server and client front-end tools. To store and manage warehouse data, ROLAP uses relational or extended-relational DBMS.

ROLAP includes the following:

• ·         Implementation of aggregation navigation logic.
• ·         Optimization for each DBMS back-end.
• ·         Additional tools and services.

Advantages

• ·         ROLAP servers can be easily used with existing RDBMS.
• ·         Data can be stored efficiently, since no zero facts can be stored.
• ·         ROLAP tools do not use pre-calculated data cubes.
• ·         DSS server of micro-strategy adopts the ROLAP approach.

Disadvantages

• ·         Poor query performance.
• ·         Some limitations of scalability depending on the technology architecture that is utilized.

MOLAP uses array-based multidimensional storage engines for multidimensional views of data. With multidimensional data stores, the storage utilization may be low if the dataset is sparse. Therefore, many MOLAP servers use two levels of data storage representation to handle dense and sparse datasets.

• ·   MOLAP tools process information with consistent response time regardless of level of summarizing or calculations selected.
• ·         MOLAP tools need to avoid many of the complexities of creating a relational database to store data for analysis.
• ·         MOLAP tools need fastest possible performance.
• ·         MOLAP server adopts two level of storage representation to handle dense and sparse datasets.
• ·         Denser sub-cubes are identified and stored as array structure.
• ·         Sparse sub-cubes employ compression technology.

Advantages

• ·         MOLAP allows fastest indexing to the pre-computed summarized data.
• ·         Helps the users connected to a network who need to analyze larger, less-defined data.
• ·         Easier to use, therefore MOLAP is suitable for inexperienced users.

      Disadvantages

• ·         MOLAP are not capable of containing detailed data.
• ·         The storage utilization may be low if the data set is sparse. 

MOLAP vs ROLAP

MOLAP	ROLAP
Information retrieval is fast.	Information retrieval is comparatively slow.
Uses sparse array to store datasets.	Uses relational table.
MOLAP is best suited for inexperienced users, since it is very easy to use.	ROLAP is best suited for experienced users.
Maintains a separate database for data cubes.	It may not require space other than available in the data warehouse.
DBMS facility is weak.	DBMS facility is strong.

HOLAP Hybrid OLAP is a combination of both ROLAP and MOLAP. It offers higher scalability of ROLAP and faster computation of MOLAP. HOLAP servers allows to store the large data volumes of detailed information. The aggregations are stored separately in MOLAP store.

Specialized SQL servers provide advanced query language and query processing support for SQL queries over star and snowflake schemas in a read-only environment.