Implementing Business Intelligence, the Open Source Way

Sufyan bin Uzayr, Freelance Writer, Graphic Artist, Photographer, www.sufyan.co.nr

Most SMEs run multiple business apps, with little or no synchronization between them. The situation works fine as long as you don't need to pull out a consolidated report of the data being churned out from these apps. But if you intend to make strategic business decisions, you will need to interpret and make sense of this data. This is where Business Intelligence, or BI, comes into play!

Understanding BI

In simple terms, BI comprises of techniques that can be used to extract, identify, locate and interpret business data. Such data may include details of sales and revenue, cost-expenditure logs, etc. While larger organizations tend to employ BI for data or text mining as well as benchmarking, SMEs generally use Business Intelligence for analytic and predictive purposes.

This is how Wikipedia defines BI: “BI uses technologies, processes, and applications to analyze mostly internal, structured data and business processes while competitive intelligence gathers, analyzes and disseminates information with a topical focus on company competitors.”

The major purpose of implementing a BI mechanism is to help SMEs assess their growth and progress as well as make decisions for future plans.

With the introduction out of the way, let us turn to implementation of BI to suit our needs. The two most popular suites for BI implementation are Pentaho and Spago. The process is the same with either of them, excluding minor variations.

Pentaho: Pentaho is a cost-efficient open source BI implementation program that lets you build highly versatile BI mechanisms. It is well known for its mobility features. While Pentaho is the industry leader when it comes to open source BI, its vast plethora of services may seem unused from an SME's point of view. (www.pentaho.com)

SpagoBI: This is an complete Business Intelligence Suite that comes pre-loaded with numerous components and modules. SpagoBI too, just like Pentaho, is open source and seems ideal for SMEs. (www.spagoworld.org)

Implementing BI using SpagoBI

Before going further, you will need to download the SpagoBI software from www.spagoworld.org/xwiki/bin/ view/SpagoWorld/Download. SpagoBI can run on any operating system that supports JVM 1.5 (all major operating systems such as Windows XP Server, Ubuntu 9.10 and above, RHEL 4+ and Windows 7 support JVM 1.5). In order to store the metadata and other configuration related information, you will need any one of these DBMSes:

MySQL 5.x

Oracle 9.0+

PostGRE SQL 8.2+

IngreSQL 9.2/9.3

hSQL

Lastly, you may need an application server such as Tomcat 6.0.18, WebSphere 7 or jBoss 4.0.3.

In this article, I've implemented it under Crunch Bang Linux (a derivative of the Ubuntu and Linux Mint family) using Chromium web browser. Spago supports almost all major web browsers, so that will not be an issue.

Installating SpagoBI

In order to install SpagoBI, follow these steps:

1. You will need to install JDK on your computer, if it isn't already installed.

2. Next, download Tomcat from http://tomcat. apache.org/download-60.cgi Once done, unzip apache-tomcat-6.0.18.zip and install it.

3. You will now need to install the SpagoBI metadata DB. The procedure varies for each DBMS used, for instance, if you are using MySQL, you will need to execute the following:

MySQL_create.sql

MySQL_create_fk.sql

MySQL_create_quartz_schema.sql

4. After this, you need to set up the data source and environment variables. In order to set up data sources, you can insert this XML code under :

name="jdbc/spagobi" password="root" type="javax.sql.DataSource" url="jdbc:mysql://localhost/spagobi" username="root"/>

name="jdbc/something" password="root" type="javax.sql.DataSource" url="jdbc:mysql://localhost/something" username="root"/>

This will set up the connection pool for the SpagoBI metadata and for the sample term 'something'.

Now, to set up environment variables, insert this code under :

Obviously, you will need to insert the resource link in each context:

There are numerous environment variables available, such as resource path, spagobi-sso-class, spagobi-service-url, spagobi-host-url, etc. You can explore them in depth using the documentation or the SpagoBI Wiki pages.

