Install The Pentaho 5.0 Suite

Pentaho 5

This post is about installing the tools that were published on november 19th, 2013 on Sourceforge, that is version 5.0 and above. Installing a Database that can hold your data permanently and the Pentaho desktop tools and BI web server.

Future posts will be about creating a replica of the demo data on a permanent database that can hold your data, customize the apperance of web server, add notes on the new repository and articles about each Pentaho tool. They will be added through 2014 and the index of the series will be updated as work is done.

Open Source Software

You will download and install:

  • Java Development Kit
  • MySql Database and MySql Dashboard
  • Pentaho Tools:
    1. The ETL or Data Integration. A stand alone app that can be used to access data on diferent formats and systems, process and distribute it to the apropiate people. You can schedule its excecution with the web server or use it as a datasource it with the report designer.
    2. Report Designer, a desktop banded report builder that let you ask for parameters and present your reports on web, excel or pdf.
    3. Metadata Editor A desktop modeler tool that lets you build a meta-model of your data to make easier to your users to navigate it and you to control their access to it.
    4. BI Server, a Tomcat Web Server preconfigured with users, demo data and the pentaho integrated projects.

Java SDK Installation

Use the 32 or 64 bit java version that corresponds to your operating system. To find out which OS version is on your computer:

  • On linux: Open a terminal (launcher->Type Terminal, click on it), type on the command prompt:
    uname -m
    If the answer is i686 you have 32 bits.
  • On windows: Click on Start -> Right Click on Equipment. Select properties. Look up the OS information in the window.

ON Linux

If you know how about user administration, create and use a pentaho user and a pentaho group to install the software, if not, your current user will do.

  1. On Ubuntu:
    Open the ‘Ubuntu Software Center’ (type it on unity search). On the search box type:
    Executable java OpenJDK 7
    click on its install button.
  2. On any linux box:
    In a Terminal, type three commands:

    sudo add-apt-repository ppa:webupd8team/java
    sudo apt-get update
    sudo apt-get install oracle-java7-installer

In version 5.0 the scripts (bat & sh) looks for a PENTAHO_JAVA_HOME variable, so add it yo your profile. Please be carefull this is a configuration file.

  • You need to know the location of the java files.
    If you used the ubuntu center, it must be on
    /usr/lib/jvm/java-7-openjdk-i386
    Confirm this before continuing
  • In a command prompt edit your user .profile or the general profile:
    sudo gedit /etc/environment
    Add at the bottom the variable and the java (the one above the ‘bin’) folder:
    export PENTAHO_JAVA_HOME=/usr/lib/jvm/java-7-openjdk-i386
    save and exit

You can check your java installation by typing on a terminal:

java -version

Windows

Open a web browser and go to:

http://www.oracle.com/technetwork/java/javase/downloads/jdk7-downloads-1880260.html

Download either the 32 bit version: jdk-7u51-windows-i586.exe or the 64 bit file: jdk-7u51-windows-x64.exe and excecute it using administrator privileges (right click on it).

To configure the windows environment variable:

  1. Click Start Button, right-click equipment, select Properties.
  2. On your left, there should be an option: advanced system settings, click on it.
  3. Click on the environment variables button
  4. A dialog will open with two list, in the bottom one, type:
    PENTAHO_JAVA_HOME
    and then the java files path that should be something like:
    C:\Program Files\Java\jdk1.7.0_51

You can check java by typing on a console: java -version

MySQL Database

LINUX

To install MySql :

  1. On Ubuntu:
    Open the ‘Ubuntu Software Center‘ and then type ‘MySql’ to search for it, click on ‘MySQL Server‘ and then the Install button.
    Do the same with ‘MySQL Workbench‘ so you can interact with the database with a graphical tool.
  2. Or you can select other recomended ways for installing your software
    http://dev.mysql.com/doc/refman/5.6/en/linux-installation.html

WINDOWS

This is the main oracle/MySql page. You can select different methods to install it on windows, I prefer to use a MSI installer as it installs and configures the database server to the appropriate access permissions. On your system it can be as simple as running the msi with administration access or exhaustive and detailed port, process and file ownership configuration.

General instructions to download and Install are in this page. The download page is http://dev.mysql.com/downloads/ so install the MySQL Community Server and MySQL Workbench. Remember to select your Windows 32-bit OR 64-bit and MSI not zip download button. You will have standard users, configuration and lots of literature to chatch up, but you are ready to go now.

