Friday, January 3, 2014

Business Intelligence - OLTP vs OLAP (Differences)

Before I go to bed, I would like to share about OLTP and OLAP. This article is taken from 2 source: MicroStrategy and Datawarehouse4u. I think this article will be useful for us in order to understand more about the foundation of Business Intelligence. Enjoy. :)


#Source Systems (OLTP)

Source systems are typically databases, file sources, web sources or mainframes that store transaction processing data. As such, they are an OnLine Transaction Processing System (OLTP). Transaction processing involves simple recording of transaction like sales, inventory, withdrawals, deposits, and so forth.

The data in source systems have the following characteristics:
  • Data access is read/write (numerous selects, inserts, updates, and deletes).
  • Data is aligned by application (business activities and workflow).
  • Data formats are not necessarily uniform across different source systems.
  • Data history is typically limited to recent or current data.
A bank is one example of a business with many different types of source systems. The average bank offers their customers a variety of services and, therefore, needs these different type of source systems to support their services, each of which has a different workflow.

For example, consider the Automated Teller Machine (ATM) and the bank teller. At an ATM, you can withdraw or deposit money as well as check your account balance. However, if you need a money order, you have to go inside and speak with a bank teller. The bank has different operational system, each design to perform tasks that are specific to the service.

To take our example further, if a bank wanted to see a unified view of a particular customer - such as a customer's ATM activity, loan status, account balances, money market account information, and so forth - all of the customer's information from each of the different source systems would have to be collected. After this data is collected, it is stored in a centralized data warehouse.

#Data Warehouse (OLAP)

A well-designed and robust data warehouse lies at the heart of the business intelligence system and enables its users to leverage the competitive advantage that business intelligence provides. A data warehouse is an example of an OnLine Analytical Processing System (OLAP).

While source systems are designed and optimized for transaction processing, data warehouses are designed and optimized for analytical

processing. Analytical processing involves manipulating transaction records to calculate sales trends, growth patterns, percent to total contribution, trend report, profit analysis and so forth.

The data in a data warehouse have the following characteristics:
  • Data access is typically read-only (IN-SQL, mostly selects are used. Generally very few inserts, updates, or deletes are used).
  • Data is aligned by business subjects.
  • Data format are uniformly integrated.
  • Data history extends long term, usually 2 to 5 years.
A data warehouse is populated with data from the existing operational systems with an ETL process.

#Summary of Differences - OLTP VS OLAP

The following table summarizes the differences between OLTP and OLAP systems:

#ETL Process

The extraction, transformation, and loading (ETL) process contains information that facilitates the transfer of the data from the source systems to the data data warehouse.

Specifically, the ETL Process does the following:
  • Store information about the source system structure and content
  • Store information about the data warehouse structure and content
  • Correlates source system structure and content to that of the data warehouse
  • Provides information to the data extraction tools that physically execute the transfer of data from the source systems to the data warehouse



We can divide IT systems into transactional (OLTP) and analytical (OLAP). In general we can assume that OLTP systems provide source data to data warehouses, whereas OLAP systems help to analyze it.

OLTP (On-line Transaction Processing) 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).

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).

