Community Server

  • Subscribe to our RSS feed.
  • Twitter
  • StumbleUpon
  • Reddit
  • Facebook
  • Digg

Thursday, 23 July 2009

Pentaho Xactions: Prepare your data for the Google Visualization

Posted on 08:02 by Unknown
Pentaho Xactions: Prepare your data for the Google Visualization API


We want to create a line chart like the one shown in http://code.google.com/apis/visualization/documentation/gallery/linechart.html . Take a good look at the code below the chart, this is the one that we will try to prepare here.

Let's kick off with a simple MDX query that will display the amount of new registrations for the period 2009 Week 10 to Week 22:

SELECT 
[Country].Children
ON COLUMNS,
{[Registration Time Weekly].[2009].[10]:[Registration Time Weekly].[2009].[22]}*{[Measures].[New Registrations]}
ON ROWS
FROM
[Users]


I created a "Get Data From OLAP" step in my Xaction in the Pentaho Design Studio and inserted this query plus specified all other necessary parameters.

This step will bring back a query result. Name the result "query_result". To check what it looks like, you can drag and drop the "query_result" into the "Process Outputs" area. Save the file and execute it on the Pentaho BI Server. Now you will get a vague idea how the data looks like.

Now, let's write some JavaScript. Create a new step by choosing "Get data from > JavaScript". For the script inputs choose our "query_result". Now paste following JavaScript code into the main text area.

It is important to understand that x and y axis titles are stored in the metadata object. As we need these titles for the Google Chart definition, we will have to read them out. 
In the y axis, we have actually two titles, the first one being "New Registrations" and the second one being the week numbers. We will have to add the week numbers to our main dataset that we pass on to the Google chart definition. We will use getRowHeaders() and a loop to read out these titles. It is worth mentioning that in the array the week numbers will be in the first column and "New Registrations" in the second column (so actually in the opposite way as it is displayed). 

Let's start with our JavaScript:

var thedata="";

// we get a simple row and column count of the data set
rowCount=query_result.getRowCount();
colCount=query_result.getColumnCount();

var meta = query_result.getMetaData();

// get the column headers
var colHeaders = meta.getColumnHeaders(); //getColumnHeaders() returns object[][]

var colName="";

// create the code for the google chart definition
var i = 0;
for ( i = 0 ; i < colCount ; i++ ){
   colName+="data.addColumn('number','"+colHeaders[0][i]+"');\n"
     // use colName for something
}

var rowHeaders = meta.getRowHeaders(); // returns object[][]

var rowName="";

var i = 0;
for ( i = 0 ; i < rowCount ; i++ ){
// get the second row of the array as there are two dimensions in the y axis
   rowName+=rowHeaders[i][0]+"\n"
}


It is worth testing the x and y axis title output separately before going ahead. Make sure that everything looks fine! This is the first part. Define calName as a script output, then drag and drop "colName" from the "Process Actions" area to the "Process Outputs" area. Save everything and execute the Xaction on the BI Server. You will see that we have now created some part of the code that we will use in the Google Visulatization Chart definition. Do the same test for "rowName", and if everything is fine, carry on with this code:

// loop trough each row and column
for(row=0; row<rowCount; row++)
{
thedata+="data.setValue("+row+",0,'"+rowHeaders[row][0]+"');\n"
    for(column=0; column<colCount; column++)
    {
        // check if it is not a number
        if(isNaN(query_result.getValueAt(row,column))) {
        // now we prepare the string for that our Google Visualization Chart expects - > for text
        // Important: the column index has to be increase by one as we have made the week axis title as our first column!
        thedata+= "data.setValue("+row+","+(column+1)+",'"+query_result.getValueAt(row,column)+"');\n"
        }
        else
        {
        // now we prepare the string for that our Google Visualization Chart expects - > for numbers
        // Important: the column index has to be increase by one as we have made the week axis title as our first column!
        thedata+= "data.setValue("+row+","+(column+1)+","+query_result.getValueAt(row,column)+");\n"
        }
    }
}

Test the output for "thedata" and check if it is in line with the Google Chart definition.

Now, let's create a Message template to prepare the final html output. Define colName, rowCount and thedata as input. Insert this code into the main text area:

<html>
  <head>
    <script type="text/javascript" src="http://www.google.com/jsapi"></script>
    <script type="text/javascript">

//-------------------- Column Chart ------------------//
      google.load("visualization", "1", {packages:["linechart"]});
      google.setOnLoadCallback(drawChart);
      function drawChart() {
        var data = new google.visualization.DataTable();
        data.addColumn('string', 'Week');
// get column heads from dataset metadata
{colName}
        data.addRows({rowCount});

{thedata}

        var chart = new google.visualization.LineChart(document.getElementById('chart_div'));
        chart.draw(data, {width: 400, height: 240, legend: 'bottom',  title: 'New Registrations By Channel Last 7 Days'});
      }
    </script>
  </head>

  <body>
<div id="chart_div" style="float:left;clear:both"></div>

  </body>
</html>


Then define the output name as "out". Drag and drop it into the Process Outputs area (make sure you delete any other outputs before), then click on out in the Process Outputs area and change the "Name" from "out" to "content" in the Output Destination.

That's it. Save everything and then you should have a fancy Google line chart working.

