Applying a Query

Apply and start a query from the Subscription Wizard pane to populate the Excel worksheet.

1. From the Subscription Queries menu, select the desired query.
2. 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.
3. 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.