Hitachi Vantara Pentaho Community Forums
Results 1 to 9 of 9

Thread: SQL Server Connection

  1. #1
    Join Date
    Mar 2008
    Posts
    2

    Default SQL Server Connection

    Does this product only work with open source databases? I downloaded it for evaluation purposes, and the reporting wizard doesn't seem to allow for ODBC connections (only JDBC?), and no SQL Server connection is offered.

  2. #2

    Default

    Well being Java based you'd need JDBC drivers, having said that I'm sure you can get a JDBC to ODBC bridge somewhere, and having said that, I know for a fact if you search around you would find that there is a JDBC driver for SQL Server.

    Tom
    This is a signature.... everyone gets it.

    Join the Unofficial Pentaho IRC channel on freenode.
    Server: chat.freenode.net Channel: ##pentaho

    Please try and make an effort and search the wiki and forums before posting!
    Checkout the Saiku, the future of Open Source Interactive OLAP(http://analytical-labs.com)

    http://mattlittle.files.wordpress.co...-bananaman.jpg

  3. #3
    Join Date
    Mar 2008
    Posts
    2

    Thumbs down Further on JDBC drivers

    Yes, I believe there is; I'm getting close to finding it at the Microsoft site, but the technical instructions for installing and configuring this driver are pretty buried.

    Food for Thought on this, as provided by someone making their first foray into the world of open source. The rap on open source as I've had it preached to me by friends at Microsoft is that the cost savings on software are nominal relative to the additional cost of experimentation, and knowledge-building required by OS. This is the case to the point that the total cost of ownership is considerably higher for OS, or so the story is told by them.

    My instincts tell me that the economics of OS are far better in the long term, but too often prohibitive in the near term. I think this driver issue is somewhat a case in point. I'm interest in evaluating the product in comparison to my current closed source option, Crystal Reports. My main interest at this point is to get a sense of what the learning curve is for the tool itself, ie, is the look-and-feel of Pentabo's report writer close enough to justify powering through the differences I have to overcome in the short-term? Perhaps, too, the interface might even be more intuitive, thus providing an immediate gain on the ease-of-use front, to go with the better long-term economics of an OS solution? One can hope...

    With that in mind, my hope was that the first thing I could do in the evaluation mode was connect to a standard ODBC source, ie, Excel, Access, SQL Server, and take the product for a ride in the world I live in. I now understand that as a java-based application, JDBC drivers are required, but with that reality in mind, why not make the availability of those drivers one of the FIRST design priorities? In other words, make this aspect of testing the product a transparent, point-and-click experience. Afterall, these data sources are the most prevalent data sources in the world at large, true? Instead, the dropdown list for available drivers provides a comprehensive list of Open Source database drivers, but none for the far more common data sources I mentioned. What's more, no instructions/help/advice/links for DIYs to find and install these drivers.

    From an opportunity cost perspective, I'm guessing I will burn anywhere from 2 to 6 hours of otherwise billable consulting time getting past a basic install issue. My TCO is racking up, and I've only just started...at any rate, those are my thoughts on the matter.

  4. #4

    Default

    There are plenty of drivers available to all manner of Databases, I agree there is a shortage of documentation in some places but also there is the end users unwillingness to actually search and spend some time sorting it out.
    The reason there isn't a drop down interface for the system is
    a) everyones setup is different, be it tomcat, jboss for the PCI
    b) licencing, for a lot of the connectors, packaging them up with the PCI is against the T&C's, go grumble to the suppliers, maybe they'll change their minds.

    Anyway as you are testing out the Report Designer, its undergoing a major rewrite as we speak(god knows it needs it) who knows maybe there will be an easier interface, similar to Kettle's, I expect so.

    Tom
    This is a signature.... everyone gets it.

    Join the Unofficial Pentaho IRC channel on freenode.
    Server: chat.freenode.net Channel: ##pentaho

    Please try and make an effort and search the wiki and forums before posting!
    Checkout the Saiku, the future of Open Source Interactive OLAP(http://analytical-labs.com)

    http://mattlittle.files.wordpress.co...-bananaman.jpg

  5. #5
    Join Date
    Mar 2003
    Posts
    8,085

    Default

    http://java.sun.com/j2se/1.3/docs/gu...ridge.doc.html

    So as Driver use:

    sun.jdbc.odbc.JdbcOdbcDriver

    As Connection-URL:

    jdbcdbc:<data-source-name>[;<attribute-name>=<attribute-value>]*

    example:
    jdbcdbc:mydb;UID=me;PWD=secret


    Well, ODBC is a relatively closed technique (despite the word "Open", which is also used, for instance in "Office Open XML" with a similar meaning ). To make ODBC work, you first have to define your ODBC connection using any native means your operating system offers, and then you have to use the JDBC-ODBC bridge to connect Java to it. As Microsoft burned itself severely on Java in the past, the Microsoft pages wont be very helpful on Java-topics. That company tries to pretend that Java does not exist and that everyone loves their own model.

    And then: Using ODBC is a dangerous thing in itself. Like SQL and JDBC, ODBC is a very complex specification, and so translating from one complex spec (JDBC) to another complex spec (ODBC) to talk about another complex spec (SQL) is bound to cause errors.

    The fact that we ship with so many open-source drivers is simply caused by the fact, that only a handful of people are able to afford a SQL-Server licence (along with the Windows-Licenses) (and same for Oracle etc), while everyone can install and run one of the OpenSource alternatives. ODBC datasources may be rather common on Windows, but everyone else uses JDBC, if they run Java-Applications. If you come from a Microsoft-only world, getting used to the Enterprise-Java terms and techniques is surely not that easy, but crossing the boundary inwards - from only Unix/Java into a Microsoft-world would be equally hard. The Wiki contains some documentation that might help to get you started easier: http://wiki.pentaho.org/display/Repo...eport+Designer

    For the sake of ease-of-use, you should definitely use the latest and greatest version of the report-designer (that is 1.7.0-M1), as this version already contains many usability-improvements from our "reporting-ease-of-use" development sprint.
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

  6. #6

    Default Yes, it works.

    cobbtom-

    I can tell you with certainty that Pentaho works with MS-SQL because that's what I run in my shop.

    However, I'm using JDBC drivers and JNDI connections, rather than ODBC, since, as you pointed out, Pentaho doesn't support ODBC natively (someone correct me if I'm mistaken).

    As Taqua pointed out, MS doesn't allow re-distribution of their binaries, so Pentaho can't ship MS compatibilty out of the box without paying lots of money.

    However, adding MS SQL JDBC is fairly simple; below is the approach I used, and I'm not saying its the best way to do it, but it worked for me.

    First, read N. Goodman's blog on the topic:

    http://www.nicholasgoodman.com/bt/bl...-own-database/

    Then (per his instructions, boiled down to the bare minimum) follow the steps below:

    1. Download the MS JDBC driver; there are a bunch of versions, so YMMV. I'm using the version labelled "SQL Server 2000 Driver for JDBC". Go to Microsoft's download site, and search for "JDBC". Obviously this only works if you're running a MS database; other vendors need to provide their own drivers.

    2. After extracting the JDBC driver, it will contain either one or more JAR files; these are important.The version I'm using has three files: msbase.jar, mssqlserver.jar and msutil.jar. Newer versions look like they only contain a single JAR file.

    These JAR files will need to be placed in the appropriate place for whatever program your using, be it the report designer, Pentaho, or the Action Sequence Editor. For example, in the Report Designer, there is a "lib" directory that these JAR's need to go in. In Pentaho, it's jboss\server\default\lib.

    3. You'll need to provide the connection info (See Mr. Goodman's blog; he covers creating a JNDI datasource for Pentaho, but you'll need the same information in Report Designer)

    That's about it; once you've installed the JAR files, and provided the connection strings, you're ready to connect to your data. Granted, its not quite as simple as selecting "Microsoft SQL Server" from a drop down, but once you've done it once or twice, it's no big deal.


    -d-
    Have you hugged your computer geek today?

  7. #7

    Default

    Nice instructions dherzog but your failing to realise that would tak e time and effort reading and implementing by the end user, which just isn't good enough

    Tom
    This is a signature.... everyone gets it.

    Join the Unofficial Pentaho IRC channel on freenode.
    Server: chat.freenode.net Channel: ##pentaho

    Please try and make an effort and search the wiki and forums before posting!
    Checkout the Saiku, the future of Open Source Interactive OLAP(http://analytical-labs.com)

    http://mattlittle.files.wordpress.co...-bananaman.jpg

  8. #8

    Default

    Now Tom-

    <sarcasm>
    I don't know where you'd get an idea like that. People that don't search Google, the forums and the wiki for answers? Inconceivable... Why, if that were the case, you'd spend all day answering the same questions over and over.
    </sarcasm>

    Have you hugged your computer geek today?

  9. #9

    Default

    <sarcasm>I know, good job I don't have to do it, that would be a waste of time</sarcasm>
    This is a signature.... everyone gets it.

    Join the Unofficial Pentaho IRC channel on freenode.
    Server: chat.freenode.net Channel: ##pentaho

    Please try and make an effort and search the wiki and forums before posting!
    Checkout the Saiku, the future of Open Source Interactive OLAP(http://analytical-labs.com)

    http://mattlittle.files.wordpress.co...-bananaman.jpg

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.