Wednesday, July 12, 2006

Hands on: MySQL Samples For Pentaho 2 (PCI Post-installation tasks)

In my previous blog entry I described how to install a Pre-configured version of the Pentaho BI Platform. That entry concluded with testing a few out of the box samples to verify that the BI Platform is working correctly.

In this entry I will discuss some Pentaho (PCI) post-installation tasks. All these are also described in the Pentaho Quick start Guide.

Running the PCI as a server



A demo environment with samples is nice, but it's even nicer when you can offer the samples right at the fingertips of your audience. Suppose you want let a business play around with some of the samples: you certainly do not want to force them to crawl behind your demo machine. That would be like going to a restaurant and find out that they've only got one plate to eat from, so that all the customers need to queue before they can have dinner.

In order to allow the PCI to be accessed from a remote client, we need to edit a configuration file: the web.xml file. You can find it in the

%PCI%/jboss/server/default/deploy/pentaho.war/WEB-INF

directory.

The WEB-INF/web.xml file contains a so-called java servlet web application deployment descriptor. That's just what the Pentaho BI platform is - a web application based on java servlet technology. For the PCI, the servlet container happens to be JBoss.

Anyway, once you found the web.xml file, it's probably a good idea to make a copy before you start editing. You can open file with any text-based editor. Search for the string base-url. You should end up finding a fragment such as this:



So, we've located the parameter with the name base-url and it's value is now http://localhost:8080/pentaho. We need to change the hostname (now: localhost) to either the DNS-name or the IP address of our server. It is probably a wise idea to use a DNS-name instead of the IP address, so here we go:

http://%My Computer's Name%:8080/pentaho

Now, save the file and (Re)start the server (using the start-pentaho script). Test if your modification was successful by navigating to http://%My Computer's Name%/pentaho. Of course, you should also test if it still works when you try it from a remote machine. If that is not the case, it's most likely that this is due to a firewall that blocks remote requests or outbound responses on port 8080.

If nothing happens, or when you get an error message, you most likely made a typo. Besides comparing with the old web.xml file, you can monitor the output of the start-pentaho script. Any errors will be output by the script and it might give you a hint as to what's wrong.

Ok, so now we can reach our pentaho demo environment from a remote machine.

You can also configure the server to use a port other than 8080. For example, port 80, the default http port, seems a sensible choice. This would allow users to connect to the much friendlier-looking url:

http://%My Computer's Name%/pentaho.

To modify the server port, you must modify the pentaho web app configuration file (the web.xml file we've just been editing) accordingly. So, again, we edit the value of the base-url parameter, deleting the :8080 bit so that it reads:

http://%My Computer's Name%/pentaho

We also need to modify the server configuration file. According to page 10 of the Pentaho Advanced Install guide, this corresponds to the server.xml file that is located in the

%PCI%/jboss/server/default/deploy/jbossweb-tomcat55.sar

directory. Again, make a copy of the original first, and store it somewhere safe. Then, you can open the original with a regular text editor. Once you've opened it, you should look for the fragment that defines the HTTP connector. The lines look something like this:



Just modify the port attribute of the Connector element so that it reads 80. Save the file and (re)start the server with the start-pentaho script.

It is worth noting that this this did not work for me at first. I got the following error:

20:07:13,234 ERROR [Http11Protocol] Error initializing endpoint
java.net.BindException: Address already in use: JVM_Bind:80

This indicates that another application is already using this port. Now, usually this would be another HTTP server, but I thought of that and made sure none of the other webservers I have installed were running. Finally, I found that Skype was using port 80. The issue was solved by configuring skype not to use port 80. (For everybody laughing their eyeballs out: this option was on by default.)

Configuring Mail


In my previous blog entry, I mentioned that not all of the packaged samples will run out of the box. For example, this is the case with the Bursting examples.

To configure mail, we need to edit the email_config.xml file which resides in the directory:

%PCI%/pentaho-solutions/system/smtp-email/

Again it's best to make a copy of the file first. Then, open it with a plain text editor.

If you want to use a google mail adres, I recommend you discard the original email_config.xml. Then, rename the email_config_gmail.xml file in the same directory to email_config.xml.

In most cases, you'll need to provide values for only these elements:


  • mail.smtp.host - provide the name of your smtp server

  • mail.userid - the user name

  • mail.password - the password for this mail



The config files have excellent inline comments and it should not be a problem to set this up.

When you're done, start pentaho if you did not already do so. Changing the mail configuration does not require a restart as far as I can tell.

Now, navigate to the Bursting Examples section. From there, follow the sequence of instructions to test if the workflow engine is working correctly. You can tell that it does when you see that your mailbox is being spammed by your pentaho demoserver.

Next time


Ok, so far we've just been busy setting up the pentaho BI platform: installing and configuring. Next time, we will get ourselves some development tools, and actually build our first MySQL sample.

20 comments:

Anonymous said...

good site and good content, all super!

Unknown said...

It has changed since 1.6-RC2:

http://wiki.pentaho.org/display/PentahoDoc/Deployment+Configuration

pci compliance said...

I've just came across to your blog.
Helpful blog!
Cheers..:-)

