Bullet Graph Dashboard: PDI-MDX-SQL DataSource + BI Server + CDE

To Do

The idea to build this dashboard comes from joining Stephen Few’s spec [pdf], an excelent implementation from Slawomir Chodnicki and finding out that the C*Tools (Webdetails dashboard project) has published a Bullet Graph widget from the Protovis Library that is easier to use and customize.

Dashboard PDI Final

Final Dashboard

Components

We’ll need:

  1. CDE Editor. Install or update to the latest version [Instructions]
    Used to create and edit the parameter, datasource and mappings to the graph.
  2. Pentaho PDI/Kettle ETL tool [Download].
    Used for data retrieval by selected parameters, normalization and PKI process.
  3. The Pentaho BI Sever
    To excecute the dashboard via its menu and see the html result.

Building The Layout

Lets start a new CDE dashboard by a click on the CDE icon in the top toolbar: CDE icon

Header Rows

The CDE Editor will open with ‘Layout’ selected. This is the asociated toolbar:
cde layout toolbar

  • Click on the row icon (=)
    Select Row
    Set:

    • Name=header
    • BackgroundColor=99a5de
      (in the bottom text field, close clicking on the color ball)
    • In the ‘Corners’ attribute. Use the down-arrow to scroll to Round
  • Click on the row icon (=) three more times to have a total of 4 rows.
    Be carefull, the toolbar is dynamic and the icons will change.
  • On the first and second rows add one ‘Html’ element (<>)
  • On the third row, set Name=ParamRow
  • Type in the first html type a title:
    <h2>Sales Dashboard By Product Line</h2>
  • On the second Html type:
    <div class="topRow1">Sales YTD</div>
    <div class="topRow2">Actual amount (horizontal bar) and Previuos Year (blue mark)</div>
    <hr class="topRowHR"/>

It should look like this.

Dash 00

Save in your development folder with a meaningfull ttitle (used by the BI menu system)  and click Preview to see the headers. Close the dialog.

Parameter Row

  • The third row will need four columns.
  • The first and the third column will have also an Html element.
  • Put in the first one the text Year, and in next Month.
  • Select the second column and look for the name attribute set it to nYear (node for year select).
    Name the forth one nMonth.

Result Row

  • Add two columns
  • Name the first one nResultGraph
    and the other nResultTable
  • Set for each column span size=12 (there’s a total of 24 columns in the template)

Save the final layout, that should look like this.

Dash n

Important: If the message “Dashboard saved successfully” doesn’t appear, check your mofifications for errors. If you can’t fix it, exit and resume from the latest point saved.
To edit a dashboard, select it on the browse panel in the BI Server. Right click on it and then select Edit in the context menu.

Building Datasources and its Widgets

Three types of datasources:

  1. Two SQL queries are needed for Year and Month.
  2. The table will be  a simple MDX.
  3. The Graph will need additional columns for ranges, we’ll use an ETL transformation for this one.

SQL

Click on Data Sources ‘button’ in your top-right. The left side of the CDE will list all the connections the CDE can use to get data.

  • Click on SQL Queries, then on sql over sqlJndi
    The group SQL Queries will be added
  • Set:
    name=dsYear
    Jndi=SampleData
    query=
SELECT DISTINCT YEAR_ID FROM DIM_TIME ORDER BY YEAR_ID DESC

Click on Components ‘button’ in your top-right.

  • We need a parameter to hold the Year selection
    • Click on the Generic button on the left list
    • Click now on Simple parameter
    • Set Name=pYear
      Set de default to Property value=2005
    • Save
  • Drop Down
    • Click on Selects button on the left list
    • Click now on Select Component
    • Set Name=csYear
      Parameter=pYear (the one defined above)
      HtmlObject=nYear (attach point, that is the column we named that way in the layout)
      Datasource=dsYear
    • Save
  • Click Preview. Check the select box. Close the preview dialog.

For the Month SQL, the set up is a little diferent as we need to set up a listener for changes on the Year select box and then pass that parameter to the Component and then the SQL datasource.

  • Click on Data Sources
    • Click SQL Queries, then sql over sqlJndi.
    • Set:
      name=dsMonth
      Jndi=SampleData
      Click on Parameters, click on the Add Button.
      in the dialog: Name=pYear, value=2005, Type=String
      query=

      SELECT DISTINCT MONTH_NAME
       FROM DIM_TIME
       WHERE YEAR_ID=${pYear}  ORDER BY MONTH_ID
  • Click on Components
    • Click on the Generic button
    • Click on Simple parameter
    • Set Name=pMonth
      Set de default to Property value=May
    • Click on Selects button on the left list
    • Click now on Select Component
    • Set Name=csMonth
      Parameter=pMonth
      HtmlObject=nMonth
      Click on Listeners, on the select arrow choose pYear
      Click on Parameters, click on the Add button,
      in the dialog set Arg=pYear, Value=pYear (you can select with the “…” button)
      Datasource=dsMonth
    • Save

