SQL Query Links versus Excel

Using SQL queries to extract data directly from an Excel spreadsheet has the following benefits:

•There are no performance overheads with continually communicating with Excel.

•If the Key Performance Indicator (KPI) is moved to another cell the link is maintained.

•If the Excel file is relocated, you only need to update the file location in the SQL Query as opposed to updating every metric link.

The first task is to name a range of cells in Excel which contains the metric data. The advantage of ranged cells is that even if they are moved, for example if a new column is added, the range is maintained. SQL queries can then be defined using the TIBCO Nimbus® AdminUtil.

To define an Excel range:

1.Open a spreadsheet in Excel.

2.Select the cell, range of cells or non-adjacent selections that you want to name.

Note: There must be a cell that will be used as the field name. In the example below the cell that contains 'KPI' will be used as the field name.

3.Click in the Name Box and enter a name for the cells, then press Enter.

These ranges can then be referenced when creating SQL links in the AdminUtil. Refer to the Administration Guide (Accessing Excel Data via a SQL Query link under Metrics Support Tools) for more information.