Excel Add-In Version 7.4.0
December 6, 2017 - Build 6210
The supported versions of Excel include the following: Excel 2010, Excel 2013, and Excel 2016. Although not explicitly supported, there are currently no known issues when using the add-in with Excel 2007.
The supported versions of Essbase include 9.3.0 and higher.
The release notes for this version contain the following sections:
Add-In Load Behavior
In previous releases, when the Dodeca Add-In failed to load into Excel for any reason, the add-in could become disabled in a way that could not be corrected without modifying the HKCU LoadBehavior in the registry. This behavior has been addressed in a way that will not prevent the end-user or IT from manually enabling or disabling the add-in as needed. #1865
Essbase Connections: Stateless versus Stateful
In previous versions, the connections to Essbase were always stateless, meaning that the dodeca-essbase service would log on to Essbase only for the duration of a request or operation. While this is still the default behavior, a new deployment configuration setting is now supported, which controls whether the connections to Essbase are stateless or stateful. When stateful, the connection is established when the user connects to Essbase from the add-in, and is persisted until the user disconnects all worksheets that are connected to the database, the workbook is closed (and no other open workbook is using the connection), Excel is exited, the credentials associated with the connection time out, or the connection times out.
The StatefulEssbaseConnectionsEnabled setting is configured in the Add-In deployment configuration file. By default, the setting is false.
The use of stateful connections is recommended when there are databases that are slow to connect to, such as a database that uses security filters extensively for dimensions that contain a large number of members. #1876
Fixes
Caching – An error similar to the following is encountered when the user opens Excel or performs an operation that depends on a cached option: Cannot find cache info file / Unable to delete cache info. #1711
Connect to Essbase – In Excel 2010, the selected server, application, or database node is not highlighted in the task pane control. #1877
Essbase Options Configuration – Initial default option values, as defined in the deployment configuration, are not applied for those options that the user is allowed to modify. #1880
Member Select – An error similar to the following is encountered when the user attempts to expand an alternate rollup, which contains shared members that already appear in the tree: Unable to populate the child members of "Diet": Key already exists, key: 300-30. This issue was introduced in version 7.3.0.6125. #1868
Licensing and User Validation
In previous releases, when the Dodeca Add-In loaded during Excel startup, the licensing and user validation operations occurred synchronously. This could delay the launch of Excel by a number of seconds, depending on proximity to the dodeca server and various aspects of the network configuration. This behavior has been addressed by performing the licensing and user validation operations on a background thread, which allows Excel to launch and the Dodeca Add-In to finish loading while connectivity to the dodeca server is being established. #1884
Retrieval Mode Option: Advanced Interpretation, Free Form, and Template Retrieve
Retrieval Mode Overview
With this release, the Free Form and Template Retrieve retrieval modes, which were available in the Classic Add-In, are now supported by the Dodeca Add-in. In previous versions, Advanced Interpretation was the only mode supported, so the retrieval mode was not presented in the Essbase Options task pane. #1881
For a brief overview, all of the retrieval mode options allow a user to enter members into a spreadsheet and then perform a retrieve operation. The member names are interpreted by Essbase, which creates the most appropriate Essbase-compliant layout as determined by the location of the member names. The primary distinction between the Advanced Interpretation and the Free Form modes is that Free Form (including Template Retrieve) allows the user to enter Essbase Report Script Editor commands directly into the cells. When the user retrieves the data, the commands are executed on the server and the results are presented in the worksheet.
As with the Classic Add-In, there are guidelines associated with the retrieval modes.
For Advanced Interpretation, Free Form, and Template Retrieve:
-
A numeric member name must be preceded by a single quotation mark.
In addition, for Free Form and Template Retrieve:
-
The formatting commands, such as {BRACKETS}, {DECIMAL}, and \{EUROPEAN) are not supported due to incompatibilities with Excel formatting.
Retrieval Mode Example
The difference between Free Form and Template Retrieve modes is best described by way of an example, using the following script:
The results of the query are shown below. In Free Form mode, member accumulation is turned on, which is equivalent to using the <ACCON command. (Internally, the <ACCON command is prepended to the script if the script does not already contain an <ACCON or <ACCOFF command.)
As stated in the Oracle Technical Reference: “This command selects all members, regardless of the order of the select statements. Use this command to mix members from different dimensions in select statements.”
Retrieval Mode Configuration
As with other Essbase options, the deployment configuration XML controls whether the Retrieval Mode as well as each of options, Advanced Interpretation and Free Form, are displayed in the Essbase Options task pane, and sets the initial default retrieval option. By default, the option is set to Advanced Interpretation.
The retrieval option is displayed in the task pane on the Operations tab.
The retrieval option is a sheet-level option, which is persisted in a saved workbook.
Free Form and Template Retrieve Algorithm
The following is the current algorithm in use for free-form mode. The contents of the worksheet are scanned cell by cell and, if the trimmed value of the cell:
-
Begins with \{, <, //, or !, the cell is added to the report script unchanged;
-
Begins is a “ character and ends with a “ character, the cell is added to the report script unchanged;
-
Is a number and is a member name in the connected outline, it is surrounded by double quotes and added to the report script;
-
Is a number and is not a member name in the connected outline, it is ignored;
-
Is equal to the missing text string or the no access label, it is ignored;
-
Starts with a single quote, the single quote is stripped from the value, surrounded by double quotes and added to the report script;
-
Otherwise, the value is surrounded by double quotes and added to the report script.
After this processing is complete, if no lines are detected that begin with a \{, <, or ! character, normal Essbase grid retrieval processing is used. Otherwise, the report script undergoes further processing:
-
If the last line ended was not a ! command, then a ! command is added to the end of the script;
-
If the Use Aliases Essbase option is checked and an alias table Essbase option is specified, the appropriate <OUTALTSELECT and {OUTALTNAMES} commands are prepended to the report script;
-
If the Latest Time Period Essbase option is checked and a Latest Member Name Essbase option is selected, the appropriate <LATEST command is prepended to the report script.
-
If the Auto Sort Rows Essbase option is checked, the <SORTROWS command is prepended to the report script.
-
If the Suppress Zero Rows Essbase option is checked, the {SUPZEROROWS} command is prepended to the report script.
-
If the Suppress Missing Rows Essbase option is checked, the \{ SUPMISSINGROWS } command is prepended to the report script.
-
If the Free Form retrieval mode Essbase option is selected, and the Template Retrieve Essbase option is unchecked, and the report script does not contain the <ACCON or the <ACCOFF command, the <ACCON command is prepended to the script.
-
If the script does not contain the <QUOTEMBRNAMES command, the <QUOTEMBRNAMES command is prepended to the script.
-
If the script does not contain the \{DECIMAL n} command (where n is any number between 0 and 40 inclusive, or the value VARIABLE), the \{DECIMAL 40} command is prepended to the script.
-
If the script does not contain the \{INDENTGEN n} command (where n is any set of digits with an optional + or – sign), the \{INDENTGEN n} command, where the value of n is -5, 0, or 5, depending on the selected Indentation option, is prepended to the script.
-
If the script does not contain the {TABDELIMIT} command, the {TABDELIMIT} command is prepended to the script.
| The search for DECIMAL, INDENTGEN, and TABDELIMIT commands in the script does not currently search for these commands embedded with other format commands within a single set of curly braces. As a result, the script may, in certain cases, prepend those commands to the script. The impact of prepending those commands has not been thoroughly tested when duplicate embedded entries are found in the script, so it is recommended that you review your report scripts for such embedded commands and test them accordingly. |
Unknown Members and Strict Mode
A new Essbase option, referred to as Strict Mode, controls whether or not Essbase operations are performed on the server when unknown members are detected. The operations include all retrieve-based operations and the send operation. When strict mode is turned on, the detection of an unknown member prevents the operation from being executed, and the existence of unknown members is reported in the client. When the Display Unknown Members option is also turned on, the unknown members are also presented. #1539
The Strict Mode option is particularly useful within the context of performing a send operation. It allows for the data to be corrected before another send is attempted, and ensures that all members are valid before any data is written to the database.
The deployment configuration XML controls whether the Strict Mode is displayed in the Essbase Options task pane and sets the initial default value for the option. By default, the Strict Mode option is displayed in the task pane on the Global tab, and the option is turned off.
As with other options, once the user changes the setting, the user-specified value is persisted across sessions.
In the screenshot below, both the Display Unknown Members and Strict Mode options are turned on.
When both the Display Unknown Members and Strict Mode options are turned on, the status bar reports the detection of unknown members. If the Unknown Members Explorer task pane is already open, the unknown members are automatically presented in the task pane control.
If the Strict Mode option is turned on, and the Display Unknown Members option is not, the status bar reports the detection of unknown members as shown in the screenshot below.
Clicking the Details button displays the following message:
To view the unknown members, the user can simply turn on Display Unknown Members without having to resubmit the operation.