Excel Add-In Version 8.2.0

August 25, 2022 - Build 913

The supported versions of Excel include the following: Excel 2010, Excel 2013, Excel 2016, Excel 2019, and Excel 2021. 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 contain the following sections:

Essbase Data Audit Log

  • Fixed Issue: In version 8.1.1.72, support was added for writing Essbase data audit log records to the Dodeca repository when sending data to Essbase. Version 8.2 addresses an issue where the audit log records were not being properly added to the audit log table. #3177

Essbase Drillthrough Reports

  • Fixed Issue: When drilling through to a native Essbase drillthough report on a member whose name contains a comma, an error similar to the following is displayed: Unable to drill through. Line 1 cannot be parsed because of trailing characters after an end quote. #3234

Sign On Configuration Settings

In 8.2, additional configuration settings are supported for the Sign On user-interface control, which is displayed in the Connect to Essbase task pane. The configuration settings are specified either in the AppliedOLAP.Dodeca.ExcelAddInForEssbase.dll.config file or in the Dodeca Excel Add-In Configuration application metadata, which is described in the Sign On Configuration Metadata section. #1416, #3045

The control contains the Essbase Service, APS URL, and Essbase Server editors.

image

For each editor, the following settings are supported:

  • enabled - Controls whether the editor is enabled to allow user input.

  • limitToList - Controls whether the user is allowed to key in a value into the editor or only select from the items available in the dropdown list. When limitToList setting is true and only a single item exists for selection, the item is automatically selected and editor is disabled.

  • visible - Controls whether the editor is visible.

  • caption - If specified, replaces the default label that is displayed above the editor. This is a new setting in 8.2.

  • selectPrompt - If specified, replaces the default text displayed in the editor when items exist in the dropdown list, and no item is currently selected. This is a new setting in 8.2.

Each editorValueConfiguration represents an Essbase service, and the associated APS URL and Essbase server(s), as appropriate.

  • For the essbaseServiceURL

    • The url is required. The optional displayText, if specified, is presented in the editor instead of the url.

    • The same Essbase service URL can be used in multiple editorValueConfiguration sections, but the displayText must be specified, so that the combination of the url and displayText is unique.

  • For the apsURL

    • Only required when connections on the server(s) require an APS URL.

    • The url is the actual path. The optional displayText, if specified, is presented in the editor instead of the url. The displayText is a new setting in 8.2.

  • For each essbaseServer

    • The name is the name of the server. The optional displayText, if specified, is presented in the editor, and other places where the server name is displayed, instead of the name. This is a new setting in 8.2.

    • The connectAsStateful controls whether database connections on the server are stateful. This is a new setting in 8.2.

    • The statefulConnectionOverrides is used to override the server’s connectAsStateful for specific databases. This is a new setting in 8.2.

In the following example, the Essbase Service Editor is configured as visible, and the caption is specified as Essbase Environment. The APS URL and Essbase Server editors are configured as hidden. Both of the Essbase service configurations use the same service URL, but each have a different displayText value.

image
image

When display text is specified for an Essbase server, the display text is used in the Connect to Essbase task pane for the Essbase Server editor, the server/application/database hierarchy tree, and the Recent Connections dropdown. The display text is also used in the Recent Connections ribbon menu, the connection information presented in the status bar, and login dialog caption.

image

At startup, validation is performed to ensure the following:

  • Within the <editorValueConfigurations>, the <essbaseServiceURL> url is specified, and the combination of the url, and displayText is unique.

  • Within the <essbaseServers> for a given <editorValueConfiguration>, the <essbaseServer> name is specified and unique.

  • Within the <statefulConnectionOverrides>, the <database> application and databaseName are specified, and the combination of the application and databaseName is unique.

Invalid entries are reported and the Dodeca Add-In for Essbase ribbon is disabled.

Sign On Configuration for Stateful Connections

The Add-In configuration can be used to control whether database connections are stateful or stateless. #3016

  • When stateless, the dodeca-essbase service logs on to Essbase when an operation is requested by the Add-In, and then automatically logs off when the operation is complete before sending the results back to the client.

  • When stateful, the dodeca-essbase service logs on to Essbase when requested by the client, and the connection is persisted until either the client requests that the user be logged off or the connection times out. The connection timeout is configured in the dodeca-essbase.properties file in the dodeca-essbase service deployment.

