Skip to Content
Author's profile photo Former Member

Planning technologies – OLAP or OLTP?

Before getting into the debate, let us get it into explaining what is OLAP and OLTP to the non tech gigs who are more the business process experts.

OLAP – online analytical processing – is used to process data in a summary  form. We could say it is sales for a product group for the month of Mar. It allows comparisons of data across periods as well as allows real time calculations to see the data in the hierarchy – on product lines, regions or financial accounts.

OLTP – Online transaction processing – is used to capturing data to record transactions. In this usually the data is represented transaction by transaction. In the above example, the values by each invoice for the month of mar are normally processed by an OLTP system, whereas the reporting of the summary happens in a olap system.

In the Evolution of Planning processes and technologies, the advent of olap technologies in late 90s has been very favorable to planning.

Planning is an assumption based activity and very calculation centric. It needs both a summary level view as well as detailed level view and writeback.

Some considerations for considering an OLTP based approach:

  • Data model can be created very flexibly, data and representation to focus on specific problem
  • We all like undo commands, building undo (rollback) is easier in OLTP. May be expensive to keep transactions unlocked.  Would support versioning effectively by rolling back to previous versions
  • Reporting is easier with OLTP data
  • Changing the model (adding members, dimensions, fields, etc.) is very fast.

Challenges with OLTP approach:

  • OLTP is very rigid, it hard to  handle new planning/analysis requirements, if you make changes to schema, the data is lost
  • No user customizable calculation engine is built in.  This can be solved by using complex queries, or by writing stored procedures.  However, queries tend to be slower on complicated data sets, and stored procedures tend to be database specific

Some considerations for considering an OLAP based approach:

  • Users can slice and dice the data along any dimension, even if it was not envisioned when the model was designed.
  • With applications on top of the OLAP cube, such as UI you can allow the admin to create a reasonably rich and domain specific UI that combines spreadsheets, images, text fields, drop downs, etc.  This is valuable in a planning context

Challenges with OLAP approach:

  • OLAP saves all the data in a cube, there is only one copy of the data, and therefore cannot be denormalized to provide a better view.
  • OLAP typically only supports the storage of numbers.  This is a big challenge for date/text information to be handled in the planning process
  • OLAP engines have difficulty supporting hundreds of small cubes and linking them together efficiently.  If they could, powerfully OLTP like features are possible.  For example, you could make a separate cube per look up table, and then access the look up table in a calc script. 

So based on some of the analysis, we can conclude that for planning you actually need a technology framework that should have an element of olap and oltp. A Relational OLAP engine is a suitable alternative to handle both.  SAP Business Objects Planning  (BPC) is now built on NW BW, which is a relational olap engine and would be good alternative to address the pros and cons of both the above options and good alternative to address some of the pain points that is required especially in an operational planning environment.

Assigned tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Witalij Rudnicki
      Witalij Rudnicki
      I am not sure how is it displayed on your screen, but as I look at this blog it has lots and lots of html tags. Is there any chance to fix it?

      Thank you!

      Author's profile photo Former Member
      Former Member
      Blog Post Author
      Thanks for pointing that out Vitaliy, have updated it, hope this is fine now
      Author's profile photo Former Member
      Former Member
      Very informative blog outlining OLTP vs OLAP.
      I need a some clarification though. I did not quite understand the challenge of OLAP - "OLAP  has challanges merging data from several small cubes". Multiproviders are widely used and can be used to merge data from various sources having common characteristics and granularity.


      Author's profile photo Former Member
      Former Member
      Blog Post Author

      The comparison was not necessarily with respect to SAP BW. In general, the molap engines are not good at merging data from several small cubes e.g essbase or msas. So these challenges can be addressed by a ROLAP. Probably will be good if we can bring out the differences between MOLAP and ROLAP. Any one can comment on that, if not, will try to highlight that when i find some time

      Yes BW has functionality such as multiproviders that can address the specific challenge.

      Author's profile photo Former Member
      Former Member

      hi muthu, can you explain further why do you think "Reporting is easier with OLTP data", because the way I see it is reporting is one of the most compelling reasons for using OLAP

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Hi Ivan,

      it was a blog that i wrote long ago and based on conversation with one of my architect colleagues, before SAP HANA.

      Reporting - transactional reporting is more suited in a OLTP system, as you start to create reports out of the transactional data. E.g. invoice listing, inventory listing, AR reports etc. You necessarily don't have to build a OLAP cube for this but just query and produce this reports with totals etc.

      OLAP is more suited for more dynamic analysis/analytics, where people what to do slide and dice of data dynamically

      Now with SAP HANA, the idea is to eliminate the difference

      Hope this helps