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