Open Source ERP Academy

Business Intelligence (BI) Platform

  The purpose of this page is to help you get a Business Intelligence (BI) Platform up and running as quickly as possible. There are two primary components to the solution: (1) the transformation script that turns iDempiere transactional data into BI friendly data, and (2) the BI server. The page walks you through the process of building ad-hoc reports using Jaspersoft BI Server. Please note that this page will not make you an expert with Jasperserver; however, it will get you up and running with iDempiere in a very short period. I divide Business Intelligence into two categories (1) Business Intelligence (BI) Platform and (2)Data Visualization. The big difference between these two options is the data warehouse. When you are implementing a BI platform, you are essentially creating a data warehouse that can include information from many different sources including iDempiere. The data warehouse is essentially a copy of the original transactional data formatted for reporting and integration with data from other sources. When you are implementing data visualization, you are looking at your original data source (not a copy) with a tool designed to make reporting on transactional data easier. This might seem confusing since both categories are technically labeled as BI, and both options include data visualization. However, the cost to create, maintain and derive meaningful insights from these categories is considerably different. Tableau (data visualization) is considered simple and easy to use. Jasper Server (BI Platform) requires much more work to build the metadata layer for spanning data sources. The only completely open source BI tool I have found is SpagoBI. Here is a discussion thread that will give you more details about SpagoBI.

BI Introduction

Ad Hoc Reporting Discussion

Ad Hoc Reporting Demonstration

BI Infrastructure

BI Installation - Data Warehouse Script Execution

BI Script - More Details

BI  Installation - Server Execution

iDempiere Data Model

Please be aware that the above data warehouse script does not include all iDempiere tables. I am happy to extend the model. Please do the following if you would like me to add areas not currently included in the data warehouse.
  1. Send me an email that you plan to attend an open discussion to review new tables for the data warehouse
  2. Plan to give me one week to execute the changes and incorporate them into the Jasper schema

Recommended Data Warehouse Script usage

Here is my recommendation for using the data warehouse script on your server:
  1. Clone the script repository as demonstrated above.
  2. Make changes inside the script as needed for your business.
  3. Commit all your changes and push them to your private repository. This includes tracking changes to your schema (schema export/import).
  4. If you would like to share your changes (i.e. add new tables to the data warehouse and schema), you have two options. (A) create a patch with your changes, or (B) share your repository with me, and I can extract the changes for you.

More Resources

Other BI Server Options

I spent most of my BI research time in to products (1) Microstrategy and (2) Jasper. Microstrategy is strong because of its feature set. Cons include licensing price and hosting prices. The AWS cost for the license is about $1 per hour. The server to run Microstrategy is at lease $1 per hour.  This adds up to an expensive solution. Jasper is inferior in terms of comparing the feature set to Microstrategy; however, it seems to be good enough for most people's needs. Jasper also does a great job of supporting the open source community in various ways. I did not spend any time with Pentaho's BI solution. A sub-set of BI is data visualization. Tableau is a leading provider in this area.

Group Discussions

The remaining videos are group discussions about this topic. Please be aware they can be long.

2015-05-28 Business Intelligence BI Introduction