Wednesday, February 25, 2009

Part II: Difference Between BI & ERP Report

Welcome to the part II of Difference between BI and ERP reports. You can read the Part I of this post here. 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.

Some of the concepts which I promised to explain in my earliar post are explained one by one here:

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

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.

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.


  1. Well written, thank you!!
    BI does go further than ERP systems in crystallising data. Dashboards were first developed in France as the tableau du bord (hence the name dashboard as it is the equialant in english). This developed further into the anglo saxon dashboard in a way throughthe balanced scorecard ideas developed in 1992. For me, a good BI system should be closey linked to corporate strategy, focussing on thoses metrics that measure achievemnet of strategy. A surveillance measure to accurately measure, in a concise format, thos important metrics. Hopefully a good ERP system will contain most if not all of the metrics, but it is a good B system that summarizes and presents in a clear fashion. Smaller sompanoies do not necessarily nee expensivesystems. Rudimentary but valuable systems can be written in Excel or Access or similar achieving high payback for a small, limite resources company. Since most companies are small and medium sized enterprises, it is an interestingly large market that the large systems companies do not often target.
    Slicing and dicing, drilldowns have ben around for a while, it is the dashboard presentation that when well written to strategy, by its simplicity to evaluate tht can be a strong winner with managers and executives unfamiliar with data mining!!

  2. Thank you Strach!
    This is indeed a valuable addition in this post

  3. Excellent discussion... I hope that you will address some of the challenges of BI metrics. Below, I identified two specific challenges associated with defining the KPIs that have caused rework or slow adoption.

    Many of the KPIs in BI are calculated metrics that have business rules behind them and, as was noted, should be the metrics associated with the company's strategic goals. As such, they should be defined by the business. Also needed are the business rules behind the metrics that define the calculation, define what is included in the metric, define how missing values or bad data are included, define when the metrics are undefined or null, etc. These rules are defined by the organization for the organization and must have a single definition for each metric. If Finance requires a different definition than Sales (example could be 'Revenue' where Sales may look at sold/anticipated revenue while Finance needs revenue received), two different KPIs must be created. This way, anyone in the organization using the reports will have the appropriate measure required to track specific aspects of performance. It is the development of these agreements with the business users that will speed the adoption of the BI reports.

    The second challenging is associated with the drill down 'slice and dice' structure. Specifically, determining when the metric follows the dictum 'the total is the sum of the parts' needs a clear understanding of the business need and KPI. Most KPIs, like percentages, indicies, turnover rates, growth rates, etc. are not additive and require 'aggregation' calculations. Establishing a work group (comprised of BI developers, end users, source data experts and testers) will help to address, reach agreement and document the business rules associated with each KPI for the organization at each level of the 'drill down'. Sufficent time should be allocated to this activity.

  4. Fantastic work! You are doing a fantastic job and I am disappointed that the next installment is not out yet. :( Waiting impatiently...


  5. @Anonymous,

    Thanks for appreciation. Its just that i was busy with some official assignments. Will be publishing a new post within a few days...

  6. hey Aksam Dar,

    thats was a very good blog. waiting for your next blog.

  7. Lwgarmbruster@yahoo.comDecember 31, 2009 at 8:59 PM

    Thanks Aksam, this is very good material.

    I copied an important part from Florence below...

    "Establishing a work group (comprised of BI developers, end users, source data experts and testers) will help to address, reach agreement and document the business rules associated with each KPI for the organization at each level of the 'drill down'. Sufficent time should be allocated to this activity."

    ..In my humble view many organizations still underestimate the massive time required to define measures companywide...Furthermore you also need to have access to people who have the specific knowledge, for instance where to find certain data and how to extract it to be used in KPI's...etc
    I am currently in a situation where I deal with 3 departsments and every of them wants me to define the measure differently...

    Another asspect here is to rank measures/KPI's based on how much business value they might deliver and how hard it's going to be to find the source and extract the data. It may sound unimportant but I have been in situations where we could not get Budget data because knowbody knew where (Database) they were saved...

    Anyway, great post..please continue..

  8. Dear Sir,

    I'm completely new to this industry so request you to take me and my question accordingly.

    My Query after going through your blog is-

    Can ERP (any brand), if I can generalize, always gives two dimensional report only?

  9. thanks
    It is really well written and shows lot of your personal experience...
    I was searching about this matter over internet and found exectly what I wanted through your blog (or article)

    Ankur Agarwal

  10. Hello, all your 2 posts have been very helpful to me. I'm writing my final study on similar topic. I'm not an IT person jet, but hopefully will be soon :-) regards

  11. Hello, just catching up on the discussion - which is of great interest. Have you posted any additional blogs on this topic?

  12. Thanks for the useful info.