Explain OLTP vs OLAP

 

Difference between OLTP and OLAP

OLTP System

OLTP System handle with operational data. Operational data are those data contained in the operation of a particular system. Example, ATM transactions and Bank transactions, etc.

OLAP System

OLAP handle with Historical Data or Archival Data. Historical data are those data that are achieved over a long period. For example, if we collect the last 10 years information about flight reservation, the data can give us much meaningful data such as the trends in the reservation. This may provide useful information like peak time of travel, what kind of people are traveling in various classes (Economy/Business) etc.

The major difference between an OLTP and OLAP system is the amount of data analyzed in a single transaction. Whereas an OLTP manage many concurrent customers and queries touching only an individual record or limited groups of files at a time. An OLAP system must have the capability to operate on millions of files to answer a single query.

FeatureOLTPOLAP
CharacteristicIt is a system which is used to manage operational Data.It is a system which is used to manage informational Data.
UsersClerks, clients, and information technology professionals.Knowledge workers, including managers, executives, and analysts.
System orientationOLTP system is a customer-oriented, transaction, and query processing are done by clerks, clients, and information technology professionals.OLAP system is market-oriented, knowledge workers including managers, do data analysts executive and analysts.
Data contentsOLTP system manages current data that too detailed and are used for decision making.OLAP system manages a large amount of historical data, provides facilitates for summarization and aggregation, and stores and manages data at different levels of granularity. This information makes the data more comfortable to use in informed decision making.
Database Size100 MB-GB100 GB-TB
Database designOLTP system usually uses an entity-relationship (ER) data model and application-oriented database design.OLAP system typically uses either a star or snowflake model and subject-oriented database design.
ViewOLTP system focuses primarily on the current data within an enterprise or department, without referring to historical information or data in different organizations.OLAP system often spans multiple versions of a database schema, due to the evolutionary process of an organization. OLAP systems also deal with data that originates from various organizations, integrating information from many data stores.
Volume of dataNot very largeBecause of their large volume, OLAP data are stored on multiple storage media.
Access patternsThe access patterns of an OLTP system subsist mainly of short, atomic transactions. Such a system requires concurrency control and recovery techniques.Accesses to OLAP systems are mostly read-only methods because of these data warehouses stores historical data.
Access modeRead/writeMostly write
Insert and UpdatesShort and fast inserts and updates proposed by end-users.Periodic long-running batch jobs refresh the data.
Number of records accessedTensMillions
NormalizationFully NormalizedPartially Normalized
Processing SpeedVery FastIt depends on the amount of files contained, batch data refresh, and complex query may take many hours, and query speed can be upgraded by creating indexes.


Ref:

Comments

Post a Comment

Popular posts from this blog

Difference between ETL vs. ELT

Image
  Difference between ETL vs. ELT Basics ETL ELT Process Data is transferred to the ETL server and moved back to DB. High network bandwidth required. Data remains in the DB except for cross Database loads (e.g. source to object). Transformation Transformations are performed in ETL Server. Transformations are performed (in the source or) in the target. Code Usage Typically used for Source to target transfer Compute-intensive Transformations Small amount of data Typically used for High amounts of data Time-Maintenance It needs highs maintenance as you need to select data to load and transform. Low maintenance as data is always available. Calculations Overwrites existing column or Need to append the dataset and push to the target platform. Easily add the calculated column to the existing table. Analysis
Read more

What are the for each loop enumerators available in SSIS?

  Below are the lists of various types of enumerators provided by SSIS Foreach Loop Container: Foreach  File Enumerator : It enumerates files in a folder. The plus point here is it can traverse through subfolders also. Foreach  Item Enumerator : It enumerates items in a collection. Like enumerating rows and columns in an Excel sheet. Foreach  ADO Enumerator : Useful for enumerating rows in tables. Foreach  ADO.NET Schema  Rowset  Enumerator : To enumerate through schema information about a data source. For example, to get list of tables in a database. Foreach   From  Variable Enumerator : Used to enumerate through the object contained in a variable. (if the object is enumerable) Foreach   NodeList  Enumerator : Used to enumerate the result set of an XML Path Language (XPath) expression. Foreach  SMO Enumerator : It enumerates through SQL Server Management Objects (SMO) objects.
Read more

What are the SSIS package protection levels?

  There are 6 different types of protection levels. Do not save sensitive – (When exporting using DTUTIL specify for protection- 0) Encrypt sensitive with user key – 1 Encrypt sensitive with password – 2 Encrypt all with password -3 Encrypt all with user key – 4 Rely on server storage Do not save sensitive:  makes the sensitive data unavailable to other users. If a different user opens the package, the sensitive information is replaced with blanks and the user must provide the sensitive information. Encrypt sensitive with user key:  Uses a key that is based on the current user profile to encrypt only the values of sensitive properties in the package. Only the same user who uses the same profile can load the package. If a different user opens the package, the sensitive information is replaced with blanks and the current user must provide new values for the sensitive data. If the user attempts to execute the package, package execution fails. Encrypt sensitive with password:...
Read more