Settings

The Settings section of the Deephaven ribbon has several features:

• the Configure button
• the Auto-Refresh Frequency Selector
• the Excess rows drop-down menu
• the Manage Deephaven Console panel

Each feature is described below.

Configure

Selecting Configure in the Deephaven ribbon will open the Configuration panel.

There are five tabs in the Configuration panel. Each is described in detail below.

General

The General tab allows you to control basic functions common to most Deephaven Excel Add-In operations:

• Max # of rows - This is the maximum number of rows the Deephaven Excel Add-In will retrieve from a single data request. Deephaven itself can handle billions of rows of data; Excel has a much lower limit, and may begin to display poor performance well before Excel’s actual row limit is reached. Testing by Deephaven has found that a limit of 100,000 rows generally avoids negative impacts on Excel. You may increase or decrease this value as needed.
• Max # of columns - This is the maximum number of columns the Deephaven Excel Add-In will retrieve from a single data request. Deephaven itself does not have a formal limit on the maximum number of columns a single table can have. Excel has a maximum number of columns it can manage, and may be difficult to interact with well before that column limit is reached. Testing by Deephaven has found that a limit of 500 columns generally avoids negative impacts on Excel. You may increase or decrease this value as needed.
• Timeout - This is the number of seconds the Deephaven Excel Add-In will wait for most operations to complete, such as logging in or retrieving a table schema. The default value is 30 seconds; however, these operations are expected to be relatively quick.
• Extended timeout - This is the number of seconds the Deephaven Excel Add-In will wait for long-running operations to complete, such as running a custom query or retrieving table data. While many data retrieval operations will be much shorter, it is possible for large or complex tables to require additional time for processing or transmitting. The default value is 600 seconds.
• Error on too many rows? - This check box controls how the Deephaven Excel Add-In will respond if a data retrieval request returns more than the "Max # of Rows" value. If this is checked, then the Deephaven Excel Add-In will display an error message if that situation occurs. If this is not checked, then the Deephaven Excel Add-In will display a warning and stop processing any additional rows received beyond the limit if that situation occurs. This check box is functionally identical to the Excess Rows drop-down on the Deephaven ribbon.
• Clear data on refresh? - This dropdown controls how the Deephaven Excel Add-In will operate when refreshing data.
  • If you have selected Clear data when refreshing, then for each query being refreshed, the data that was present from the previous data-retrieval will be erased first, and the new data will be written into blank cells. If you are using a query that returns a variable number of rows, this option will mean that any rows present in your previous data-retrieval will be removed; you will always see exactly the set of rows returned from your query at the moment it refreshed. If you have entered any other data or formulas into that area, that data will be removed as well.
  • If you have selected Leave data when refreshing, then for each query being refreshed, the data that was present from the previous data-retrieval will be left in place, and the new data will be written on top. If you are using a query that returns a variable number of rows, this option will mean that only rows up to the current rowcount will be updated; if you retrieved 100 rows before and there were only 90 rows now, this will leave those extra 10 rows with no further indication.
  • If you have selected Ask each time, the Deephaven Excel Add-In will ask for confirmation of what to do each time you manually refresh. If you select this option and then activate auto-refreshing, the 'Leave data when refreshing' behavior will be used, to avoid potentially overwriting user data.

Table Data

The Table Data tab contains controls relating to normal data retrieval from Deephaven. The following fields are presented:

• URL - This is the URL of the last server to which you tried to connect.
• Username - This is the last username you used when trying to connect.
• Default language - This is the language your custom queries will be assumed to be written in, unless you choose a different option in the Custom Queries panel.
• Show debug options? - This checkbox controls whether advanced debugging options are displayed on this panel.

Date handling

The Date Handling tab allows you to control how the Deephaven Excel Add-In handles date and time values in Deephaven columns.

The Maximum Precision checkbox

When this box is checked, the Deephaven Excel Add-In will return all of the available date information that is retrieved from Deephaven. However, Excel has limitations on how much precision it can display for sub-second values and time zone information. If this box is checked, dates will be treated as text values by Excel, but will have all the precision and time zone information available from Deephaven.

If this box is not checked, dates and times are returned as Excel-style dates in the following format:

yyyy-mm-dd hh:mm:ss

In this format, the 18th of April in 1775 at six forty-three and twelve seconds and seventy-five microseconds in the afternoon in the Eastern time zone would be displayed as 1775-04-18 18:43:12. Sub-second data and time zone information are not available when this box is not checked, and the auto-format options are not used.

Auto-Format Dates

