Monday, July 10, 2006

Hands on: MySQL Samples For Pentaho 1

I got a quite a few encouraging comments in response to my previous blog entry, Some thoughts on Pentaho and MySQL. Now let's see if I can turn all this talk about Pentaho and MySQL into something we can touch and look at.

I want to make a start in creating a bundle of MySQL centric Pentaho samples. The idea is to end up with at least one simple educational sample to for each of the main flavours of functionality offered by pentaho.

Pentaho


As far as Pentaho is concerned: I'm taking the preconfigured demo environment as a basis for a couple of reasons:

  • Right now I'm not really interested in getting lost in the intricacies of setting up a J2EE server. There's plenty of time for that later on

  • As an extension to my first point, the samples will be easier to deploy for other people as well

  • The samples can be included in a future Pentaho demo environment with minimal effort



(Later, if and when I have a decent set of samles I might get rough and set up a demo pentaho environment from scratch to see how it works out if I replace the hypersonic database with MySQL.)

MySQL


For MySQL, I will assume a 5.0 generally available release. I will probably build most of the samples around the Sakila sample database. I hope it will be included in the MySQL release, and even if that's not the case, it is sufficiently easy to set it up.

It will also be quite interesting to build some things around some of the MySQL server level entities. The SHOW commands, several of the server variables and of course the mysql database are really always there, even in earlier MySQL versions. I envision that it should not be too difficult to build a few pentaho solutions that could actually be useful for real world database administration. Just a thought.

Functionalities



I want to have samples to cover at least the following areas of the BI space:

  • Reporting

  • ETL



Those are no-brainers actually. I think I really should also do


  • Analytics

  • Graphing



It starts to get really interesting if I could be able to use the more sophisticated Pentaho features:


  • Datamining

  • Business Rules

  • Dashboards / KPI visualization

  • Bursting

  • Workflow



Taking the first step...



Ok, let's not hesistate and make a start right away:

Installing the Pentaho BI Platform



  1. Use a web browser to navigate to the Pentaho download area

  2. Pick up some of the pentaho documentation while you're in the download area. I recommend you download the Pentaho Quick Start and the Creating Pentaho Solutions pdf documents. Besides, this will give you something to read while the BI Platform is busy downloading.

  3. Download the Pentaho BI Platform (pre-configured installation or PCI)

  4. Install the BI Platform. The Quick start guide contains just enough details, but I'm not afraid to spoil the surprise: Windows and Mac users only need to unzip the distribution. Linux users need to install a rpm.

  5. Installation results in a pentaho-demo directory. This is the serverroot, and it is commonly referred to as the PCI root or PCI install directory or something similar. To start the server, windows users run start-pentaho.bat; *nix users run start-pentaho.sh.

  6. Open an internet browser, and navigate to: http://localhost:8080/. This may take a little while - the server needs to warm up.



You should see a web page that looks something like this:





On the right side, you see a few links to access a few administrative tasks. The folders on the left side contain the samples, and we'll be adding a new folder there to hold our MySQL samples.

Before we go there, we should first try a few of the packaged samples to verify that the server is functioning correctly. It is important to realize that some of the samples need some configuration before they'll work. However, we'll avoid that for now - There's penty of time to set that. Again, the Quick start guide gives you all the info you need in just 15 pages, but let's highlight a couple of things that work right out of the box and that are visually appealing:


  1. Getting Started::Hello World

  2. Reporting. Just give a few of there reports a sping

  3. Chart Examples. Shows some of the included charting capabilities

  4. Analysis / OLAP Examples. Demonstrates slice and dice

  5. Dashboards


When you're done with pentaho, locate the stop-pentaho script in the PCI installation directory. Execute the script to stop de server.

Next time


Next time, I'll discuss some configuration tasks. I'll also discuss installing the sakila sample database. Stay tuned!

22 comments:

Anonymous said...

Perfect for beginners. Many Thanks.

Anonymous said...

thanks for this introduction to pentaho

Anonymous said...

Your links are not working anymore...Seems very interesting though.

rpbouman said...

Hi Anonymous...

"Your links are not working anymore...Seems very interesting though."

That's odd...from what I can tell it all still works fine. Anyway, there have been a few changes.

For example, to download stuff I currently recommend:

http://sourceforge.net/search/?type_of_search=soft&words=pentaho

For documentation, I currently recommend:

http://wiki.pentaho.com/display/COM/Community+Wiki+Home

of course, http://localhost:8080/pentaho only works after you installed and started the server on your local box. ;)

If you don't want that, check out the live demo here:

http://demo.pentaho.org/

Anonymous said...

A very nice text helped me a lot. Thank you.

Anonymous said...

Hello,

Installation OK, Pentaho BI Server and Repository Solution services are started.

I encounter the following error:
Can not find http://localhost:18088/
idem with http://localhost:8080/

Can you help me ?

rpbouman said...

Anonymous,

there is nothing running on port 18088. What were you expecting to find there?

As for 8080: that should just work. Check the log. You can find it in the tomcat dir.

good luck, Roland.

Anonymous said...

Roland,
thank you for your prompt response
I loaded the Enterprise Edition and tried "Pentaho Enterprise Console" shortcut
=> Error on //localhost:18080

I did not see that there was "Pentaho User Console" Shortcut
I tried it and it's OK with
http://localhost:18080/pentaho ??

Jacques

rpbouman said...

Most likely you need to first start the enterprise console. The console is a separate daemon, at least in the community edition.

Anonymous said...

Roland,

Good morning and thank you for the great info! Just getting into Pentaho BI, ran through the initial install (Windows/Oracle 11g) and when bringing up the Admin Console get the following three errors:

