Contents
The TIBCO StreamBase® Add-In for Microsoft Excel has two primary features:
- Publish-Dequeue Features
-
This feature lets you use a StreamBase or TIBCO Live Datamart application in combination with an Excel spreadsheet to process tuple data in real time and display the data dynamically. For StreamBase, the Excel Add-in is bidirectional and can both receive data from an application and publish data to a running StreamBase application. For Live Datamart, the Excel Add-in can receive data from a Live Datamart Server and publish it in an Excel spreadsheet or to a running StreamBase application. You cannot publish data from an Excel spreadsheet directly to a Live Datamart table, but you can publish data to a StreamBase application that serves as a Data Source for a Live Datamart Data Table. See Publish and Dequeue Functionality for a discussion of these features.
- Decision Table Editing Features
-
This feature allows you to create and edit decision tables for use with the StreamBase Decision Table operator. The Add-in can download decision tables from a running Decision Table operator and re-upload them to a running server. These features require Excel Add-in release 2.5.1 or later and StreamBase 7.6.3 or later. See Decision Table Functionality for a discussion of these features.
Excel Add-in releases 2.7.x and later support downloading decision tables from a running TIBCO Artifact Management Server and uploading them to a running AMS server.
The Excel Add-in installation includes a few samples, described in a separate document included with the Excel Add-in installation.
The TIBCO StreamBase Add-in for Microsoft Excel has the following requirements:
-
The StreamBase Excel Add-in is only supported on Microsoft Windows platforms.
-
Installation requires Microsoft Excel 2007 or later.
-
There are two versions of the installation kit, one each to match 32-bit and 64-bit Microsoft Office. Most users will need the 32-bit Add-in installer, even on 64-bit Windows. Match the bitness of the Excel Add-in to the bitness of your Microsoft Office installation, not your Windows version.
The two kits provide identical functionality for their target Excel editions. Each edition has a separate installer. The two editions cannot coexist on the same PC.
Only install the 64-bit StreamBase Add-in for Excel if you have 64-bit Excel 2010, 2013, or 2016 installed.
-
To determine your edition of Excel 2010, select → . In the About Microsoft Excel section, look for "Version XX. ... (64-bit)."
-
To determine your edition of Excel 2013 or 2016, select → and click the button. The top line of text in the resulting dialog has either "32-bit" or "64-bit" at the end of the line.
If you are running 64-bit Excel, then you must install and use the 64-bit Excel Add-in. The 32-bit kit does not run with 64-bit Excel. When running 64-bit Excel, the companion 64-bit Excel Add-in supports connectivity to large-scale StreamBase data.
-
-
The PC running Excel must have a working network connection to the instance of StreamBase Server hosting the StreamBase or Live Datamart application from which your spreadsheet reads data (or to which it publishes data). The same requirement applies to the AMS server; one must be reachable when uploading or downloading decision tables to that server.
-
Only one Excel Add-in can be registered for use on one PC at any given time. If you have two or more StreamBase installations, each with its own Excel Add-in, you might need to re-install the Excel Add-in for the StreamBase version you want to use. Re-installing the kit re-registers the Excel Add-in's XLL as currently active. The most recently installed kit is the currently active one for the PC.
-
To open some of this Excel Add-in's sample files, your Excel macro security settings must be set to medium or lower. Follow the steps in Setting the Excel Macro Security Level below.
Note
Lowering the Excel macro security level is NOT a requirement for deploying and running the StreamBase Excel Add-in in a production environment. TIBCO Software does not provide digitally signed macros because the macros in these samples are only examples on which to base the development of your own site's macros.
Lowering your Excel macro security is only required for the duration of testing with the macros in this sample's spreadsheets, and the lowered security can be localized to a few development PCs. Once you develop your own Excel macros using these samples as guidelines, you can digitally sign your site's macros so that they can be distributed easily and securely to PCs running standard levels of macro security.
Current releases of the StreamBase Excel Add-in require TIBCO StreamBase® release 7.4.0 or later to support the Add-in's Publish and Dequeue Features. Earlier releases of the Add-in support StreamBase releases back to 7.2.0, as shown in the following table.
TIBCO® Live Datamart requires a specific matching StreamBase release, so it is not accurate to describe which Live Datamart releases are supported by the Add-in. It is only accurate to say which StreamBase releases are supported, with the reliable assumption that the corresponding Live Datamart release supports the Add-in. See the Live Datamart product documentation for information on Live Datamart's compatibility with StreamBase.
| Decision Table Features | Publish and Dequeue Features | ||
|---|---|---|---|
| Excel Add-in Release | StreamBase Release | StreamBase Release | Live Datamart Release |
| 2.7.0 | 7.7.2, 10.2.x | 7.6.x, 7.7.x, 10.2.x | 2.1.x, 2.2.x, 10.2.x |
| 2.6.0 | 7.6.4 | 7.6.x, 7.7.x | 2.1.x, 2.2.x |
| 2.5.1 | 7.6.3 | 7.6.x | 2.1.x |
| 2.5.0 | — | 7.6.x | 2.1.x |
The StreamBase Add-in for Microsoft Excel has a separate installation kit from StreamBase itself, and is available for supported Microsoft Windows platforms only. Install the Excel Add-in kit as follows:
-
The Excel Add-in kit must be installed on any PC that will run an Excel spreadsheet that reads from or writes to a StreamBase Server instance, or that reads from a Live Datamart instance. (The kit's installer registers the Excel Add-in's XLL so that Excel can locate the Add-in when it is called in a spreadsheet cell.)
-
For production PCs, the Excel Add-in kit is a standalone package that does not require either StreamBase Studio, StreamBase Server, or Live Datamart to be installed on the same machine.
-
For development PCs, and for running the Excel Add-in kit's sample, you can install StreamBase and the Excel Add-in kit on the same PC.
The Publish and Dequeue features are described in the following subsections:
The StreamBase Add-in for Excel uses many of Microsoft Excel's extension facilities to provide a tight integration experience. It integrates with Excel's ribbon to provide menu choices, and installs a custom task pane in the main Excel window called the TIBCO Explorer. This task pane facilitates the connection to StreamBase Server and Live Datamart instances, and allows you to construct appropriate formulas to be placed in spreadsheet cells to access tuples from these servers. The installer also registers several user-defined functions (UDFs) for use in cell formulas and VBA code, as well as a number of COM objects to allow VBA code to construct and send tuples to StreamBase Server applications. Finally and most importantly, the installer includes an implementation of a Microsoft Real-Time Data (RTD) server, as described on Microsoft's product support page, to allow the Add-in to display incoming tuples from connected StreamBase Server and Live Datamart instances. The Add-in can be used in two modes, where the data flows in opposite directions:
-
Dequeuing tuples from one or more StreamBase application output streams and passing them to Excel.
-
Publishing tuples from Excel to a StreamBase application.
The preceding diagram illustrates how a StreamBase application, running in a StreamBase Server instance, receives a stream of tuples from a data feed, processes the data, and emits tuples on one or more output streams. The RTD server, listening for tuples from StreamBase Server, receives the data and passes it to Excel. In Excel, a UDF function determines how each tuple field of interest is displayed in the spreadsheet. The diagram also shows the publishing option, where tuples are enqueued from an Excel spreadsheet into a StreamBase application for processing there.
In Excel, you can use a combination of two techniques to organize the data on your spreadsheet:
-
Historical topics are associated with the relative order of tuples in the output stream. For example, one row can be set to display the most recent output tuple; the next row might display the previously output tuple, and so on. In a typical historical data display where rows are set up to display tuple numbers sequentially, tuples appear to scroll up or down the spreadsheet as new ones are emitted.
-
Filtered topics are associated with specific values appearing in tuple fields. For example, a row might display only tuples that contain a certain ticker symbol.
The Add-in includes user interface facilities that can be used to control environment and performance parameters. Specifically, after installing the Add-in and restarting Excel you will notice that a new task pane entitled TIBCO explorer is visible on the right-hand side of the main window:
This pane is the focal point of operations when accessing data from a server. It allows you to quickly construct and place the Excel formulas necessary to access StreamBase data in real time using Excel's own RTD functionality. This pane has four tabs, devoted to displaying StreamBase Servers, Live Datamart servers, AMS servers, and logging messages, respectively. Each server included in the server tabs in this pane can be used to receive real-time data from the StreamBase, Live Datamart Server, or AMS Server it defines. If a defined server is available, it will also list its available output streams (for StreamBase Servers) or tables (for Live Datamart servers), along with the list of fields contained therein. Right-clicking on those nodes brings up a context menu from which you can add real-time values to your spreadsheet.
You will also see a tab entitled TIBCO on the ribbon:
On this tab are buttons to show or hide the TIBCO Explorer pane, control the rate at which StreamBase data refreshes on the spreadsheet, work with decision tables, and call up the Excel Add-in's documentation and dialog.
To display the contents of the last 10 dequeued tuples from a running StreamBase Servers, follow these steps:
-
Ensure you have a running StreamBase Server available.
-
Start Microsoft Excel and create a new workbook.
-
Ensure that there is a definition for your server on the StreamBase Servers tab in the TIBCO Explorer task pane. The top-level node, representing the server itself by name and URI, will show in green if the server is accessible and the node will be expandable to show the running application's output streams and their fields. (If the server is inaccessible the node will display an error icon and red text.)
-
Expand the server node and locate the stream for which you would like to display values. Right-click on the stream's node and select Add tuple values to selected cells. A dialog box will open asking you to enter more details on the information you would like to display:
-
You may select the fields you wish to display on your spreadsheet, as well as reorder them by dragging them around the list with your mouse. You may also enter a filter expression to limit the tuples that will be displayed to those for which the expression evaluates to true.
Note
When specifying a filter expression to filter tuples, the option should be preferred over in most cases for efficiency. Each different server-side filter expression results in a separate stream subscription, which can quickly lead to performance problems.
-
Once you are done, click . The Excel Add-in constructs the necessary Excel formulas and place them in your active worksheet, starting at the active cell. At this point your data should start streaming in:
The Excel Add-in also makes it possible to construct and send tuples to a running StreamBase Server. To get started, follow these steps:
-
Make sure you have a running instance of a StreamBase application, and this it is correctly defined in the Excel Add-in's TIBCO Explorer pane in Excel.
-
Make sure the Developer tab is showing on Excel's ribbon. You may find more information on the Developer tab and how to display it by searching Excel's Help System for the topic Show the Developer tab or run in developer mode.
-
Create a new workbook and decide on an event source for publishing tuples to StreamBase. The code below assumes a button click serves this purpose. Thus, add a Command Button to the spreadsheet and name it
CommandButton1. (For instructions on adding controls such as buttons to a spreadsheet, search Excel's Help System for the topic Add an ActiveX control.) -
Double-click the button you have created to bring up the VBA code window and add a Click() event handler for the button.
-
Before editing the event handler you just created, make sure the workbook loads a reference to the StreamBase COM classes by selecting from the menu and making sure the StreamBase_Excel_Addin item is checked in the list and clicking to return to the VBA code window.
-
Add the following code to the event handler:
Private Sub CommandButton1_Click() Dim pub As SBClient Dim t As SBTuple Dim serverName As String ' StreamBase objects will throw a VBA error if something goes wrong. On Error GoTo EH ' Create an instance of the SBPublisher class & connect to the server. Set pub = New SBClient ' The name must match the one in the StreamBase Explorer pane in Excel. pub.Connect "DefaultServer" ' Create an empty tuple. This tuple uses the schema of the named input ' stream in your SB app. Set t = pub.CreateTupleForStream("DataIn") ' Set the fields on the tuple t.SetField "Symbol", "SB" t.SetField "Price", 1000.5 t.SetField "Volume", 1000000 ' "Date" objects can be used to set SB Timestamps Dim dt As Date dt = Now t.SetField "TransactionTime", dt ' OK, all our tuple's fields are initialized; send it to the server! pub.SendTuple t GoTo Done EH: MsgBox Err.Description & vbCrLf & "Line: " & Erl, , "StreamBase Exception" Done: ' Clean up before exiting pub.Disconnect End Sub -
Save the editor sessions in the VBA Editor and close it.
-
Return to the spreadsheet, make sure it is no longer in design mode by looking at the Developer tab and ensuring the Design Mode toggle is not active and click CommandButton1 to enqueue a tuple to StreamBase.
-
Confirm that tuples are being published to your StreamBase application by watching the Application Output view in Studio or by running sbc dequeue from a StreamBase Command Prompt.
To display Live Datamart values, you can follow similar steps as above but use a server from those listed on the Live Datamart Servers tab, selecting the table for which you wish to display values. From there, right-clicking on a table node will bring up this dialog:
From here you can enter a Live Datamart predicate, select the type of query to use (continuous or snapshot), select and reorder the table's fields and specify the maximum number of rows you wish to display. Additionally, you may specify a filter expression to further restrict the rows that will be displayed as well as sort criteria used to order the result set.
The following syntax is available to construct expressions in the Filter Expression edit box of the Insert A StreamBase Function dialog:
-
<expression> := <predicate> [ {<or> | <and>} <predicate> ]...
-
<predicate> := <fieldname> <operator> <literal>
-
<fieldname> := Valid StreamBase field name as defined in the stream or table schema, without any quotes
-
<literal> := <number> | <string> | TRUE | FALSE
-
<number> := Valid number as defined by Excel
-
<string> := A string enclosed by single quotes (preferred) or double quotes
-
<operator> := '==' | '!=' | '<' | '<=' | '>' | '>='
-
<or> := '||'
-
<and> := '&&'
Additionally, you may group predicates using parentheses to control the order in which they are evaluated.
Here are a few examples of valid filter expressions:
-
MyStringField != 'Some value'
-
MyNumericField > 3.1416
-
MyBooleanField == TRUE
-
MyField1 == 'red' && MyField2 != 42
-
(MyField1 == 'red' | MyField1 == 'blue') && MyField2 != 42
Note
The expression you type will be included as a parameter to the SBGetField() or LVGetField()
user-defined function call placed in the spreadsheet's cells, so the Excel Add-in
will take care of enclosing the entire expression in double quotes. Any double-quote
character you include in your expression will be escaped by the Excel Add-in in the
function call by inserting two double-quote characters in its place according to
Excel function syntax, possibly making your resulting UDF harder to read. For
example, the expression MyField == "Hello World" becomes
"MyField == ""HelloWorld""" in the function call. To help
keep your UDFs more readable you should consider using single-quote characters
whenever possible instead.
Note
It is possible to use Excel cell references in your filter expressions. This is
accomplished by disabling the Excel Add-in's default behavior of enclosing the entire
expression in double-quotes (which is done by beginning your expression with a
double-quote character) and constructing the UDF parameter verbatim instead (that is
to say, exactly how you would type it in the Excel function definition if you were
constructing the UDF call manually). For example, instead of writing MyField == 'Hello World' you may place the string Hello World in cell A1 and enter the following as
your expression filter: "MyField == " & A1. Because
this expression begins with a double-quote the Excel Add-in will use it verbatim in
the UDF, and this evaluate to the correct value for the filter expression parameter.
Note
When using StreamBase if you select Evaluate On the Server instead of the default of Evaluate Locally, you may use the entire range of the StreamBase expression language syntax instead of the subset outlined above, giving you access to a richer and more flexible logic to specify your predicate logic. However bear in mind that using server-side predicates may result in an excessive number of separate subscriptions to your StreamBase application's streams and in the worst case severely affect the performance of your Excel spreadsheet. For this reason you should always try to use local filters when specifying filter predicates.
When specifying a predicate in the Insert A Live Datamart Function dialog's Predicate edit box you may use the entire range of the Live Datamart syntax for doing so. However as noted in the previous section you should favor the use of single-quote characters for specifying strings whenever possible to keep your UDFs easier to read. Additionally, you can use Excel cell references within your expression by using the same mechanism as described above for filter expressions. That is, begin your expression with a double-quote and construct the UDF parameter verbatim.
This example uses the Hello LiveView sample. To run this example, you need to load and run this sample in Live Datamart. See the TIBCO Live Datamart product documentation for full information about Live Datamart samples.
The Hello LiveView sample contains two data tables, ItemsInventory and ItemsSales. Right-click on the ItemsSales table in the TIBCO Explorer tab and select . The Insert a Live Datamart Function dialog opens.
-
Enter a server name. This example uses Live Datamart Hello LiveView sample running on the default port on the same machine as the Excel Add-in.
-
Ensure that ItemsSales is selected in the Table Name field.
-
Enter the query predicate in the Predicate field. The predicate can use the full range of the LiveQL query language.
This simple query predicate returns all rows where the quantityRemaining field is less than 50:
WHERE quantityRemaining < 50
-
Select Continuous or Snapshot query type with the radio buttons. This example uses Continuous:
-
Select the query projections. Projections that are simple field names are listed in the Fields table. All fields are selected by default. To change the default settings, select the radio button for These Fields or click the button. This example returns the table fields for transactionTime, Item, and category:
Optionally, you can select Include Field Names As Headers to create column headers for the returned projections.
-
You can select the number of rows to display in the output. By default, the Excel Add-in limits the number of returned rows to ten. This example returns ten results with the sorted by the most recent transaction time:
This example uses the same Hello LiveView Sample as in the previous example to demonstrate dynamic, that is, query-based aggregation. Dynamic aggregation queries require:
-
A GROUP BY clause.
-
An aliased LiveQL aggregate expression.
This example counts the number of items in each category.
-
Select the server and table names as in the previous example. For the query predicate, enter:
GROUP BY category
LiveQL clauses are case-insensitive; the clauses are shown in ALL CAPS for readability.
-
Aggregate expressions are entered with the query projections. Select the button in the fields pane to enter an aggregate expression:
The following UDFs are defined by the Excel Add-in:
-
SBGetField(), which retrieves the value of a field in a StreamBase tuple and displays it in the cell in real time. Its parameters are:
Parameter Name Type Description Server Name string The name of the StreamBase Server to which to connect, as it appears in the TIBCO Explorer pane. Must be specified. Stream Name string The name of the stream from which to dequeue, as it appears in the TIBCO Explorer pane. Must be specified. Field Name string The name of the field of which to display the value, as it appears in the TIBCO Explorer pane. Must be specified. Tuple Number int The number of the tuple for which to display a value. The default value is 1, meaning the latest dequeued tuple. A number of 2 means the second-latest dequeued tuple, and so forth. Filter Expression string A logical expression which will be applied to filter incoming tuples -- only tuples which satisfy the expression are displayed. The syntax used to define this expression depends on the Is Server-side Filter value. Leaving this parameter blank (the default) will cause all tuples to be included in the results sent to the Add-in. Is Server-Side Filter boolean If this is set to false (the default), the filter specified in Filter Expressionis applied by the Add-in after a tuple is dequeued. In that case, the allowed expression syntax forFilter Expressionis described in Reference: Filter Expression Syntax. If it is set to true, the filter is applied by the server to decide whether to send a given tuple to the Add-in. The syntax forFilter Expressionis then the same as the full StreamBase expression syntax. Server-side filters should be used sparingly as every different expression will result in a separate stream connection to the server and may lead to performance issues. -
LVGetField, which retrieves the value of a field in a Live Datamart row and displays it in the cell in real time. This UDF should be considered deprecated and LVGetFieldEx() should be used instead. Note that when inserting new cells the LVGetFieldEx() function is used.
-
LVGetFieldEx(), which supersedes LVGetField() and should be used instead. Its parameters are:
Parameter Name Type Description Server Name string The name of the Live Datamart server to which to connect, as it appears in the TIBCO Explorer pane. Must be specified. Table Name string The name of the table to query, as it appears in the TIBCO Explorer pane. Must be specified. Limit int The total number of rows to include in the Live Datamart result set. Required to be non-zero for tables supporting "Top N" queries. The default is 0, which means 'Use legacy (non-Top-N) query'. Query Fields string A comma-separated list of fields (and/or dynamic aggregate expressions) to include in the query results, or ' *' (or empty) to include all fields from the table.Predicate Expression string The predicate clause (' WHERE ... [FOR x MILLISECONDS] [GROUP BY ...] [WHEN ...]' used to create the query. May be left empty.Field Name string The name of the field for which to display the value. Row Number int The number of the row from which to display the field in this cell. Sort Order string A list of comma-separated sort criteria for the rows, in the form ' field1 [DESC][, field2 [DESC]...]'. The default sort order is ascending (ASC). If this is left blank, row will be displayed in whatever order was returned by the Live Datamart server.Query Type string Either SNAPto only get a snapshot orCONTto get continuous updates. Default isCONT. -
SBEnableRTDUpdates(boolean enable), which enables programmatic control over whether real-time data is enabled (
enable=true) or disabled (enable=false) -
SBClearHistory(string ServerName, string Stream), which removes all tuples from the given StreamBase Server and stream's history queue
You may also refer to Excel's own Function Arguments dialog box for an online list and explanation of the parameters to each of these functions.
The SBGetField() and LVGetFieldEx() UDFs defined above are convenient and easy to use, however they do incur an additional performance cost over using raw RTD directives for accessing StreamBase and Live Datamart data. If your Excel workbook needs to display thousands of cells containing StreamBase or Live Datamart data (or both), consider using the RTD functions instead of UDFs. When using the Insert a StreamBase Function or Insert A Live Datamart Function dialogs to populate the workbook's cells as described in sections above, specify the RTD option in the Function Used section. When entering functions manually (or programmatically using VBA), here are the parameters to the RTD() function:
For StreamBase:
| Parameter Name | Type | Description |
|---|---|---|
| Server Name | string | The name of the StreamBase Server to which to connect, as it appears in the TIBCO Explorer pane. Must be specified. |
| Stream Name | string | The name of the stream from which to dequeue, as it appears in the TIBCO Explorer pane. Must be specified. |
| Field Name | string | The name of the field of which to display the value, as it appears in the TIBCO Explorer pane. Must be specified. |
| Tuple Number | int | The number of the tuple for which to display a value. The default value is 1, meaning the latest dequeued tuple. A number of 2 means the second-latest dequeued tuple, and so forth. |
| Local Filter Expression | string | A logical expression which will be applied to filter incoming tuples -- only tuples which satisfy the expression are displayed. This filter is applied by the Add-in after a tuple is dequeued. The expression syntax is described in Reference: Filter Expression Syntax. Leaving both Local Filter Expression and Server Filter Expression blank (the default for both parameters) will cause all tuples to be included in the results sent to the Add-in. |
| Server Filter Expression | string | A logical expression which will be applied to filter incoming tuples -- only tuples which satisfy the expression are displayed. This filter is applied by the server to decide whether to send a given tuple to the Add-in. The syntax is the same as the full StreamBase expression syntax. This filter should be used sparingly as every different expression will result in a separate stream connection to the server and may lead to performance issues. Leaving both Local Filter Expression and Server Filter Expression blank (the default for both parameters) will cause all tuples to be included in the results sent to the Add-in. |
| Metadata | string | Reserved. Should be left blank. |
For Live Datamart:
| Parameter Name | Type | Description |
|---|---|---|
| Server Name | string | The name of the Live Datamart server to which to connect, as it appears in the TIBCO Explorer pane. Must be specified. |
| Stream Name | string | The name of the stream from which to dequeue, as it appears in the TIBCO Explorer pane. Must be specified. |
| Query Fields | string |
A comma-separated list of fields (and/or dynamic aggregate expressions) to
include in the query results, or '*' (or empty) to
include all fields from the table.
|
| Predicate Expression | string |
The predicate clause ('WHERE ... [FOR x MILLISECONDS]
[GROUP BY ...] [WHEN ...]' used to create the query. May be left empty.
|
| Field Name | string | The name of the field of which to display the value. Must be specified. |
| Row Number | int | The number of the row from which to display the field in this cell. Must be specified. |
| Unused | N/A | Reserved. Should be left empty. |
| Sort Order | string |
A list of comma-separated sort criteria for the rows, in the form 'field1 [DESC][, field2 [DESC]...]'. The default sort order is
ascending (ASC). If this is left blank, row will
be displayed in whatever order was returned by the Live Datamart server.
|
| Query Type | string |
Either SNAP to only get a snapshot or CONT to get continuous updates. Default is CONT.
|
| Limit | int | The total number of rows to include in the Live Datamart result set. Required to be non-zero for tables supporting "Top N" queries. The default is 0, which means 'Use legacy (non-Top-N) query'. Must be specified. |
The following classes and methods are available for use when publishing data to a StreamBase application:
-
SBClient class
-
Connect(String serverName)
Connects to the given StreamBase Server
-
Disconnect()
Disconnects from the server
-
SBTuple CreateTupleForStream(String streamName)
Creates an instance of SBTuple suitable for use with the given input stream.
-
SendTuple(SBTuple tuple)
Sends a tuple to the StreamBase application, on the stream for which the SBTuple instance was created
-
-
SBTuple class
-
SBTuple CreateTupleForField(String fieldName)
Creates an instance of SBTuple suitable for setting the value of the named field, which is assumed to be of type TUPLE or LIST OF TUPLE.
-
SetField(String fieldName, Object value)
Set the tuple's given field to the given value. The field is assumed to be of a StreamBase type compatible with the value (for example, attempting to set a string value on a field of type DOUBLE will result in an error being generated). VBA objects of type Date may be used to set fields of type TIMESTAMP. Zero-based VBA arrays may be used to set fields of type LIST; the type of the array must be compatible with the type of the LIST field's element type.
-
If an exception occurs during the execution of the methods described above an error will be returned to the VBA calling code via the On Error/Goto mechanism, with the Err object correctly initialized to provide more information on the error.
Note
In order for a workbook to use these classes you must:
-
Have VBA support installed. You can tell that VBA is available if the button is enabled on the Developer tab. If it is dimmed, you can install VBA by running the Microsoft Office installation, choosing , and selecting Office Shared Features | Visual Basic for Applications from the Installation Options tab.
-
Ensure that the workbook has a reference to the StreamBase COM objects representing the publishing classes described above. To do so, open the Visual Basic code window by pressing Alt-F11 and selecting from the menu, and making sure the StreamBase_Excel_Addin item is checked in the list before clicking to dismiss the dialog.
Configuration is achieved through the use of the TIBCO Explorer pane and saved automatically. To help get you started when you launch Excel for the first time after installing the Excel Add-in, notice that one StreamBase server, one Live Datamart server, and one AMS server are already defined on their respective tabs in the TIBCO Explorer pane, pointing to the default server URI for each product. If you have a server running locally at the default port this will allow you to get started immediately; otherwise you must add your own servers' definitions by clicking the button and entering the necessary information in the resulting dialog. Any changes you make to the server definitions in either the StreamBase Servers tab, the Live Datamart Servers tab, or the Artifact Management Servers tab are persisted to your system on a per-user basis. Also saved automatically are the state of the Explorer pane and the settings on its Logging tab as well as the current refresh rate.
Using the button you can save the list of configured servers to a separate XML file to be loaded back using the button, possibly on another machine. In this way you can share your server definitions with other users. Just remember: once you have loaded server definitions from an XML file, the new definitions are saved locally to your workspace and you no longer need the XML file. Also note that by loading servers from an XML file, you are supplementing your existing list of defined servers, not replacing it. Finally, note that any username and password information you may have added to a server definition does not get included when you save your list of servers in an XML file; other users loading the list must supply their own credentials.
Note
If you load a new set of server definitions using the button, or if you edit or remove server definitions (using the and buttons respectively), you will need to restart Excel before the changes are reflected in your Workbooks' data.
The Excel Add-in allows you to create a decision table, which is a collection of business rules that can be published to a Decision Table operator in a currently running StreamBase application. You can also:
-
Download a decision table from a running Decision Table operator into Excel, edit it, then re-upload it.
-
Download a decision table from a running AMS server into Excel, edit it, then re-upload it to the server.
Decision tables provide a way to express a complex set of business rules as a table of conditions that apply to incoming tuple fields, with one or more specified actions for each row of conditions. By default, all rows of the table are evaluated against each incoming tuple, with an option to stop evaluation at the first matching row.
For more information about the Decision Table operator and decision tables in general, see Using Decision Tables in the StreamBase Authoring Guide.
The Decision Table features are described in the following subsections:
From the TIBCO tab in Excel, use the following buttons to work with decision tables.
| Button Name | Description |
|---|---|
| New Decision Table | Creates a new decision table. |
| Download from StreamBase | Downloads a decision table from a running StreamBase Server and optionally downloads a domain model. |
| Upload to StreamBase | Uploads a decision table to a running StreamBase Server in-memory. |
| Download from AMS | Downloads a decision table from a running Artifact Management Server. |
| Commit to AMS | Uploads a decision table to a running Artifact Management Server. |
| Add Rows | Adds n number of table rows from the currently loaded decision table with the option to select rule priority. |
| Remove Rows | Removes one or more selected rows from the currently loaded decision table. The selected rows need not be contiguous. |
| Add Column | Brings up a dialog box to add a column to the decision table. |
| Update Column | Brings up an identical dialog as the Button, allowing the existing name, type, and/or data type to be changed for the selected column. |
| Remove Columns | Removes the selected columns, which need not be contiguous. |
Use the Excel Add-in to publish a decision table into a StreamBase application as follows:
-
Create a decision table file as a standard Excel file in any location. Then from StreamBase Studio, import the file into your Studio project. Imported decision tables from Studio can be re-edited using the Excel Add-in.
-
Create a decision table file and click the Add-in's button to upload the current decision table state to a specific Decision Table operator in the currently connected and running StreamBase Server instance.
-
Download the decision table running in a Decision Table operator in the currently connected and running StreamBase Server, edit it in Excel, then re-upload the changed decision table (or save the downloaded and edited file as an Excel file).
Other ways StreamBase applications use decision tables:
-
From StreamBase Studio, import an Excel decision table originally created in TIBCO BusinessEvents, or exported from BusinessEvents to the Excel Add-in.
-
From StreamBase Studio, import a
.rulefunctionimpldecision table file originally created in and saved from TIBCO BusinessEvents Studio. -
From the Excel Add-in, import a decision table from a running AMS server. Then from the Excel Add-in, import the file into your Studio project.
Configure decision tables to meet your business rule needs. Configurable decision table options are:
-
Effective and Expiration Date
-
Single Row Execution enabled or disabled
-
Table priority
-
Per-row rule enablement
-
Per-row priority
-
Rules with a subset of conditions populated (unpopulated conditions default to true)
-
Rules with no conditions populated
-
Rules with a subset of actions populated (unpopulated actions default to null)
-
Rules with no actions populated
-
Rules with no conditions and no actions populated (rule is disabled)
To create a new decision table, do the following:
-
From the TIBCO tab in Excel, click . If your worksheet already contains a decision table, a dialog appears prompting whether to overwrite the existing table. If the current worksheet is empty, a new, empty decision table is created in the blank worksheet.
Create as many decision tables as desired, up to one per worksheet. Regardless of how many you create, when a decision table is imported as a file into a StreamBase application, the Decision Table operator reads only the first worksheet. However, you can use the button to apply a decision table from any active Excel Add-in worksheet.
-
Select a Decision Table operator from the running StreamBase Server.
-
Select fields from the Decision Table operator's schemas to be used as the headers for the condition and action columns.
-
Select the check box to choose an optional Effective Date for this decision table from the calendar that appears.
Note
Setting an effective date for the future generates a warning message.
-
Select the Expiration Date check box to choose an expiration date for this decision table.
Note
StreamBase prevents decision tables from loading when either the expiration date is less than or equal to the effective date (that is, the rules will never be active), or when the expiration date is set to the past (rules will never be active).
-
Select the Execute Actions check box to choose whether or not actions for all matching rows should be executed, or whether the action associated with the first matching row is executed.
-
Select the Table Priority {1 highest, 10 lowest. Default: 5}.
Note
Table priority is currently ignored.
-
Select the number of desired empty rows for the newly created decision table.
-
The decision table appears with the selected condition and action columns. The headers, condition, and action columns are color-coded to help you know where to enter new rules.
To download a decision table or a domain model from a running StreamBase Server, do the following:
-
From the TIBCO tab in Excel, click . If your workbook already contains a decision table, a dialog appears prompting whether to overwrite the existing table. Alternatively you can create a new worksheet into which you download the new decision table. Next, the Download a Decision Table from StreamBase dialog appears.
-
Select the desired StreamBase Server and Decision Table operator from their respective drop-down lists.
-
Select the Include Rules check box to download a decision table.
-
Select the Include Domain Models check box to download an optional domain model.
-
Click . The contents of the selected decision table in the Decision Table operator are downloaded to the Excel Add-in, overwriting any currently open decision table.
Domain models restrict the set of values that can be placed in a condition or action cell. By default, a cell has no restrictions. In StreamBase Studio, the Decision Table operator uses an optional JSON file to define one or more domain models. A decision table supports one domain model file per decision table, and one domain model per column.
The following example shows a domain model containing one array with four values: {Jim, Frank, Bob, and Steve}.
[
{
'name': 'Names',
'field': 'Name',
'isConditionField': 'true',
'isActionField': 'false',
'isMultiSelect': 'false',
'values': ['Jim', 'Frank', 'Bob', 'Steve']
},
]
Once you download the domain model to your Decision Table, drop-down options become available in the corresponding decision table columns. For example:
|
Make your changes as needed for each available field. From the TIBCO tab, click Upload to StreamBase to apply the changes to the running StreamBase Server.
To upload your decision table to a running StreamBase Server in-memory, do the following:
-
From the TIBCO tab in Excel, click .
-
Select the desired StreamBase Server and Decision Table operator (for example, if your StreamBase application contains multiple Decision Table operators) from the upload dialog.
-
Select the option if you want to check rules for errors. Validated rules are uploaded to StreamBase Server for validation purposes, but are not loaded into the Decision Table operator. Click .
-
A status message indicates the number of rules uploaded. On the StreamBase Studio side, a message in the Console tab confirms Studio received the rules, and a tuple is published to the Decision Table operator's control input stream.
Uploading a decision table to a StreamBase Server in-memory only applies to the currently running application. The next time the application is run, the Decision Table operator reverts to its locally sourced decision table file.
-
To download a decision table (which are known as
artifactsin AMS) from a running AMS Server, do one of the following:-
Right-click an available artifact in the Artifact Management Servers tab from the TIBCO Explorer pane. This brings up the Download Decision Table from AMS dialog (just as clicking on the Download from AMS button in the ribbon does) and pre-populates it with the AMS server, project, and path of the selected artifact.
-
From the TIBCO tab in Excel, click (downloads into the active decision table). Alternatively, create a new worksheet into which you download the new decision table.
-
-
In the Download a Decision Table from AMS dialog, use the drop-down lists to select:
-
AMS Server.
-
AMS Project containing the artifact you want to download.
-
Artifact Path (name of the decision table artifact contained in the project).
-
-
Click . The contents of the selected decision table artifact are downloaded to the Excel Add-in, overwriting any currently open decision table.
To upload your decision table to a running AMS server, do the following:
-
From the TIBCO tab in Excel, click .
-
In the Download a Decision Table from AMS dialog, use the drop-down lists to select the following. Note that if the decision table in the sheet was previously downloaded from AMS, the commit dialog will be pre-populated with the server, project, and path from which the artifact was downloaded.
-
AMS Server.
-
AMS Project containing the decision table artifact you want to download.
-
Artifact Path (name of the decision table artifact contained in the project).
-
-
Add a commit message or use the default message.
-
Click . The contents of the decision table artifact are uploaded to the AMS server for approval. Note that until the committed changes are approved, a subsequent download of this decision table from AMS will retrieve the old contents of the artifact and will therefore not include the changes that were committed for approval.
Once you have a decision table file to work with, you can add and remove rows and columns, and update columns as follows.
To add a row to your decision table, do the following:
-
Using an available decision table, from the TIBCO tab, click . The Add Decision Table Rows dialog appears.
-
Select the desired number of rows. New rows are appended below the last rule row in your decision table.
-
Select a rule priority {1 highest, 10 lowest. Default: 5}.
-
Rules are enabled by default. To disable rules, clear the check box and click .
Click to delete one or more selected rows. The selected rows need not be contiguous.
Note
Deleting rows does not reset the rule ID for the remaining rows. For example, deleting the third row in a five-row table does not affect the numbering of rule IDs four and five.
To add a column to your decision table, do the following:
-
Click . In the following dialog, enter the Column Name, and select the Column and Data Types.
Note
StreamBase conditions are locked to the bool data type, since these conditions must evaluate to a bool.
-
Click . The new column is moved right or left to be grouped with other columns of the same type.
-
If you created an action column (whether regular or StreamBase), you must add the column to the action schema in your decision table application, in StreamBase Studio.
The button allows you to change the existing name, type, and data type for the selected column. One column can be changed at a time. When changing a column's type, the Excel Add-in moves the column right or left to be grouped with other columns of the same type. Also, when you change the column name, you must update the decision table application's condition or action schema in StreamBase Studio to match.
The macros provided in this sample are deliberately unsigned because they are not meant to be used and distributed as-is, but are only examples for building your own macros. Once you have your own working, site-specific macros, you can digitally sign them for easier and more secure distribution at your site.
While developing your own macros by modifying the unsigned macros in this sample, Excel
might display a dialog warning that macros are not digitally signed. This occurs when you
open the sample spreadsheets other than rtd.xls using any
supported version of Excel. To resolve the problem temporarily while developing your
site-specific macros, lower your Excel security settings using the following steps:
-
Select the Office orb in the upper left.
-
Select Excel Options.
-
Select Trust Center, then Trust Center Settings.
-
In the next dialog, select Macro Settings.
-
Select the Enable all macros check box.
Now each time you open the spreadsheet, Excel displays a different dialog with another security warning about macros. Click to dismiss the warning.
The dynamic display of data using the StreamBase Add-in for Excel is influenced by a number of factors. These include the rate at which tuples flow in and out of the StreamBase Server or Live Datamart server.
Another variable is how frequently Excel accepts data from the RTD server, referred to as the Excel refresh interval. If your Excel spreadsheet data is being updated too slowly, you can try changing the refresh interval to speed up the frequency of refreshing, as described in this topic.
Note
The information in this topic is adapted from the Microsoft MSDN Library, which documents many aspects of configuring and tuning RTD servers.
Recall that tuples flow from the StreamBase Server output stream or Live Datamart table through the Excel Add-in's RTD server before being consumed by the Excel spreadsheet. Excel is not continually open to updates; it accepts them only at set intervals (the refresh interval). If the RTD server has an update and Excel is busy (such as when you are editing a cell), it might wait longer than the set refresh interval. If the refresh interval has passed, Excel then retrieves the data changed since its last refresh.
The refresh interval in the Excel Add-in is set by default to one second. To modify the refresh interval, use the TIBCO tab's Refresh Rate control.
Caution
If updates come in so frequently that Excel is continuously updating values and doing calculations, Excel might end up in a state where it never gives the user a chance to do anything. This is effectively putting Excel in a hung state. If this happens, set the Excel refresh interval higher.
This section contains new features and resolved issues for the Excel Add-in.
- Buttons Added to Upload and Download Decision Tables to/from AMS
-
The Excel Add-in now includes two buttons for uploading to and downloading decision tables from a running Artifact Management Server (AMS). The TIBCO explorer panel also includes an Artifact Management Servers tab for configuring connections to AMS servers.
- Buttons Added to Support Decision Tables
-
The Excel Add-in now includes four buttons (Remove Rows, Add Column, Update Column, Remove Columns) to support creating decision tables for use with the Decision Table operator in StreamBase. This feature requires Add-in release 2.6.0 or later and StreamBase 7.6.3 or later.
Note
The button introduced in 2.5.1 now includes a Validate Only option, which is supported in StreamBase 7.6.4 or later.
- Support for Decision Tables
-
The Excel Add-in now supports creating decision tables for use with the Decision Table operator in StreamBase. This feature requires Add-in release 2.5.1 or later and StreamBase 7.6.3 or later.
- Support for Excel 2016
-
The Excel Add-in can now be loaded into Excel 2016.
- Support for LiveView Authentication
-
The Excel Add-in now supports LiveView authentication and authorization.
- Configurable Null-Value Display Option
-
The new Show Null Fields As drop-down on Excel's ribbon presents two options for the display of null-valued cells:
-
Empty Cell leaves the spreadsheet cell empty.
-
#NULL populates the cell with #NULL.
The drop-down determines how StreamBase fields with null values will be represented in a cell. The default value is Empty Cell. The chosen settings may not take effect until you restart Excel.
-
- Dialog Box Settings Saved
-
The settings for dialogs are kept from one invocation to the next within the same Excel session.
- Automatic Refresh for Server Views
-
When StreamBase or LiveView Server is stopped or changed and then restarted with a different list of streams, fields, or tables, the TIBCO Explorer tree views are updated even if the explorer was hidden during the change.
- Refresh Rates Less Than One Second Available
-
The Refresh Rate drop-down list on the Excel ribbon's StreamBase tab now contains values for 0.5, 0.2, and 0.1 seconds.
- New Option for Raw RTD Functions
-
The dialogs for Insert a StreamBase Function and Insert a LiveView Function now have a new Function Used pane that allows you to choose the option of using RTD functions instead of LVGetField or SBGetField. The RTD option may offer improved performance for updates that involve a large number of cells.
The following issues were resolved in the Excel Add-in.
| Fixed in Version 2.4.1 | |
|---|---|
| Number | Resolution |
| SB‑27845 |
A repeated error in the Logging tab reported Error
parsing filter expression followed by Couldn't
find local filter object for stream. The cause was identified and
corrected.
|
| SB‑27820 | Under some circumstances, a NullReferenceException error would appear when opening workbooks with existing StreamBase formulas in them. This was fixed. |
| Fixed in Version 2.4.0 | |
|---|---|
| Number | Resolution |
| SB‑27243 | This documentation page for the Excel Add-in was updated to reference Microsoft Office 2013. |
| SB‑26773 | Excel Add-in documentation pages were updated to reflect TIBCO branding. |
| Fixed in Version 2.2.0 | |
|---|---|
| Number | Resolution |
| SB‑24816 | When adding a server definition in Excel, selecting prompt for password caused a correct password to be rejected as invalid, but not when the password was cached by entering it in the use this password field. This was fixed. |
| Fixed in Version 2.1.0 | |
|---|---|
| Number | Resolution |
| SB-24625 |
The SetField() method did not correctly handle null values in tuples. This was
fixed. Calling the Excel Add-in's StreamBase.Excel.Addin.Pub.SBTuple.SetField
method with these parameters:
now correctly sets the value of myFieldName to null
in the tuple mySBTupleInstance.
|
| SB-24311 | If a cell value was entered for a filter expression without appropriate quotes, Excel would sometimes crash. This was fixed. |
| Fixed in Version 2.0.1 | |
|---|---|
| Number | Resolution |
| SBTS-12738 | In a previous release, the Excel Add-in, when used with LiveView 1.3, did not properly handle NULL-valued cells. This was fixed. |