Hitachi Vantara Pentaho Community Forums
Results 1 to 10 of 10

Thread: How to use your Own Data???

  1. #1
    Join Date
    Feb 2006
    Posts
    1

    Default How to use your Own Data???

    Hi All,

    Pentaho has plenty of very impressive BI functions but it is an island. An island simply because despite a day or so of trying I still have no real idea on how to use it with MY data. Afterall this is the point of reporting.

    No where on this site can I find info on what ODBC or ODBO drivers it will connect to let alone HOW. Heck I can not report off anything but the sample data.

    For others looking this is as close as I have found:

    ETL Tool

    http://forums.pentaho.org/showthread.php?t=25848

    Writing my own database retrival program

    http://www.pentaho.org/index.php?opt...&limitstart=18

    Am I missing something? Or is Pentaho still in demo mode?

    Thanks for any light,
    James

  2. #2
    Join Date
    Nov 1999
    Posts
    579

    Default Re:How to use your Own Data???

    Hi James,

    Have you looked at the Creating Pentaho Solutions Guide?

    http://prdownloads.sourceforge.net/p...1.pdf?download

    In this guide, we detail how to report against your own data, and give examples against other databases.

    Some basic concepts you'll need to know though:

    a- You won't be using an ODBC or ODBO driver. The Pentaho BI Platform is a 100% pure java solution. As such, you'll be incorporating a JDBC driver.

    b- We've successfully reported against Oracle, DB2, MySQL, MS SQL 2000, Teradata, Hypersonic, Derby, and Postgre SQL.

    c- If you are having trouble locating a JDBC driver for your favorite database, I suggest you consult "the list" on the Sun site:

    http://developers.sun.com/product/jdbc/drivers

    Note that the list of driver providers for databases such as Microsoft SQL Server spans multiple pages, and is alphabetical. For this reason, the free Microsoft JDBC driver for MSSQL isn't found on the first page of drivers, but actually on the second page.

    d- If you're having specific difficulties incorporating your own data in the platform, please post the errors you're receiving (from the server log), the action sequence you're using, the JNDI datasource XML file (for JBoss), the report definition, and any other relevant information. Our community is very knowledgable, and is happy to help people get started with the platform.

    I hope this helps,

    Marc

  3. #3
    Join Date
    Sep 2006
    Posts
    2

    Default Re:How to use your Own Data???

    I have been reading the solutions guide and other info and I do have to say it is in some cases a little high level information.

    Alittle to vague I think so let me ask a couple of specific questions.

    1) I have data in Access and excel and text files.

    So what I need to do is bring them all together in a cube correct? this would be done via kettle?

    I would then use the report designer to create a report of this data cube correct?

    I would then use Design Studio to tie it to the Pentaho BI?

    Am I following this correct?

  4. #4
    Join Date
    Sep 2006
    Posts
    2

    Default Re:How to use your Own Data???

    I have been reading the solutions guide and other info and I do have to say it is in some cases a little high level information.

    Alittle to vague I think so let me ask a couple of specific questions.

    1) I have data in Access and excel and text files.

    So what I need to do is bring them all together in a cube correct? this would be done via kettle?

    I would then use the report designer to create a report of this data cube correct?

    I would then use Design Studio to tie it to the Pentaho BI?

    Am I following this correct?

  5. #5
    Join Date
    Oct 2006
    Posts
    8

    Default Re:How to use your Own Data???

    I've gone thru the "Creating_Pentaho_Solutions_1.1.1.pdf" and on page 31 it has a section called "JDBC Driver Setup". It says: "If you are using a database driver other than Hypersonic you'll need to configure the JBoss PCI to use that driver. Skip ahead to the Jasper section on JDBC Configuration for full details.".

    HOWEVER, there is no Jasper section on how to configure the JDBC.

    Am I missing something? I believe this may be an oversight in the current documentatin and the previous documentation.

    With the previous release I was able to figure it out, but I'll now need to figure it out again. I knew I should of documented this, but didn't follow my instincts. I was hoping the updated documentation would have this information.

  6. #6
    Join Date
    Nov 1999
    Posts
    579

    Default Re:How to use your Own Data???

    I'm sorry that happened in the documentation. Here's the quick and simple way.

    The PCI ships with several datasource XML files in the jboss/server/default/deploy folder that we provide to make this easy. Right now, they're named as follows:

    datasourcex-ds.xml where 'x' is a number from 1 to 5. All you have to do is modify one of them to point to your datasource. Then, in your action sequence, you can use the jndi name datasourcex.

    Example procedure:

    1- Stop the PCI

    2- edit datasource1-ds.xml in a text editor.

    3- Set the connection-url, driver-class, user-name and password

    4- Save datasource1-ds.xml

    5- Copy your JDBC driver into the jboss/server/default/lib directory

    6- Restart the server

    At this point, you can set the JNDI name in the action sequence to be datasource1.

    The component definition for your JasperReportsComponent should look like this:

    Code:
    <component-definition> 
            <jndi>datasource1</jndi> 
    </component-definition>
    That's it.

    Sorry it is incorrect in the documentation - I've created a JIRA case to get it fixed: PLATFORM-708.

  7. #7
    Join Date
    Oct 2006
    Posts
    8

    Default Re:How to use your Own Data???

    Great, thank you!! I've also been working on my documentation and will include this in my docs for an Oracle implementation. Thanks.

  8. #8
    Join Date
    Oct 2006
    Posts
    8

    Default Re:How to use your Own Data???

    What you said should work. The report wizard works just fine (can connect to the database and create a report/preview). However, I'm getting the following error message when trying to run through the PCI:

    Error: ConnectFactory.ERROR_0001 - Database connection could not be established to: pinnacle - org.jboss.util.NestedSQLException: Could not create connection; - nested throwable: (org.jboss.resource.JBossResourceException: Failed to register driver for: oracle.jdbc.driver.OracleDriver; - nested throwable: (java.lang.ClassNotFoundException: oracle.jdbc.driver.OracleDriver)); - nested throwable: (org.jboss.resource.JBossResourceException: Could not create connection; - nested throwable: (org.jboss.resource.JBossResourceException: Failed to register driver for: oracle.jdbc.driver.OracleDriver; - nested throwable: (java.lang.ClassNotFoundException: oracle.jdbc.driver.OracleDriver))) (org.pentaho.plugin.sql.SQLLookupRule)

    I believe I have done everything correctly (this is what I did with the previous version and it worked fine):

    web.xml has the following:
    <resource-ref>
    <description>pinnacle</description>
    <res-ref-name>jdbc/pinnacle</res-ref-name>
    <res-type>javax.sql.DataSource</res-type>
    <res-auth>Container</res-auth>
    </resource-ref>

    jboss-web.xml has the following:
    <resource-ref>
    <res-ref-name>jdbc/pinnacle</res-ref-name>
    <res-type>javax.sql.DataSource</res-type>
    <jndi-name>java:/pinnacle</jndi-name>
    </resource-ref>

    Created a datasource file (pinnacle-ds.xml) with the following:
    <?xml version="1.0" encoding="UTF-8"?>
    <datasources>
    <local-tx-datasource>
    <jndi-name>pinnacle</jndi-name>
    <connection-url>jdbcracle:thin:@oracleprod.morinda.com:1521rod</connection-url>
    <driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
    <user-name>test</user-name>
    <password>password</password>
    <check-valid-connection-sql>select count(*) from dual</check-valid-connection-sql>
    </local-tx-datasource>
    </datasources>

    Lastly put the jdbc driver in the jboss/lib directory and updated my CLASSPATH.

    Can you see anything that is wrong with what I have done?

  9. #9
    Join Date
    Nov 1999
    Posts
    579

    Default Re:How to use your Own Data???

    Yep - the driver .jar shouldn't go in the jboss/lib. It should go in the jboss/server/default/lib.

    I hope this helps,

    Marc

  10. #10
    Join Date
    Oct 2006
    Posts
    8

    Default Re:How to use your Own Data???

    THANK YOU. I knew I was missing something. That resolved the issue and everything is working fine now. I'll update my doc and post my entire document on how to get the Oracle JDBC driver working. Hopefully this will be a help to others.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.