Tuesday Dec 02, 2008

Using Brio Intelligence to query MySQL databases on Solaris

I'm part of the engineering group responsible for building the Sun Software Library.  The deployment architecture for this site is the classic 3-tier based model, with glassfish and MySQL acting as the application server and database server, respectively.

Quite understandably, our (internal to Sun) users wanted all sorts of reports to understand the data.  As engineers, we wanted to avoid building custom reports, since we felt like we'd end up in an endless loop of "can you get me this data" and "can you get me that data".  So we looked for tools that enable our end users to query the data directly and produce the reports themselves.

There are two tools that are popular within Sun for these kind of purposes: OpenOffice Base and Brio (originally from Hyperion, which was acquired by Oracle).  We are using the following:

  • MySQL 5.0.51 Source distribution
  • Brio Intelligence v6.6.4 on a Solaris 10 based SPARC system

About Brio

This blog entry describes how to get Brio to query a MySQL database, which unfortunately was not trivial. The assumption is that Brio and MySQL are already installed, and the database contains schema and data.

Special Kudos to May Mu, Donna Ling, and Wendy Klinke at Sun for helping figure this out.

Brio Intelligence (or at least the version that we have) does not support integration with MySQL directly (the way OpenOffice Base does).  In order to get Brio to query against MySQL, you need to install the MySQL ODBC driver on your Brio system, and configure the ODBC driver to query MySQL.  This is not hard to do, just follow these steps:

  1. Download the MySQL ODBC driver.  You specifically need version 3.51.06, which comes in a file named "MyODBC-3.51.06-sun-solaris2.9-sparc.tar.gz".  I tried it with the latest version (3.51.27), and it doesn't work, as this later version seems to be trying to link against libodbcinst.so.1.  This issue doesn't exist with the older version of the driver.

  2. Unpack the file by typing in "gunzip < MyODBC-3.51.06-sun-solaris2.9-sparc.tar.gz | tar xfvp -", as follows:
    $ gunzip < MyODBC-3.51.06-sun-solaris2.9-sparc.tar.gz | tar xfvp -
    x MyODBC-3.51.06-sun-solaris2.9-sparc, 0 bytes, 0 tape blocks
    x MyODBC-3.51.06-sun-solaris2.9-sparc/ChangeLog, 12595 bytes, 25 tape blocks
    x MyODBC-3.51.06-sun-solaris2.9-sparc/COPYING, 19106 bytes, 38 tape blocks
    x MyODBC-3.51.06-sun-solaris2.9-sparc/README, 1949 bytes, 4 tape blocks
    x MyODBC-3.51.06-sun-solaris2.9-sparc/INSTALL-BINARY, 5253 bytes, 11 tape blocks
    x MyODBC-3.51.06-sun-solaris2.9-sparc/RELEASE-NOTES, 5478 bytes, 11 tape blocks
    x MyODBC-3.51.06-sun-solaris2.9-sparc/odbc.ini, 851 bytes, 2 tape blocks
    x MyODBC-3.51.06-sun-solaris2.9-sparc/libmyodbc3-3.51.06.so, 354904 bytes, 694 tape blocks
    x MyODBC-3.51.06-sun-solaris2.9-sparc/libmyodbc3.a, 187388 bytes, 366 tape blocks
    x MyODBC-3.51.06-sun-solaris2.9-sparc/libmyodbc3.la, 839 bytes, 2 tape blocks
    x MyODBC-3.51.06-sun-solaris2.9-sparc/libmyodbc3.lai, 840 bytes, 2 tape blocks
    x MyODBC-3.51.06-sun-solaris2.9-sparc/libmyodbc3_r-3.51.06.so, 359276 bytes, 702 tape blocks
    x MyODBC-3.51.06-sun-solaris2.9-sparc/libmyodbc3_r.a, 187388 bytes, 366 tape blocks
    x MyODBC-3.51.06-sun-solaris2.9-sparc/libmyodbc3_r.la, 855 bytes, 2 tape blocks
    x MyODBC-3.51.06-sun-solaris2.9-sparc/libmyodbc3_r.lai, 856 bytes, 2 tape blocks
    x MyODBC-3.51.06-sun-solaris2.9-sparc/libmyodbc3.so symbolic link to libmyodbc3-3.51.06.so
    x MyODBC-3.51.06-sun-solaris2.9-sparc/libmyodbc3_r.so symbolic link to libmyodbc3_r-3.51.06.so      
  3. You now need to edit the odbc.ini file that came with the driver to include information about your database.  You can use your favorite text editor for this, I personally use Emacs.  The odbc.ini file looks as follows (the same file demonstrates how to set up multiple databases, I only include one for simplicity):
    ;  odbc.ini configuration for MyODBC and MyODBC 3.51 Drivers
    [ODBC Data Sources]
    database_name      = database_description
    Driver       = /home/as32693/downloads/MyODBC-3.51.06-sun-solaris2.9-sparc/libmyodbc3.so
    Description  = Connector/ODBC 3.51 Driver DSN
    SERVER       = bco132.central.sun.com
    PORT         = 3306
    USER         = reports
    Password     = 
    Database     = catalog_reporting
    OPTION       = 3
    SOCKET       = 
    You should replace the database_name, database_description, and SERVER, USER, and Database options with the appropriate values.  database_name and database_description can be anything you want (they show up inside of Brio, when you are prompted to choose the database to query).  You need to change the Driver to the actual directory you are using.  SERVER, USER, and Database need to be valid, your MySQL DBA can provide you this information.

  4. You now need to set two environment variables that the MyODBC library needs.  These can be put into your appropriate files (e.g. .cshrc or .bashrc).  I personally put them into a file named "run-brio" as follows:
    export ODBCSYSINI=/home/as32693/downloads/MyODBC-3.51.06-sun-solaris2.9-sparc
    export ODBCINI=$ODBCSYSINI/odbc.ini
  5. And now you can run Brio by typing "run-brio", and configure it to use an ODBC connection.  The following sequence of images demonstrate this.

  6. Select ODBC as the "Connection Software" and "Type of Database".

  7. When prompted, select your database host (this is the same name that was in the odbc.ini file), and type in your username and password.

  8. If you get the following error message: 'MySQL][ODBC 3.51 Driver]Client does not support authentication protocol requested by server: consider upgrading MySQL client", which looks like the following:

    Then you need to ask your MySQL administrator to change the authentication method on the user provided, as described in this post http://forums.mysql.com/read.php?38,2511,2828#msg-2828 and http://dev.mysql.com/doc/refman/5.0/en/old-client.html.  It's also quite possible that the later ODBC drivers work with Brio, and support the latest authentication schemes.  If you get this to work, let me know.

  9. If your username and password are accepted, press "finish", you'll be prompted to save the OCE file.  If you plan on reporting against this database frequently, you should save the OCE file, so you can connect to this database through it every time.

  10. And you are now all set!  Here is how Brio looks like connecting to a MySQL database.  The attributes table is unique to our schema.

I hope this is useful to you.


Ari Shamash is the software engineering manager of the Sun Software Library engineering team at Sun Microsystems.


« August 2016