The Auto-Format Dates section allows you to specify the names of individual columns that should always be formatted as dates only when returned from Deephaven, when using full precision. Clicking the Add button will open a dialog allowing you to enter the name of a column to always format as a date. If you have selected a row in the Auto-Format Dates section, the Remove button will remove that column from the list. Columns auto-formatted as dates will be returned in the form yyyy-mm-dd, with no time information attached. In this format, the 18th of April in 1775 at six forty-three and twelve seconds and seventy-five microseconds in the afternoon in the Eastern time zone would be displayed as 1775-04-18, with no time information displayed.

Auto-Format Times

The Auto-Format Times section allows you to specify the names of individual columns that should always be formatted as times only when returned from Deephaven, when using full precision. Clicking the Add button will open a dialog allowing you to enter the name of a column to always format as a time. If you have selected a row in the Auto-Format Times section, the Remove button will remove that column from the list. Columns auto-formatted as times will be returned in the form hh:mm:ss.fff, with no date information attached. In this format, the 18th of April in 1775 at six forty-three and twelve seconds and seventy-five microseconds in the afternoon in the Eastern time zone would be displayed as 18:43:12.075, with no date information displayed.

SBE

The SBE tab controls the behavior of the Simple Binary Encoding components of the Deephaven Excel Add-In. Simple Binary Encoding is an infrequently used option of the Deephaven database.

• URL - This is the address of the SBE server to which you are connecting. This is not the same address as your Deephaven API server.
• Port - This is the port of the SBE server to which you are connecting.
• Table - This is the name of the last table you retrieved via SBE.
• Show SBE section? - When this checkbox is checked, the SBE section of the Deephaven ribbon will be displayed. When this checkbox is not checked, the SBE section of the Deephaven ribbon will be hidden. Most users are not expected to need the SBE section.

Advanced

Excess Rows

The Excess Rows drop-down menu to the right of the Configure button allows you to modify what the Deephaven Excel Add-In will do if it attempts to retrieve more rows than it can handle.

When this is set to ‘Fail’, the Deephaven Excel Add-In will display an error message when that situation occurs and will not return the data. If this is set to ‘Truncate,’ the Deephaven Excel Add-In will retrieve as many rows as it can handle, but cause a warning message to indicate that the rows have been truncated.

Auto-Refreshing

The Auto-Refreshing dropdown menu to the right of the Configure button allows you to activate the Deephaven Excel Add-In's auto-refresh feature. This dropdown allows you to select an auto-refresh frequency:

While auto-refreshing, error messages about excess numbers of rows will not be displayed when queries automatically refresh. By default, queries do not auto-refresh. You must use the Manage Workspace function to mark each query that you want to refresh under the Auto? column.

You can also see from the Last Refreshed Label the last time you refreshed your queries, either manually or automatically.

Manage Deephaven Console

The Manage Deephaven Console option underneath the Configure menu allows you to control the Deephaven worker on the server with a finer degree of control. Your Deephaven worker is what handles requests to Get Table Data, Run Custom, or retrieve data from RTD. If you do not have a Deephaven worker, then you may only retrieve pre-defined data via Get Persistent Query.

By default, the Deephaven Excel Add-In will request a Deephaven worker from the server, using whatever worker and Java profile are configured as server defaults. For most users of the Deephaven Excel Add-In, this should be all they need. Some users may need to select a specific Deephaven worker, or a special Java profile, or other advanced connection features.

Any users familiar with creating consoles via Deephaven's own core interface will be familiar with these options. Not all of the options available in Deephaven's core interface are available from the Deephaven Excel Add-In; if you require those advanced features, please use Deephaven's core interfaces.

• The Available hosts dropdown allows you to select which of the available workers to use to process data for your queries from Excel. In general, only workers marked as 'query' workers are suitable for Excel workers.
• The Available profiles dropdown allows you to select which of the Java profiles configured on the server to use for the worker that will process data for your queries from Excel.
• The Console language dropdown allows you to select which language your worker will use. This is identical to the Default Language option on the Configuration page.
• The Max heap MB option allows you to specify how much memory you want to reserve for your Deephaven worker. Most queries run from Excel are expected to be under 2 GB at most. If you are running custom queries with particularly high data usage, you may need to increase this figure. Be aware that your server only has so much memory available; you can potentially use up all of the server's memory by setting this figure to an overly-high value.
• Additional parameters allows you to specify extra parameters to the Java Virtual Machine that runs your worker. Most users should never need to enter anything here.
• Start new console will stop your current worker (if any) and attempt to create a new one using the parameters specified. If there is an error starting your worker, you will see an error message appear in the Startup Messages section.
• Stop current console will stop your current worker, but not attempt to create a new one. If for any reason you do not need your console, you can stop it from here.