Friday, October 21, 2005

Using the Spreadsheet Add-In -

This is not a tutorial on how to use the Spreadsheet Add-In, but more as a brief look at its basics...
Once you have started the add-in, you have to connect to an Oracle OLAP data source. You have to define a connection first - which is the host name, port number, and the SID.

You can define multiple data sources in this manner, and using the 'OLAP Connection' dialog you select from one of those defined connections. Enter the user name, password, click 'Connect' and the familiar query builder is displayed upon a successful authentication.

While the Add-In attempts to establish a JDBC connection to the Oracle OLAP server you even get to see a nice little animation.

The familiar Query Builder is displayed - the same as what you see when using Plus OLAP. Because it is the same, that's why! Cool, isn't it? :-)

Anyway, I won't go into the intricacies or mechanics of query creation. Once the query displays in the Excel sheet, you can right-click any cell in the query to be displayed a context-menu, like the one shown below.

Now, there is no practical restriction on the number of OLAP queries you can have in a single sheet in Excel, or within an Excel workbook. This means that you may not know how many queries you have, or where they are. This is where the 'Properties' option from the Add-In context menu (or even from the menu) comes in handy. It will list how many OLAP queries in how many sheets you have in the current workbook. And in the details tab you get to see the connection details, cell location, and last-refresh date-time for each query.

While recognizing that people who refuse to evolve out of Excel need therapy (I am quoting an analyst here), I also recognize that habits die hard, especially those formed as a result of certain market anomalies... So if you have an OLAP query, you can add totals to it, Excel charts, etc...
I will look at the 'Expand Pages to Worksheets' option in a later post - though this is sort of similar to the 'Export all combinations of page-items' option when exporting data from Discoverer Plus OLAP.