Real-time Access of Microsoft Excel Worksheet Cells

From an Oracle Database

Using Periscope

Goal

Real-time seamless access of Microsoft Excel Worksheet Cells from an Oracle database

Benefits

� Microsoft Excel Worksheet cells become another real-time database source within Oracle

� Excel Worksheet cells can now be accessed by any application that can connect to the Oracle database

� Excel Worksheet data can be �joined� with other business data, and made available thru Oracle tables

� Excel data does not have to be replicated to Oracle, but can be accessed real-time out of the Oracle database.

� Excel data is transformed into an Oracle view, so that Oracle applications can work with consistent data types.

� Business Units can continue to use Excel Worksheets, while enterprise applications have full access from an Oracle standard

� Reports can be generated out of Oracle that contain a real-time snapshot of all data across the enterprise

There are four major components to the Microsoft Excel Periscope connection:

1. Microsoft Office Excel ODBC driver

2. Easysoft JDBC to ODBC Bridge

3. Periscope Administration

4. Periscope Universal Database Access

Microsoft Office Excel ODBC driver

Periscope uses the Microsoft Office Excel ODBC driver for direct Excel Worksheet access. The ODBC driver comes as a standard part of Microsoft Office Excel and is maintained by Microsoft. Using this ODBC driver ensures the closest possible match-up between Excel and Oracle.

The Microsoft Office Excel ODBC driver runs as a service under the Microsoft ODBC Data Source Administrator. The ODBC driver is loaded within the administrator, which then makes it available for Periscope access.

The ODBC driver permits a User to name contiguous portions of Excel Worksheets. The named Worksheet regions then become available for real-time data selection by Periscope.

Easysoft JDBC to ODBC Bridge

Periscope uses the Easysoft JDBC to ODBC Bridge to map real-time JDBC requests to ODBC requests. Easysoft communicates directly with the Microsoft Office Excel ODBC driver. The Easysoft driver is loaded directly into the Oracle database and is managed by the Periscope product.

Periscope Administration

The Periscope Administration package runs within the Oracle OC4J container as a web service. Periscope Admin is responsible for the definition of all database connection gateways, and for all Oracle code generation necessary to implement virtual database access to a remote database.

Periscope Admin is active only for system configuration. Once a remote database has been �virtualized�, the Periscope Admin tool is not required to be active and can be terminated.

Configuration of virtual database access requires no programming and typically takes less than a couple of minutes for a table.

Periscope Universal Database Access

Periscope implements Universal Database Access to any disparate data source that a driver has been configured for. Periscope is installed into the Oracle database. Drivers for the disparate data source can be ODBC, JDBC, or of a custom type. JDBC drivers can be loaded directly into Oracle if the JDBC driver JAVA version matches the JVM version within the Oracle database. JDBC drivers can also be loaded into OC4J and accessed from Oracle as a web service. We use this method if the JDBC driver JAVA version does not match the Oracle JVM version.

Once Periscope has been installed into Oracle, we use the Periscope Admin tool to �virtualize� a disparate database into virtual Oracle tables. These Periscope driven tables appear as local Oracle tables, but are actually virtual connections into the disparate database source.

Once a disparate database has been virtualized within Oracle, it can be accessed thru normal Oracle functions with few restrictions.

When an application selects data from the Periscope/Oracle table, Oracle invokes Periscope. Periscope maps the IO request thru the appropriate driver back to the disparate data source to be executed. The result of the IO is returned to Periscope, which then transforms the data back into a normal Oracle view. All Periscope driven data accessed thru Oracle will appear as normal Oracle tables and will be completely transparent to the application.

While Periscope is complex, the actual overhead of Periscope against a virtualized table ranges anywhere from 10ms to 150ms for the actual SQL request. Since database access is typically performed against larger subsets of the database, actual overhead per record retrieved is typically insignificant.

Technical Limitations

The Microsoft Office Excel ODBC driver supports all data type transfers from an Excel Worksheet into an Oracle table. Cell access is real-time.

The ODBC driver does not cleanly support the update of Excel Worksheet cells, and is not supported. Excel supports direct ODBC access of data for loading data into Excel. When a Worksheet is opened, cells can be refreshed directly from Oracle with this method.

The combination of Periscope for Excel access, and ODBC for Excel refresh, give the user a �full function� solution for real-time Excel Worksheet access from an Oracle database.

In Partnership with