If you click Preview you should see something like this.

Preview 01

MDX

Let’s define the query against the ‘Steel Wheels Sales’ cube as our Datasource. There are some links at the bottom of the article about MDX and sumary functions.

  • Click on
    • Data Sources
    • MDX Queries
    • mdx over mondrianJndi.
  • Set:
    • name=dsTable
    • Mondrian squema=SteelWheels
      (jndi will be set to SampleData)
    • Query=
      with
      member [Measures].[Sales_Curr] as 'Sum(Ytd([Time].CurrentMember), [Measures].[Sales])'
      member [Measures].[Sales_Prev] as 'Sum(Ytd(ParallelPeriod([Time].CurrentMember,1)), [Measures].[Sales])'
      select
      {[Measures].[Sales_Prev], [Measures].[Sales_Curr]} ON COLUMNS,
      {[Product].Children} ON ROWS
      from [SteelWheelsSales]
      where [Time].[2005].[QTR2].[May]

And now the grid element to display our data.

  • Click on:
    • Components
    • Others
    • Table Component
  • Set:
    • Name=ctTable
    • HtmlObject=nResultTable
    • Datasource=dsTable
  • Save & Preview
  • Now click on Advanced Properties (below the Component button)
  • Click on:
    • Column Headers
      add button for: Sales, Previous, Actual
    • Column Formats
      add button for: %s, $%.2f , $%.2f
      Those are for: string (%s) and currency with two decimals ($%.2f)
  • Set:
    • Paginate=False
    • Show Filter=False
    • Sort Data=False
  • Save & Preview

For testing and format purposes the used query doesn’t change when the month or year are modified. Now we need to add listeners, the parameters in the component and the datasource to calculate the ‘quarter’ depending on the month.

Click on Components (you may be already there)

  • Click on:
    • (left) Generic button
    • Simple parameter
  • Set:
    • Name=pQtr
    • Property value=QTR2

Click on Data Sources

  • Click on:
    • (group triangle) MDX Queries
      select dsTable
    • Parameters
  • Add three lines
    • Name=pYear, Value=2005, Type=String
    • Name=pQtr, Value=QTR2, Type=String
    • Name=pMonth, Value=May, Type=String
  • Click on Query
    change last line from:

    where [Time].[2005].[QTR2].[May]

    To:

    where [Time].[${pYear}].[${pQtr}].[${pMonth}]
  • Save & Preview.
    You should see the same image than before. Close the dialog.

Click on Components

  • Click on:
    • (group triangle) Others
      select ctTable
    • Listeners
      Select in the drop down: pYear, pMonth. Click OK
    • Parameters
  • Add three lines:
    • Arg=pYear, Value=pYear
      (you can use the three dots (…) button to select  value)
    • Arg=pQtr, Value=pQr
    • Arg=pMonth, Value=pMonth
  • Click on:
    • Advanced Properties (at the top link on your right)
    • preExecution
  • Paste this javascript function to set Quarter based on month selected:
    • function Q (){
      var vQtr='';
      switch (pMonth) {
      case 'Jan':
      case 'Feb':
      case 'Mar':
      vQtr='QTR1'; break;
      case 'Apr':
      case 'May':
      case 'Jun':
      vQtr='QTR2'; break;
      case 'Jul':
      case 'Ago':
      case 'Sep':
      vQtr='QTR3'; break;
      case 'Oct':
      case 'Nov':
      case 'Dec':
      vQtr='QTR4'; break;
      }
      pQtr=vQtr;
      }
  • Save & Preview.
    Try changing the month to ‘Jan’. You should see something like this.Table Preview

Close the preview dialog.

ETL

In he last part we’ll use an ETL process and the Bullet Graph widget.

Start the PDI. Create a new transformation and save it in the demo/development folder with the name SalesDashboard.ktr.

This is the PDI transformation

Dashboard PDI

