+1-757-461-3022 x124

What You Should Know About Open Source Business Intelligence

Wednesday, February 1, 2012

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.

Jeff Gunderson

Software Development at xTuple, 2013 – 2014
Jeff Gunderson is the founder of ERP BI Solutions provides business intelligence solutions for popular open source ERP systems including PostBooks® and xTuple ERP. Solutions are designed using data warehousing best practices and are built on best-of-breed open source BI technology giving you cost effective, innovative business intelligence.