5. SpagoBI comes with several libraries that can be placed in the /lib folder. If you wish to add further functionality to your installation using such libraries, you can get the entire library repo by downloading the All-In-One-SpagoBI-3.x.x-apache-tomcat-6.0.18-xxxx.zip file from the SpagoBI download page.

You can now run and test your installation of SpagoBI. The default admin user credentials are biadmin (username) and biadmin (password). There are four other users available too: bitest, bimodel, bidev and biuser (the passwords are same as the usernames).

The above installation details pertain to SpagoBI 3.x If, for some reason, you choose to run SpagoBI 2.x you will need to explicitly run the external engines before defining the context. Rest of the installation procedure is identical as above.

Installing as a portal app

You can also install SpagoBI as a simple web application or in a portal environment by editing the SPAGOBI.SPAGOBI-MODE property under Tools-->Manage Configurations.

SPAGOBI.SPAGOBI-MODE.mode="WEB or "PORTLET"

Following that, you need to edit the /webapp/SpagoBI/WEB-INF/conf/config/ initializer.xml file as under:

The interface

SpagoBI supports different profiles for users, administrators and developers. Some of the noteworthy components of the interface are:

Publication Workflow: This manages creation, test and validation cycle for BI documents such as reports, graphs, etc. The major components used for reports include JasperReport and BERT while those for analysis include JPivot and JPalo.

Data integration: Since 2009, SpagoBI has been using the SpagoBI Studio, that integrates Eclipse IDE to allow development of reports and configuration of various engines. Along similar lines, SpagoBI Meta is also evolving, which shall act as a semantic layer for reporting engines.

//screenshot -- spago1.jpg

The mode of operation of the general SpagoBI interface is extremely simple and is backed by extensive documentation and help files. Let us now take a look at some of the advanced operations:

Tweaking SpagoBI

Configuring the engine: In Spago 3, by default, all engines are configured. But if you wish to do so manually, you can use the Engine Management option present under Resources. There are two types of engines: Internal, which are installed in /SpagoBI context, and External, which are installed in a specific context. In case of internal engines, you simply need to specify the implementation class.

//screenshot -- spago-engines.png

For each type of engine, you can set multiple options such as label, name, description, document type, engine type, data source and data set.

You must note, however, that if you are using engines such as Chart, GEO, DashBoard, KPI, BIRT and JasperReport, you will have to check the Data Set. Similarly, if you are using engines such as BIRT, JasperReport, JPivot and JPalo, you will have to check the Data Source.

Configuring Logging: SpagoBI uses a J2EE Application Framework that can be changed in /webapps/SpagoBI/WEB-INF/conf/config/tracing. xml For instance, if you wish to debug, you can set debug to 'true' in the following code:

Configuring the mail server: Mail server details are the same as those on your enterprise's email settings. Under Tools-->Manage Configurations, you can edit the scheduler, kpi_alarm and user profiles.

Configuring the DBMS and database schemas: If you wish to configure the DBMS settings, the file to alter is hibernate.cfg.xml :

org.hibernate.dialect.MySQLDialect

In most cases, you just need to edit the above property clause, according to you database server. For example, if you wish to add a new schema, simply use this code:

(where name of the schema is the database schema's name).

//screenshot -- spago-config.jpg

Configuring themes: Themes can only be changed in SpagoBI version 2.1 and higher. Also, the option to change themes is available only if more than one theme is installed. The process to select and activate a theme is fairly straight forward. If, however, you wish to install a theme using the XML code, simply implement this snippet in the spagobi.xml file:

where 'theme name' is the name of the theme.

Conclusion

Implementing a BI solution can be a challenging task. While the interface and usage methods are straight

forward in nature and do not demand great level of

expertise, the installation and configuration is an equally daunting endeavor. While SpagoBI seems to suffice for most SME usages, you may also consider taking a look at Pentaho — an equally efficient BI implementation solution. In either case, BI implementation, if done well, can save your enterprise a lot of efforts and resources in the long run!