Dashboard PDI Transformation

  1. Prepare the default parameters for testing.
    On the menu click Edit ->Parameters
    Select Parameters Tab, Define pYear, pQtr, pMonth.
    Dash PDI 00
  2. Set the MDX
    Add Input->Mondrian Input
    Dashboard PDI 01
    Check the ‘Replace variable’  checkbox so the parameters are used.
    The query is:
with
member [Measures].[Sales_Curr] as 'Sum(Ytd([Time].CurrentMember), [Measures].[Sales])/1000'
member [Measures].[Sales_Prev] as 'Sum(Ytd(ParallelPeriod([Time].CurrentMember,1)), [Measures].[Sales])/1000'
select
{[Measures].[Sales_Prev], [Measures].[Sales_Curr]} ON COLUMNS,
{[Product].Children} ON ROWS
from [SteelWheelsSales]
where [Time].[${pYear}].[${pQtr}].[${pMonth}]
  • Rename columns
    Here we copy columns to new ones but set the new names. This is necesary if you want to remove the dynamic nature of column names in MDX results, for example when you use time in columns then you may get [Time].[2005].[QTR2].[May] or [Time].[2005].[QTR1].[Jan] then you better use ‘CurrentMonth’ to use in PDI steps.
    Add Scrpting->Modified Java Script
    Dashboard PDI 02
    The script is:

    var rowOut = getOutputRowMeta();
    newRow = createRowCopy(rowOut.size());
    var ProductLine = rowMeta.getString(newRow, 0);
    var Actual   = rowMeta.getNumber(newRow, 1);
    var Previous = rowMeta.getNumber(newRow, 2);
  • Get the maximum value for each column.
    Here max value for Actual is renamed to Max1 and Previous o Max2.
    Add Statistics->Group by
    Dashboard PDI 03
  • Calculate max values and two ranges.
    Add Scrpting->Modified Java Script
    This is an oversimplified ETL, with ranges set on maximum values for all rows. In any case you could use KPIs from tables and a lookup step.
    – maximum value from both columns
    – round it up
    – I set two marks one at 70% other at 90%, for grade of color in the graph bar.
    Dashboard PDI 04
    – The sample script use is:

    var Maximum=0.0;
    var Mark1=0.0;
    var Mark2=0.0;
    if ( (Max1!=null) || (Max2!=null) )
    if ( Max1 > Max2 )
    Maximum = Max1;
    else
    Maximum = Max2;
    if (Maximum!=0.0) {
    if (Maximum>=1.0) {
    Maximum = Maximum / 10.0;
    Maximum = ceil(Maximum) * 10.0;
    } else {
    Maximum = Maximum / 1.0;
    Maximum = ceil(Maximum) * 1.0;
    }
    Mark1=Maximum*0.7;
    Mark2=Maximum*0.9;
    }
  • Process measures
    Each row may need processing, if KPI is set for each measure then it should be calculated on this thread. Here we just add a subtitle string (k=thousand).
    Add Transform->Add constants
    Dashboard PDI 05
  • Remove excesive text from product line description
    The first colum has text like “[Product].[Ships]”, we can remove the fixed part with a regular expression to replace the unwanted and fixed text
    Add Transform->.Replace in String Step.
    Type in Search:  ^\[Product\]\.\[(.*)\]$
    Notice that no output field is specified so ProductLine will be used, that the dot and squared brackets are ‘escaped’ with backslash and that the replace string is (.*)=$1
    Dash PDI Regex
    For using this step: Reference1, Reference2
  • Join the data and its ranges.
    Add Joins->Join Rows (cartesian product)
    Dashboard PDI 06
    The important thing is to state the step from where is the data coming from, if you don’t do this you end up missing data or even complete rows: Add constants.
  • Sort as you like.
    Add Transform->Sort rows
    Dashboard PDI 07
  • Output
    Make available only the relevant columns:
    – Measure Title
    – Measure Subtitle
    – Value of Bar
    – Value of Mark
    – Min Range (darker shade in bar)
    – Max Range (lighter shade in bar)
    – Max value
    Add Transform->Select values
    Dashboard PDI 08

Selecting this last step and previewing the output you get.

Dashboard PDI Result

You can close the PDI.

Bullet Graph

Back to the CDE Editor.

