Advertisements

Pentaho BI Server 4.5.0 & Pentaho BI Server 4.8.0- MySQL installation guide


How to install Pentaho BI Server 4.5.0 & 4.8.0 Community Edition with MySQL 5.x and Windows

Hi all this is David Fombella and I would like to share a correct way to deploy Pentaho Community last edition installing its security into a MySQL database server.

Help me keep the guides up to date and the posts flowing by donating, every small amount of money helps!

btn_donatecc_lg

Here is the download links to get the biserver 450 and 480 editions.

http://sourceforge.net/projects/pentaho/files/Business%20Intelligence%20Server/4.5.0-stable/biserver-ce-4.5.0-stable.zip/download

http://sourceforge.net/projects/pentaho/files/Business%20Intelligence%20Server/4.8.0-stable/biserver-ce-4.8.0-stable.zip/download

Remember to paste mysql_connector.jar into  /tomcat/lib  for biserver 480 edition since it is not included

Creation of Databases & Users

In our first stage we are going to create hibernate database and the user hibuser. To achieve this execute the SQL script  create_repository_mysql.sql included at biserver-ce\data\mysql5

SQL code

CREATE DATABASE IF NOT EXISTS `hibernate` DEFAULT CHARACTER SET latin1;
USE hibernate;
GRANT ALL ON hibernate.* TO 'hibuser'@'localhost' identified by 'password';
DROP TABLE IF EXISTS DATASOURCE;
--  Create Users Table
CREATE TABLE DATASOURCE(NAME VARCHAR(50) NOT NULL PRIMARY KEY,MAXACTCONN INTEGER NOT NULL,DRIVERCLASS VARCHAR(50) NOT NULL,IDLECONN INTEGER NOT NULL,USERNAME VARCHAR(50) NULL,PASSWORD VARCHAR(150) NULL,URL VARCHAR(512) NOT NULL,QUERY VARCHAR(100) NULL,WAIT INTEGER NOT NULL);
commit;
 

Next we create quartz database and the user pentaho_user. You only need to execute the SQL script  create_quartz_mysql.sql included at biserver-ce\data\mysql5


CREATE DATABASE IF NOT EXISTS `quartz` DEFAULT CHARACTER SET latin1;

grant all on quartz.* to 'pentaho_user'@'localhost' identified by 'password';

USE `quartz`;

DROP TABLE IF EXISTS QRTZ_JOB_LISTENERS;
DROP TABLE IF EXISTS QRTZ_TRIGGER_LISTENERS;
DROP TABLE IF EXISTS QRTZ_FIRED_TRIGGERS;
DROP TABLE IF EXISTS QRTZ_PAUSED_TRIGGER_GRPS;
DROP TABLE IF EXISTS QRTZ_SCHEDULER_STATE;
DROP TABLE IF EXISTS QRTZ_LOCKS;
DROP TABLE IF EXISTS QRTZ_SIMPLE_TRIGGERS;
DROP TABLE IF EXISTS QRTZ_CRON_TRIGGERS;
DROP TABLE IF EXISTS QRTZ_BLOB_TRIGGERS;
DROP TABLE IF EXISTS QRTZ_TRIGGERS;
DROP TABLE IF EXISTS QRTZ_JOB_DETAILS;
DROP TABLE IF EXISTS QRTZ_CALENDARS;
-- continues
 

Appart from that to fix an issue that occurs on BI Server 4.8.0 execute the following code below the error log


