Wednesday, February 18, 2009

Difference between BI and ERP reports

Yesterday I had a meeting with a potential client in Abu Dhabi and a question popped up. What is the difference between reports/information obtained from an ERP and that of a BI solution?

I have seen this question on numerous discussion boards, forums and blogs and I felt that there are certain buzz words which are been repeatedly being told to audience without realizing that audience understand it or not. Some common phrases like

  • “ERP is for data input and BI is for data retrieval”.
  • “ERP is an OLTP system and BI is an OLAP System”
  • “You can do more analytical reporting in BI”.
  • “BI provides single version of truth”
  • “ERP reports are for day to day needs only” etc
These phrases still do not clear the concept in the mind of a non-IT business user that "why they need a BI solution when their existing ERP solution is already generating reports for them".

I will try to explain the difference for a non-IT business user giving some examples. Two phrases which I mentioned earlier were:

“ERP is for data input and BI is for data retrieval”…
“ERP is an OLTP system and BI is an OLAP System”…
That’s right. ERP software being an Online Transaction Processing (OLTP) system is used to record/edit transactions as and when these happen. The data architecture is designed in such a way that it provides maximum speed in recording a transaction keeping disk space utilization at a minimum. For those of you know who know about IT, this is the application of normalization in a database environment. BI on the other hand being an Online Analytical Processing (OLAP) system provides you robust access to different reports, dashboards and balanced scorecards. Now lets see how ERP reporting is different from BI reporting using a very simple case scenario.
Alpha Company, Inc is in the business of Food & Beverages. Mike is company’s Sales Director and wants to see the sales report for January 2009. A typical ERP system will generate a report like this:

Now what if Mike wants to analyze the report based on geographic region and compare it with last month’s sales. He doesn’t want to see the products appearing row wise. Instead he requires products to appear column wise and show the sales amount of respective geographic region in rows. Developer Buddies!! Remember cross tab / pivot table reports ??? But don’t think Mike’s expectations are going to end here. He also wants to compare these figures with previous months figures in the same report. Wait here. Still don’t think its end of the demands. Mike also wants to show the dropped sales figure in different color. So it needs to be like this

A typical ERP system doesn’t have enough capability to support this. Although there are cross tab / pivot table reports available in latest tools like ORACLE and Microsoft SQL Server 2005 (and above) but they are:
  1. Very Complicated,
  2. Very Slow
  3. Put a lot of burden on processor
So Mike might have to wait 10 minutes or more for the report shown in figure 2 using an OLTP system. But using an OLAP system, it will only take a few seconds. I have explained three concepts in the figure 2 that a BI system is capable of and those are:

  • Time series comparisons (between Jan 09 & Dec 08)
  • Drill Down (e.g. on regions)
  • Ability of OLAP to do cross tab/pivot table format
I will need at least 2 to 3 more posts to further explain/cover rest of the powers of BI solution like slice & Dice concepts, Dashboard Gauges and Scorecards, etc. Also I will explain about the single version of truth phenomenon. But developers!! please don't assume that you can accomplish the required results putting all data (including precalculated data) in one single table. This is a big misconception among developers and you will know it when i will explain the concept of dimensional data modeling in my next blogs i.e.what is the difference between a normalized relational database model (used in ERP) and a dimensional data model (or the data warehouse used for BI). That would be of much interest for those who are already in the field of ERP and want to know more about BI.

Watch out for my next post on this blog. Your comments/thoughts are always welcome.


  1. good entry, great introduction, pretty focused... looking forward to the next episode

  2. I would agree on the fact that it's a common misconception, rather false expectation from an ERP system to server exhaustive enterprise wide reporting needs. ERP is meant to serve the purpose of achieving "integration" amongst your disparate business functions and to establish consistency in terms of data access (called Master Data Management otherwise). ERPs are commercial off-the shelf (COTS) packages that can be configured in accordance to an organization's business needs (whatever sector/industry it maybe). Businesses may invest in ERPs due to several strategic/tactical or technical reasons such as to achieve integration between legacy systems/platforms, increase operational efficiency, reduce redundant workflows or even to conform to industry wide standardizations (ISO related or Sarbanes Oxley for Financials etc). Therefore, the basic essence of ERP lies in the extent of leverage it provides you to integrate your organization wide processes and to better manage your operational performance.

    BI on the other hand serves a different quadrant, which is called "business performance management" and is driven by things like Key Performance Indicators (KPIs). The purpose is then again to consolidate data from various sources and arrange it in a format that would give you greater analytical and reporting capability and combined with Data Mining to determine future trends as well. The conventional relational database design falls short here and we start talking in terms of dimensional constructs (or perspectives from which you can analyze your data). The offering here is not operational efficiency and reduced overhead, rather complex analysis and reporting. The blog justifies the same point by developing a contrast between two versions of reports (ERP and BI based).

    Just to conclude, we should understand the purpose behind both the systems and draw a line between their individual strengths and weaknesses.

    Carry on the good work!

    Cheers - Omar.

  3. Hi Askam Dar,
    This is one of the best concise description I have seen.
    I'll use it for reference if this question appears again.


  4. @Kashir, Omar & Paul,

    Thank you to all of you. Your feedback is very much encouraging for me. Infact that is one source of motivation to write more. Just within 2,3 days, i will put another post going into further details of this topic.

  5. I have published the part II of this post. You can read it here

  6. Seen a couple of problems with BI solutions in ERP implementations. Do you have any solutions to these?

    1. Building the Cubes in OLAP can be very resource intensive, often having to be completed overnight and requiring additional hardware.

    2. The cubes are only as good as the dimensions and measures created for it and MDX (the language used to write these) appears to be a skill that is hard to find.

    3. BI gets pushed to "Phase 2" and subsequently sometimes never gets completed as higher priority tasks such as invoicing and general ledger reporting takes up the time / budget.

    Nice post, keep up the good work!

  7. Hi
    Really good stuff - we work with number of Oracle/Hperion clients - think the big connect will be BI and Fin Services in area of risk eg rabellous
    One of companies we are working has been in this sector for years - started with Siebel Analytics now into MS, SAP, Hyperion, Oracle etc.. if cna position yourself for big take up (which is already starting to happen with BI in Europe ) then will be in powerful position. There are masses of companies especially in new joiners to EU who will be possibly jumping staight to BI platforms - funded partly by EU
    We also look after companies in aerospace, security and fin services who keen to work in UAE but need support

  8. Hi

    Very useful and informative article in very simple language. Keep up the good work :-)

  9. Pradnya DeshmukhJune 11, 2009 at 1:44 PM

    Hello Aksam Dar,

    I have just satarted my carrer with BI,& i was confused to the core understanding the difference, i wouldnt articulte the difference between these two. You made life simple, thanks a ton.waiting for more blogs on this topic.

    Pradnya Deshmukh

  10. i have foudn a great link to pass it to my friend,we were arguing about this yesterday.He opined ERP and BI are same,i have this link to make him understand its not what he thinks.

  11. Kudos!!

    What a beautiful explaination!!I was discussing the same with my colleague today and here, I got the answer and clarity as well.

    Thanks and keep it gng!! :)

  12. Great , Iam very new to the BI field may be termed as layman , you explained beautifully about the difference but how should i start in BI , i dont want to be a developer but want to understand the in and out of BI and it tools in next few years ...