Click on Data Sources

  • Click on:
    • MDXKettle Queries button
    • ketle over kettleTransFormFile
    • Select the ketle over kettleTransFormFile Component
  • Set
    • Name=dsBulletGraph
    • Kettle Transformation File=SalesDashboard.ktr
    • Click on Variables, a dialog will open
      add three lines for:
      – Arg=pYear
      – Arg=pQtr
      – Arg=pMonth
    • Click on Parameters, in the dialog add three lines:
      – Name=pYear, Value=2005, Type=String
      – Name=pQtr, Value=QTR2, Type=String
      – Name=pMonth, Value=May, Type=String
    • Click on Output Options, in the dialog add six lines:
      – Index=0, Index=1, Index=2, Index=3, Index=4
      Index=5, Index=6
    • Click on Columns, in the dialog add six lines:
      – Index=0, Name=Product Line
      – Index=1, Name=Subtitle
      – Index=2, Name=Actual
      – Index=3, Name=Previous
      – Index=4, Name=Mark1
      – Index=5, Name=Mark2
      – Index=6, Name=Maximum
    • Click on Query=OutputBullet
      (last activity)
    • Save CDE

Click on Components

  • Click on:
    • Charts
    • CCC Bullet Chart
      a group will be added
    • Select the new CCC Bullet Chart Component.
  • Set
    • Name=cgBulletGraph
    • Width=420
    • Height=220
    • Datasource=dsBulletGraph
    • Clear Title and Subtitle
    • Click Bullet Ranges
      click on the minus button (-) to delete each line
      Close the dialog
    • Click on Parameters, in the dialog add three lines:
      – Arg=pYear, value=pYear
      – Arg=pQtr, value=pQtr
      – Arg=pMonth, value=pMonth
    • Set HtmlObject=nResultGraph
    • Click on Listeners
      Select pYear, pMonth

Save and preview. We are almost finished

Dashboard PDI Almost

Fine Formatting

There are three formating options in the CDE

  1. Format options in CDE, either in the component advanced properties or html or column layout
  2. CSS class from the web page
  3. Some properties of the components