Error Loading Database Connections
Unable to refresh database connection list: PacService.ERROR_0023 - Failed to get a Data Source: Cannot open connection

Error
Unable to refresh security information: The call failed on the server; see server log for details

Error
The entity "nbsp: was referenced, but not declared
Thanks for your help in advance!

Unknown said...

HI ! Thnks for your helpful information. I got just the same error "Unable to refres database connection list: PacServer.ERROR_0023 - Failed to get a Data Source: Cannot open connection. What can I do ¿ Thanks in advance

rpbouman said...

Anonymous and Laura,

this article is about a stone-age version of Pentaho. In the more recent versions you can manage data sources with the Pentaho Administration Console. Please see http://wiki.pentaho.com/display/ServerDoc2x/.04+Configuring+Data+Sources.

Also, you the book Pentaho Solutions might be helpful to get you started with Pentaho: http://www.amazon.com/Pentaho-Solutions-Business-Intelligence-Warehousing/dp/0470484322.

Kind regards,

Roland.

Unknown said...

Hi ! I succeded in making a report from the Pentaho User Console, but now I'm trying to publish a mondrian schema for olap analysis and I'm stuck, I don't know how to create a JNDI datasource !!! What do I need to publish the schema to de Pentaho BI SERVER ?

Thanks !

rpbouman said...

Hi Laura,

in the recent versions of Pentaho, you can create JNDI datasources using the Pentaho Administration Console. If you download and extract the Community version of the Pentaho BI Server, you should find 2 directories: one for the bi server itself, one for the administrative console.

Also see: http://wiki.pentaho.com/display/ServerDoc2x/The+Pentaho+Administration+Console
For creating data sources see:
http://wiki.pentaho.com/display/ServerDoc2x/.04+Configuring+Data+Sources

Sorry for the self plug, but the book i wrote with Jos covers these things quite nicely: http://tinyurl.com/yzt72rd

Unknown said...

Thank you so much por your request. It was very helpfull, and I've just ordered the book ....

Continuing with JNDI connections, I've tried what you said, but I'm not allowed to write DRIVER CLASS name, I can only select one of the options available from a combo list and there's none for Postgres, how I can do that ?

Thanks again in advance !

rpbouman said...

Hi Laura!

thanks for ordering the book - I hope you'll like it :)

Regarding the driver: can you please check the contents of the administration-console\jdbc director and see if the .jar file for postgres is in there? You're going to have to make sure that is in tomcat/lib/common (i think) too.

If this doesn't work, try running the admin console with JRE 1.6

I hope this helps. Let me know if you still have issues, and I'll try to dig deeper to find this out.

Good luck,

Roland.

Unknown said...

Hi Roland ! Thanks again for your reply !. It worked ! Now I'm in troubles again, I tried to create an analysis view with the pusblished schema but I got the following error message: "The requested resource (/ pentaho / NewAnalysisView) is not available" !. Sorry to bother you too much ! Thanks !.

rpbouman said...

Hi Laura,

glad you got that driver issue solved.

Now, the second problem you're describing, I don't know what that is. Please search the pentaho forums, and post there for more help. Or try the ##pentaho IRC channel on freenode.net.

Good luck,

Roland.

Unknown said...

Hi Roland ! Thanks for your advice. I've tried the ##pentaho IRC channel and I found o lot of people willing to help. I was very helpful ! thanks again.

Unknown said...

HI ! I have a problem with the pentaho metada editr now. Id doesn't run. Here is the debug:

DEBUG using JAVA_HOME
DEBUG: _PENTAHO_JAVA_HOME=D:\BI\BIserver360\biserver-ce\jre
DEBUG: _PENTAHO_JAVA=D:\BI\BIserver360\biserver-ce\jre\bin\javaw
"D:\BI\BIserver360\biserver-ce\jre\bin\javaw"
GOTO was unexpected at this time.

Any idea ? Thanks !!

Ai said...

Hi Roland...
I've installed pentaho using your book as guides. On the installation and configuration part, there is a reference to Prashant Raju's website to move the sample data from HSQLDB to MYSQL. There are some scripts that must be run in order, i have followed your book:
1. create_repository_mysql.sql,
2. create_sample_datasource_mysql.sql,
3. create_quartz_mysql.sql.
But in Prashant's website, he said (in order):
1. create_repository_mysql.sql,
2. create_quartz_mysql.sql, then
3. create_sample_datasource_mysql.sql.

So, which is true?

I have also an error in the administration console, like Laura's. When entering the administration console page (after login), there are messages:
1. Unable to refresh security information: The call failed on the server; see server log for details
2. Unable to refresh database connection list: PacService.ERROR_0023 - Failed to get a Data Source: Cannot open connection

I cannot implement the Wiki's problem solving that you gave to Laura, because my Administration page is empty (no users list, no datasource list, etc. there just the menu but not the list). I've tried to make a connection but also failed.
In the user console, i can't view the data and the charts.

It seems that my user&administration console are not connected with the database or the datasource.

Any suggestion for my problems?

rpbouman said...

Hi Ai,

After Pentaho Solutions was published, there have been a number of changes in the BI Server. For example, exact names of configuration files have changed.

Nowadays, I always recommend Prashant's guides, because they are frequently updated and cover a wider range of platforms. though I must say that I do not believe the particular order of these steps makes much difference, assuming you restart the server after all 3 steps have been executed.

It is hard to trouble shoot your problem from here. I recommend starting over with a fresh bi server, and cautiously executing the configuration according to Prashants guides. You can also log into the ##pentaho IRC channel on freenode.net and ask questions to the volunteers there.

Good luck, i hope this helps.

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...