José Garrido said...

Hi,

nice post, i just have one question. You stopped the http servers to run pentaho. So you're saying that you need to have the https servers stopped in order to run pentaho under port 80?

And if i need have both? Http servers and pentaho, under port 80, running at the same time.

Any clue

Tks

rpbouman said...

Hi Jose!

"You stopped the http servers to run pentaho. So you're saying that you need to have the https servers stopped in order to run pentaho under port 80?"

Well, the answer is yes. It's like this: Pentaho is a Java web application. Java web applications like Pentaho are Java Servlets (see: http://java.sun.com/products/servlet/). Java Servlets are basically plugins for a Java Servlet container. The Java Servlet container is typically a HTTP server that can load and run Servlets to handle HTTP requests.

Well, to be a regular HTTP server, the Servlet container needs to listen for HTTP requests on a port - preferably the standard HTTP port (80). Now, as is common with server sockets, no two processes can bind to the same port. So, this is not specific for Pentaho, or Java Servlet containers: this holds for any server software. You simply cannot create a server socket on a port that is already used by another server.

"And if i need have both? Http servers and pentaho, under port 80, running at the same time."

Well, it depends. You might be able to get by with just one server - if you only serve static web pages, then you can simply use Tomcat instead of Apache httpd. However, many people prefer to have both servers running, each on their own port (or even each on their own machine) and have the primary HTTP server (typically Apache httpd) redirect the requests that are meant for the other server.

With Apache httpd you can do this usinng mod_proxy. Start reading here http://httpd.apache.org/docs/2.2/mod/mod_proxy.html. What you want to do is to set up Apache httpd as a reverse proxy (gateway) to the pentaho server. Apache httpd would listen on port 80 and thus be accessible like any other webserver, and tomcat would listen on its own port (8080). Typically, you would have tomcat behid the firewall, and only allow the outside to access it through the apache httpd server.

José Garrido said...

Hi Roland, tks for your kind answer.
I'll do as yo say. Tks :)

rpbouman said...

Jose, I forgot to mention - the new Pentaho release (3.0) does not ship with JBoss, but with Tomcat. Lots of things are a lot easier now. But the point is, some of the info in these blogposts isn't valid anymore. I don't have a whole lot of time to update the entries right now, sorry.

Just beware that JBoss!=Tomcat.

Eric Njogu said...

Thank you Roland.
This information was helpful to me in setting a repository on community edition v3.

The base URL instructions work for tomcat with no modifications.

rpbouman said...

Hi Eric!

in pentaho 2 and onwards, there is a better, simpler way...just right click in the solution repository browser in the user console, and create a new folder using the context menu option :)

This post is...ancient.

Neogamer87 said...

hi Roland

I am Surendirane form India

I have a Peculiar Problem in Pentaho will you please Help me

Heres the problem

1. I Have Installed pentaho in my PC and its working well and i integrated pentaho in Compiere ERP and Its also working Perfect

2. But When i am trying to access in a Client PC I Have a Following Problems
(i) In Client system the Corresponding process is working good both the compiere are pentaho are responding well but the problem is the output of that process will be shown in my PC