To work with the CSS or DOM elements we add a resource file:

  • Create a text file and type:
    That is mainly header fonts and colors, parameter style and table formatting.

    #header { margin-top:1em; }
    h2 { font-size:1.4em; padding-top:7px; padding-left:1em; height:1.6em; }
    .topRow1 { font-size:1.2em; font-weight:700; margin-left:1em; }
    .topRow2 { font-size:1em; margin-left:1em; }
    hr.topRowHR { height:2px; width:900px; color:red; }
    #ParamRow { font-size:0.9em; vertical-align:bottom; margin-bottom:2em; }
    #ParamRow select { font-size:0.9em; vertical-align:top; }
    #ParamRow input { font-size:0.9em; vertical-align:top; }
    #nResultTable { margin-top:2em; }
    table tr.even, table tr.odd{ background-color: white; }
    table thead th { border-bottom: 1px solid #CCC; background-color: #fff; }
    table tbody td.number, th.number{ text-align: right; }
    table tbody td.string, th.string{ text-align: left; }
    table tfooter td { background-color: white; }
    .ui-widget-header { background: white; color: white; border-bottom:0px none; border-top:1px grey solid; border-left:0px none; border-right:0px none;}

    Save it in the demo/development folder as Dashboard.css

  • On the CDE, click on Layout option
  • Add a css resource file, select from the options:
    css
    external
  • Set:
    name=cssDashboard
    Resource file=Dasboard.css

This will be the result

Dash Final 1

We can modify the Graph if we access its properties, in this case the ‘Extension Points’. Lets change the Marker shape and color.

Click on Components

  • Click on the triangle of the Group Charts
  • CCC Bullet Chart
  • click on Advanced Properties.
  • Click on Extension points a dialog will open.
    Add Three lines. Fill with:

    • Arg:bulletMarker_shape  Value:bar
    • Arg:bulletMarker_lineWidth  Value:3
    • Arg:bulletMarker_strokeStyle  Value:#2e559c

Note:
Check posible values for Extension Points at CCC (Bullet Chart) and Protovis sites.
In the CCC (Bullet Chart) page check the keyword to access: base, mark, measure, title, rule, range. In the rest of the document and examples look for the second part: font, shape. Join them with an undescore: bulletMarker_lineWidth.
Or if you want to change the color of the inner bar, you could try:
Arg: bulletMeasure_fillStyle, Color: green or #0F0

You can use other properties to change the appearence, for example the tooltip to show the value of a value can ve modified.

Click in the Advanced Properties of the Graph, in Value format:

function f(value){return ('$'+sprintf("%.1f", value)+'m')}

The final product should be the initial dashboard image.

.

Additional Links

  • Bullet Chart on webdetails: here.
  • Class, Field, Method and Extension Points on webdetails: here.
  • MDX function YTD youtube video by phiintegration.
  • MDX Time Series Functions Part I. Series by William Pearson.
  • Another fine example about building CDE dasboards OSBI.
    Its in French, but the code and diagrams are easy to understand.

Complete Pentaho Installation on Ubuntu, Part 5

Prepare your ‘Production’ Server

There are a few things we can do to our working server:

Usefull Changes

  1. Add your own logo
  2. Remove user selection login option
  3. Modify login screen text
  4. Increase connection timeout
  5. Increase memory limit for apache
  6. Set up a publishing password
  7. Change your passwords
  8. Remove warning messages.

Optional Activities

  1. Rename your Server and port if necesary
  2. Use a connection pool to avoid disconnection
  3. Move the database to other machine (not standard)
  4. Customize the BI server messages (rarely needed)
  5. Email account setup
  6. Automatic startup
  7. Additional notes

Not all of them are needed in your installation, choose which one you’ll use and which ones will be common in your developer and production server. Work up to that point and make a file system [folders] copy to deploy in your server machine. Continue the configuration on that server.

I come from a developer background where an additional quality server would be set up to test work in progress and deployment packages before releasing them into production but in BI I think that’s too much, the two environments are enough to avoid shooting yourself on the foot either by over-confidence, late hour distractions or human error.

In any case the backup policy of the servers should be independent. One for continuing operation depending on how fast a machine can be set up with the last data snapshot used. The other one is an incremental backup so you’ll won’t loose more than one or few day’s work.

To do the backup you shut down the DB and BI servers and copy the folders, just remember to set up the replacement machines with the same names. Or do a SQL backup with MySql Administrator or ‘Percona XtraBackup’ that can do a hot -operational- DB backup for your InnoDB tables (ISAM tables use a table locks). For the BI folder, copying the files are enough.

1. Add your own logo

To change the top-right image of ‘Pentaho’ overwrite the logo.png image in:

3.8 [152×60 pixels]: /Pentaho/biserver-ce/tomcat/webapps/pentaho/mantle/

3.9 [152×75 pixels]: /Pentaho/biserver-ce/tomcat/webapps/pentaho/mantle/themes/onyx/images and …/themes/slate/images

By the way, the login screen logo [224×94 pixels] in 3.9 is at:

/Pentaho/biserver-ce/tomcat/webapps/pentaho-style/images/login/logo.png

2. Remove user selection login option

If you have tested your users and your admin user and password (for authorization you’ll need Admin, Authenticated and ceo roles) you can hide the user selector from the login dialog. Edit the following XML file:

/Pentaho/biserver-ce/pentaho-solutions/system/pentaho.xml

and change the content from true to false:

3.8: <login-show-users-list>false</login-show-users-list> 
3.9 and 3.10: <login-show-sample-users-hint>false</login-show-sample-users-hint> 

3. Modify login screen text

Pentaho uses the first screen to present their product to new users. You might want to replace them with your project objectives, links for specific user manuals, tips, instructional videos*, news or local resources.

Specifically -I think- you’ll want to modify the scary text “Supplied free of charge with no support… no maintenance, no warranty” into something more conforting like “Pentaho Open Source Enterprise Quality Software supported by [use your contact data]”.

You can use the ‘Komposer’ editor to easily modify the text in:

/Pentaho/biserver-ce/tomcat/webapps/pentaho/jsp/PUCLogin.jsp

* For screencast videos I recomend wink (windows freeware) it produces flash files. Or if you are adventurous, use services with text-to-speach characters like http://www.xtranormal.com or http://goanimate.com/, short videos are great for instructional guides.

This has been fixed, this is no longer needed

[Edit: In 3.9 4.0 this file should be edited to remove the demo users to apear if you click on the Evaluation Login link. Search for joe, and remove the following text (strikedthrough) and replace de text striked and bold like this:
<tr>    <td style=”font-size: .8em;”>  <strong>Administrator</strong><br>     User Name: joe<br>  Password: password</td>     <td style=”font-size: .8em;”>  <strong>Business User</strong><br>     User Name: suzy<br> Password: password</td>
</tr>  <tr>    <td colspan=”3″ style=”padding: 4px 20px 0 0; font-size: .8em;”>    <a href=”http://www.pentaho.com/helpmeout/&#8221; target=”_blank”>Request free evaluation support.</a>    <img src=”/pentaho-style/images/login/help_link.png” width=”20″ height=”20″ align=”absbottom”>    </td>    <td style=”font-size: .8em;”>Contact [your data] for user accounts and support </td> </tr>

4. Increase connection timeout

The default browser session connection time out is 30 minutes, you can change that modifying the file:

/Pentaho/biserver-ce/tomcat/conf/web.xml

The unit is in minutes so for 3 hours set it to:

<session-timeout>180</session-timeout>

and also with the same xml tag in:

/Pentaho/biserver-ce/tomcat/webapps/pentaho/WEB-INF/web.xml

5. Increase memory limit for apache

This activity was a must in previous versions, but now the parameters are ok. Anyway if you want to play with the variable -I havent found benchmarks on it- you can edit:

/Pentaho/biserver-ce/start-pentaho.sh

set memory minimum (Xms) and maximum (Xmx) in tomcat, for example: -Xms516m -Xmx1024m

CATALINA_OPTS="-Xms256m -Xmx768m -XX:MaxPermSize=256m -Dsun.rmi.dgc.client.gcInterval=3600000 -Dsun.rmi.dgc.server.gcInterval=3600000"

6. Set up a publishing password

On future articles we’ll be saving queries, metada or OLAP models into the production web server. But you need to set the ‘publishing password’. In the file:

/Pentaho/biserver-ce/pentaho-solutions/system/publisher_config.xml

type a password:

<publisher-password>[your-password]</publisher-password>

7. Change your passwords

There are several steps for this to work:

  • Set up a new user as administrator.
    – Start PAC (administrator console).
    – Add Admin and ceo roles to your selected user.
  • Stop PAC and the BI server.
  • Change the database passwords.
    – Enter theMySql  administrator
    – Change the passwords for: pentaho_admin, pentaho_user and hibuser.
  • Re-edit the following files with the new passwords:
    – hibuser password in:
    /Pentaho/biserver-ce/pentaho-solutions/system/hibernate/
    mysql5.hibernate.cfg.xml
    -All user’s passwords in:
    /Pentaho/biserver-ce/pentaho-solutions/system/simple-jndi/
    jdbc.properties

    – hibuser password in:
    /Pentaho/biserver-ce/pentaho-solutions/system/
    applicationContext-spring-security-jdbc.xml
    and (tanks to Raul Chavarría and mahamood notes):
    /Pentaho/biserver-ce/pentaho-solutions/system/
    applicationContext-spring-security-hibernate.properties
    – hibuser and pentaho_user passwords in this identical files:
    /Pentaho/biserver-ce/tomcat/conf/Catalina/localhost/pentaho.xml
    /Pentaho/biserver-ce/tomcat/webapps/pentaho/META-INF/context.xml
  • Edit the URL tag and change joe and password to your new user and password in
    <URL>…userid=joe&amp;password=password</URL> in the file:
    /Pentaho/biserver-ce/pentaho-solutions/system/olap/datasources.xml
  • There are some examples (.xaction files)… but check them as you click on them and they report errors.
  • Reset the server and test.
  • In the PAC delete the demo users: joe, pat, suzy, tiffany

8. Remove warning messages

In 3.9.stable and up the OLAP results of jpivot shows a warning about being deprecated in favor of the new PAT OLAP browser:

JPivot has been replaced by Pentaho Analyzer.
It is provided as a convenience but will no longer be enhanced or offically supported by Pentaho.

and I think it will not be appreciated by your users. As it is really not true that PAT has all jpivot needed functionallity, not yet you can eliminate it with a CSS tag in:

/Pentaho/biserver-ce/tomcat/webapps/pentaho/adhoc/styles/

there are two files: jpivot.css y jpivotIE6.css.You need to add this at the bottom:

#deprecatedWarning { display: none; }

In the query tool WAQR, there is also a message to remove

Message Remove

In the file adhoc.css  at the bottom add:

#waqrDeprecatedAlert { display: none; }

9. Rename your Server (and Port if necesary)

Your ‘localhost’ url must be changed so it can be reached from any computer in your network. Use your machine name as its already mapped on the network.

The default port is 8080 that’s a port for web development so it is probably used on a server, check with your system administrator for a free port or take the next one available like the enterprise edition does. If its a new machine you can leave it as it is.

The same files and lines are used to change the server name and port. The most important one is the tomcat (or web application server) services definition file:

/Pentaho/biserver-ce/tomcat/webapps/pentaho/WEB-INF/web.xml

search for ‘localhost’ and modify [your server]:[your port] like BISERVER1:8086

<param-name>fully-qualified-server-url</param-name>
<param-value>http://
BISERVER1:8086/pentaho/</param-value>

now that we are in this file we’ll add the IP address from the machines will be connecting from the administration console, use a comma separated list:

<param-name>TrustedIpAddrs</param-name>
<param-value>[your.own.pcs.ip]<param-value>

save it and edit:

/Pentaho/biserver-ce/tomcat/conf/server.xml

First change the port (On 3.9 4.0):

<Connector URIEncoding=”UTF-8″ port=”8086” protocol=”HTTP/1.1″  
           connectionTimeout=”20000″  
           redirectPort=”8443″ />

then the server:

<Engine name=”Catalina” defaultHost=”BISERVER1“>

<Host name=”BISERVER1”  appBase=”webapps”
unpackWARs=”true” autoDeploy=”true”
xmlValidation=”false” xmlNamespaceAware=”false”>

Some other files may need to be edited depending if you’re puting services in diferent machines but if they remain in your server localhost will be ok, for example, the OLAP services will work with localhost in:

/Pentaho/biserver-ce/pentaho-solutions/system/olap/datasources.xml

but if you changed the port you’ll need to change it to [your-server]:[your-port] like:

<URL>http://biserver1:8086/…

[Edit with the Saiku update this is no longer necesary]
If you installed saiku then edit also:

/Pentaho/biserver-ce/tomcat/webapps/saiku/WEB-INF/web.xml

There are other files to modify if you change the localhost-port but depend on each service. Like the metadata.mxi from the schema editor, it will be mentiones on its own post.

10. Use a connection pool

For running an uninterrupted service or inclusive for more than 8 hours you might want to use a connection pool manager that adds more advantages than just maintaining an open connection to MySQL at all times.

In the Pentaho forum they recommended c3p0 as a connection pool. So download the file

c3p0-0.9.1.2.jar

and put it into library folders:

/Pentaho/biserver-ce/tomcat/webapps/pentaho/WEB-INF/lib
/Pentaho/biserver/administration-console/lib

And add this lines:

<!– connection pool–>
<property name=”connection.provider_class”>org.hibernate.connection.C3P0ConnectionProvider</property>
<property name=”hibernate.cp3p0.acquire_increment”>3</property>
<property name=”hibernate.cp3p0.idle_test_period”>10</property>
<property name=”hibernate.cp3p0.min_size”>5</property>
<property name=”hibernate.cp3p0.max_size”>75</property>
<property name=”hibernate.cp3p0.max_statements”>0</property>
<property name=”hibernate.cp3p0.timeout”>25200</property>
<property name=”hibernate.cp3p0.preferredTestQuery”>Select 1</property>
<property name=”hibernate.cp3p0.testConnectionOnCheckout”>true</property>
<property name=”hibernate.c3p0.acquireRetryAttempts”>30</property>
<property name=”hibernate.c3p0.acquireIncrement”>5</property>
<property name=”hibernate.c3p0.idleConnectionTestPeriod”>300</property>

to your hibernate connection file in:

/Pentaho/biserver-ce/pentaho-solutions/system/hibernate/mysql5.hibernate.cfg.xml

just at the top, after the tag <session-factory>. Restart your server.

11. If you’ll move the database to other machine

Update your server and ports, users and password on:

/Pentaho/biserver-ce/pentaho-solutions/system/olap/datasources.xml
/Pentaho/biserver-ce/pentaho-solutions/system/simple-jndi/jdbc.properties

Change your strings [new-server]:[new-port]

12. If you’ll need to customize the BI server messages

Some messages in the BI server (console app) can be easily changed in case you’ll need more personalized texts. There is an excelent blog article from Prashant Raju.

The tooltip description of every program can be set with the .properties file:

/Pentaho/biserver-ce/pentaho-solutions/index.properties

Even if you’ll want to edit every message you can do it in:

/Pentaho/biserver-ce/tomcat/webapps/pentaho/mantle/messages/messages.properties
/Pentaho/biserver-ce/tomcat/webapps/pentaho/mantleLogin/messages/ MantleLoginMessages.properties

Remember that every language has its sufix, for example the spanish files are ‘_es’ like messages_es.properties. And if you’re using special caracters you better make sure youre saving in UTF-8 coding (editor scren when you save).

13. Email account setup

The BI server can use a mail server if you have one set up already but in case you don’t get authorization to connect to it you can always send mail from the automated processes (ETL) using your mail acount. This is how I did it and we’ll review it with PDI.

The setup for the server is easy, if you know some parameters from your email server. Edit

/Pentaho/biserver-ce/pentaho-solutions/system/smtp-email/email_config.xml

Put your data in:

<mail.smtp.host>[your.mail.ip.address]<mail.smtp.host>
<mail.userid>[your-user]</mail.userid>
<mail.password>[your-password]</mail.password>

We’ll test it with the pentaho designer,

14. Automatic startup

The correct way to do this is adding a user and group with a perfect defined user account, make scripts and placing them on the correct folders.

The fast track is based on the pentaho wiki like:

  • Edit a file in a terminal/command window, type:
    sudo gedit /etc/init.d/pentaho
  • Add this text (Important: Don’t forget to use YOUR user folder):
    case “$1” in
    start)
    /home/[your-user]/Pentaho/biserver-ce/start-pentaho.sh > /tmp/pentaho.out
    ;;
    stop)
    home/[your-user]/Pentaho/biserver-ce/stop-pentaho.sh
    ;;
    esac
    exit 0
  • Save and exit. Then make the file executable:
    sudo chmod +x /etc/init.d/pentaho
  • update the boot scripts
    sudo update-rc.d pentaho defaults

