Dodeca Version 7.3.0

October 3, 2017 - Build 6125

This version of the Dodeca Framework uses the .NET Framework, version 2.0, Service Pack 1 and higher on the desktop. The components used in this version of Dodeca are SpreadsheetGear 2017 (*, NetAdvantage 2011, Volume 1 (11.1.20111.2042), Aspose.Cells, Syncfusion Essential Studio, and GdPicture.NET 11.0.6.

*Upgraded from SpreadsheetGear 2012 to 2017

This version of Dodeca has two server-side services that run inside a Java Application Server. The Dodeca service is supported and tested on Java 1.6. The Dodeca-Essbase service for all Essbase versions prior to, and including, Essbase are supported and tested on Java 1.5. The Dodeca-Essbase service for all Essbase versions and higher are supported and tested on Java 1.6. Both services are known to run on Java 1.7, although extensive testing has not been performed on that Java version.

The release notes for this version contain the following sections:

Authentication Services

  • RoleFilters – A new property has been added to Authentication Services that obtain roles directly from an upstream provider (EssbaseUserRolesFromGroupNames, LDAPUserRolesFromGroupNames, and DodecaUserRoles). RoleFilters allow administrators to govern which roles obtained from the provider are utilized and displayed within the context of a Dodeca Application. Multiple filter strings can be leveraged, and wildcards are supported. #1802.

  • LDAP Authentication Services: ConnectionMethod – A new property has been added to the LDAPConnectionProperties leveraged by the LDAPUserRolesFromGroupNames and LDAPUserRolesFromSqlPassthroughDataSet authentication services. The ConnectionMethod applies primarily to the Active Directory ImplementationType and dictates the way that authentication and role lookups are performed. #1847

Dodeca Metadata Service

  • Fixed Issue: When the dodeca metadata database credentials, as specified in the file, are invalid, an error similar to the following is returned by the service: The servlet connection validation failed: "SOAP-ENV" is an undeclared namespace. Line 1, position 41. #1835

Essbase Adhoc Views

  • Fixed Issue: When an adhoc view’s ExcelAddInMode property is True and a connection is assigned to the EssbaseConnectionID, the RetainOnRetrieval setting is not applied for a workbook that was opened using the Open Workbook tool. #1772

  • Fixed Issue: An Undo of an Essbase operation positions the restored data with the origin at A1 instead of at the same origin as the original data. #1810

Essbase Outline Viewer

The Outline Viewer utility, which is included with the OLAPUnderground utilities, has been implemented in Dodeca as a new view type. The support of this capability involves both server-side and client-side components. On the server, the Dodeca metadata service is responsible for generating and storing the outline XML files. The client supports the ability to control whether outline extraction is performed for a connection, manage the outline XML files, and view the outlines and database properties. #1668

Dodeca Metadata Service

The Dodeca metadata service performs outline extraction when requested by the client and when the service detects a change in the database. The check for database changes occurs at a specified time interval, which is configured in the file. By default, the interval is 1440 minutes, or 24 hours. The timer starts when the Java application server is started. Only the databases for which outline extraction is enabled for the associated Essbase connection, are checked for changes.


When extraction is requested by the client or a change is detected (or if no extraction has been performed for a database when the check is performed), the service generates a new version of the outline XML, which is stored in the Dodeca metadata repository as a binary artifact.

This is a screenshot of example Essbase Outline XML entries in the BINARY_ARTIFACTS table for the SAMPLE tenant. Included are two versions of the Sample.Basic database outline XML.


Dodeca Client

In the Dodeca client, the Outline Viewer view type renders the outline XML as a hierarchical representation of the outline and the database (or cube) properties.

This is a screenshot of an Outline Viewer view. This particular view is configured to present the Sample.Basic outline and properties.


The integration of the Outline Viewer into the Dodeca client includes the following:

  • Essbase Connections Editor – Outline extraction can be enabled on a connection-by-connection basis by setting a connection’s OutlineExtractEnabled property.


After setting the OutlineExtractEnabled property to True, you can force the outline to be extracted by clicking the Update Outline XML button to initiate the first extraction (or at any time to force an update.) As described above, subsequent extractions are performed (at the configured time interval) when a change to the outline or database is detected. If you do not click the Update Outline XML button to generate the outline XML after enabling extraction, the first extraction is performed by the server when the next check for changes occurs.

The Outline Viewer button opens an Outline Viewer for the active Essbase connection. The most recent version of the extracted outline is presented.

Outline extraction requires Admin database privileges. In the screenshot above, the AdminUsername and AdminPassword are specified, but as an alternative, a DataSource can be specified. Use of a JNDI data source is the preferred approach, and is described in the JNDI Connections for Essbase section of the Release Notes.
  • Essbase Outline XML Binary Artifacts Editor – This editor can be opened from the Admin menu. It lists and provides the ability to manage the outline XML binary artifacts stored in the metadata repository. Multiple versions of a given database’s outline XML are likely to exist. Individual artifacts can be deleted, although maintaining older versions of the outline XML artifacts allows for viewing different versions of an outline side-by-side.


The View XML button allows the actual XML to be viewed in an external application, such as Internet Explorer. The View Outline button opens the Outline Viewer for the active outline XML binary artifact, which represents a specific version of an extracted outline.

  • Views Editor – Supports the ability to create an Outline Viewer view for a specified Essbase database. The view can be added to a view hierarchy, which provides end users the ability to view the outlines.


    The only required properties are the EssbaseConnectionID and the WindowsViewUIObjectTypeID, which should be set to OutlineViewer.

Essbase Scripts Metadata Editor

To facilitate the development, testing, refinement, and reuse of Essbase queries and scripts, this release includes a number of new capabilities which are brought about by the introduction of an Essbase Scripts Metadata Editor. Essbase MDX, Report Script, Member Query, and Calc scripts can easily be created, tested, and refined via a familiar interface for Dodeca developers and leveraged throughout the product by utilizing the EssbaseScriptID associated with a reusable Essbase Script object. #1838

  • Developing Essbase Queries and Scripts – The Essbase Scripts query editor provides syntax highlighting and context-sensitive completion hints for all available query types. In this release, it provides further code assistance via supplementary documentation for the MDX and Report Script query types. Documentation for the remaining query types will be introduced in a subsequent release.

    After creating a new Essbase Script and assigning a QueryType, the Essbase Query Editor can be opened by selecting the Query property and pressing the edit button […] on the right side of the editor.

    When a completion hint is needed but not automatically displayed, Ctrl+Space can be pressed on the keyboard to display a prompt containing the valid items for the current context.
  • Testing Essbase Queries and Scripts – The ability to test queries has been added directly to the query editor. For the MDX, Report Script, and Member Query types, results can be shown in a grid or as a list of members by toggling the "Show as Member List" checkbox.


    For Calc scripts, the script can either be executed or validated by toggling the "Validate Only" checkbox.

  • Refining Essbase Queries and Scripts – Two Essbase Script query editors can be open simultaneously, and changes can be made to the Essbase Script properties while a query editor is open. This was incorporated so that refinements to the Test properties can be made while an editor is open, and an open script can be minimized while other work is performed or another script is opened for comparison. The Test properties, including Tokens, are utilized to provide context for testing the script, and they serve to provide information otherwise provided by a View or Workbook Script. Test Properties do not apply when an Essbase Script is executed in the context of a View; rather, these properties are provided by the View or Workbook Script in which the query is executed.


    As with SQLPassthroughDataSets, Essbase Scripts support token replacement at the time of execution, and Test Tokens can be added to the script for testing. Test Token values can be loaded from an open View (see SQLPassthroughDataSets – Testing and Debugging Enhancements ) or entered manually in the query editor. In addition to providing completion hints for query functions, properties, and commands, the query editor provides completion hints and displays the values of defined Test Tokens when you begin typing a token name.

  • Reusing Essbase Queries and Scripts – With the introduction of the Essbase Scripts Metadata and Query Editors, EssbaseScriptIDs can now be used throughout the product where appropriate. For script and query-based Essbase Selector Lists, a filtered list of EssbaseScriptIDs is now available.


    Similarly, filtered EssbaseScriptID arguments have been added to all Essbase Workbook Scripts that utilize MDX, Report Script, Member Query, or Calc.


Essbase Selectors

  • New: EssbaseMdxQuery Selector List – The new MDX script selector list type supports the ability to generate the list of Essbase members presented by the selector with an MDX query, which can be used, for example, to create dynamic subsets of members. #1514

  • Enhancement: Essbase Selector Tree – The Item Selection and Item Expansion filters exposed in the Essbase Selector Tree have been enhanced to allow members to be hidden/shown or designated selectable/non-selectable in a tree on the basis of attributes and attribute expressions. #1850

  • Fixed Issue: Essbase Selector Tree – When an Essbase Selector Tree is using either an EssbaseDelimitedString or an EssbaseSQLPassthroughDataSet selector list and the selector tree has the BaseMemberNodePolicy set to AddBaseMemberAsDescendantNode, the first branch of members is displayed with the appropriate base members as the leaf nodes, but subsequent based members are not displayed (but rather only the parent nodes are displayed.) #1783

  • Fixed Issue: Essbase Selector Tree – When multiple selections are allowed and a base member and the related shared members (or only the shared members) are represented in the tree by multiple nodes, and either the user selects multiple occurrences of the member OR the selection defaults to the last used items (and the SharedMembersCheckboxChecked is True), the selector tool displays the member name/alias for each selected occurrence. (This may only be obvious in the tooltip if the SelectionTextMaxCharacters is used to limit the number of displayed characters.) In this release, only unique member strings are presented in the selector tool and tooltip. #1848

  • Fixed Issue: Essbase Selector Tree – In previous releases, when the selector is configured to allow multiple selections, and the control is opened by the user when the last used item includes a shared member, all occurrences of the shared member are selected when the SharedMembersCheckboxChecked is True. In this release, only the base member or first shared member (if the base member is not represented in the tree) is selected regardless of the value of the SharedMembersCheckboxChecked setting. #1851

Excel Views

  • Added the ability to save Excel-based views in CSV format using the standard Save As Excel tool. This functionality is introduced in conjunction with the SaveWorkbookAsCSV Workbook Script method, which can be utilized to accomplish the same result. #1790

JNDI Connections for Essbase

To address a potential security vulnerability exposed by the storage of elevated credentials (i.e. encrypted Essbase administrator username and password) on the local client hard drive, this release provides a custom implementation that leverages the Java Naming and Directory Interface (JNDI) for storing and utilizing specific Essbase connection information, which is configured as part of the Java application server. #1823

The use of the JNDI configured Essbase connections is optional; however, the advantage is that the capability eliminates the risk of (encrypted) elevated credentials being stored on the client hard drive as well as providing the ability to manage those credentials completely on the application server.

Prior to this release, the only way to configure Essbase administrator credentials was to specify the AdminUsername and AdminPassword properties for a given Essbase connection. Administrator credentials are required for the following functionalities.

  • Relational Essbase outline caching (refer to version

  • The EssbaseUserRolesFromGroupNames authentication service under certain circumstances (refer to version

  • When the Essbase connection’s ExtendedCubeInfoEnabled is set to true (refer to version

  • Essbase Outline Extraction, which is used in conjunction with the Outline Viewer capability also being introduced with this release.

For these functionalities, the specification of the AdminUsername and AdminPassword for the appropriate Essbase connection will continue to work as before, but the JNDI capability can be used in lieu of these properties.

Elevated credentials are also sometimes needed when performing a calculation or send operation. In previous releases, this has been handled by specifying credentials with the appropriate privileges as the Username and Password properties of an Essbase connection. This will continue to work as before as well, but it can also be replaced by using the JNDI functionality.

Using the JNDI connections for Essbase requires the following:

  1. Configuring the JNDI data sources for the dodeca-essbase service in the Java application server. The configuration is contained in a context.xml file. The deployment of the file depends on which Java application server is used. Contact for additional details.

  2. Associating a JNDI data source with an Essbase connection by using the Essbase Connection Metadata Editor on the client to specify the DataSource property for an Essbase connection.

Configuring JNDI Data Sources on the Server

The JNDI Data Source configuration contains the following properties:

Property Type Description



The data source name, which must be prefixed with "essbase/", less the double quotes. "essbase/DEFAULT" is reserved for providing default settings for all connections.



Scope of the authorization



Class instance type created by the JNDI class factory



JNDI class factory



Instancing model for the data source



Essbase server for the connection



Essbase application for the connection



Essbase database for the connection



The APS server to use with the data source, which will override the default APS defined in



Set to true to indicate the specified username and password properties are TripleDES encrypted



Username used for normal Essbase operations. Typically used for send and calc functionality.



Password used for normal Essbase operations. Typically used for send and calc functionality.



Indicates whether to use admin credentials for all operations using this data source



Used for certain Essbase functionalities requiring enhanced credentials



Used for certain Essbase functionalities requiring enhanced credentials

This is a sample context.xml file.


Associating a JNDI Data Source with an Essbase Connection on the Client

Specify the appropriate JNDI data source as the Essbase connection’s DataSource property.


Metadata Editors

  • In prior releases, when a metadata instance, such as a View, was created by copying another instance, the CREATED_BY, CREATED_DATE, UPDATED_BY, and UPDATED_DATE fields in the metadata repository database reflected the values for the source metadata instance. For this release, the values for these fields reflect the current date and the current user. #1734

Metadata Property Search Utility

  • Fixed Issue: When the Search Metadata Properties dialog is open and the Workbook Script Editor is not also open, pasting a value into the search text field results in two occurrences of the pasted value appearing in the field. #1855

SpreadsheetGear 2017

The SpreadsheetGear 2017 upgrade includes the following features and enhancements:

  • Official support for Excel 2013 and Excel 2016.

  • Support for 50 new Excel 2013 functions as well as the Excel 2010 AGGREGATE function:




















































  • Excel 2016 compatible conditional formatting including:

    • Icon sets

    • Data Bars

    • Color Scales

    • Highlight Cells

    • Top n / Bottom n Rules

    • More than 3 Rules per Cell

    • Advanced Rules

    • Overlapping Rules

  • Autofilter by font color, cell color and conditional format icon.

  • Support for reading, writing, and rendering gradient cell backgrounds.

  • Enabled multi-threaded recalc for array formulas and worksheet functions which evaluate as arrays (including functions such as SUMPRODUCT).

  • Improved the performance of copying formulas with 3d cell references, 3d name references and / or addin function references.

  • Improved the performance of a number of worksheet functions.

  • Improved the performance of parsing formulas with 3d references which refer to a workbook with a large number of worksheets. This yielded a dramatic performance improvement when reading Open XML workbooks with a significant number of such references.

There are also numerous bugfixes, which are not listed here.

SQL Connections

  • Enhancement – When connecting to a Microsoft SQL Server database, support has been introduced for the equivalent of WindowsAuthentication authentication mode, where queries for a connection are executed as the Windows user currently logged in. Because all queries are executed on the server, this is accomplished via impersonation. Although the end result is the same, the specified connection user must have impersonate permissions in order to execute queries as the Windows user. This functionality is leveraged by setting the Database to "Microsoft" and the AuthenticationMode to "WindowsAuthentication". #1799

SQL Selectors

  • Fixed Issue: When migrating a selector between a Listbox or Combobox and a SQL Tree, an error similar to the following is encountered: The view "<view name> window cannot be initialized. Unable to setup selectors. Unable to initialize selector "<selector ID>". Unable to cast object of type AppliedOLAP.Dodeca.Common.SelectorItemCache" to type "AppliedOLAP.Dodeca.Common.SQLSelectorItemCache. #1789

Fixed the following issues with the SQL Selector Tree:

  • The MinGenSelectable setting is not applied unless at least one SelectionFilter is defined. #1778

  • An error similar to the following is encountered when opening a view that uses a SQL selector tree, and the selector is attempting to restore the default selection (with the selector list’s ValidateDefaultSelection property set to True.): "The view "<view name>" window cannot be initialized. Unable to setup selectors. Unable to initialize selector "<selector ID>". Object Reference not set to an instance of an object." #1779

  • The Import from Excel command fails to restore selections for a SQL selector tree. #1780

SQLPassthroughDataSets - Testing and Debugging Enhancements

To facilitate the testing and debugging of relational queries, this release includes two new capabilities and two SQL query editor enhancements:

  • Automatically load the test tokens for a SQLPassthroughDataSet from the point-of-view selections of an open view.

    In version, the ability to define test token values for a SQLPassthroughDataSet was introduced. The test tokens values are used by the Test Data Set utility to replace the tokens within a SelectSQL statement before executing the query. Once defined, the test tokens are very useful for testing a query, but can be cumbersome to enter. To assist with the definition of the test tokens, the Load Test Tokens dialog allows you to load the test tokens directly from an existing open view. The test tokens are generated from the application tokens, the selected view’s tokens and current point-of-view selections. #1776



    1. Open the view that you want to use as the source of test tokens. Typically, this will be the view that will be using the selected SQLPassthroughDataSet.

    2. Make the point-of-view selections.

      It is not necessary to build the view.
    3. From the SQL Passthrough DataSets Editor, select the data set, then click the Load Test Tokens button to display the dialog, which lists all of the open views. Select the appropriate view, then click OK.

      The Remove existing test tokens checkbox can be checked if you want to clear all of the existing tokens before the test tokens are loaded from the selected view. Note that any duplicate tokens are simply replaced.

      The test tokens are now defined.

  • From the Workbook Script Debugger, view a resolved query within context (i.e. with tokens replaced) and optionally preview the query results.

    While test tokens can be extremely useful for testing and debugging relational queries, the ability to view and test a query within context from the Workbook Script Debugger is sometimes needed. Often, tokens are added on-the-fly by a workbook script and cannot easily be reproduced through the use of test tokens. #1777

    The Workbook Script Debugger interface now includes a Data Sets tab, which contains a toolbar with the following tools:

    • Data Set Range dropdown – Lists all the data set ranges defined for the view.

    • Data Table Range dropdown – Lists all the data table ranges defined for the selected Data Set Range.

    • Token Replacement Options, which include:

      • Do not replace tokens – Displays the query as defined in the SQLPassthroughDataSet.

      • Use Selector Tokens – Uses the existing point-of-view selector tokens (along with all non-selector tokens) to replace the tokens in the query. Depending on the context, which is determined by the current event link, the selector tokens may or may not reflect the current selections.

      • Use Current Selections – Uses the current point-of-view selections (along with all non-selector tokens) to replace the tokens in the query.

Depending on the context, which is determined by the current event link, the selector tokens may or may not reflect the current selections.
  • Preview Query Results – Opens the Preview dialog, which executes the query and displays the results.

The query associated with the selected data table range is displayed in the area below the toolbar. When the Token Replacement Option is changed, the query is automatically refreshed to reflect the option.

  • SQL Query Editor Enhancements: Find & Replace and better Token recognition/autocomplete.

    In order to better propagate changes throughout a query in the SQL query editor, the Find form, accessed by pressing Ctrl+F, has been replaced with a Find & Replace form that includes more robust search options and the ability to mark or replace all instances of discovered strings.


    More complex token naming conventions are now supported by the SQL query editor, and Token auto-completion has been more seamlessly integrated into the SQL query editor, which now shows defined TestToken values in a tooltip to the right of the auto-completion prompt.


View Metadata Editor

  • The Copy view dialog now supports the ability to optionally copy the source view’s Excel template binary artifact and/or workbook script and automatically associate the new template and/or workbook script with the new view. The copy of the associated metadata is created when the new view is committed. #1372

  • The Delete view command now supports the ability to optionally delete the view’s Excel template binary artifact and/or workbook script, if applicable. If either an Excel template or workbook script are associated with the view, the Delete View dialog is displayed when the Delete button is clicked. The deletion of the associated metadata occurs when the deletion of the view is committed. #1504

It is the responsibility of the administrator/developer to ensure that the Excel template and workbook script are not used by other views when electing to delete either of these metadata instances.
  • When a new view is created, the WindowsViewUIObjectTypeID is pre-set to the appropriate value based on the view type. For instance, the value is set to PDF for a PDF view type, WorkbookView for all Excel-based view types, etc. #1853

View Selector Tree

  • Fixed Issue: When the last leaf node in the View Selector Tree is not a child of a root node, the Find command fails to locate a node that appears in the tree above the currently selected node. #1775

Workbook Script Editor

  • The Workbook Script Editor now allows a value to be pasted into the Name cell in the Property section. #1486

  • Fixed Issue: The "Trim trailing linefeeds" checkbox at the lower left of the Value Editor dialog does not trim the trailing linefeeds when checked. #1858

Workbook Scripting Functions, Event Links, and Methods – Fixes and Enhancements

  • New Method – The CopyFromBinaryArtifact method supports the ability to copy a range or a sheet from an Excel template binary artifact to the view’s workbook. This functionality could be achieved previously, but required calling LoadWorkbook, then either CopyRange or CopyWorksheet, and finally UnloadWorkbook. #1662

  • New Method – The OpenApplication method supports the ability to easily launch a Dodeca Spreadsheet Management System instance with either a ClickOnce URL or the application parameters. When application parameters are provided, the target instance will be launched using the deployment mechanism of source instance. #1803

  • New Method – The SaveWorkbookAsCSV method supports the ability to save the view’s workbook as a CSV file. The CSV filename and folder may be specified as method arguments or, optionally, the Save File dialog can be presented to allow the user to specify the filename and location. The PreserveLeadingSingleQuotes argument controls whether the leading single quote within a cell, which used by Excel to denote a text value, is preserved or removed from the saved cell values. #1790

  • New Method Overload – The AddDataCache method has a new overload, EssbaseMDXScript, which allows a data cache to be populated with the results of an MDX query. For instance, the overload can be used to create a set of rows (or columns) with missing data rows suppressed.

    The advantage of using the EssbaseMDXScript overload rather than the EssbaseReportScript overload for this purpose is that with the latter, a member from a given row dimension is returned regardless of whether all items are suppressed or not. If all items are suppressed, the report script returns the dimension member name, whereas with an MDX query, no rows are returned when all items are suppressed. #1513

  • New Method Argument – The EssbaseReport method has a new argument, IncludeAttributes, which controls whether an additional retrieve is performed on the server in order to obtain and return member attributes on calculated and member cells. #1655

  • New Method Argument – The SendEmail method has been enhanced by the addition of the ZipAttachments and ZipCompression arguments. These arguments allow the attachments to be compressed and sent in a .zip archive with the outgoing e-mail. #1795

  • New Function – The BinaryArtifactExists function allows a workbook script to determine whether a specified binary artifact exists in the metadata database for the current tenant. #1782

    Function Usage:
    @BinaryArtifactExists(<BinaryArtifactID>, [<VersionNumber>])
  • New Functions – The FirstColumnLetter (@FColL) and LastColumnLetter (@LColL) functions are similar to the FirstColumn and LastColumn functions, respectively, but they return a column letter instead of a column number. #1807, #1811

  • New Function – The IsSharedView function returns whether the current instance of a View was shared by another user. #1800

  • Modified Functions: Added an optional AdjustBy argument to the FirstRow (@FRow) and LastRow (@LRow) functions to adjust the returned value based on the specified integer, which either increments or decrements the actual first row or last row, respectively. #1809, #1813

  • Modified Functions: Added an optional AdjustBy argument to the FirstColumn (@FCol) and LastColumn (@LCol) functions to adjust the returned value based on the specified integer, which either increments or decrements the actual first column or last column, respectively. #1808, #1812

The @FRow, @LRow, @FCol, and @LCol functions were reverted to their original implementations in the release.
  • Fixed the following issues with LookupValue Function:

    • When the KeyColumnNumber argument is 1, which is the default, and the MatchType argument is StartsWith, the function fails to detect an existing match. #1801

    • When the KeyColumnNumber argument is not 1 and the MatchType is Exact, which is the default, the function fails to detect an existing match when the string specified as the Value argument is not all lower-case. #1856

    • When the KeyColumnNumber argument is not 1 and the MatchType is Exact, which is the default, the function fails to detect an existing match, the following error is returned: Unable to solve formula after 1000 iterations. #1857

  • Fixed Issue –– SQLPassthroughExecute Method: When the Transaction argument is False and a SQL exception occurs during processing, an error similar to the following is encountered: Cannot invoke a rollback operation when the AutoCommit mode is set to "true". #1806