Complete Pentaho Installation on Ubuntu, Part 7

Install Pentaho Design Studio

The Pentaho BI suite started too ambitious as it would have the shark open source workflow engine to coordinate activities like asking for parameters, excecuting jobs and showing the results on the web application. But a compromise was reached and we now have the .XACTION files.

Sequencing of activities are a key factor for usability and scheduling of tasks on your site. Pentaho give us a tool based on Eclipse to edit XML files but building the sequences with it’s components is complex and difficult task, for me, its the hardest to make it work in the  Pentaho BI suite.

Installation

You need to find out if you have a 32bits or 64bits operating system. So open a Terminal and type:

/Pentaho/design-studio$ uname -m

The output could be:

  • i686 or i386 for 32 bits.
  • x86_64 for 64 bits.

Now you know what version to install so:

  1. Browse to sourceforge in the Pentaho Design Studio (PDS) files and download the latest stable version. The equivalent for the 4.0 enterprise edition in the open source version is 3.7. We’ll not enjoy the Agile BI wizardry, but thats the way it will be, get: pds-ce-linux-64-3.7.0-stable.tar.gz or pds-ce-linux-32-3.7.0-stable.tar.gz.
  2. Open it and extract it to our HOME/Pentaho folder. You should have a new design-studio folder.
  3. In the Terminal type:
    /Pentaho/design-studio$ gedit eclipse.ini
    add at the top:
    -vm
    /usr/lib/jvm/java-6-openjdk/bin/java
    save and exit.
  4. Start the editor:
    /Pentaho/design-studio$ eclipse
  5. Accept the default workspace or choose a different folder. Check the do not ask again option.
  6. Now configure your project (as recommended in the wiki):
    – Select File -> New -> Project.
    – Choose General -> Project from the New Project wizard
    – Press the Next> button.
    – Enter Pentaho Solutions or any other name as the project name.
    – Uncheck the Use default check box
    – Browse to the /Pentaho/biserver-ce/pentaho-solutions folder.
    – Select Finish.
    – In the navigator panel you could see the files.
    Open any .action file to test your PDS.
  7. You can exit the editor

Its time to browse the reference material.

References

  1. Pentaho Design Studio User Guide, ver 1.2 2006 [PDF 1.2MB].
    Skip to the Quick Tour section and creating your first action sequence.
  2. Create Action Sequences [pdf]

Notes

Tip #1: The Secure/Filter Prompt componet has a terrible name, as it only makes shure that you chose a valid option. You can use a Sql Query Lookup component to get a result with values and descriptions and then pass the ‘array’ to the prompt. Or you can type the options in a list if you define an INPUT first and pass it to the prompt as input. Here is the XML Sql lookup for a pair (City and City name). Notice the datamart jndi datasource:

<!-- Ask for a City and its Name -->
    <action-definition>
    <component-name>SQLLookupRule</component-name>
    <action-type>Get Citys</action-type>
    <action-outputs>
    <query-result type="result-set" mapping="city_result"/>
    <city type="string"/>
   <city_name type="string"/>
    </action-outputs>
    <component-definition>
    <jndi><![CDATA[Datamart]]></jndi>
    <query>
    <![CDATA[SELECT DISTINCT `City`, CONCAT_WS('-', City, CityName) AS city_name
      FROM `dim_cities`ORDER BY `City`]]>
    </query>
    <live><![CDATA[false]]></live>
    </component-definition>
 </action-definition>

Use the values to ask for a choice:

<!-- Aks for a City -->
  <action-definition>
    <component-name>SecureFilterComponent</component-name>
    <action-type>Ask for Report Parameters</action-type>
    <action-inputs>
   <city_opt type="string"/>
    <city_result type="result-set"/>
    </action-inputs>
    <action-outputs/>
    <component-definition>
    <selections>
    <city_opt style="select" prompt-if-one-value="true">
    <title>Choose a City:</title>
    <filter value-col-name="city" display-col-name="city_name">city_result</filter>
    </city_opt>
    </selections>
    </component-definition>
 </action-definition>

Tip #2: It is very difficult to trace parameters between actions. I use the hello word component to check if I’m building them right (paste at the botom of your .xaction files and add your variables):

<action-definition>
  <component-name>HelloWorldComponent</component-name>
  <action-type>Values</action-type>
  <action-inputs>
      <VARIABLE1 type="string-list"/>
      <VARIABLE2 type="string-list"/>
  </action-inputs>
  <component-definition>
    <quote>
 <![CDATA[Result:- Var#1: {VARIABLE1}. Var#1: {VARIABLE2}.]]>
   </quote>
  </component-definition>
 </action-definition>

What you get

When you finish your .xaction and reload the solutions (In the tools menu)in your browser  you will see the file, and you can schedule it by selecting it, right click on it and set the options to run at a specified hour or run weekly, et. You can also use the administration console (PAC) to do this.

If you want a more descriptive title than the file-name.xaction copy and rename the file with sufix .properties to change the title string. For localization use the file-name_xx.properties file where xx is your language code, for example es for spanish.

Workarounds

There are on the net several articles on how to improve the parameter window experience. Here are some resources:

  1. Create a Custom parameter Template: Bizcube Page.
    Shows how to use a .html template with the Prompt/Filter Component.
  2. Use AJAX for filling dependent select fields: Pentaho Wiki Page.
    This example also uses an .html file and shows how to fill master-slave fields.
  3. Use a Calendar Date Picker: Pentaho Forum Thread.
    Uses AJAX to pup up a calendar so you can choose a date.
  4. Advanced code: Secure/Filter source code.

Firefox Fix

If you use the BI Suite and the Prompt/Filter component with Firefox you need to make a code modification to avoid the error: “Please select something for undefined” when you click the  OK button.

Head over and edit:

/Pentaho/biserver-ce/tomcat/webapps/pentaho/js/parameters.js

Search for if( lastName != null ) {, its on the ‘function getParameters’ and change it to:

if( elements[i].name != lastName ) {
  if ( (lastName != null) && (lastName != "") ) {
    var ckRtn = checkParams( form, element.type, lastName, gotOne );
    if( ckRtn == 0 ) {
      return null;