You can restart your server.

Note: How to set up a tomcat pentaho windows service:

  1. Open a terminal window as administrator.
    Click on start on exec, type cmd, right click on list, select run as admin
  2. Change folder to your pentaho-tomcat excecutables:
    C:\Pentaho\biserver-ce\tomcat\bin
  3. Register the service:
    tomcat6.exe //IS//Tomcat6 –DisplayName=”PentahoBIServer”
  4. Start the configuration application:
    tomcat6w //ES//PentahoBIServer
  5. On ‘Log On’ tab, select the windows user you used for the instalation or use another one that has ownership of the folder as some temporal files requires it.
  6. On ‘java’ tab tipe:
    1. Virtual machine:
      C:\Program Files\Java\jdk1.6.0_25\jre\bin\server\jvm.dll
      Classpath:
      C:\Pentaho\biserver-ce\tomcat\bin\bootstrap.jar CD:\Pentaho\biserver-ce\tomcat\bin\tomcat-juli.jar
    2. On ‘java options’
      a. type (use your path):
      -Dcatalina.base=C:\Pentaho\biserver-ce\tomcat      -Dcatalina.home=C:\Pentaho\biserver-ce\tomcat      -Djava.endorsed.dirs=C:\Pentaho\biserver-ce\tomcat\endorsed      -XX:MaxPermSize=256m      -Dsun.rmi.dgc.client.gcInterval=3600000      -Dsun.rmi.dgc.server.gcInterval=3600000      -Djava.io.tmpdir=C:\Pentaho\biserver-ce\tomcat\temp      -Djava.util.logging.manager=org.apache.juli.ClassLoaderLogManager      -Djava.util.logging.config.file=C:\Pentaho\biserver-ce\tomcat\conf\logging.properties
      b. Initial memory pool:        512
      c. For max memory pool:  1024
      d. Thread stack size:          64
    3. On ‘startup’ tab:
      a. Class:           org.apache.catalina.startup.Bootstrap
      b. Arguments:  start
      c. Mode:           jvm
    4. On ‘shutdown’ tab:
      a. Class            org.apache.catalina.startup.Bootstrap
      b. Arguments   stop
      c. Mode           jvm
  7. Apply and close the application
  8. Use services.msc and change its properties to automatic start
  9. Restart your PC-Server

15. Additional Notes

A. There are some errors in the console or logs (/tomcat/logs/) that are not really important, but you can get rid of one (sooner or later someone is going to ask about them): To avoid the “WARNING: Security role name PENTAHO_ADMIN used in an <auth-constraint> without being defined in a <security-role>”. Add before the security-constraint tag in:

/Pentaho/biserver-ce/tomcat/webapps/pentaho/WEB-INF/web.xml

add

<security-role>
   <description>security role</description>
   <role-name>PENTAHO_ADMIN</role-name>
</security-role>
<security-constraint>

That’s it. If you know something else to add, please let me know.