SpagoBI Installation

The new SpagoBI (version 3.6.0) – Installation instructions and Troubleshooting (link)

SpagoBI released their new version i.e. 3.6.0 with more enhancements and bug fixes on Oct 22 last month. This tool is becoming more popular worldwide now, and user will get the new version more attractive and stronger from the last version. You can get the release notes at http://forge.ow2.org/project/shownotes.php?release_id=5019 , which will help you to know the changes and enhancements done in this version.
Easy way to install from windows installer

1. Download “InstallerSpagoBI-3.6_06112012.zip” from http://forge.ow2.org/project/showfiles.php?group_id=204.
2. Extract the zip file in your preferred location and run the setup file i.e. SpagoBI.exe.
3. Follow the setup instructions to complete the installations.
4. Login into the server at http://localhost:8080/SpagoBI/ with biadmin / biadmin and play with the examples. This installation will work with HSQL and it has examples for different reporting engines.

Notes: After you install SpagoBI with sample data, you can configure it to work with external database. Please find mysql configuration steps in sections below.
Manual installation on Windows/Linux from All-In-One package:

1. Download “All-In-One-SpagoBI-3.6.0_22102012.zip” from http://forge.ow2.org/project/showfiles.php?group_id=204.
2. Extract the zip file in your preferred location and run the startup fileWindows: run the batch file i.e. /bin/SpagoBIStartup.bat (You should have admin permission or, run the cmd through administrator)Linux: run ./SpagoBIStartup.sh from inside /bin folder (Run it from sudo command or, root account)
3. Login into the server at http://localhost:8080/SpagoBI/ with biadmin/biadmin and play with the examples.

Notes: After you install SpagoBI with sample data, you can configure it to work with external database. Please find mysql configuration steps in sections below.
Configure external database MySQL:

1. Install SpagoBI Metadata DB (MySQL)
   1. Install MySQL 5.1 or, later.
   2. Download “mysql-dbscript-3.6.0 _22102012.zip” from http://forge.ow2.org/project/showfiles.php?group_id=204.
   3. Create a mysql database named “spagobi” and import the schema from .sql file downloaded above with following command:
      mysql –u –p  spagobi < MySQL_create.sql
      mysql –u c –p  spagobi < MySQL_create_quartz_schema.sql
2. Configure data sources

• Open /conf/server.xml and add a new Data Source, fragment of XML under tags,

e.g.
This is the Connection poll for SpagoBI metadata.

• We can add more data sources for each data warehouse we have.

e.g.

• Comment all previous data sources which were present for HSQL.
• Make sure the context information for data sources defined above are embedded in /webapps/SpagoBI\META-INF /context.xml. You can insert this xml in server.xml if you prefer.

i.e.

3. Configure DBMS Type

• We must configure DBMS type used by SpagoBI under \webapps\SpagoBI\WEB-INF\classes\hibernate.cfg.xml

                          org.hibernate.dialect.MySQLDialect
                           <!–
                           org.hibernate.dialect.SQLServerDialect
                           org.hibernate.dialect.PostgreSQLDialect
                           org.hibernate.dialect.Oracle9Dialect
                           org.hibernate.dialect.IngresDialect
                           org.hibernate.dialect.HSQLDialect
                           org.hibernate.dialect.DB2400Dialect       
                            –>

• Then we would need to configure hibernate dialect for JBPM workflow engine: look at file /webapps/SpagoBI/WEB-INF/classes/jbpm.cfg.xml, there is a reference to JBPM hibernate file:

May be you read jbpm.hibernate.cfg.hsql.xml instead of jbpm.hibernate.cfg.xml,           jbpm.hibernate.cfg.hsql.xml is the hibernate file suitable for HSQLDB; if you are not using HSQLDB change resource.hibernate.cfg.xml property to jbpm.hibernate.cfg.xml.

• Then, edit /webapps/SpagoBI/WEB-INF/classes/jbpm.hibernate.cfg.xml and change the following as according to your database server.

org.hibernate.dialect.MySQLDialect

• At last you have to configure Quartz scheduler engine: edit /webapps/SpagoBI/WEB-INF/classes/quartz.properties you will read something like:

# job store delegate class -

# Hsqldb delegate class

org.quartz.jobStore.driverDelegateClass=org.quartz.impl.jdbcjobstore.HSQLDBDelegate

# Mysql/Ingres delegate class

#org.quartz.jobStore.driverDelegateClass=org.quartz.impl.jdbcjobstore.StdJDBCDelegate

# Postgres delegate class

#org.quartz.jobStore.driverDelegateClass=org.quartz.impl.jdbcjobstore.PostgreSQLDelegate

# Oracle delegate class

#org.quartz.jobStore.driverDelegateClass=org.quartz.impl.jdbcjobstore.oracle.OracleDelegate

#-

Uncomment the job store delegate class suitable to your database server and comment all others.

4. Database schema configuration

When creating SpagoBI metadata tables, they should be put into the default schema of the JDBC connection’s user.  In case this is not possible, you should edit hibernate.cfg.xml adding the following line:

– modify SpagoBI/WEB-INF/classes/jbpm.hibernate.cfg.xml adding the following line:

                Notes: you can skip this step.

