Monday, August 3, 2015

DW - OLTP vs OLAP

OLTP stands for On-line Transaction Processing. OLTP based systems (account, ticket booking, banking systems, money transfer system) are used to perform a large number of short transactions. Almost all of the database queries in OLTP system consist of commands insert, update, delete. Select queries are mainly designed to enable users to select data from different entities. The majority of query is known in advance at the design stage of the system. Thus, critical for OLTP system is the speed and reliability of the performance of short operations updating data.

We know that OLTP has also been used to refer to processing in which the system responds immediately to user requests. In OLTP databases, there are detailed and current data, and schema used to store transnational databases is the entity model.

OLAP stands for On-line Analytical Processing which was described by Edgar Codd in 1993. OLAP based systems are used for management decisions and these system known as Decision Support System (DSS). OLAP-applications operate with large amounts of data accumulated in the operational databases OLTP. Typical applications of OLAP include business reporting for sales, marketing, management reporting, business process management (BPM), budgeting and forecasting, financial reporting and similar areas.

As we know that OLAP is the most important component of business intelligence, which also encompasses relational database, report writing and data mining. 

Collaboration of OLTP and OLAP systems
Following work flow structure is capable to show the importance of the each system based application and how are they link with each -

Major differences between OLTP and  OLAP

Contents
OLTP System 
OLAP System 
Source of data
Operational data; OLTP databases are meant to be used to do many small transactions, and usually serve as a "Single Source Of Truth"
Consolidation data; OLAP data comes from the various OLTP Databases which is used for analytics, data mining, less queries but they are usually bigger (they operate on more data).
Purpose of data
To control and run fundamental business tasks, facilitate and manage transaction-oriented applications
To help with planning, problem solving, and decision support, report writing and data mining.
What the data
Reveals a snapshot of ongoing business processes, used to refer to processing in which the system responds immediately to user requests.
Multi-dimensional views of various kinds of business activities to help out the better decision making.
Inserts and Updates
Short and fast inserts and updates initiated by end users.
Periodic long-running batch jobs refresh the data by the help of ETLs processes.
Queries
Relatively standardized and simple queries Returning relatively few records which are not more than 2 years
Often complex queries involving aggregations which are based on the large volume of the data more than 2 years.
Processing Speed
Typically very fast and always hit the main databases. Provides primitive and highly detailed data.
Depends on the amount of data involved; batch data refreshes and complex queries may take many hours; query speed can be improved by creating indexes. Provides summarized and consolidated data.
Space Requirements
Can be relatively small if historical data is archived. Mostly Database size is from 100 MB to 100 GB.
Larger due to the existence of aggregation structures and history data; requires more indexes than OLTP. Database size is from 100 GB to 100 TB
Database Design
Highly normalized with many tables and provides high performance.
Highly flexible. Typically de-normalized with fewer tables; use of star and/or snowflake schemas.
Backup and Recovery
Backup religiously; operational data is critical to run the business, data loss is likely to entail significant monetary loss and legal liability
Instead of regular backups, some environments may consider simply reloading the OLTP data as a recovery method