You can download the file from here (it's not exactly the same, but quite similar).

Please note: If you are using SQL, the JavaScript has to be different. I provide one example using SQL here.








 



Email ThisBlogThis!Share to XShare to Facebook
Posted in Google Charts, Google Visualization API, Pentaho, Xactions | No comments
Newer Post Home

0 comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Popular Posts

  • Pentaho Kettle Parameters and Variables: Tips and Tricks
    Pentaho Kettle Parameters and Variables: Tips and Tricks This blog post is not intended to be a formal introduction to using parameters and ...
  • Using Parameters in Pentaho Report Designer
    Using Parameters in Pentaho Report Designer Introduction How to define a parameter Additional info about the new parameter dialog Hidden Par...
  • Pentaho Data Integration: Scheduling and command line arguments
    Pentaho Data Integration (Kettle): Command line arguments and scheduling Tutorial Details Software: PDI/Kettle 4.1 (download here ), MySQL S...
  • Jaspersoft iReport: How to pass a parameter to a sub-dataset
    Jaspersoft iReport: How to pass a parameter to a sub-dataset Let’s say our main report is grouped by continent and the details band holds sa...
  • Using regular expressions with Pentah...
    Using regular expressions with Pentaho Data Integration (Kettle) There are quite some transformations steps that allow you to work with regu...
  • Pentaho Data Integration and Infinidb Series: Bulk Upload
    Pentaho Data Integration and InfiniDB Series: Bulk Upload Introduction Prepare Tables Using mainly Kettle steps Check if file exists Setup I...
  • Pentaho Data Integration: Remote execution with Carte
    Pentaho Data Integration: Remote execution with Carte Tutorial Details Software: PDI/Kettle 4.1 (download  here ), installed on your PC and ...
  • How to create a loop in Pentaho Kettle
    I finished my first ever video tutorial! This video will demonstrate you how easy it is to create a loop in Pentaho Kettle. Enjoy!
  • Understanding the Pentaho Kettle Dimension Insert/Update Step Null Value Behaviour
    We will be using a very simple sample transformation to test the null value behaviour: We use the Data Grid step to provide some sample dat...
  • Pentaho Data Integration: Supplying Variables to Shell Scripts
    Pentaho Data Integration (Kettle): Supplying Kettle Variables to Shell Scripts Tutorial Details Software: PDI/Kettle 4.1 (download here ) Kn...

Categories

  • "Bulk Loader"
  • "Bulk Loading"
  • "Hadoop"
  • "Kettle"
  • "Pentaho Book"
  • "Pentaho Data Integration"
  • "Pentaho Kettle"
  • "Pentaho Report Designer MDX MQL JDBC Parameters How To"
  • "Pentaho Report Designer MDX Parameters"
  • "Pentaho Report Designer MQL Parameters"
  • "Pentaho Report Designer Parmaters"
  • "Pentaho Report Designer"
  • "Pentaho Reporting 3.5 for Java Developers"
  • "Pentaho Reporting Book"
  • "Routing"
  • "Schema Workbench"
  • "Testing"
  • "Unicode"
  • "Unit testing"
  • "UTF8"
  • Agile development
  • automated testing
  • Big Data
  • Book Review
  • C-Tools
  • CBF
  • Clustered transformation
  • Command Line Arguments
  • Community Build Framework
  • D3JS
  • Dashboarding
  • Data Integration
  • Data Warehouse
  • Database Change Management
  • Database Version Control
  • Date Dimension
  • DBFit
  • ETL
  • ETLFit
  • Federated database
  • Google Charts
  • Google Visualization API
  • Hadoop
  • HTML5
  • iReport
  • JasperReports
  • JasperSoft
  • JasperStudio
  • Kettle
  • Kimball
  • Loop
  • Master data management
  • Metadata
  • Metedata editor
  • Mondrian
  • multidimensional modeling
  • OLAP
  • Open Source
  • Parameter
  • Parameters
  • Pentaho
  • Pentaho BI Server
  • Pentaho Data Integration
  • Pentaho Data Integration 4 Cookbook
  • Pentaho Kettle
  • Pentaho Metadata Editor Tutorial
  • Pentaho Report Designer
  • PostgreSQL
  • PRD
  • Report Layout
  • REST
  • Routing
  • Saiku
  • Scheduling
  • Slowly Changing Dimension
  • Sqitch
  • SVG
  • Talend
  • Talend MDM
  • Talend Open Studio
  • Tutorial
  • Variable
  • Web service
  • Xactions

Blog Archive

  • ►  2013 (24)
    • ►  December (2)
    • ►  November (3)
    • ►  October (2)
    • ►  September (1)
    • ►  August (3)
    • ►  July (2)
    • ►  June (1)
    • ►  May (2)
    • ►  April (1)
    • ►  March (3)
    • ►  February (1)
    • ►  January (3)
  • ►  2012 (20)
    • ►  November (3)
    • ►  October (3)
    • ►  August (1)
    • ►  June (1)
    • ►  April (1)
    • ►  March (3)
    • ►  February (5)
    • ►  January (3)
  • ►  2011 (19)
    • ►  November (3)
    • ►  July (2)
    • ►  June (1)
    • ►  May (4)
    • ►  April (2)
    • ►  March (1)
    • ►  February (3)
    • ►  January (3)
  • ►  2010 (17)
    • ►  December (1)
    • ►  November (6)
    • ►  September (1)
    • ►  August (1)
    • ►  June (2)
    • ►  May (1)
    • ►  April (3)
    • ►  February (1)
    • ►  January (1)
  • ▼  2009 (18)
    • ►  December (3)
    • ►  November (1)
    • ►  October (5)
    • ►  September (7)
    • ▼  July (2)
      • The 10 Seconds Pentaho Metadata Editor Tutorial
      • Pentaho Xactions: Prepare your data for the Google...
Powered by Blogger.

About Me

Unknown
View my complete profile