What You Should Know About Open Source Business Intelligence


jgunderson's picture

Open Source Business Intelligence Technology is growing rapidly - see Open Source Technology Heats Up.  Open Source Business Intelligence (OSBI) offers a number of advantages over proprietary business intelligence including cost-effectiveness, high innovation and ease of integration with existing systems.  And of course, more freedom from licensing restrictions and vendor lock-in.

OSBI can provide an excellent infrastructure for building a solutions.  But although it is maturing rapidly, OSBI tends to be more complex than proprietary BI, requiring higher levels of technical expertise.

The best approach is to use a well-architected solution already developed with OSBI (see http://www.erpbisolutions.com )  Or at least, use the existing solution as a starting point.  You can also team up with a services partner who has expertise in developing solutions.

But if you develop the solution yourself, what skills will you need?  That depends on the scope of your solution.  Business intelligence solutions range from simple reporting to enterprise data warehousing and sophisticated analytics.  The following list assumes a data warehouse with OLAP support and Web-based reports and dashboards.

  1. Getting the Data.  Extracting your opertional data typically means navigating a relational database.  Good SQL skills will save you a great deal of time.
  2. Transforming and Loading the Data.  Data is often transformed for faster analysis and to enable OLAP.  Good knowledge of Extract, Transform and Load (ETL) tools is essential.  Especially when you need to develop processes that will scale to large databases.  ETL tools will probably not handle every transformation you need so you might need to extend them with scripts.
  3. Data Warehousing.  Designing and tuning a data warehouse that scales with increasing data and users requires good data warehousing and relational database skills.
  4. Analysis (OLAP Cube).  Designing cubes and analysis queries requires knowledge of the Multidimensional Expression (MDX) query language.
  5. Web Presentation.  Development of Web-based reports and dashboards typically requires skills in html, cascading style sheets and scripting.

Companies such as Actuate, Jaspersoft and Pentaho offer free community editions as well as commercial products.  The commercial products build on the community version and provide more end-user tools for creating reports and dashboards.  The commercial products also provide more comprehensive documentation.  This can reduce the time needed to develop a solution, but not necessarily the skills required.

Using commercial editions of OSBI can also help with support.  Or you can team up with a services partner who has expertise in the technologies.  You can also get help from the open source communities.  But you need to research the strength and helpfulness of the communities.  And note that within an OSBI technology stack, the communities for some components may be stronger and more active than communities for other components.  If in doubt, post a question in the community forum and see how much help you receive.

fblauer's picture
Joined: 01/11/2007
What's all this stuff about big data?


I am hearing a lot about this stuff in the media. Does it have any relevance to ERP?

PS: thanks for your ERPBI model. It looks really nice.

jgunderson's picture
Joined: 05/11/2011
Big Data & ERP

Big Data (and No SQL) engines are good at handling large quantities of data and easily scaling to larger sizes and workloads. Also they can handle unstructured or semi-structured data. The typical applications are search engines, social media and scientific databases.

But Big Data solutions could also become useful for data warehousing. Data warehousing databases can become very large as they build historical data. So this could be interesting for ERP data warehouses. Big Data solutions can utiltize commodity hardware and would be much more cost effective than traditional large data warehouse solutions.

Hadoop is very popular http://hadoop.apache.org/ . It has a SQL front end so it will work with Pentaho. Its on my list of things to try (but it requires a minumum of 8 gig RAM !)

biosecure's picture
Joined: 09/01/2009
Cant Log In


I followed the instructions in the package and I manage to log in but the page just stalls on processing.Any advice??

jgunderson's picture
Joined: 05/11/2011
Can't Log In

Usually the problem is getting the connections defined to the data mart database or the xTuple database. I am working on a new release with an embedded data mart so you can try it without having to load the data. It should be available this week if you want to try it.

To help with your problem I would need to your ETL log and your Tomcat log. You can send them to jgunderson@erpbisolutions.com. If you need help on how to find these, send me an e-mail.