Apply and start a query from the Subscription Wizard pane to populate the Excel worksheet.
Prerequisites
Define a query in the Subscription Wizard pane.
- From the Subscription Queries menu, select the desired query.
- Click Apply.
- The ESP Add-in for Microsoft Excel first
verifies that the supplied subscription query name has not already been used
then verifies that the provided Start Cell is a valid Excel cell address. If
either condition is false, resolve the problem.
- Next, the ESP Add-in for Microsoft Excel
constructs Excel real-time data formulas based on the specified subscription
query, and inserts one formula per cell into the active worksheet. Depending on
the query, hundreds of formulas may be inserted. The
ESP Add-in for Microsoft Excel uses this
logic to insert formulas:
- Formulas are always inserted as a grid, starting at the
specified Start Cell location. Each selected column appears in separate
but contiguous columns in the Excel worksheet. The value of Max Rows
controls the number of rows to which the filter is applied.
- Soon after the first formula is inserted into the active
worksheet, Excel recognizes the real-time data formula and makes a call
to the ESP Add-in for Microsoft Excel
server that passes the query information for the first filter. The
real-time server looks at the information passed, recognizes it as a new
query, and spawns a query object. The real-time data server also stores
the passed-in information for future use.
- This process is repeated for every formula of the query,
except the real-time server recognizes that the formula is part of the
previously seen query, and therefore, it does not create a new query
object. Rather, it stores the information so that it can return the data
corresponding to the formula.
- Click Start.
- The ESP Add-in for Microsoft Excel verifies that
the connection to Event Stream Processor is active and that
the specified query is still valid. If either of these conditions are false,
then it returns to the formula.
- Next, the ESP Add-in for Microsoft Excel spawns
a new read thread to read the transaction log data from
Event Stream Processor, and stores it in an internal
buffer.
- Every tenth of a second, the ESP Add-in for
Microsoft Excel reads the transaction logs from the internal buffer, and
decides whether to insert, update, or delete records in a display buffer, based
on the user-specified key fields. When there is an insert into the display
buffer and the number of records in the buffer is equal to the specified Max
Rows, the oldest record in the buffer is deleted, the rest of the records are
moved up, and the record is inserted at end. When a record needs to be updated,
an in-place update is performed. This insert and update mechanism results in a
more stable view of the data in the Excel worksheet, and makes it easier to
create charts based on the subscribed data.
- Once the display buffer has been populated, the
ESP Add-in for Microsoft Excel notifies
Excel that new data is available. When it receives a request for the data, it
sends the data in a format that Excel can understand and shows it the
appropriate location in the worksheet.