Come hear me present Pentaho (sneak peak to my next use case)

If you are interested in seeing what I have been up to lately, please feel free to join me at 9:00 AM EST or 1:00 PM EST:


http://www.pentaho.com/resources/events/20120503-pentaho-business-analytis-4.5/

Big Data, Business Analytics, Rich Visualizations, it's all here!

This week I will have the honor to show you guys something that I think is pretty amazing...and seeing how many people are adopting our technology, many of you find amazing as well.  This week Thursday, May 3rd, I will be hosting two events, showing the power and capability of Pentaho.  So what does Big Data, Business Analytics and Rich Visualizations have in common?  Well...you will just have to register to find out!

I will give you one clue though...you will get to learn this while simultaneously learning about a compelling, real world example of Business Intelligence

Date and Time

Thursday May 3, 2012
9:00 AM ET / 14:00 GMT
10:00 AM PT / 1:00 PM ET

Register Today

Speakers

Donna Prlich
Director, Product & Solutions Marketing
Pentaho Corporation
Wayne Johnson
Senior Sales Engineer
Pentaho Corporation

Pentaho 4.5 is here

This is a very short blog and I will have more information coming very soon but you HAVE GOT to check this out...Pentaho 4.5 has new visualizations that really help build very useful analytics...use cases coming soon...plus if you want to see me in action, I will be doing worldwide webinars...see below:

• Visit pentaho.com/4.5-release to learn more about the new release and why ‘Better Together’ is best.
• Download a free 30-day fully supported trial of the Pentaho Business Analytics 4.5.
• Attend the live demo of what’s new in Pentaho Business Analytics 4.5 on May 3, 2012. Register at pentaho.com/4.5-webinar.

Processing Zip Files using Pentaho Data Integration

Recently I was on a call and the requirement was that they received zip files that contain a .csv file from their customers and needed to take the contained .csv file and put it into a database.  In this particular example, the structure of the .csv file is already known.  Currently this process is a manual process where they unzip the file, take that file and run it into a transformation that populates it into a database.  Their goal is to automate this process.  In the example below I have created a transformation that shows how Pentaho Data Integration can automatically take a zip file, unzip it, pass the contents to a transformation, process the file into a database.

The first step is to create a Job that handles the unzipping of the file

The job then passes that file to a transformation that takes the enclosed .csv file, processes it and inserts it into a database:

I have created a file for you to download a working example.  In order to run through this example you need to perform the following:

1. Download the “PDI - UNZIP AND PROCESS FILE.ZP” file and extract its’ contents into a directory called “PDI - UNZIP AND PROCESS FILE”
   
   
2. Open up Pentaho Data Integration (do not connect to the repository)
   
   
3. Open the following job: “unzip.kjb”
   
   
4. Open the following transformation: “populated database from unzipped file.ktr”
   
   
5. In the transformation, open up the “Table Output Step”
   
   
6. Select the “SQL” button and then select “Execute”, then click “ok” and “close” on the next two dialog screens.
     
1. NOTE – We are using a database that comes with Pentaho for testing purposes
   
   
7. Now you can go to the job and run the job.  The following will happen:
   
   
1. The zip file contents will placed in the main directory “PDI - UNZIP AND PROCESS FILE.ZP” and be called sales_data_{timestamp}.csv
2. The job will then take the location of that .csv file and pass it to the transformation, which will take that file and insert it into the database

DOWNLOAD SAMPLE HERE

Creating a Dynamic Target Range using Pentaho Report Designer and a Line Chart

Here is a quick example of how you can easily create a line chart with the ability to dynamically set a target range on the chart.  Here is what the end results look like:

How do you do this?  Well it is really pretty straight forward.  First you create your connection and retrieve the data you want to graph.  Then drag a chart object to your report and set the category-column, value-column, and Series-by-value to your data.  Once you have your chart reading your data set, next you will create your parameters, here is an example of the lower range parameter

