Develop multidimensional analysis reports using Jpalo engine in SpagoBI

Introduction

SpagoBI allows multidimensional analysis through OLAP engines, i.e. JPalo which are more flexible and user-friendly, compared to structured reports.

The users can monitor data on different detail levels and from different perspectives, through drill-down, drill-across, slice-and-dice, drill-through processes.

Configurations
The Jpalo engine comes configured with latest version of SpagoBI i.e. v3.4, but we would need to go through data source and related configurations. Please follow the step below to configure it properly:

1. Mondrian Configuration
   a. In order to configure Mondrian to use a datasource as a JNDI resource, define resource in server.xml.b. Then configure it in SpagoBIJPaloEngine/META-INF/context.xml.c. Suppose your JNDI datasource resource is named “jdbc/foodmartmondrian”, then you need to configure DataSourceInfo tag in SpagoBIJPaloEngine/WEB-INF/datasources.xml
   to use the correct JNDI resource.

   Provider=mondrian;DataSource=java:comp/env/jdbc/foodmartmondrian;

   Use the following in case of jdbc (mysql) resource
   Provider=mondrian;Jdbc=jdbc:mysql://localhost/?user=root&password=;JdbcDrivers=com.mysql.jdbc.Driver;
   d. Remember that you can even put multiple catalogs and datasources under Mondrian control, by setting them in datasources.xml file.
   e. Further you have to place database schema FoodMart.xml (or the one you have prepared with all cubes defined), under SpagoBI resources folder.
   f. To be accessed by Mondrian server, change Definition tag in SpagoBIJPaloEngine/WEB-INF/datasources.xml.
   file:./../resources/Olap/foodmart.xml 
2. Palo Pivot Configuration
   a. Change settings in SpagoBIJPaloEngine/WEB-INF/classes/deploy_it.properties. By default JPalo uses Hsql db to store its data, but it could eventually use Mysql. You can specify it by setting use.mysql=true otherwise you can set use.mysql=false. By default JPalo uses http protocol to connect to Mondrian. If you want to change protocol to https set is.ssl=true otherwise set is.ssl=false.More customization can be done:
   • jpalo.admin.user=[jpalo_username]
   • jpalo.admin.password=[jpalo_password]
   • jpalo.mondrian.connection.url=[mondrian_host]:[mondrian_port]
   • jpalo.mondrian.connection.service=SpagoBIJPaloEngine/xmla
   • jpalo.mondrian.connection.name=[name_of_mondrian_connection]
   b. Once you have decided what kind of database JPalo is going to use to store views and connections, you need to edit the proper settings.
   Case JPalo uses HSQL DB, change Hsql db settings in SpagoBIJPaloEngine/WEB-INF/classes/sql/hsql/credentials
   • jdbcDriver = org.hsqldb.jdbcDriver
   • jdbcURL = jdbc:hsqldb:file:[absolute_path_to_your_hsql_files]
   • jdbcPort = [port]
   • userName = [username]
   • userPassword = [password]
   Case JPalo uses Mysql DB, change Mysql db settings in SpagoBIJPaloEngineWEB-INFclassessqlmysqlcredentials
   • jdbcDriver =com.mysql.jdbc.Driver
   • userPassword = [password]
   • userName =[username]
   • databaseName =[schema_name]
   • jdbcURL =jdbc:mysql://[database_host]:[port]
3. Please make sure the engine instance is created in SpagoBI server. Go to Resources->Engines Management and define/check SpagoBIJPaloEngine:
• Document type : On-line analytical processing
• Url : [protocol]://[host]:[port]/SpagoBIJPaloEngine/com.tensegrity.wpalo.SpagoBIJPaloEngine/JPaloEngineStartServlet
• Driver : it.eng.spagobi.engines.drivers.jpalo.JPaloDriver

Configuration Source: http://wiki.spagobi.org/xwiki/bin/view/spagobi_server/JPalo

How to create Modrian Schema?This is a  .xml file where you define your olap cubes. The Jpalo engine read it and process it’s information to show the multidimensional reports.

There is an open source tool named “Schema-Workbench” where you can design your multidimensional cubes and save it as .xml file. As per above instruction, we store this file inside resource path of SpagoBI server.

We can download it from http://sourceforge.net/projects/mondrian/files/schema%20workbench/

Other links for schema-workbench and modrian:

http://mondrian.pentaho.com/documentation/schema_workbench.pdf
https://docs.google.com/a/paxcel.net/gview?url=http://jasperserver.sourceforge.net/docs/3-7-0/Mondrian-3.0-Technical-Guide.pdf&chrome=true

You can work on other tools in the market also to develop mondrian schema.

Create Document in SpagoBI and run the report

a. Create new On-line analytical processing Document in spagoBI, using SpagoBIJPaloEngine. Go to “Analytical model->Documents development” and click on the Insert icon from the top left and fill the following information:

Type: on-line analytical processing
Engine: JPalo Engine
State: Development

e.g.

http://paxcel.net/blog/wp-content/uploads/2012/05/document.jpg

b. Save the document, and click on “Template build” button to create the view and execute the document just created.

c. In the next screen, click on the “Create new views” button from the left navigation area,

d. Choose the account, select the mondrian cube and click on Create button.

e. The view is created, now drag and drop the dimensions and save your view.

Once it is saved, it can be excuted directly from “Analytical Model->Documents development” page.

This is a detailed document which will help you to build Jpalo document with SpagoBI, please let me know in case you face any issue so that I can try to help you there. You can reach me through your comments.

This entry was posted in BI, Paxcel Labs and tagged Jpalo, Jpalo engine in SpagoBI, Jpalo in SpagoBI, multidimensional analysis,multidimensional cubes in SpagoBI, multidimensional reports. Bookmark the permalink.

My Five Rules for Remote Working

On October 20, 2014 by Joram Barrez With 6 Comments - Activiti, Alfresco

A couple of weeks ago, there was a stir (again) about remote working and its succes and/or failure: it was reported that Reddit, the website where many people lose countless of hours, were forcing all their employees to move to SF. After a similar thing happened at Yahoo last year it made me think about why remote work is such a huge success for us at Activiti and Alfresco.

You see, I’m a remote worker for more than five years now. First at Red Hat and then at Alfresco. I worked a couple of years as Java consultant before that, so I’ve seen my share of office environments (checking my Linkedin, it comes down to about 10 different office environments). I had to go to these offices each day.

Comparing those experiences, I can – without exaggeration – say that I’m way more productive nowadays, working from home. Many people (both in and outside IT) ask me how I do it. They say “they couldn’t do it”. Maybe that’s true. Maybe some people need a lot of people around them. But for the kind of job I am into – developing software – I believe having a lot of people around me doesn’t aid me in writing higher quality software faster.

Anyway, like I said, I did some thinking around it and I came to the following “rules” which I have been following all these years which I believe are crucial (at least for me!) to making remote working a success.

