Wednesday, January 30, 2013

Pentaho Report Parameters: validate start date is before end date


You can use a Post-processing formula on a parameter to validate the paramter. A parameter can access itself and all previously defined parameters.

=IF([date1] >= [date2]; NA(); [date2])

Wednesday, January 16, 2013

Dimension lookup error with mysql 5.6 in Pentaho Report



I updated Mysql from version 5.0 to version 5.6.
The transformation works good with mysql 5.0 but Not work in 5.6

Solution :-

The solution to this problem is to upgrade to MySQL connector 5.1.20 or above.
Replace the current MySQL connector JAR in your kettle libext directory 


Ref :- http://forums.pentaho.com/showthread.php?94086-dimension-lookup-error-with-mysql-5-6-%28

Wednesday, January 9, 2013

Pentaho BI Server 3.6 in CentOS 5.3 64 bit



http://setijoagus.wordpress.com/2011/04/28/pentaho-bi-server-3-6-in-centos-5-3-64-bit/#more-1217

http://survivalguides.wordpress.com/2010/12/22/install-pentaho-bi-on-ubuntu/

http://pe-kay.blogspot.in/2013/01/shell-script-to-run-pentaho-as-service.html

https://docs.google.com/document/edit?id=12Im2tX9Qi45EpQx-MM2162uZkhQG2upT7KLZ21CvhQ8&pli=1#

http://lcardinaals.wordpress.com/2009/09/20/pentaho-bi-server-3-5-installatie-in-centos-5-3/

Tuesday, January 8, 2013

Don't hardcode host names, use JNDI in Pentaho


I had created lots of reports and published on Pentaho BI server. One day my Technical team told me our database server is going to changed. I am really very get tense now I have to change database details in every report that is very hectic and silly work.

So that time I have in mind "Don't hardcode host names, use JNDI", my best friend google always help me & found two blogs that really very help to configure JNDI (in Pentaho called as Pentaho Datasources).

JDNI is a technique to hold database connections outside of the report definition. By holding this information in a central spot, you only have to change one place and all your reports get updated. The JNDI stored connection information is stored on the computer where your report runs. This also allows you to have separate definitions based on where the report runs. Your report designer can access a local copy of your production database, while your production server uses the real database server.

Formaximum performance, you should establish your own JNDI data connections at the Web application server level, and tune them for your database.

http://forums.pentaho.com/showthread.php?135337-Multiple-PRPT-files-and-One-JDBC-connection

How to configure :-

Before changes please check JDBC library copied on following location
For PRD :- prd-ce-3.6.1-stable\report-designer\lib\jdbc

For BI Server :- biserver-ce\tomcat\common\lib
                         administration-console/jdbc
Following changes in Pentaho Report Designer :-


Follow this procedure to establish a connection to a JNDI data source.
  1. Quit Report Designer if it is currently running.
  2. Edit the .pentaho/simple-jndi/default.properties file. The .pentaho directory is in the home or user directory of the user account that runs Report Designer. If you have multiple copies of Report Designer installed to multiple user accounts, each default.properties file will have to be edited. ~/.pentaho/simple-jndi/default.properties file on Linux.                                                                  Design Studio requires that connection details be added to /pentahosolutions/system/simple-jndi/jdbc.properties
  3. Add your JNDI connection information, beginning with the JNDI name on each line, as shown in the example below:
    SampleData/type=javax.sql.DataSource            
    SampleData/driver=org.gjt.mm.mysql.Driver
    SampleData/url=jdbc:mysql://localhost:3306/mydatabase
    SampleData/user=root
    SampleData/password=root
  4. Save and close the file, then start Report Designer.
  5. To add this data source to a report, add a JDBC data source, choose JNDI as the connection type, and type in the JNDI name in the appropriate field.
Report Designer can now access your JNDI data sources.

Following changes in Pentaho BI Server (Admin Console) :-

To define the connection, start the Pentaho Administration Console. 
The console starts up on port 8099, and can be reached via the URL "http://localhost:8099/". 
The user and password are "admin" and "password" (sadly not documented anywhere in the download). 
The console can only be run successfully if the BI-Server is up and running as well.

To define a datasource, activate the "Administration" mode on the left hand side of the window and switch to the "Database Connection" tab. Hit the small white "+" to add a connection. 

In General Tab 

1. Name :-  JNDI Name as you mention in report designer file (i.e SampleData) 
2. Select Driver Class  (e.g. org.gjt.mm.mysql.Driver) The Pentaho Administration Console is smart enough to detect all installed drivers. If your driver is not listed here, you probably have forgotten to add it to the "lib" directories outlined above.
3. username :- database username   (e.g root)
4. password :- database password  (e.g. root)
Do not use # character in password because SimpleJNDI has a severe problem parsing '#' in properties - it interprets everything after that as comment, and there is no way to escape that character.

ref :- http://forums.pentaho.com/showthread.php?136121-PRD-3-9-1-JNDI-Error-but-working-in-PRD-3-6-1
5. URL :- database connection string   (e.g. jdbc:mysql://localhost:3306/mydatabase)

If you are facing issue like as :-

Invalid JNDI connection SampleData1 : 
Access denied for user 'pentaho'@'127.0.0.1' (using password: YES)
org.pentaho.di.core.exception.KettleDatabaseException: 
Error occured while trying to connect to the database

Password contains special characters is treated as comment in properties IIRC, so maybe this Jira: http://jira.pentaho.com/browse/PRD-3621

Solution :- Change the .properties File-extension to .ini and retry



Reference :- http://www.sherito.org/2011/08/dont-hardcode-host-names-use-jndi.html

http://infocenter.pentaho.com/help/index.jsp?topic=%2Freport_designer_user_guide%2Ftask_jndi_data_source.html

Monday, January 7, 2013

Introducing the Pentaho Reporting compatibility mode





http://www.sherito.org/search/label/report-designer



http://www.sherito.org/2012/12/introducing-pentaho-reporting.html

Saturday, January 5, 2013

Thursday, January 3, 2013

How to increase GROUP_CONCAT function length in MySQL

http://forums.pentaho.com/showthread.php?135082-how-to-add-maximum-length-for-GROUP_CONCAT-in-pentaho-report-designer&p=330725#post330725

GROUP_CONCAT() in a MySQL query to convert multiple rows into a single string. However, the maximum length of the result of this function is 1024 characters.

 I'm very well aware that I can change the param group_concat_max_len to increase this limit:

SET SESSION group_concat_max_len = 1000000;

Above is set for SESSION

SET GLOBAL group_concat_max_len = 1000000;

above is set for GLOBAL

ref :- http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

http://rpbouman.blogspot.in/2008/07/calculating-nth-percentile-in-mysql.html

http://rpbouman.blogspot.in/2009_03_01_archive.html

Create MongoDB service in Windows OS

1) Open Command Prompt 2) Create directory for Database and Logs                    mkdir c:\data\db                     mkdir c:\...