1
Getting Started with Express Spreadsheet Add-In

Chapter summary

This chapter describes how to get started with Oracle^® Express Spreadsheet Add-In (hereinafter referred to as "Express Spreadsheet Add-In") .

List of topics

This chapter includes the following topics:

What Is Express Spreadsheet Add-In?

Working with Express data in a spreadsheet

Express Spreadsheet Add-In is an add-in to Microsoft Excel. The add-in allows you to fetch data from one or more Express databases and work with the data in a familiar spreadsheet environment.

Retrieving data from Express databases

You can use the add-in to retrieve data from the following types of Express databases:

Local databases that are accessed using Personal Express release 6.x
Remote databases that are accessed using Oracle Express Server (hereinafter referred to as "Express Server") release 6.x
Databases that are configured to accept data from a relational database management system (RDBMS) using Oracle Express Relational Access Manager

Basic features

Using the add-in with Excel, you can:

Query one or more Express databases and import the data directly into a spreadsheet using an intuitive wizard.
Perform powerful OLAP analysis operations on the data, such as drilling, rotation, and data selection, without leaving the spreadsheet.
Format the data using familiar spreadsheet tools.
Use drill icons and indentation to work easily with hierarchical data in the spreadsheet.
Specify the default presentation of data values that are not available. The add-in represents those values with the value NA by default, but you can specify an alternative default value.
Remove rows or columns of data from query results to create an "asymmetric" layout for your data. This is useful if you want to pare down the results of your query to display only the necessary information.
Create multiple queries across multiple spreadsheet files during one session.
Write data back to the database (provided that you have the appropriate database privileges).
Use the right mouse button to display a popup menu that contains options for performing various functions on a query.
Display as many as 8 dimensions on the page edge.
Use the add-in's API to create scripting hooks (for example, VBA within Microsoft Excel) that enable the spreadsheet power-user to manipulate Express data programmatically.

Basic Steps for Working with Queries

Two-phase process

Express Spreadsheet Add-In allows you to display queries of data from one or more Express databases in a spreadsheet environment. Before working with queries, you should understand the basic steps in the process for using the add-in. This process has two phases: an installation phase and a querying phase.

Procedure: Installing the components

Before you begin working with queries, you must install the appropriate components. The following procedure lists these components and describes where you can find more information.

Install Excel.

See your Excel documentation.
Install and configure Personal Express or Express Server.

See the Personal Express Installation and Configuration Guide or the Express Installation and Configuration Guide.
Install SNAPI.

See the Oracle Express SNAPI Guide.
Install Express Spreadsheet Add-In software.

See "Installing the Add-In Software".
Configure a connection to Personal Express or to Express Server that (optionally) requires authentication.

See the Oracle Express Database Administration Guide.
Install the add-in into Excel.

See "Installing the Add-In into Excel".

Procedure: Querying Express data

Once you have installed the appropriate components, you can begin querying Express data in the spreadsheet. The following procedure describes how to query Express data with the add-in.

Start Excel.

Result: The add-in should load automatically. You should see an Express menu in the menu bar.
From the Express menu, choose Run Express Wizard and create a query.
Use the options on the Express menu to affect the data in the query. For example, you can rotate and drill in the data, display the Selector, or work with custom measures.
If you have the proper access, then write changes to the database.
From the File menu, choose Save to save the query.

Tips: Improving performance

Use the following tips to improve the performance of the add-in software:

To see an improvement in the performance of large queries when writing data to a database or refreshing the existing data on a sheet, change the automatic calculation setting in Excel to manual. To change the setting, choose Options from the Tools menu in Excel, then select the Calculation tab. Select Manual in the Calculation box.
To avoid errors when displaying more than 5000 rows of data or when trying to retrieve a very large number of data values, use an INI file setting called RowFetchData. For example, you can include the following line in the xpaddin.ini file:
```
RowFetchData=7000
```

Installing the Add-In Software

Before you begin

Before you install the add-in software, ensure that the following conditions are met:

Your system meets the requirements that are outlined in Appendix A.
You know where you want your Oracle home folder to be. The first time you install an Oracle product, you choose a location for the home folder. All Oracle products are installed in subfolders of Oracle home. Each release of a particular product is installed into a separate subfolder. Do not change your Oracle home.
An earlier release of the add-in is not installed on your PC. If an earlier version is already installed, uninstall the older version before installing the new version, as explained in the following procedure.

Procedure: Uninstalling the add-in

Use the following procedure to uninstall a previous version of the add-in.

From the Excel Tools menu, choose Add-Ins.

Result: A list of the available Excel add-ins appears.
In the Add-Ins dialog box, remove the check mark from the Express Wizard box and choose OK.
Exit from Excel.
Using Installer, uninstall the older version of the add-in from your PC.
Remove references to this version of the add-in from your paths setting. This setting is in the autoexec.bat file for Windows 95 or 98 and in the system settings for Windows NT.

Procedure: Installing Express Spreadsheet Add-In software

When you run Oracle Universal Installer (hereinafter referred to as "Installer"), you choose the software that you want to install from a list of available products and utilities. After you make selections, Installer installs them in the correct order. Use the following procedure to install the Express Spreadsheet Add-In software onto your PC.

Start Microsoft Windows and insert the OLAP Express Client CD into the appropriate drive.
From the Windows Start menu, click Run.
In the Run dialog box, enter x:\setup.exe, where x is your CD-ROM drive. Click OK.
Click Next in the Welcome screen.
In the File Locations screen:
- The Source field will already contain the correct path information.
- In the Destination fields, choose an Oracle home folder name and provide the full path to its location.
  
  See the Installer documentation for more information on the Oracle home folder.
Click Next.
In the Available Products screen, choose Oracle Express OLAP Client Products and Utilities, then click Next.

Note: If you want to install Personal Express, then you can choose it here. Once that installation is complete, you can return to this screen to continue installing client products. For additional information about installing Personal Express, consult the Personal Express Installation and Configuration Guide.
In the Installation Types screen, choose either the End-user or Custom type, then click Next.

If you chose Custom, then select Oracle Express Spreadsheet Add-In in the Available Product Components screen. Click Next.
Respond to any remaining prompts, clicking Next to move to the next screen. When you reach the Summary screen, which lists all of the components that you are about to install, click Install.
When installation is complete, click Exit to close Installer.

Related information

For information on the Oracle home folder and on removing products, see the Oracle Express Objects and Oracle Express Analyzer Installation Guide.

Installing the Add-In into Excel

Procedure: Installing the add-in into Excel

Use the following procedure to install the add-in into Excel.

From the Excel Tools menu, choose Add-Ins.

Result: A list of the available Excel add-ins appears.
Choose Browse and select the xpaddin.xll file. If you did not make any changes to the default installation path or move the file to another folder, then xpaddin.xll is located in the following folder, where z is the letter of your hard drive and nnn is the version number:

z:\oracle-home\olap\xsannn

Result: This adds the Express Wizard to the list of available add-ins that is shown in the Add-Ins dialog box.
Select Express Wizard check box in the Add-Ins dialog box and choose OK.

Changes to Excel

When you install the add-in into Excel, an Express menu is displayed on the Excel menu bar.

Connecting to Express

Configuring connections

In order to use Express Spreadsheet Add-In, you must be connected to Express so that you can display data from Express databases in a spreadsheet.

The Oracle Express Connection Editor (hereinafter referred to as "Express Connection Editor") is used to configure Express connections for all users of the add-in. The Oracle Express Database Administration Guide describes how to configure Express connections.

Connecting and disconnecting from Express

Once Express connections are configured, add-in users can use menu options to connect and disconnect from Express.

Connecting -- The Express menu contains various menu options that initiate Express connections. For example, when you choose to run the wizard or attach a database, the add-in begins the process of establishing an Express connection, if a connection is not already established.
Disconnecting -- The Express menu contains the Disconnect option. You must detach all databases from the add-in before disconnecting from Express.

1 Getting Started with Express Spreadsheet Add-In