What are OLTP and OLAP What is the difference between them

Question

Actually what do they mean  All articles I find about them don t give me an idea  or my knowledge is too insufficient to understand it   Will some one give me some resources with which I can learn this from scratch

User · Answer

The difference is quite simple   OLTP  Online Transaction Processing   OLTP is a class of information systems that facilitate and manage transaction-oriented applications  OLTP has also been used to refer to processing in which the system responds immediately to user requests  Online transaction processing applications are high throughput and insert or update-intensive in database management  Some examples of OLTP systems include order entry  retail sales  and financial transaction systems   OLAP  Online Analytical Processing   OLAP is part of the broader category of business intelligence  which also encompasses relational database  report writing and data mining  Typical applications of OLAP include business reporting for sales  marketing  management reporting  business process management  BPM   budgeting and forecasting  financial reporting and similar areas   See more details OLTP and OLAP

User · Answer

OLTP  It stands for OnLine Transaction Processing and is used for managing current day to day data information  OLAP  It stands for OnLine Analytical Processing and is used to maintain the past history of data and mainly used for data analysis  it can also be referred to as warehouse

User · Answer

Very short answer    Different databases have different uses  I m not a database expert  Rule of thumb    if you are doing analytics  ex  aggregating historical data  use OLAP if you are doing transactions  ex  adding removing orders on an e-commerce cart  use OLTP   Short answer   Let s consider two example scenarios   Scenario 1   You are building an online store website  and you want to be able to    store user data  passwords  previous transactions    store actual products  their associated prices   You want to be able to find data for a particular user  change its name    basically perform INSERT  UPDATE  DELETE operations on user data  Same with products  etc    You want to be able to make transactions  possibly involving a user buying a product  that s a relation   Then OLTP is probably a good fit   Scenario 2   You have an online store website  and you want to compute things like   the  total money spent by all users   what is the most sold product    This falls into the analytics business intelligence domain  and therefore OLAP is probably more suited   If you think in terms of  It would be nice to know how what how much      and that involves all  objects  of one or more kind  ex  all the users and most of the products to know the total spent  then OLAP is probably better suited   Longer answer   Of course things are not so simple  That s why we have to use short tags like OLTPand OLAP in the first place  Each database should be evaluated independently in the end   So what could be the fundamental difference between OLAP and OLTP   Well  databases have to store data somewhere  It shouldn t be surprising that the way the data is stored heavily reflects the possible use of said data  Data is usually stored on a hard drive  Let s think of a hard drive as a really wide sheet of paper  where we can read and write things  There are two ways to organize our reads and writes so that they can be efficient and fast   One way is to make a book that is a bit like a phone book  On each page of the book  we store the information regarding a particular user  Now that s nice  we can find the information for a particular user very easily  Just jump to the page  We can even have a special page at the beginning to tell us on which page the users are if we want  But on the other hand  if we want to find  say  how much money all of our users spent then we would have to read every page  i e  the whole book  That would be a row-based book database  OLTP   The optional page at the beginning would be the index   Another way to use our big sheet of paper is to make an accounting book  I m no accountant  but let s imagine that we would have a page for  expenditures    purchases     That s nice because now we can query things like  give me the total revenue  very quickly  just read the  purchases  page   We can also ask for more involved things like  give me the top ten products sold  and still have acceptable performance  But now consider how painful it would be to find the expenditures for a particular user  You would have to go through the whole list of everyone s expenditures and filter the ones of that particular user  then sum them  Which basically amounts to  read the whole book  again  That would be a column-based database  OLAP    It follows that    OLTP databases are meant to be used to do many small transactions  and usually serve as a  single source of truth   OLAP databases on the other hand are more suited for analytics  data mining  fewer queries but they are usually bigger  they operate on more data     It s a bit more involved than that of course and that s a 20 000 feet overview of how databases differ  but it allows me not to get lost in a sea of acronyms   Speaking of acronyms    OLTP   Online transaction processing OLAP   Online analytical processing   To read a bit further  here are some relevant links which heavily inspired my answer    http   datawarehouse4u info OLTP-vs-OLAP html http   www ibmsystemsmag com Blogs You-and-i Archive db-102-database-orientation-row-vs-column  http   martinfowler com bliki DataLake html

User · Answer

Here you will find a better solution OLTP vs  OLAP   OLTP  On-line Transaction Processing  is involved in the operation of a particular system  OLTP is characterized by a large number of short on-line transactions  INSERT  UPDATE  DELETE   The main emphasis for OLTP systems is put on very fast query processing  maintaining data integrity in multi-access environments and an effectiveness measured by number of transactions per second  In OLTP database there is detailed and current data  and schema used to store transactional databases is the entity model  usually 3NF   It involves Queries accessing individual record like Update your Email in Company database  OLAP  On-line Analytical Processing  deals with Historical Data or Archival Data  OLAP 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   Sometime query need to access large amount of data in Management records like what was the profit of your company in last year

User · Answer

oltp- mostly used for business transaction used to collect business data In sql we use insert update and delete command for retrieving small source of data like wise they are highly normalised     OLTP Mostly used for maintaining the data integrity   olap- mostly use for reporting data mining and business analytic purpose  for the large or bulk data deliberately it is de-normalised  it stores Historical data

[database] What are OLTP and OLAP. What is the difference between them?

Examples related to database

Examples related to database-design

Examples related to database-schema

Examples related to olap

Examples related to oltp