You can start the server doube clicking on <Your path>\bin\mysql.exe. With the Workbench (or the command window) you can run SQL commands or table editing.

ETL aka Pentaho data Integration (PDI)

Now the easy part: to install Pentaho.

Access the Pentaho Project on source forge. Click on the Data Integration folder. The latest version is 5.0.1-stable (as this writing). Download the .zip file pdi-ce-5.0.1.A-stable.zip.

On your computer, It is easier to put al versions of the Pentaho Suite on a folder, so create a Pentaho5 directory under your home directory and unzip the downloaded file in it (right click and select ‘unzip here’). You should now have a /Pentaho5/data-integration directory with the ETL files.

To test the application:

  • On linux:
    Open a terminal window, change to the the app folder, something like
    cd Pentaho5/data-integration
    and then type
    ./spoon.sh
  • On Windows:
    Use the File Navigator to get to your ETL files, like C:\Pentaho5\ Double click on spoon.bat.

There are a lot of ETL samples in the directory Pentaho5/data-integration/samples/transformations/ from reading text files, sort group, write to database tables. For example ‘Fixed Input…‘ reads a file ‘Textfile input – fixed length sample data.txt‘. To see it working, select the first icon (Fixed..) and hit the F10 key. You will see its contest if you click on launch.

You can also browse and install plugins if you click on help in the toolbar and select marketplace.

Exit the app.

Pentaho Report Designer

The process is similar for the Report Designer. The sourceforge folder is here. The latest version is 5.0.1-stable. You can download prd-ce-5.0.1-stable.zip directly.

Download the file and unzip it on your Pentaho5 folder (right click an unzip it). You should now have a /Pentaho5/report-designer directory.

To test the application;

  • On linux:
    Open a terminal window, change to the the app folder, something like
    cd Pentaho5/report-designer
    and then type
    ./report-designer.sh
  • On Windows:
    Double click on report-designer.bat.

You will notice the desinger elemnets tools on your left, and all cramped on your right the Structure Tab of your report and its elements in the bottom and Data Tab to add your data sources and parameters.

There are a lot of samples in the directory Pentaho5/report-designer/samples from invoice status, sales sumaries, charts or advanced html. The inventory.prpt on the operational reports folder is a nice example. You can execute them by clicking on the green ‘play’ icon in the toolbar.

Exit the app.

Pentaho Metadata

The process of installation is the same as in the previous examples. The sourceforge folder is here. The latest version is 5.0.1-stable. You can download pme-ce-5.0.1-stable.zip directly.

Download the file and unzip it on your Pentaho5 folder (right click an unzip it). You should now have a /Pentaho5/metadata-editor directory.

To test the application;

  • On linux:
    Open a terminal window, change to the the app folder, something like
    cd Pentaho5/metadata-editor
    and then type
    ./metadata-editor.sh
  • On Windows:
    Double click on metadata-editor.bat.

You can browse the steel wheels fisical (database) model, and how it is used then in the business model for browsing from tools like the WAQR -now deprecated- or report designer.

Exit the app.

Pentaho BI Server

The sourceforge folder is here. The latest version is 5.0.1-stable. You can download biserver-ce-5.0.1-stable.zip directly.

Download the file and unzip it on your Pentaho5 folder (right click an unzip it). You should now have a /Pentaho5/biserver-ce. The ce stands for community edition that is the open source or ‘basic’ version or without the much hyped capabilities on the new marketing and videos. Dont worry its pretty capable software.

You must run the Tomcat server to allow it to ‘deploy’ its web applications. It will uncompress folders and directories and set the server to a ‘localhost’ configuration.

  • On linux:
    Open a terminal window, change to the the app folder, something like
    cd Pentaho5/biserver
    and then type
    ./start-pentaho.sh
  • On Windows:
    Double click on start-pentaho.bat.

The startup will take a few minutes. Remember that the server has versions of all the sofware installed, the web server and a database created and running in memory to allow the demo run, so be patient.

To access the login page open your browser (any moder browser is reorted to work ok) and type the URL:

 http://localhost/pentaho:8080

Browse the files, in the public section you’ll find the standar report, olap report and dashboardof the Pentaho Suite.a

You can also add very valuable plugins like saiku o the ctools dashboard web tools (cdf, cde, cda) or waqr using the marketplace option.

To ‘close’ the web server you will need to execute the stop-pentaho script.

Ending comments