67 comments:

  1. The last item is a simple to-utilize programming bundle that can be utilized even by non-mathematicians to adequately break down the information they have. data science course in pune

    ReplyDelete
  2. Well, The information which you posted here is very helpful & it is very useful for the needy like me.., Wonderful information you posted here. Thank you so much for helping me out to find the Data science course in mumbai
    Organisations and introducing reputed stalwarts in the industry dealing with data analyzing & assorting it in a structured and precise manner. Keep up the good work. Looking forward to view more from you.

    ReplyDelete

  3. Excelr is providing emerging & trending technology training, such as for data science, Machine learning, Artificial Intelligence, AWS, Tableau, Digital Marketing. Excelr is standing as a leader in providing quality training on top demanding technologies in 2019. Excelr`s versatile training is making a huge difference all across the globe. Enable ?business analytics? skills in you, and the trainers who were delivering training on these are industry stalwarts. Get certification on "
    data science training institute in hyderabad
    "
    and get trained with Excelr.

    ReplyDelete
  4. Such a very useful Blog. Very interesting to read this article. I have learn some new information.thanks for sharing. know more about

    ReplyDelete
  5. I feel very grateful that I read this. It is very helpful and very informative and I really learned a lot from it.
    Click here

    ReplyDelete

  6. Great post i must say and thanks for the information. Education is definitely a sticky subject. However, is still among the leading topics of our time. I appreciate your post and look forward to more. click here to know Excelr PMP

    ReplyDelete
  7. I am really enjoying reading your well written articles. It looks like you spend a lot of effort and time on your blog. I have bookmarked it and I am looking forward to reading new articles. Keep up the good work.
    business analytics course

    ReplyDelete
  8. This post is very simple to read and appreciate without leaving any details out. Great work! data science courses

    ReplyDelete
  9. Such a very useful article. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article.
    courses in business analytics

    data science course in mumbai

    data analytics courses

    data science interview questions

    ReplyDelete
  10. Great post i must say and thanks for the information. Education is definitely a sticky subject. However, is still among the leading topics of our time. I appreciate your post and look forward to more.

    orthodontist in bangalore

    ReplyDelete
  11. I would also motivate just about every person to save this web page for any favorite assistance to assist posted the appearance.

    data science course
    360DigiTMG

    ReplyDelete
  12. I will really appreciate the writer's choice for choosing this excellent article appropriate to my matter.Here is deep description about the article matter which helped me more.
    PMP Certification Pune
    I think this is an informative post and it is very useful and knowledgeable. therefore, I would like to thank you for the efforts you have made in writing this article.
    Great post i must say and thanks for the information. Education is definitely a sticky subject. However, is still among the leading topics of our time. I appreciate your post and look forward to more.

    ReplyDelete
  13. Through this post, I know that your good knowledge in playing with all the pieces was very helpful. I notify that this is the first place where I find issues I've been searching for. You have a clever yet attractive way of writing.
    data science courses

    ReplyDelete
  14. Great post i must say and thanks for the information. Education is definitely a sticky subject. However, is still among the leading topics of our time. I appreciate your post and look forward to more.data scientist course in pune with placement

    ReplyDelete
  15. I Want to leave a little comment to support and wish you the best of luck.we wish you the best of luck in all your blogging endeavors.Business Analytics course in bangalore

    ReplyDelete
  16. Ucuz, kaliteli ve organik sosyal medya hizmetleri satın almak için Ravje Medyayı tercih edebilir ve sosyal medya hesaplarını hızla büyütebilirsin. Ravje Medya ile sosyal medya hesaplarını organik ve gerçek kişiler ile geliştirebilir, kişisel ya da ticari hesapların için Ravje Medyayı tercih edebilirsin. Ravje Medya internet sitesine giriş yapmak için hemen tıkla: https://www.ravje.com

    İnstagram takipçi satın almak için Ravje Medya hizmetlerini tercih edebilir, güvenilir ve gerçek takipçilere Ravje Medya ile ulaşabilirsin. İnstagram takipçi satın almak artık Ravje Medya ile oldukça güvenilir. Hemen instagram takipçi satın almak için Ravje Medyanın ilgili sayfasını ziyaret et: instagram takipçi satın al

    Tiktok takipçi satın al istiyorsan tercihini Ravje Medya yap! Ravje Medya uzman kadrosu ve profesyonel ekibi ile sizlere Tiktok takipçi satın alma hizmetide sunmaktadır. Tiktok takipçi satın almak için hemen tıkla: tiktok takipçi satın al

    ReplyDelete
  17. One of the best article i had read ,
    PMP Certification Pune Training .keep it up. If you are Searching for more info click on given link

    ReplyDelete
  18. A fascinating summary of the importance of OLAP in decision-making! Your comparison of OLTP and OLAP systems is both interesting and well-presented. It is obvious that OLAP's multidimensional perspectives enable better business decisions.
    Data Analytics Courses in India

    ReplyDelete
  19. I just wanted to say that I'm here to support you and wish you well. I am wishing you success in all of your blogging activities.
    Data Analytics Courses in Agra

    ReplyDelete
  20. An informative article likely discussing the key differences between OLTP (Online Transaction Processing) and OLAP (Online Analytical Processing), fundamental concepts in the world of databases and data management.

    Data Analytics Courses In Kochi



    ReplyDelete
  21. Hello Blogger,
    This article offers a comprehensive explanation of the distinctions between OLTP and OLAP systems. It's a valuable resource for anyone seeking to understand their roles and significance in the world of data processing. Well-detailed and informative!
    Is iim skills fake?

    ReplyDelete
  22. This blog post likely delves into the critical distinction between OLTP (Online Transaction Processing) and OLAP (Online Analytical Processing) in the context of data warehousing. Understanding the differences between these two fundamental approaches is essential for effective data management and analysis. This post is likely a valuable resource, offering insights into the purposes, characteristics, and use cases of both OLTP and OLAP systems. A must-read for data professionals and decision-makers seeking to optimize their data strategies and infrastructure.
    Data Analytics Courses in Delhi



    ReplyDelete
  23. This comment has been removed by a blog administrator.

    ReplyDelete
  24. This comment has been removed by a blog administrator.

    ReplyDelete
  25. Well, for someone in need like myself, the information you posted here is very beneficial. You provided some wonderful information here.
    Data Analytics Courses in Agra

    ReplyDelete
  26. This comment has been removed by a blog administrator.

    ReplyDelete
  27. This comment has been removed by a blog administrator.

    ReplyDelete
  28. This comment has been removed by a blog administrator.

    ReplyDelete
  29. This comment has been removed by a blog administrator.

    ReplyDelete
  30. This comment has been removed by a blog administrator.

    ReplyDelete
  31. This comment has been removed by a blog administrator.

    ReplyDelete
  32. This comment has been removed by a blog administrator.

    ReplyDelete
  33. This comment has been removed by a blog administrator.

    ReplyDelete
  34. This comment has been removed by a blog administrator.

    ReplyDelete
  35. This comment has been removed by a blog administrator.

    ReplyDelete
  36. This comment has been removed by a blog administrator.

    ReplyDelete
  37. This comment has been removed by a blog administrator.

    ReplyDelete
  38. This comment has been removed by the author.

    ReplyDelete
  39. This comment has been removed by a blog administrator.

    ReplyDelete
  40. This comment has been removed by a blog administrator.

    ReplyDelete
  41. This comment has been removed by a blog administrator.

    ReplyDelete
  42. This comment has been removed by a blog administrator.

    ReplyDelete
  43. This comment has been removed by a blog administrator.

    ReplyDelete
  44. This comment has been removed by a blog administrator.

    ReplyDelete
  45. This comment has been removed by a blog administrator.

    ReplyDelete
  46. This comment has been removed by a blog administrator.

    ReplyDelete
  47. This comment has been removed by a blog administrator.

    ReplyDelete
  48. This comment has been removed by a blog administrator.

    ReplyDelete
  49. This comment has been removed by a blog administrator.

    ReplyDelete
  50. This comment has been removed by a blog administrator.

    ReplyDelete
  51. This comment has been removed by a blog administrator.

    ReplyDelete
  52. This comment has been removed by a blog administrator.

    ReplyDelete
  53. This comment has been removed by a blog administrator.

    ReplyDelete
  54. This comment has been removed by a blog administrator.

    ReplyDelete
  55. Your article serves as a great introduction to Microsoft Business Intelligence, and it will undoubtedly assist those looking to leverage these tools for their data-driven decision-making processes. Keep up the good work!
    Data Analytics Courses In Chennai

    ReplyDelete
  56. This comment has been removed by a blog administrator.

    ReplyDelete
  57. This comment has been removed by a blog administrator.

    ReplyDelete
  58. This comment has been removed by a blog administrator.

    ReplyDelete
  59. This comment has been removed by a blog administrator.

    ReplyDelete