Sunday, July 16, 2006

Hands on: MySQL Samples For Pentaho 3 - (Making a MySQL Samples directory)

In my previous blog entry I described some configuration tasks that make the pre-configured pentaho demo environment installation act more like a server.

In this entry I will describe how to add a folder for our own MySQL samples. We will use this folder to deploy our real samples to. We want to use our own folder for two reasons:

  • presentation - it's much nicer if our samples stand out among the common ones everybody knows already

  • maintenance - we can easily remove, rename or do whatever we want with all of our samples without affecting the other ones



Let's start off right away. Oh yeah, we won't need to stop pentaho - in fact, to demonstrate that, make sure it is started right now. Remember, you can start the pentaho BI platform by running the start-pentaho script located in the root of the preconfigured installation.

Creating a directory on the filesystem


The first step is to create a directory on the filesystem. This will show up as a folder on the pentaho entry page next to all the other pentaho folders.

Just create a directory named MySQL in samples directory, which is located at

%PCI%/pentaho-solutions/samples.

So, now we have a directory:

%PCI%/pentaho-solutions/samples/MySQL.

Adding index files


We're not done yet. This is not enough to make pentaho display a MySQL folder at the entry page. We need to put an index.xml file in the directory to do that. The easiest way to do this is to copy a index.xml file from one off the other sample directories. For example go to the dashboard sample directory:

%PCI%/pentaho-solutions/samples/dashboard

And open the index.xml file. You should see something like this:



Well, it's just guess-work on my part, but I think that the index document element actually represents an index-entry of the actual index one level higher- that is, on the level of the samples directory. The name and description elements contain a variable reference - hence the % prefix. We'll discuss variables and references later on. The icon element contains some sort of reference to a resource that is used to graphically represent the item. The visible element contains either true or false, which denotes wheter the entry should be displayed. The display-type element contains a value that most probably tells pentaho how to display the contents of the entry.

Let's focus on those variabeles - you know, the funny values inside the name and description elements. Well, a bit of looking around learned me that the variables are defined in the index.properties file. Here's what the index.properties file for the dashboard samples looks like:



Obviously, the red scribbling is by my hand to show you where the variables are 'declared' so to speak.

Now what could be the reason for putting this in a separate file? This all becomes clear when you look inside the directory associated with the "Getting Started" samples folder:

%PCI%/pentaho-solutions/samples/getting-started

Whereas we only have just index.xml and index.properties for "Dashboards", we have a whole bunch of .properties files for the "Getting Started" samples. To name just a few: index_cn.properties, index_cn.properties, index_es.properties...

It doesnt take a scientist to conclude that the suffixes denote a landcode, and these .properties files are used to localize.

Now that we know how it is organized, we can apply it to our MySQL samples folder. Simply copy a index.xml file and a index.properties file, and place them inside the MySQL directoy we just created. First, open the index.xml file and make sure what the names are of the variables that are used there. Then, edit the .properties file. Make sure that the names of the variables referenced in the index.xml file are in fact defined in the index.properties file.