The StatefulEssbaseConnectionsEnabled application setting is a global setting, which applies to all connections. By default, the setting is false, which indicates that all connections are stateless.

image

The <editorValueConfigurations> section can be used to override the StatefulEssbaseConnectionsEnabled for specific Essbase servers, using the connectAsStateful server setting. For a given server, the connectAsStateful setting can be overridden for a specific database by defining a <statefulConnectionOverride>.

image

In the example configuration, StatefulEssbaseConnectionsEnabled is set to false, so by default, all database connections are stateless.

For the OnPrem Server, the connectAsStateful setting is also false, so all database connections on the server are stateless, except for sample basic, demo basic, and ASOSamp sample, which override the setting by specifying the stateful setting value as true.

The configuration settings can be specified either in the AppliedOLAP.Dodeca.ExcelAddInForEssbase.dll.config file or in the Dodeca Excel Add-In Configuration application metadata, which is described in the Sign On Configuration Metadata section.

Sign On Configuration Metadata

In versions prior to 8.2, the configuration of the Sign On user-interface control, which is displayed in the Connect to Essbase task pane, can only be defined in the AppliedOLAP.Dodeca.ExcelAddInForEssbase.dll.config file. The file is packaged inside the .war file, which is used for deployment. #3015

In 8.2, the Sign On configuration settings can alternatively be stored in the Dodeca Spreadsheet Management System metadata repository database, which allows for administrators to change the configuration without having to redeploy the Add-In. The settings are administered using the Applications Metadata Editor within the Dodeca Spreadsheet Management System client.

DODECA_EXCEL_ADDIN Tenant

The configuration is defined by the Dodeca Excel Add-In Configuration application in the DODECA_EXCEL_ADDIN tenant. To access the application, the tenant must be populated with the necessary metadata, including an administrator application.

When the dodeca servlet is deployed, if the DODECA_EXCEL_ADDIN tenant does not exist, the tenant is automatically created, and will only contain the tenant record.

image

The tenant is not listed by the list-tenants command, but can be set as the active tenant using the tenant command.

image

To populate the tenant with an administrator application, ADMIN, and the Dodeca Excel Add-In Configuration application, use the import-tenant command to import the dai_management.zip, which is installed in the metadata folder of the Dodeca Framework. The ADMIN application may also be used for administering Add-In users.

image

Dodeca Excel Add-In Configuration Application

Run the ADMIN application in the DODECA_EXCEL_ADDIN, open the Applications Metadata Editor, and then select the Dodeca Excel Add-In Configuration application.

The properties include the EssbaseSignOnControlConfiguration and StatefulEssbaseConnectionsEnabled, which represent the equivalent settings in the AppliedOLAP.Dodeca.ExcelAddInForEssbase.dll.config file.

image

To define the configuration settings in the metadata using the Application Metadata Editor, you can use any of the following approaches:

  • Edit the .config file with the appropriate values for your environment using a text editor, and then import the settings by using the Import Add-In Config Settings tool.

    image

  • Specify the values in the metadata directly with the user-interface described below.

  • Initialize the configuration by importing the settings, and then modify the values as changes are required.

    IMPORTANT

    In order for the Add-In to use the configuration defined in the metadata instead of the .config file, the StatefulEssbaseConnectionsEnabled setting and <essbaseSignOnControlConfiguration> section must be completely commented out in the .config file in the deployment.

When using the Application Metadata Editor to edit the settings directly, the user-interface consists of the following dialogs:

Editor Value Configurations Editor

The Editor Value Configurations Editor is opened by clicking the Open "…​" button for the EditorValueConfigurations property.

image

Essbase Server Configurations Editor

For a given Editor Value Configuration, the Essbase Server Configurations Editor is opened by clicking the Open "…​" button for the EssbaseServerConfiguration property.

image

Stateful Connection Overrides Editor

If the server’s ConnectAsStateful setting needs to be overridden for any specific database, the Stateful Connection Overrides Editor is opened by clicking the Open "…​" button for the StatefulConnectionsOverride property.

image