ERROR [org.quartz.core.ErrorLogger] An error occured while scanning for the next trigger to fire.org.quartz.JobPersistenceException: Couldn't acquire next trigger: Field 'PRIORITY' doesn't have a default value
 [See nested exception: java.sql.SQLException: Field 'PRIORITY' doesn't have a default value]
 at org.quartz.impl.jdbcjobstore.JobStoreSupport.acquireNextTrigger(JobStoreSupport.java:1778)
 at org.quartz.impl.jdbcjobstore.JobStoreTX.acquireNextTrigger(JobStoreTX.java:1218)
 at org.quartz.core.QuartzSchedulerThread.run(QuartzSchedulerThread.java:233)
 * Nested Exception (Underlying Cause) ---------------
 java.sql.SQLException: Field 'PRIORITY' doesn't have a default value

 

SQL code to fix PRIORITY column errors


ALTER TABLE  QRTZ_TRIGGERS DROP COLUMN PRIORITY;
ALTER TABLE  QRTZ_FIRED_TRIGGERS DROP COLUMN PRIORITY;

 

Finally you have to get a MySQL  dump of Sampledata  database.

Configuring JDBC Security

This section describes how to configure the Pentaho BI Platform JDBC security to use a MySQL server, this means the Pentaho BI Platform will now point to the hibernate database on the MySQL server instead of the packaged HSQL in memory database.

1. applicationContext-spring-security-jdbc.xml

Edit the file pentaho-solutions\system\applicationContext-spring-security-jdbc.xml.

Original code

<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="org.hsqldb.jdbcDriver" />
<property name="url" value="jdbc:hsqldb:hsql://localhost:9001/hibernate" />
<property name="username" value="hibuser" />
<property name="password" value="password" />
</bean>
 

Make the changes necessary to get the snippet of code below

<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://localhost:3306/hibernate" />
<property name="username" value="hibuser" />
<property name="password" value="password" />
</bean>
 

     2. applicationContext-spring-security-hibernate.properties.

Edit the file pentaho-solutions\system\applicationContext-spring-security-hibernate.properties.

Original code

jdbc.driver=org.hsqldb.jdbcDriver
jdbc.url=jdbc:hsqldb:hsql://localhost:9001/hibernate
jdbc.username=hibuser
jdbc.password=password
hibernate.dialect=org.hibernate.dialect.HSQLDialect
 

Make the changes necessary to get the snippet of code below

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/hibernate
jdbc.username=hibuser
jdbc.password=password
hibernate.dialect=org.hibernate.dialect.MySQLDialect
 

     3. hibernate-settings.xml

Edit the file pentaho-solutions\system\hibernate\hibernate-settings.xml.

Original code

<config-file>system/hibernate/hsql.hibernate.cfg.xml</config-file>
 

Make the changes necessary to get the snippet of code below

<config-file>system/hibernate/mysql5.hibernate.cfg.xml</config-file>
 

4. mysql5.hibernate.cfg.xml

Edit the file pentaho-solutions\system\hibernate\mysql5.hibernate.cfg.xml .

You do not need to make any changes to this file if you would like to use the default user hibuser. However, if you would like to specify your custom user, change connection.username and password  properties.

Original code

<property name="connection.driver_class">com.mysql.jdbc.Driver</property>
<property name="connection.url">jdbc:mysql://localhost:3306/hibernate</property>
<property name="dialect">org.hibernate.dialect.MySQL5InnoDBDialect</property>
<property name="connection.username">hibuser</property>
<property name="connection.password">password</property>
 

Configuring Hibernate and Quartz

Hibernate and Quartz need to specifically use the hibernate and quartz databases which were created on the MySQL server. To do so modifications need to be executed in context.xml file .

     5. context.xml

Edit the file tomcat\webapps\pentaho\META-INF\context.xml.

Remember deleting tomcat\conf\Catalina\localhost\pentaho.xml , Pentaho creates  on startup pentaho.xml as a copy of context.xml.

Original code

<Resource name="jdbc/Hibernate" auth="Container" type="javax.sql.DataSource"
factory="org.apache.commons.dbcp.BasicDataSourceFactory" maxActive="20" maxIdle="5"
maxWait="10000" username="hibuser" password="password"
driverClassName="org.hsqldb.jdbcDriver" url="jdbc:hsqldb:hsql://localhost/hibernate"
validationQuery="select count(*) from INFORMATION_SCHEMA.SYSTEM_SEQUENCES" />

<Resource name="jdbc/Quartz" auth="Container" type="javax.sql.DataSource"
factory="org.apache.commons.dbcp.BasicDataSourceFactory" maxActive="20" maxIdle="5"
maxWait="10000" username="pentaho_user" password="password"
driverClassName="org.hsqldb.jdbcDriver" url="jdbc:hsqldb:hsql://localhost/quartz"
validationQuery="select count(*) from INFORMATION_SCHEMA.SYSTEM_SEQUENCES"/>
 

Make the changes necessary to get the snippet of code below

<Resource name="jdbc/Hibernate" auth="Container" type="javax.sql.DataSource"
factory="org.apache.commons.dbcp.BasicDataSourceFactory" maxActive="20" maxIdle="5"
maxWait="10000" username="hibuser" password="password"
driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/hibernate"
validationQuery="select 1" />

<Resource name="jdbc/Quartz" auth="Container" type="javax.sql.DataSource"
factory="org.apache.commons.dbcp.BasicDataSourceFactory" maxActive="20" maxIdle="5"
maxWait="10000" username="pentaho_user" password="password"
driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/quartz"
validationQuery="select 1"/>
 

Configuring Apache-Tomcat Web Server and Servlet Container

In order to configure the settings of the Apache-Tomcat server for your Pentaho BI Server 4.5.0 the great majority of the modifications should be made inside the web.xml file which is located under the \tomcat\webapps\pentaho\WEB_INF\ folder.

     6. web.xml

In this first modification we will indicate the web application the location of our solution folder , the place where reports, xactions, dashboards …  will be stored.

Edit the file tomcat\webapps\pentaho\WEB-INF\web.xml.

Original code with empty solution-path

<context-param>
<param-name>solution-path</param-name>
<param-value></param-value>
</context-param>
 

Make the changes necessary to indicate your pentaho-solutions path

<context-param>
<param-name>solution-path</param-name>
<param-value>C:/Pentaho/biserver-ce/pentaho-solutions</param-value>
</context-param>

 

fully-qualified-server-url

Now we are going to set the URL to access Pentaho’s BI User console, the default URL is the following http://localhost:8080/pentaho .If you would like others to access the site (remotely or on a network) you will need to make changes to this parameter.

Original code with default url

<context-param>
<param-name>fully-qualified-server-url</param-name>
<param-value>http://localhost:8080/pentaho/</param-value>
</context-param>
 

Make the changes necessary to indicate your server’s domain

 <context-param>
<param-name>fully-qualified-server-url</param-name>
<param-value>http://www.mydomain.com:8080/pentaho/</param-value>
</context-param>
 

Quit HSQL Hypersonic automatic startup

By default  Hypersonic database starts up automatically – to avoid this  comment or delete locate the following snippets of code from web.xml:

<!-- [BEGIN HSQLDB DATABASES] -->
<context-param>
<param-name>hsqldb-databases</param-name>
<param-value>sampledata@../../data/hsqldb/sampledata,hibernate@../../data/hsqldb/hibernate,quartz@../../data/hsqldb/quartz</param-value>
</context-param>
<!-- [END HSQLDB DATABASES] -->
 

Second  section you need to comment or eliminate

<!-- [BEGIN HSQLDB STARTER] -->
<listener>
<listener-class>org.pentaho.platform.web.http.context.HsqldbStartupListener</listener-class>
</listener>
<!-- [END HSQLDB STARTER] -->
 

Pentaho User Console

Now it is time to run Pentaho BI Server, you only need to execute  start-pentaho.bat script stored in biserver-ce, another script named stop-pentaho.bat is included in the same directory.

If you haven’t changed the URL type on your browser the following: http://localhost:8080/pentaho . The default admin user is joe and joe’s password is password.

Pentaho Administrator Console

Now we are going to start the admin console  a web tool where we could manage users,roles and datasources. We execute start-pac.bat  and  if we open a browser and we typehttp://localhost:8099 we will be prompted for administrator console credentials user: admin and admin’s password:password.

We move to Database Connections and clicking on + we create SampleData datasource, pentaho_user should be previously granted access to this sample database

After creating the datasource restart BI server, stop-pentaho.bat …start-pentaho.bat and enjoy the SampleData database.

Hope you have enjoyed

Big Data Panelmania


Big Data Panelmania.

San Francisco New YorkFrom sea to shining sea, Pentaho leaders will be busy with big data discourse during two panel discussions on Tuesday, September 25th. In San Francisco, Chief Technology Evangelist Ian Fyfe will weigh in on what makes data visualization the hot, must-have big data tool. Later in the day, founder Richard Daley will the discuss the effects (and the challenges) big data technology has on business intelligence. He’ll also discuss why SMBs shouldn’t feel left out of what big data has to offer. We’d love to see you at either panel (or even both, if you have one of these).The Details:

What: DataWeek panel on “Why is Data Visualization Insanely Popular”
Where: San Francisco
When: Tuesday, September 25th, 12pm/pt
Join Us: http://www.dataweek.co/index/conferencespeaker/event/23

What: Enterprise Insights panel on “Go Big (Data) Or Go Home”
Where: 1290 Avenue of the Americas, New York City
When: Tuesday, September 25, 2012, 6pm/et
Join Us: http://www.nybimeetup.org/events/78002042/?eventId=78002042&action=detail

CDE Dashboard (Datasources Kettle -MDX OLAP Components Dashboard)


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

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>Sales YTD</div>
    <div>Actual amount (horizontal bar) and Previuos Year (blue mark)</div>
    <hr/>

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.

Using  the following function in the Post Fetch , you will colour the background regions of the Bullet Chart:

function f(data){

function setExtPoint(pArray, name, value) {
for (var index in pArray) {
if(pArray.hasOwnProperty(index)) {
var propCell = pArray[index];
if(propCell[0] === name){
propCell[1] = value;
return;
}
}
}
pArray.push([name, value]);
}

setExtPoint(
this.chartDefinition.extensionPoints,
‘bulletRange_fillStyle’,
pv.colors(‘red’, ‘yellow’, ‘green’));
return data;
}

Click in the Advanced Properties of the Graph (this shows only 1 decimal digit .1) , in Value format:

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

The final product should be the initial dashboard image.

Getting session variables when using the PRD scriptable data source


In a previous post I was asked in the comments how to use a session variable as a parameter when using the scriptable data source in Pentaho Report Designer.  I finally figured it out and thought I’d share with everyone. The solution is relevant for scriptable data sources, not just those that use MongoDB, so I’ll leave that complexity out of this discussion.

 

Pre-conditions

 

Before this will work you need to have some way you are getting a session variable set that you want to use.  You can create an action sequence that runs when a user logs in or, if you are using single sign-on, you can set it during the log in process that way.  These are describe other places, so I won’t go into how to set the session variable.  For the sake of this example, lets assume you have somehow set a session variable for a user called “Region” that has the region that applies to the user, North, South, East, or West.

 

Create a Report Parameter

 

The first thing to do is to create a report parameter that will get the session value.  Then set the Name and Value Type as appropriate.  The key step is to set the Default Value Formula to =ENV(“session:Region”). The ENV function will get the session value for the attribute with the name “Region”.  You should also set the parameter to be Hidden by checking the box, although while testing it can be handy to have it unchecked.  Note that if you preview in report designer this will have no value (there are ways to set it), so a default value can be handy.  I don’t recommend deploying to production with a valid default, though.

 

The following figure shows getting the Region value from the session.

 

Image

 

Using the Parameter

 

Using the parameter from your script is simple.  The scriptable interface provides a dataRow object with a .get(String name) command to get the value.  So, to get the value of Region at run time use the following line (in Groovy):

 

def region = dataRow.get(“Region”)

 

Then just use the value in the script.

via Getting session variables when using the PRD scriptable data source.