(ie) I have created a Schema and published that in Pentaho User Console and created a analysis View in User console and I Integrated in Compiere by means of a java class file.

Then i opened compiere and run the report process the output will be shown in a separate browser showing the analysis view

So if i want that same process to run in a client PC the process are going well but the output opens in my PC not in a client PC

rpbouman said...

Hi Surendirane,


this sounds like a pretty specific question. I don't really know what the solution is. Have you tried online forums? It may have more to do with compiere than with pentaho.

Good luck! Please post back if you figured it out, I am sure your experience may be useful to others too.

kind regards,
Roland

Neogamer87 said...

Hi Roland

I Figured it out the 50% of the solution But still i have some Problems like

Changes that i made

1. I edited the web.xml file

E:\Pentaho Community edition\biserver-ce\tomcat\webapps\pentaho\WEB-INF

In that edit the corresponding line


base-url
http://sks1:8082/pentaho/

and then

go to the compiere destination folder
D:\SVN_Srikesh\Compiere2\Prop in that edit the file named
Pentaho.properties

http://sks1:8082/pentaho/

Then i moved the (D:\SVN_Srikesh\Compiere2\Prop)

Prop folder to my client system

Now Compiere and Pentaho integration works perfect in swing UI but its not working in WEB UI as usual the same result

rpbouman said...

I'm sorry - this is too complicated to solve this way. try some forums.

Neogamer87 said...

Hi Roland

Thank you for your inforamtion

I have one more question, I hope that i am not distrubing you

Is it possible to use Dashboard in Pentaho community Edition, because i am not having Dashboard in my Pentaho

When i open my localhost userconsole it shows only three options

1.New Report
2.New Analysis View
3.Manage Content

Is there any solution to get dashboard in community Edition

Because previsoly I used Pentaho Enterprise Edition its only for 30 days trail pack but it contains Dashboard option in its User Console

So thats why i am asking if it is possible in (CE) Community Edition

Thanks
Surendirane.B

rpbouman said...

Hi Surendirane,

yes, you can make beautiful dashboards in the community edition thanks to the Community Dashboard Framework (CDF) created by Pedro Alves and webdetails (http://pedroalves-bi.blogspot.com/)

You can find CDF examples, along with source code and documentation in your BI Developers samples folder in the user console of the community edition. You can also find information on that on the pentaho wiki here http://wiki.pentaho.com/display/COM/Community+Dashboard+Framework.

Also, my book "Pentaho Solutions" (http://www.wiley.com/WileyCDA/WileyTitle/productCd-0470484322.html) contains one chapter completely devoted to building CDF dashboards, including detailed examples on how to do it yourself. Past fall, Pedro also created a dashboard designer, to make it even easier to build dashboards with the community edition.


I hope this helps.

kind regards, Roland

Neogamer87 said...

Hi Roland

The link you send has no download file it consists of only the working details

But I Downloaded some versions from google code .com
But that is also dosent contains any exe or bat or sh file format

So how should i install CDF in windows or linux

Thanks
Surendirane.B

rpbouman said...

Surendirane,

have you read the documentation I linked to? There is no need for any .exe, .bat or .sh file. CDF dashboards are described by a .xcdf file, which is interpreted by a BI server plugin. But like I said already, it's already installed in the community edition. Like I mentioned before, you can find the dashboards in the BI Developers folder. You can also find a forum about CDF dashboards at forums.pentaho.org.

Perhaps you should by my book, "Pentaho Solutions", ISBN 978-0-470-48432-6. It contains detailed instructions on how to build your own dashboards. (chapter 17)

Neogamer87 said...

Hi Roland

Thank you very , Its Working well now



Regards
Surendirane.B

Anonymous said...

hi,
i want to integrate pentaho with my web application which is in struts2.0 i dnt knw how to integrate can u pls tell me step by step procedure of how to integrate and how to generate report in web application

rpbouman said...

Hi Anonymous,

please ask general questions in the pentaho forums: forums.pentaho.org

SAP HANA Trick: DISTINCT STRING_AGG

Nowadays, many SQL implementations offer some form of aggregate string concatenation function. Being an aggregate function, it has the effe...