The following table summarizes the major differences between OLTP and OLAP system design.


  1. Thanks a lot very much for the high quality and results-oriented help. I won’t think twice to endorse your blog post to anybody who wants and needs support about this area.

    Data Science Training in Bangalore

  2. Play online casinos with fun and money fun slots Fun money, take while there.

  3. Nice post.Thanks for sharing this post. Pridesys Business Intelligence (BI) is offering you easy-to-use analytics and business intelligence tools. With these tools you can explore your data. Create new patterns, rich visual and insights. to know more information visit: Pridesys IT Ltd

  4. Hi, thank you very much for new information, i learned something new. Very well written.It was so good to read and usefull to improve knowledge.Keep posting. If you are looking for any big data hadoop related information please visit our website.
    big data hadoop training in bangalore.

  5. On the off chance that you had concluded that you'd needed to be a business examiner, it would have been impossible that you would have considered music, show, medication, or some other apparently irrelevant subjects.
    business analysis

  6. Thanks for sharing useful information. I learned something new from your bog. Its very interesting and informative. keep updating. If you are looking for any apache spark scala related information, please visit our website apache spark scala training institute in bangalore

  7. Nice blog,I understood the topic very clearly,And want to study more like this.
    Data Scientist Course

  8. This comment has been removed by the author.

  9. Attend The Data Analytics Courses From ExcelR. Practical Data Analytics Courses Sessions With Assured Placement Support From Experienced Faculty. ExcelR Offers The Data Analytics Courses.
    Data Analytics Courses
    Data Science Interview Questions

  10. Very nice article, I enjoyed reading your post, very nice share, I want to twit this to my followers. Thanks!. 雅思代考

  11. I am looking for and I love to post a comment that "The content of your post is awesome" Great work!

    Correlation vs Covariance

  12. All the contents you mentioned in post is too good and can be very useful. I will keep it in mind, thanks for sharing the information keep updating, looking forward for more posts.Thanks Pakistan Stock Exchange

  13. It defines the limits of computability (solvable problems) and computational complexity (resources required to solve these problems in terms of time and space). artificial intelligence course in hyderabad

  14. When do you think this Real Estate market will go back right side up? Or is it still too early to tell? We are seeing a lot of housing foreclosures in Winter Springs Florida. What about you? Would love to get your feedback on this. Flow Meter LC

  15. It is really wonderful and awesome thus it is very much useful for me to understand many concepts and helped me a lot. it is really explainable very well and i got more information from your blog. Please, continue to give me such valuable posts.Data Science Training In Chennai

    Data Science Online Training In Chennai

    Data Science Training In Bangalore

    Data Science Training In Hyderabad

    Data Science Training In Coimbatore

    Data Science Training

    Data Science Online Training

  16. Be that as it may, when they show up at the speculator's or loan specialist's table some remain where they are and others go to the "I'll read them later" heap or more terrible still the garbage bin! . Knowledge Leader

  17. I just recently discovered your blog and have now scrolled through the entire thing several times. I am very impressed and inspired by your skill and creativity, and your "style" is very much in line with mine. I hope you keep blogging and sharing your design idea
    oracle training in chennai

    oracle training in velachery

    oracle dba training in chennai

    oracle dba training in velachery

    ccna training in chennai

    ccna training in velachery

    seo training in chennai

    seo training in velachery

  18. You made such an interesting piece to read, giving every subject enlightenment for us to gain knowledge. Thanks for sharing the such information with us to read this... text message marketing for small business

  19. What a sensational blog! This blog is too much amazing in all aspects. Especially, it looks awesome and the content available on it is utmost qualitative. audit tracking software

  20. Through receiving a filtering and work process answer for supplant manual cycles, improved perceivability, straightforwardness and efficiencies can be harvested. nyc budget

  21. 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.

    Simple Linear Regression

    Correlation vs covariance

    KNN Algorithm

    Logistic Regression explained

  22. I am looking for and I love to post a comment that "The content of your post is awesome" Great work!

    data science interview questions

  23. Despite your great business plan, you will fail to obtain a loan because of your nervousness and lack of confidence when it comes to acting like a clever salesperson. free email spider software full version

  24. I just got to this amazing site not long ago. I was actually captured with the piece of resources you have got here. Big thumbs up for making such wonderful blog page! strongarticle

  25. Really nice and interesting post. I was looking for this kind of information and enjoyed reading this one. Keep posting. Thanks for sharing.

    Simple Linear Regression

    Correlation vs Covariance

  26. Impressive! I finally found great post here. Nice article on data science . It's really a nice experience to read your post. Thanks for sharing your innovative ideas to our vision.

    Data Science Training in Hyderabad
    Data Science course in Hyderabad
    Data Science coaching in Hyderabad
    Data Science Training institute in Hyderabad
    Data Science institute in Hyderabad

  27. The essential explanations behind the low turnout are vulnerability on a use of web-based media, figuring rate of profitability and convince workers/partners to secure online media.CBT Bulk Email Sender Software

  28. Many entrepreneurs begin with a lot of "strong domain" experience but nützlicher Inhalt little to no business experience. Being in business is about finding solutions. That is what sets the strong businesses apart from the others.

  29. Attend The Data Analyst Course From ExcelR. Practical Data Analyst Course Sessions With Assured Placement Support From Experienced Faculty. ExcelR Offers The Data Analyst Course.
    Data Analyst Course

  30. It is in this scenario, that we often see small businesses jumping the bandwagon and creating profiles on all the available platforms. Creating social profile doesn't hamper brand image, but aggressively promoting a brand on wrong platforms can lead to brand losing its potential customers.more to go

  31. To start a business, you need to find out what it is that you are truly passionate about. "Why is that important", you say? The answer is simple. You really do not want to get yourself stuck into a kind of work that you hate.

  32. For certain components of a good business succession plan to work, business opportunities funding is required. Some common ways of funding a succession plan include investments, internal reserves and bank loans.

  33. This is a great inspiring article.I am pretty much pleased with your good work.You put really very helpful information. Keep it up. Keep blogging. Looking to reading your next post. reputation management services

  34. I agree with your point. You guys should check this brand naming agency’s new online course. It really inspired me to be more creative in my business especially with the name of my brand. business people

  35. Thanks for the blog post buddy! Keep them coming... Australian Domain Name Registration

  36. Nice blog, I will keep visiting this blog very often. mastering chemistry

  37. This is just the information I am finding everywhere. Thanks for your blog, I just subscribe your blog. This is a nice blog.. sulopay

  38. All business plans have pretty much similar areas some even have a similar substance. SEO optimalisatie

  39. I was looking at some of your posts on this website and I conceive this web site is really instructive! Keep putting up.. 123movies

  40. Thanks for sharing nice information with us. i like your post and all you share with us is uptodate and quite informative, i would like to bookmark the page so i can come here again to read you, as you have done a wonderful job. Investment in Pakistan stock exchange PSX

  41. Love to read it,Waiting For More new Update and I Already Read your Recent Post its Great Thanks. Make money online

  42. Thanks for posting the best information and the blog is very science interview questions and answers


Share Your Inspiration...