Thats it, those are the tools updated by pentaho on 2013. Open source tools like Saiku Reporting are not being ported. Aggregation o Mondrian Schema Editor are being updated. Tools like the Studio Designer are deprecated.
Remember: “Change is good. You go first.” :)

Installing the database and the server as services/startup deamons is a nice option it will be addressed on future post (you can access old articles to do that on this blog). The next post will be on customization of the bi server and then on a database (MySql) redirection, notes on the new repository and then a post on program migration, so stay tunned.

You can visit the main Index.

Pentaho Visualizations

[EDIT: So sorry for not checking this out first, but this is not for us.
  Community Edition is not allowed to play. So skip this one.
  I will not delete it. It will be a reminder to look for an alternative on 2013.]

This is just a reminder for you to visit the event-page in which Pentaho is giving away visualization plug-ins for your Pentaho BI Server.

So far they have a ‘sunburst’ tool which is a double level pie graph and a timeline zoom graph.

Pentaho

(click on the image or open the link)

All they ask for is feedback about improvement or additional ideas.

Next one will be on December 19th, 02012

Pentaho CE 4.8 – BI Server Update

[This is the third edit -15th January 2013-on this post I think this is the final version so everything works]

On November 29, Pentaho (dmoran specifically) published on sourceforge the new versión of the Pentaho BI Server 4.8. That is just a few days behind the Enterprise Edition announcement, that is great news for us.

Also was made available the Data Integration, Pentaho Metadata, Report Designer applications that you should be able to update following the Tools Update post.

The most notorious inprovement in the PUC is a toolbar button to install, upgrade or delete the Saiku or C*Tools plug-ins, its called ‘The Pentaho Marketplace’. If you click on it you will see this dialog:

marketplace

If you’re server is connected to the internet you just need to click on the buttons to upgrade or install plugins!, This is the success dialog:

Dialog

The following instructions should work from upgrading the 3.5-3.10 server if you installed acording to this series, that’s the installation and MySql-DB post. If you made further modifications or want to do the things the ‘best practice way’ then you should do a ‘diff’ for the configuration files, at least the ones mentioned.

If you need details about the instructions refer to each post where it is explained in detail.

Part I: BI Server Demo Install

Just like in the first post:

  1. Download biserver-ce-4.8.0-stable.tar.gz [445.6.5 MB]
  2. Stop your server.
  3. Make a bakup of your /Pentaho/biserver-ce folder, a zip or gzip file should do it.
  4. Rename the folder to /Pentaho/biserver-ce-old.
  5. Move the dowloaded file into \Pentaho
    untar/gzip the two folders:  tar -zxvf biserver-ce-4.8.0-stable.tar.gz
  6. Change the .sh to exetutables [I didn't have to do this but in case you need it]
    Antonio suggested (on first post comments) to use:
    find Pentaho/ -type f -name ‘*.sh’ -exec chmod +x {} \;
  7. Add the java variable to the start and stop .sh files, including the ones in the administration console folders:
    export JAVA_HOME=”/usr/lib/jvm/java-6-openjdk”
    [or java-7-openjdk]
  8. Start the Server and login with joe/password to check the demo at:

    http://localhost:8080/pentaho

    Be patient it may take a few minutes

Part II: Update Plugins

[Edit: JP -in the comments- posted that this step avoids an error on dropdown parameters that happens on some systems. As it works, I suggest you follow his advice.]

If your server is without internet access, follow the C*Tools and Saiku manual install instrucctions.

  1. Click on the marketplace icon on the toolbar.
    Locate the Comunity Dashboard Framework line and click on the view details button. Click on uninstall. Close the dialog.
  2. Restart you server.
  3. Reload the settings on the browser using the menu ->Tools -> Refresh and execute all the options. Clear your browser’s cache.
  4. Click again on view details button from the CDF option, but this time Install the ‘stable’ version not the TRUNK).
  5. Go ahead click on upgrade Saiku Analytics, CDA, CDE.
  6. You need to install the Saiku Reporting add-in manually (as described on Post 3): download it and unpack it into Pentaho/biserver-ce/pentaho-solutions/system.
  7. Reboot the server.
    I hope that in the next version of the marketplace they use use checkboxes and let the thing reiterate so you don’t have to click and waif for each one.
  8. You can check that after the procedure, the plugin-samples->CDF->CDF-> Samples->Charts samples->Chart Samples: works!

Part III: Upgrade your DB and Connections

