Difference between BI and ERP reports:
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:
- Very Complicated,
- Very Slow
- 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 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.
Part II: Difference Between BI & ERP Report
Welcome to the part II of Difference between BI and ERP reports.. Before proceeding further, let me tell you that the target audience for this blog post is Business User. However, even if you are an IT professional, you are welcome to read it and give your comments (though you will find it a bit simple but that is because it is intended for Business users). I got a pretty encouraging response to my first post on this topic. The very first response I got after posting this thread's link in my LINKED IN profile was “what is the difference between BI and DW (read. Data warehouse)”.
In ordinary words “BI is the front end of your solution and DWH is backend”.
BI solution can only be based on the data warehouse. A data warehouse has a different data structure than a normalized entity relationship structure used in ERP applications. Some sophisticated tools such as Oracle Business Intelligence Enterprise Edition (OBIEE) provide you ability to create a virtual dimensional data model layer on top of a normalized physical database but doing that for enterprise reporting would have a severe hit on performance of the system.
Before continuing the discussion on BI vs ERP reports, first let me explain some of the concepts used in business intelligence, so you will be able to understand me in later sections.
What are Dimensions and measures?
In simple words, all the facts and figures are measures. Dimensions are the elements against which we see certain measures. Normally ERP and other OLTP systems generate two dimensional reports e.g. sales of certain products based on time. Here, product is a dimension. Time is also a dimension and sales amount is the measure. At the minimum, any report can contain two dimensions and one measure. BI reports make it possible to make multidimensional reports. You can refer to the report2 I displayed in my previous blog post. You can see that it contains three dimensions, i.e. Time, Product, location and there is one measure which is sales amount. You can even add several new dimensions in this report.
What are Key Performance Indicators (KPIs)
KPIs are measures or metrics that help organizations to evaluate the progress towards organizational goals and objectives. KPIs can or can not be quantifiable in absolute numbers. Examples of some KPIs are:
- Market Share
- Market Growth
- Customer Churn Rate
- Customer Profitability
- Advertising to sales ratio
- Inventory Turnover
- Net Sales to Net worth
- Asset Turnover
There can be hundards of KPIs for any particular business sector.
What is Single Version of Truth?
Realizing the limitations of ERP reporting, middle managers/analysts mostly use spreadsheets (mostly excel) to present information / analysis to business executives or to support their day to day functions. Usually these spreadsheets are shared with collegues and management as email attachments. Sometimes converted into PDF or word formats as well. These spreadsheets and documents are called “Unstructured data sources”. There are certain inherent limitation with this approach, one obviously being difficulty to apply version controlling if data gets updated. Analysts have to prepare a new spreadsheet every time by seeking help of IT to pull data from source systems (e.g. ERP, legacy applications) and apply aggregations and other statistical/mathematical functions manually. As a result sometimes, values for certain KPIs may be different in different spreadsheets prepared by different analysts. On the otherhand, having a BI solution for analytical reports, which gets all the data from a central data warehouse, eliminates the chances of multiple values for same KPI. This is why it is referred as Single Version of Truth.
Adhoc reporting
It is basically the same thing for which analysts use spreadsheets. Means those reports which are not present in your ERP or legacy applications. A Business intelligence solution offers the flexibility and powers of custom reports development to business analysts even if they don’t know about the structure of underlying data elements. The view of report writing tool that they see contains data elements in business terms like Sales, Profit, Year, Month etc. With drag and drop functionalities, they can quickly create reporting different measures against different dimensions
Slicing & Dicing
The concept of Slicing is much like filtering the data. This is used to view only that data which is necessary for our current analysis For Example suppose we are viewing a Quarter-on-Quarter revenue comparison report. Now we need to see the 3rd quarter revenues in detail so we slice the report to display only third quarter’s report. It depends on the system or user wether it is displaying an aggregate revenue report for full quarter or displaying it in Month-on-Month comparison. By the way, in accounting terms, these comparisons are called horizontal analysis.
Dicing stands for transposing the X and Y dimensions. Like pivoting or cross tabbing
Dashboards
There is a term in BI & DWH which is called Visualization. Visualiztion means representation of information in meaningful way like charts, graphs, tables etc. A dashboard is a place where you put all the visual representation of your information. Using charts, tables, gauges etc, you can display information (e.g. your KPIs) in a very eye-catching and to-the-point way. Here are some of the screenshots from some dashboards.
Figure 1: A Business Monitoring Help Desk System Dashboard
Figure 2: A Banking system Dashboard
Figure 3: A sample Airline executive Dashboard
Drill Down and Drill Across
Drill down means viewing information at a more detailed level for same dimension. For example, when you are viewing an annual report (time dimension), you can drill it down on time dimension for half year, quarter or month.
Drill across refers to drilling from one dimension to another on same hierarchal level.
Conclusion
In this topic, I have tried to present a business case that why business intelligence is necessary for organizations. It is not leisure. It is a must. People argue that it is expensive. Sounds familiar? Didn’t you hear it when ERPs came into market?
In my next post, i will explain about how this solution works. It will get a bit more technical but intended audience would be again business users. Feel free to send in your opinion or questions in the comments. After all i am writing all this to communicate with people like you out there.
Thank you,
Santosh