(comic from http://www.dilbert.com/ )

Rule 1: The Door

Having a separate space to work is crucial when wanting do serious remote working. Mentally it is important that you can close “The Door” of your office space when you finished working. It brings some kind of closure to the working day.

Many people, when they work from home, put their laptop on let’s say the kitchen table. That doesn’t work. It is not a space that encourages work. There are distractions everywhere (kids that come home, food very close by, …). But most importantly, there is no distinction between when you are working and when you are not.

My wife and kids they know and understand that when The Door is closed, I’m at work. I can’t be disturbed until that Door opens. But when I close The Door in the evening and come downstairs, they also know that I’m fully available for them.

My door!

Rule 2: The Gear

The second rule is related to the first one: what to put in that room. The answer is simple: only the best. A huge desk, a big-ass 27″ monitor (or bigger), a comfortable chair (your ass spends a lot of time on it), the fastest internet you can buy, some quality speakers, a couple of cool posters and family pictures on the wall, ….

This is the room where you spend most of your time in the week, so you need to make it a place where you love to go to.

My setup

Often, I hear from people which company allows for remote work that their company should pay for all of this. I think that’s wrong. It’s a two-way street: your company gives you the choice, privilege and trust to work from home, so you from your side must take care that your home office isn’t decreasing anything compared to the office gear you have. Internet connection, chair and computer monitor are probably the most important bits here. If you try to be cheap on any of those, you’ll repay it in decreased productivity.

Rule 3: The Partner

Your partner is of utmost importance to make remote work a success. Don’t be fooled by the third place here, when your partner is not into it, all the other points are useless.

It’s pretty simple and comes down to one core agreement you need to make when working from home: when you are working from home you are not “at home”. When you work, there is no time for cleaning the house, doing the dishes, mowing the grass, etc … You are at work, and that needs to be seen as a full-time, serious thing. Your partner needs to understand that when you would do any of these things, it would be bad for your career.

Many people think this is easy, but I’ve seen many fail. A lot of people still see working from home as something that is not the same as “regular work”. They think you’ve got all the time in the world now. Wrong. Talk it through with your partner. If he/she doesn’t see it (or is jealous), don’t do it.

Rule 4: Communicate, communicate, communicate

More than a team in an office, you need to communicate. If you don’t communicate, you simply don’t exist.

At Activiti, we are skyping a lot during the day. We all know exactly what the other team members are currently doing. We have an informal agreement that we don’t announce a call typically. You just press the ‘call’ button and the other side has to pick it up and respond. It’s the only way remote work can work. Communicate often.

Also important: when you are away from your laptop, say it in a common chat window. There is nothing as damaging for remote workers as not picking up Skype/Phone for no reason.

Rule 5: Trust People

The last rule is crucial. Working remote is based on trust. Unlike in the office, there is no physical proof that you are actually working (although being physically in an office is not correlated with being productive!). You need to trust people that they do their job. But at the same time, don’t be afraid to check up on people’s work (for us, those are the commits) and ask the questions why something is taking longer than expected. Trust grows both ways.

The second part of this trust-story is that there needs to be trust from the company to the team. If that trust is missing, your team won’t be working remote for long. At Activiti, we are very lucky to have Paul Holmes Higgin as our manager. He is often in the office of Alfresco and makes sure that whatever we are doing is known to the company and vice versa. He attends many of the (online) meetings that happen company wide all the time so that we are free to code. There is nothing as bad for a remote team as working in isolation.

Conclusion

So those are my five (personal!) rules I follow when working from home. With all these bad press from the likes of Reddit and Yahoo, I thought it was time for some positive feedback. Remote work is perfect for me: it allows me to be very productive, while still being able to see my family a lot. Even though I put in a lot of hours every week, I’m still seeing my kids grow up every single day and I am there for them when they need me. And that is something priceless.

How To View Passwords For Wi-Fi Access Points Saved On Your Android Device

by Sameed Khan on April 05, 2011

38

With multiple Wi-Fi access points saved on your Android device, it is quite normal to forget the security key to a certain network. This article presents a small, handy work-around for the rare instance where you need to view a forgotten password. For instance, your friend needs to connect to the Wi-Fi network you’re currently using on your Android device, he or she asks you for the security key but you just can’t remember it. What do you do? Read on after the jump to find out.

Albeit through a counter-intuitive way, the app can help you with finding passwords of your Wi-Fi access points, old or new. This method requires your device to have root access. If it doesn’t, type “rootdevicename” in the search bar above to gain root access on your device using one of our own guides, or you may simply take a look at our comprehensive compilation of Android root guides to see if your device is listed there or not, and if it is, how to gain root access on it.

Also, you’ll need a file explorer that gives you read access to root-level documents. Root Explorer and Super Manager (Professional Edition) includes said feature.

• Navigate to the root directory /data/misc/wifi. If your file explorer opens the SD card (/mnt/sdcard) directory by default, press back twice to open the root directory, then navigate to the mentioned path.
• Open the document wpa_supplicant.conf. If you’re using Root Explorer, simply tap the file to open it in a text viewer or hold it (long tap) to open its context menu and select View as text to do the same.
• From within said file, you can view all your saved Wi-Fi access points along with their passwords. Look under the SSID (Access Point Name) of the Wi-Fi network whose password you need to view. The password is written within quotes in the line that says psk=”password”.

As evident by the screenshot above, once you open said conf file, you’ll be able to sneak peek into almost every access point that ever got registered with your Android. All you need to do is lookup for the required access point, note down the password displayed under it, key it in the password field, and you’re good to go. The above method has successfully been tested on HTC Desire Z (running CM7). Hopefully, we shall soon be introduced to an app that has the propensity to automatically fetch APs from said file, complete with passwords and other relevant settings, and tie them to the present APs with the same titles. Till then, manual route is the best route!

Update cm-11.0-XNPH38R-bacon-signed-fastboot.zip

Download https://cyngn.com/products/oneplusone/

Press Power + Volumn Up 10 second to go to fastboot mode

D:\AndroidSDKSlim\android-sdk-windows\platform-tools>fastboot.exe boot cm-11.0-XNPH38R-bacon-boot-debuggable.img
downloading 'boot.img'...
OKAY [  0.218s]
booting...
OKAY [  0.016s]
finished. total time: 0.250s

D:\AndroidSDKSlim\android-sdk-windows\platform-tools>fastboot.exe update cm-11.0-XNPH38R-bacon-signed-fastboot.zip
target reported max download size of 536870912 bytes
archive does not contain 'boot.sig'
archive does not contain 'recovery.sig'
archive does not contain 'system.sig'
--------------------------------------------
Bootloader Version...:
Baseband Version.....:
Serial Number........: 23e82349
--------------------------------------------
checking product...
OKAY [  0.000s]
sending 'boot' (5578 KB)...
OKAY [  1.874s]
writing 'boot'...
OKAY [  0.128s]
sending 'recovery' (7510 KB)...
OKAY [  0.889s]
writing 'recovery'...
OKAY [  0.172s]
erasing 'system'...
OKAY [  0.265s]
sending sparse 'system' (524228 KB)...
OKAY [ 22.292s]
writing 'system'...
OKAY [ 28.105s]
sending sparse 'system' (277057 KB)...
OKAY [ 11.965s]
writing 'system'...
OKAY [ 11.615s]
rebooting...

finished. total time: 77.554s

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