OLAP vs OLTP | databases

OLAP Example

BW system

Informative Environment

OLAP

Online Analytical Processing

OLAP

High

OLAP Analysis Levels

Operative and Historical data is stored

OLAP Data Age

Low

OLAP Data Extraction frequency

Data set is 2-7 years

OLAP Data Set

OLAP data is a Large amount of operative and historical data that comes from the various OLTP Databases such as BW systems, thus it is a Consolidation data.

OLAP Data Source

Typically, de-normalized with very few tables since we are using star/snowflake schemas. Large volume of data stored on OLAP system. The goal is to Reduce the number of tables read when analyzing data. So redundant data is preferred in OLAP as it allows us to read data as fast as possible.

OLAP Database design

Obtain source data Multidimensional analyses according to various business perspectives Stage information: Provides detailed analysis of historical data

OLAP MOA

Speed depends on the amount of data involved - Batch data refreshes - Complex queries may take many hours. Query speed can be improved by creating indexes.

OLAP Processing Speed

OLAP cube/ multidimensional cube/ hypercube: consists of measures i.e. numeric facts that are categorized by dimensions Multidimensional model is a structured frame where a fact table is placed in the middle surrounded by dimension tables. Use of star and / or snowflake schemas.

OLAP Schema

Used to quickly read data to help with planning, problem solving and decision support.

OLAP Usage

Online Transactional Processing

OLTP

Operative Environment

OLTP

Less

OLTP Analysis Levels

Detailed and Current data is stored

OLTP Data Age

High

OLTP Data Extraction frequency

OLTP is Operational data that we get from our day to day transactions thus the OLTPs are the original source of data.

OLTP Data Source

Data Set is 6-18 months old.

OLTP Data set

Highly normalized with many tables. Normalized means deleting or reducing excess or redundant data in a table, thus keeping the system efficient. OLTP system is used to store information quickly, so we try to Delete any extra or redundant data from our tables so we don't have to update several tables and thus the system is able to be efficient.

OLTP Database Design

Enterprise central component [ECC, customer relation management [CRM, sales relational management [SRM], Supply chain management [SCM], APO, R/3.

OLTP Examples

User request [immediate response, quick, fast query processing [effectiveness is measured by # of transactions per second] ] system facilitates and manages transaction oriented applications while maintaining data integrity in muli access environments data entry transaction processing, data retrieval transaction processing, operational data, current data, data from day to day transactions [the original data source ie source data ware house

OLTP MOA

Typically very fast

OLTP Processing Speed

Entity relationship Model [usually 3NF] ER model maintains relationship between the entity and its attributes in different ways. Has a large data flow, it is efficient but complex, thus not good for analysis purpose.

OLTP Schema

To control and run fundamental business tasks. Used for data base functions such as add, insert, update, delete, modify and read. To quickly load data.

OLTP Usage