Getting Started with ODBO Connect

What is ODBO Connect?

ODBO Connect is a connector for Excel that uses XMLA connectivity to run MDX queries on an JasperAnalysis cube. In effect, this product provides an alternative User Interface to the JPivot interface implementation in JasperAnalysis.

This page will focus on things that may not be documented. The documentation and release notes should be referenced.

System Requirements

ODBO Connect requires the followin:

• Excel 2007 : it works with Excel 2003 (SP3 minimum) but with some limitations
• Microsoft .Net framework

Installation

The installation of the ODBO driver is very straight forward. Simply follow these steps:

1. Run "Jaspersoft ODBO Connect.msi" file and choose Install.
2. When prompted, enter your license key (Note: It is recommended that you use at least a temporary key. If a key is not provided, access to the software will end after 30 days and will not be able to be extended.)
3. Reboot your computer once the installation is complete.

Configuration

Basic configuration

See the documentation provided for more details (with screenshot) Jaspersoft_ODBO_Connect_User_Guide.pdf

Note: Since we introduced Multi-tenancy in v3.5, the username must contain the organization id. For example, if you wish to connect with demo user, enter the following as your username: demo|organization_1

Optional Configurations

Saving password is no longer mandatory since ODBO v1.0.2 :

As excel saves thepassword in 'clear' in the .odc file this could be a security issue. From this version you can check or not check the box save password when you finish the connexion.

If you don't check the save password box (i strongly recommand this choice) the user will be prompted for the password once per session - means when the excel sheet is loaded and when the user tries to use the cube.

Disabling cache :

There is a easter egg in ODBO connect : you can disable the cache option.

When a dimention changes (add a country or something else ...) you have to close excel and restart it to see the change. This is due to the cache witch prevent discovery queries each time you expand/collapse a dimention. In fact using cache speeds up the execution time (3s with cache VS 15s without).

BUT in some cases cubes could change quite frequently and users need to get updates just collapsing/expanding hierachies without having to exit and reload their excel sheet.

To deactivate the cache : when creating the connexion add ?disableCache

(example http://localhost:8080/jasperserver-pro/xmla?disableCache)

Using ODBO Connect

ODBO Connect functionality is really the same as the excel pivot table with the data coming from your cube.

Accessing Data not Displayed in the Pivot Table

The following is a cool feature of the driver (witch is not directly documented).

Using the CUBEVALUE formula in excel will give you access to data that is not displayed in the pivot table.

Here is a sample of a formula to use with the sugarcrm cube sample:

=CUBEVALUE("SugarCRM SalesAnalysis","Measures.Avg Sale Amount","Account Location.All Locations")

See also how excel2007 display the mesures in a context menu while you type the query (this is a feature that many people find very powerful)

Finally you can get that kind of output (pivot table and a cube value formula) quite easilly :

Good to know ...

How to install a multi-user key for large deployement

In cases where you have purchased a multi-user key for ODBO Connect, it may be helpful to distribute the key as a registry setting to update several machines with the license key.  There are desktop managment tools that work well with updating registry settings, but end users can also update their registry easily by simply double clicking on the exported registry entry.

1. Install the product and the license key on a single machine
2. Run "regedit" and export the registry entry contained in

   HKEY_CLASSES_ROOTCLSID{DBAD6952-E12C-4e5b-B2D4-E9CF9AF5B5BD} .

3. The resulting file can then be sent to all users. The users can merge the entry into their registry prior to installing the ODBO Connect.

How to re-install a key if you get error installing a new one

• First of all uninstall the product, reboot, then reinstall it.
• In Case this doesn't work :

   

  Delete the following registry key and then re-install:

  This is in the HKEY_CLASS_ROOT: CLSID{DBAD6952-E12C-4e5b-B2D4-E9CF9AF5B5BD}

   

• As a last resort, if this still doesn't work you can try :
• Uninstall com.cincom.xmla.bridge dans assembly .
• run regsvr32 /u bioledb.dll (all dll will be here)
• Remove the directory where the program is installed.

  The program is usually located here C:Program FilesJasperSoftJaspersoft ODBO Connect

Release notes:

This contains important informations about changes since the first release ... please have a look at it as this could contain the solution to your problem. ODBO Connect release notes:

JasperSoft ODBO Connect - Release Notes
---------------------------------------

--------------------------------------------
April 2013 Version 1.0.9
--------------------------------------------
- Fixed problem with Grand Total (Visual ToTal) update in filter.

--------------------------------------------
December 2012 Version 1.0.8
--------------------------------------------

- Fixed Excel error "An operation that uses the database driver could not be completed".
- 64 bit version is available.
- Fixed Showing dimension properties.
- Fixed bug with number format that contains currency sign other than the dollar ($) sign.
- Fixed bug when hierarchy name and dimension name match.
- Allowed log settings after connecting.

Known issues:
-------------
When both 32-bit and 64-bit versions are installed on the same machine, uninstalling one
version also uninstalls some of the components of the other version. You must reinstall
or repair the version that you wish to keep. For example, if you install both
64-bit and 32-bit versions on the same machine, uninstalling the 64-bit version will
cause the 32-bit version to not work correctly. You must
re-install or repair the 32-bit version, and vice versa.


August 2011 Version 1.0.7
--------------------------------------------

- Fixed bugs in the XMLA Discover layer.
- Used 64 bit data types.

--------------------------------------------
February 2011 Version 1.0.6
--------------------------------------------

- Fixed DLL load error when a user clicks on cancel to get 30 days trial.
- Added logic for IRowsetLocate.

----------------------------
March 2009 Version 1.0.4
----------------------------

- Fixed problem with user authorization when incorrect password entered.  When an incorrect
  password was enetered, the system was not re-prompting for the password properly.

----------------------------
January 2009 Version 1.0.3
----------------------------

- Fixed problem with character encoding.

----------------------------
December 2008 Version 1.0.2
----------------------------

- Fixed various problems dealing with incorrect numbers returned from the XMLA server.

- Allow saving connection information without saving the password.  User will be prompted for the password
  if a username is specified but no password.



----------------------------
Novemeber 2008 Version 1.0.1
----------------------------

- Fixed a bug that was preventing the proper locale to be passed to the XMLA Server.

- Fixed a problem dealing with reserved XML characters.

- Allowed disabling of the metadata cache by appending ?disableCache to the connection URL.
   (Example: http://localhost:8080/jasperserver-pro/xmla?disableCache )
   
----------------------
June 2008  Version 1.0
----------------------

Initial Release