(I'm telling this explictly - the pentaho samples do not all use the same variable names. Compare for example those from the "Getting Started" and "Dasboards" samples. In my case, I copied my index.xml and index.properties from the "Getting Started" samples)

Set the value of the directory_name variable to the name of our samples directory: "MySQL". Write a nice description and assign it to the directory_description variable.

Refresh Solution Repository


Ok - we're nearly done. The only thing we need to do now is to make Pentaho aware of the changes. We can do this from the front page by clicking the "Solution Repository" link in the "Refresh" section on the front-page:



A new window will open to indicate wheter the action succeeded:



You're going to need to do this a lot. Everytime you decide to change an item that is already deployed, you must do this or else it will go by unnoticed.

Custom Icon


Ok - we've succeeded in adding a new directory. This s nice of course, but it still looks just like the other folders - we don't want that. We want ours to stand out and attract attention. The easiest way to do that, is to add a custom icon.

We don't need to think much about what image we should elect: we're more than happy to settle for that friendly little dolphin everybody knows so well:



(url: http://www.mysql.com/common/logos/mysql_100x52-64.gif)

So initially I just downloaded that and converted it to png format. Then I put it in the MySQL samples directory. After that, I opened the index.xml file, and modified the contents of the icon element, so that it's value read:

mysql_100x52-64.gif.png|MySQL.png

Remember that the original value for the icon element found in the index.xml file for the "Dashboards" folder was this:

folder.png|dashboard.jpg

And indeed, the dashboard directory also contains a folder.png image.

(My reasoning was that the folder image that is used as icon for the other Pentaho samples had tot do with the folder.png stored in each individual folder)

To cut a long story short - the image file needs to be located one level higher: inside the %PCI%/pentaho-solution folder. To do it, first make a copy of the folder.png located there, and modify that so that it looked something like this:
.

After that modification, this is what my Pentaho entry page looks like:

.

Of course, I'm reaching in the dark - why do I need such a funny picture ? What does the pipe (|) mean for these icon elements?

(If anyone could help me with that, please add a comment to the blog)

Next time


We'll continue to build on the MySQL Samples for Pentaho.

16 comments:

Anonymous said...

Hi Roland, very intresting post! I followed it step-by-ste and I successfully displayed my own reports section, but unluckly only in the standard version of Pentaho. If I go to the Portal link of the same application, my new reports folder is not visible :(
Can you guess why??
Bye
Max

rpbouman said...

Hi Max,

so glad to hear you managed to get the folder up!

Actually, I havent looked into portal much - I've had so little time sofar.

Anyway - I'll try and take a look at it. Of course if you find out something, please let me know

Anonymous said...

Hi Roland, I've got the solution :)
When you switch into Portal version of Pentaho, to add pages to the portal, you have to do it by the portal admin section. There you can add/update users, roles, portal themes, pages and portlets.

rpbouman said...

Hi Max,

teriffic! Great to hear you found out how to do it, and thanks for sharing.

I'll try it myself too. I'll probably give it a go during the weekend.

Bye,

Roland.

Anonymous said...

Invaluable posts, I've followed the steps described in all the 3 parts with success.

Thanks a lot.

Give us moooore !

Unknown said...

Keep it blazin!

Holla!

Anonymous said...

great!!!!!!!!!!!! really helpful!!!!

Anonymous said...

Hi, very intresting posts! I followed it step-by-ste and I successfully displayed my own reports. It was really helpful.. Looking forward for next posts

rpbouman said...

Hi People,

thanks again for your kind and encouraging comments.

I'd really like to dig in and get a real sample going, but as it happens I'm knee deep into my day job. I'm doing this in my spare time, and it's really hard to find the time to put a convincing sample together AND write about it in a manner that is interesting and helpful for other people.

I'm not at all lacking inspiration, and I've got quite a few handles to continue, it's just time, time, time.

However, I really appreciate all your comments, so I promise I'll try to continue the series with a new article next weekend.

Thanks again, I really appreciate your encouragements.

Nick said...

Roland,

Thank you! Thank you! Thank you! Your step by step reporting is a true Godsend. Try as I might, I couldn't find anything so remotely simple yet useful as your blog on really trying to use Pentaho.

And I haven't even tried it yet. I've been down so many application cul-de-sacs in the past, I was loathe to even invest YELP (Yet more Endlessly Lost Piles of time) in researching the subject of OS BI.

You've very quickly and simply answered my question: "What do I need in a computer and in my head to use this thing?"

Thanks and keep up the great work.

nk

Gordon said...

Roland,

What those other guys said....great stuff....really helpful! Pentaho has some fabulous stuff but getting oriented in suite can take more than a little bit of time.

Anonymous said...

thanks for providing such useful information!

i really apprechiate your work and like the simple to read and understand writing sytle.

cheers,
Wolfgang

rpbouman said...

Hi Wolfgang,

thanks for your kind words. I truly cannot wish for nicer compliments than that;)

Thank you!

Roland Bouman

sandeep said...

Hi Ronald,

Really looking forward for your next post on pentaho and mysql. The 3 posts were really helpful to me.

Anonymous said...

Hi,

I would like to know how to get the values in report while designing the report , the values should be which was given in our java application using TableModelData

rpbouman said...

Hi ap,

this sounds like an excellent question to ask on the pentaho forums.

Monday, October 12, 2009 8:59:00 AM CEST

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