Real-time Integration of Excel Spreadsheets

Within an Oracle Database

Using Periscope

Goal

1. Real-time Integration of Excel spreadsheets with Oracle

2. Automate the cross updating of Excel spreadsheets, driven from a central Oracle server

Benefits

� Excel spreadsheets become another real-time database source within Oracle

� Excel spreadsheet data can now be accessed by any application that can connect to an Oracle database

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

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

Technical Approach

This approach involves establishing two distinct data access functions, depending upon the direction of the data flow:

1. Excel Data Publish enables the real-time access of Excel spreadsheet cells from an Oracle database

2. Excel Data Retrieval enables an Excel spreadsheet to populate its cells real-time from an Oracle database

The following diagrams break down the approach for each data direction. We also diagram the combined solution when used within a single Excel spreadsheet.

	Excel Data Publish involves the real-time integration of Excel Spreadsheet data directly into an Oracle table. As data is modified within a spreadsheet, it immediately becomes available within an Oracle table to be used across the enterprise. We use Periscope/Oracle in combination with a JDBC-to-ODBC Bridge to access Excel spreadsheets and setup virtual access within Oracle. Periscope accesses Excel data thru the Microsoft Excel ODBC connection. The Excel data is transformed into an Oracle view and can be accessed by any application that can connect to the Oracle database.


	Excel Data Retrieval involves the real-time access of Oracle data from within an Excel Spreadsheet. When a spreadsheet is opened, its cells will be populated from data directly out of an Oracle table. We use the Excel �Get External Database� query from within the Excel spreadsheet to directly access Oracle tables thru the Oracle ODBC Connector.

	The Combined Solution involves both Excel Data Publish and Excel Data Retrieval. By combining both techniques, Excel spreadsheets can be used as a valid source of data input to a data warehouse, and enterprise data can be used to reflect real-time values within Excel spreadsheets. � Excel spreadsheets are virtualized within Periscope and become published tables within the Oracle database. � All Excel data is immediately available to any and all applications and spreadsheets that access the Oracle database. � Excel spreadsheets refresh external data from the Oracle database when opened. This external data can also be virtualized data from other Excel spreadsheets.

Technical Limitations

Although it would be ideal to update the individual cells of an Excel spreadsheet directly from Oracle, the underlying structure of Excel does not support external updates. The mechanism that Periscope would normally use is the Microsoft ODBC connector to Excel. This connection supports data READ access, but yields inconsistent results when attempting to INSERT, UPDATE, or DELETE within Excel. Excel prefers to access external data then refresh the cells on the sheet.

Excel spreadsheets can be used to select data directly from an Oracle database, �pulling� the external data into the spreadsheet. If you wish to create a new Excel spreadsheet directly from the Oracle database, it becomes a simple task to write a Visual Basic program to access Oracle data and directly build a new Excel spreadsheet.

In Partnership with