5. Restart the SpagoBI server, login into the server at http://localhost:8080/SpagoBI/ with biadmin / biadmin. This installation will work with MySQL , meta data.

Upgrade from older version to new version (Simple Upgrade):

1. Stop the SpagoBI Server.
2. Remove or, move (for backup purpose) your current SpagoBI installation folder.
3. Install the latest version you want to install, through steps in section “Easy way to install from windows installer” or, “Manual installation on Windows/Linux from All-In-One package”.
4. Download the upgrade script for MySQL from http://forge.ow2.org/project/showfiles.php?group_id=204 .
   Notes: Make sure you have downloaded the required version of script.
5. Run the db script downloaded above and the upgrade is done.
6. Start the SpagoBI server, login into the server at http://localhost:8080/SpagoBI/ with biadmin / biadmin. This installation will work with MySQL  upgrade version meta data.

Notes: This upgrade works well in case you have not done any personalization changes in installation files of older version.
Troubleshoot/Help for the issue I came across during installation: 

1. Very first error we got was “Address already in use” as the shutdown port 8005 was already being used on the system. Changed the port to 7005 in server.xml (located under conf folder) and it worked.
2. We came across another issue where everything were perfect but we were not able to see the login screen and it was showing an error as follows:

[main] 28 Dec 2011 09:13:46,072 ERROR it.eng.spagobi.commons.SingletonConfigCache.:36 – The table sbi_config is EMPTY
[main] 28 Dec 2011 09:13:46,074 ERROR it.eng.spagobi.commons.SingletonConfigCache.get:53 – The property ‘SPAGOBI.DATE-FORMAT-SERVER.format’ doens’t have any value assigned, check SBI_CONFIG table
[main] 28 Dec 2011 09:13:46,074 ERROR it.eng.spagobi.commons.SingletonConfigCache.get:53 – The property ‘SPAGOBI.TIMESTAMP-FORMAT.format’ doens’t have any value assigned, check SBI_CONFIG table
[main] 28 Dec 2011 09:13:48,272 ERROR it.eng.spagobi.commons.initializers.metadata.MetadataInitializer.init:
163 – Error while initializing metadata
We explored the forums and different channels and came to know that this is a bug logged into Jira and they have uploaded a patch to be downloaded at http://www.spagoworld.org/jira/browse/SPAGOBI-712.
We downloaded the patch and extracted it into ……./webapps/SpagoBI/WEB-INF/classes, the server is working fine now.

3. While working with linux, once unzipped the “All-in-one….”  Package and tried to start spagoBI server with the command, sudo sh SpagoBIStartup.sh, I got a access denied error. I used chmod command and gave all permission to the folder as follows:

          chmod 777 –R SpagoBI-DEMO-3.5 and it worked.

This entry was posted in BI and tagged SpagoBI 3.6.0, SpagoBI Installation Instructions, SpagoBI New Version, SpagoBI-Open source BI Suite, Troubleshoot SpagoBI Installation. Bookmark the permalink.

Enabling the server to communicate via TCP/IP

Most of the Linux distributions do not enable by default to accept TCP/IP connections from outside in their MySQL or Percona Server packages.
You can check it with netstat on a shell:

$ netstat -lnp | grep mysql
tcp         0        0 0.0.0.0:3306 0.0.0.0:* LISTEN 2480/mysqld
unix 2 [ ACC ] STREAM LISTENING 8101 2480/mysqld /tmp/mysql.sock

You should check two things:

• there is a line starting with tcp (the server is indeed accepting TCP connections) and
• the first address (0.0.0.0:3306 in this example) is different than 127.0.0.1:3306 (the bind address is not localhost’s).

In the first case, the first place to look is the my.cnf file. If you find the option skip-networking, comment it out or just delete it. Also check that if the variable bind_address is set, then it shouldn’t be set to localhost’s but to the host’s IP. Then restart the MySQL server and check it again with netstat. If the changes you did had no effect, then you should look at your distribution’s startup scripts (like rc.mysqld). You should comment out flags like --skip-networking and/or change the bind-address.
After you get the server listening to remote TCP connections properly, the last thing to do is checking that the port (3306 by default) is indeed open. Check your firewall configurations (iptables -L) and that you are allowing remote hosts on that port (in /etc/hosts.allow).
And we’re done! We have a MySQL server running which is able to communicate with the world through TCP/IP.


vi /usr/local/sbin/firewall.sh
/usr/local/sbin/firewall.sh stop
/usr/local/sbin/firewall.sh start

Firefox command-line arguments to set and control start page

Jasonwryan gave you what is probably the best solution. If you reall want to change the default home page (not simply start with a specific page, but change what page the Home button loads), you will need to edit your user's config file.
For firefox, the preferences are stored in

$HOME/.mozilla/firefox/RANDOM_STRING.default/prefs.js

The RANDOM_STRING is different for each user profile. The default home page is saved as:

user_pref("browser.startup.homepage", "http://foobar.com");

So, to change the homepage to foo.com from the command line you could do:

sed -i.bak '
 s#\(\"browser\.startup\.homepage\", \"\)http:.*"#\1http://foo.com\"#
' $HOME/.mozilla/firefox/*.default/prefs.js