Lets update the database config files (the same ones that we modified on post 2). Open a file explorer with the biserver-ce-old and the biserver-ce, as we will be copying files from the first one to the second, here is the list.Be carefull not to overwrite the wrong ones.

  1. Shut down the server.
  2. Copy the Hibernate and Quartz DB config files:
    tomcat/webapps/pentaho/META-INF/context.xml
    tomcat/conf/Catalina/localhost/pentaho.xml
  3. Copy the Hibernate security files:
    pentaho-solutions/system/hibernate/hibernate-settings.xml
    pentaho-solutions/system/applicationContext-spring-security-hibernate.properties
    pentaho-solutions/system/applicationContext-spring-security-jdbc.xml
  4. Copy the Datasources:
    pentaho-solutions/system/simple-jndi/jdbc.properties
    pentaho-solutions/system/olap/datasources.xml
  5. Copy your drivers (files not in the new lib folder) from:
    tomcat/lib/
    In my case: mysql-connector-java-5.1.17.jar, ojdbc14.jar, orai18n.jar
  6. Disable the In Memory database from the demo, edit:
    tomcat/webapps/pentaho/WEB-INF/web.xml
    search for: BEGIN HSQLDB in two places, and remove some coment so it will look like this:
    <!– [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] –>
    and :
    <!– [BEGIN HSQLDB STARTER]
    <listener> <listener-class>org.pentaho.platform.web.http.context.HsqldbStartupListener</listener-class> </listener>
    [END HSQLDB STARTER] –>.
  7. If you jave generated metadata using tools or dowloading samples you shoud check
    biserver-ce-old/pentaho-solutions/admin/resources/metadata/
    and copy your ‘agile’ file to your new folder.

The following modifications may vary and are optional, they were mentioned in Post 5 – Your Server:

  1. Copy company logo, repace these files from your biserver-ce-old folder:
    biserver-ce/tomcat/webapps/pentaho/mantle/themes/onyx/images/logo.png
    biserver-ce/tomcat/webapps/pentaho-style/images/login/logo.png
  2. Avoid tip on ‘demo users’ at login screen, edit:
    biserver-ce/pentaho-solutions/system/pentaho.xml
    Set these properties to false:
    <login-show-users-list>false</login-show-users-list>
    <login-show-sample-users-hint>false</login-show-sample-users-hint>
  3. Increase Timeout, edit two files:
    biserver-ce/tomcat/conf/web.xml
    biserver-ce/tomcat/webapps/pentaho/WEB-INF/web.xml
    Search for session-timeout and change it from 30 to the seconds you prefer:
    <session-timeout>180</session-timeout>
    While you are editing you can add these at the bottom, just after:
    <!– insert additional resource-refs –>
    Add:
    <security-role>
       <description>security role</description>
       <role-name>PENTAHO_ADMIN</role-name>
    </security-role>
    Its removes a warning on logs at startup, but there a re too many now.
  4. Add your publishing password. Edit:
    biserver-ce/pentaho-solutions/system/publisher_config.xml
    Set the property to your password
    <publisher-password>[your-password]</publisher-password>
  5. Remove Jpivot and waqr warnings.
    Those tool are deprecated, you should be using the Saiku tools, but If you have reports running on those tool, its not nice for your clients, so get to:
    biserver-ce/tomcat/webapps/pentaho/adhoc/styles/
    add at the bottom of these files: jpivot.css and jpivotIE6.css :
    #deprecatedWarning { display: none; }
    And add to (in the same folder): adhoc.css :
    #waqrDeprecatedAlert { display: none; }
  6. On a production system, I hide the .CDA files, its no use to the end user to see the datasource in the treeview so edit:
    biserver-ce/pentaho-solutions/system/cda/plugin.xml
    And comment the lines between:
    <!–
    <content-types>

    </content-types>
    //–>
  7. If you need advanced guidelines on changing http server name and port, connection pools, config for database on other machine, modify the messages, email account setup, and automatic startup, pool connection, etc. Check Post 5 for config notes.

Part IV: Copy your Solution Folders

  1. Check for your work (solutions) in the old
    pentaho-solutions/
    and copy them to your new folder. I have two solutions and a samples from old bi server installations.
  2. If you have kept your .xmi and other configuration files to each solution folder you can upgrade this easily.

That’s it, start your server, you’re on 4.8 now. Now you’ll need to use your own user-password.

PD. Remember to reload the settings on the browser with the menu ->Tools -> Refresh and execute all the options, and clean your browser cache.

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.