Once you have your parameters created so that end users can dynamically set the range, all you need to do is add a post-processing script, you can use the script below (I've highlighted the parameters as well):


import java.awt.Color;
import java.awt.Dimension;
import java.awt.Font;
import java.awt.GradientPaint;


import javax.swing.JPanel;


import org.jfree.chart.ChartFactory;
import org.jfree.chart.ChartPanel;
import org.jfree.chart.JFreeChart;
import org.jfree.chart.axis;
import org.jfree.chart.axis.CategoryAxis;
import org.jfree.chart.axis.CategoryLabelPositions;
import org.jfree.chart.axis.NumberAxis;
import org.jfree.chart.labels.ItemLabelAnchor;
import org.jfree.chart.labels.ItemLabelPosition;
import org.jfree.chart.labels.StandardCategoryItemLabelGenerator;
import org.jfree.chart.plot.CategoryPlot;
import org.jfree.chart.plot.IntervalMarker;
import org.jfree.chart.plot.PlotOrientation;
import org.jfree.chart.renderer.category.BarRenderer;
import org.jfree.data.category.CategoryDataset;
import org.jfree.data.category.DefaultCategoryDataset;
import org.jfree.text.TextUtilities;
import org.jfree.ui.ApplicationFrame;
import org.jfree.ui.Layer;
import org.jfree.ui.RectangleAnchor;
import org.jfree.ui.RefineryUtilities;
import org.jfree.ui.TextAnchor;


CategoryPlot plot = chart.getCategoryPlot();
        plot.setBackgroundPaint(Color.lightGray);
        plot.setDomainGridlinePaint(Color.white);
        plot.setRangeGridlinePaint(Color.white);
IntervalMarker target = new IntervalMarker(dataRow.get("range1"), dataRow.get("range2"));
target.setLabel("Target Range/Goal");
target.setLabelFont(new Font("SansSerif", Font.ITALIC, 11));
target.setLabelAnchor(RectangleAnchor.LEFT);
target.setLabelTextAnchor(TextAnchor.CENTER_LEFT);
target.setPaint(new Color(222, 222, 255, 128));
plot.addRangeMarker(target, Layer.BACKGROUND);




return chart;


That is really all it takes!  If you want to see a working example of this, feel free to download the sample .prpt file here.

HAPPY CHARTING!!!

Utilize existing SQL Code for your Report Generation with Pentaho

I often get asked if developers can utilize SQL that they already use in their legacy reports within Pentaho and the answer is always a resounding YES!  There are hardly if any companies out there that have not leveraged some type of Business Intelligence, even if it is generating csv files from a SQL query within a database.  So there is a common concern when making any switch in technology, especially Business Intelligence/Analytics, and that is leveraging the work you have already done.  here is a short video demonstration that show how easy it is to leverage Pentaho Report Designer to simply cut and paste your existing SQL code and how to extend that by parameterizing the report.  Enjoy the video!

Increasing Pentaho's Data Sampling Set to Sample your entire CSV File

Pentaho offers a very valuable capability in that you can quickly and easily prototype your data using a CSV file as your source data for the prototype.  Pentaho will bring in the CSV file, automatically profile the data and set the data types and lengths.  One important thing to note here, however, is the default sampling list is set to 200 rows.  I would recommend that you increase this limit to the size of your CSV file.  For example, if you have a CSV file that contains 10,000 rows, I would increase the limit to 10,000.  The next obvious question is how do I do this?  Well, it really is easy, just follow these simple steps:

1. Open the following file in a text editor:   C:\Program Files\pentaho\server\biserver-ee\pentaho-solutions\system\data-access\settings.xml
   
2. Change the default value that is located in the following tag to the desired sampling amount:      data-access-csv-sample-rows>10000/data-access-csv-sample-rows> (in this tag I have increased it to 10000)
   
3. Restart your BI Server

This will help ensure that you have a success production of your prototype so that you can show your end users how easy Pentaho is to use to slice and dice their data, build reports and dashboards.