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 (8.0.67.102)*, NetAdvantage 2011, Volume 1 (11.1.20111.2042), Aspose.Cells 17.1.0.0, Syncfusion Essential Studio 13.4.0.63, and GdPicture.NET 11.0.6.
*Upgraded from SpreadsheetGear 2012 7.4.7.104 to 2017 8.0.67.102.
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 11.1.1.3 are supported and tested on Java 1.5. The Dodeca-Essbase service for all Essbase versions 11.1.1.4 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:
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
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 dodeca.properties 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.
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.
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.
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.
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.
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:
context.xml
file. The deployment of the file depends on which Java
application server is used. Contact support@appliedolap.com for
additional details.Configuring JNDI Data Sources on the Server
The JNDI Data Source configuration contains the following properties:
Property | Type | Description |
---|---|---|
name | required | 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. |
Auth | required | Scope of the authorization |
Type | required | Class instance type created by the JNDI class factory |
Factory | required | JNDI class factory |
Singleton | required | Instancing model for the data source |
Server | optional | Essbase server for the connection |
application | optional | Essbase application for the connection |
Cubename | optional | Essbase database for the connection |
apsServer | optional | The APS server to use with the data source, which will override the default APS defined in essbase.properties. |
Encrypted | optional | Set to true to indicate the specified username and password properties are TripleDES encrypted |
Username | optional | Username used for normal Essbase operations. Typically used for send and calc functionality. |
Password | optional | Password used for normal Essbase operations. Typically used for send and calc functionality. |
adminCredentialsRequired | optional | Indicates whether to use admin credentials for all operations using this data source |
adminUsername | optional | Used for certain Essbase functionalities requiring enhanced credentials |
adminPassword | optional | 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.
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. #1734The SpreadsheetGear 2017 upgrade includes the following features and enhancements:
AGGREGATE
function:ACOT | DAYS | ISO.CEILING |
ACOTH | DECIMAL | ISOWEEKNUM |
AGGREGATE | ENCODEURL | MUNIT |
ARABIC | FILTERXML | NUMBERVALUE |
BASE | FLOOR.MATH | PDURATION |
BINOM.DIST.RANGE | GAMMA | PERMUTATIONA |
BITAND | GAUSS | PHI |
BITLSHIFT | IFNA | RRI |
BITOR | IMCOSH | SEC |
BITRSHIFT | IMCOT | SECH |
BITXOR | IMCSC | SHEET |
CEILING.MATH | IMCSCH | SHEETS |
COMBINA | IMSEC | SKEW.P |
COT | IMSECH | UNICHAR |
COTH | IMSINH | UNICODE |
CSC | IMTAN | WEBSERVICE |
CSCH | ISFORMULA | XOR |
Excel 2016 compatible conditional formatting including:
There are also numerous bugfixes, which are not listed here.
Fixed the following issues with the SQL Selector Tree:
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 7.0.0.5388, 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
Steps:
Make the point-of-view selections.
It is not necessary to build the view.
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:
Token Replacement Options, which include:
Depending on the context, which is determined by the current event link, the selector tokens may or may not reflect the current selections.
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.
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.
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 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>])
The @FRow, @LRow, @FCol, and @LCol functions were reverted to their original implementations in the 7.4.0.6210 release.
Fixed the following issues with LookupValue Function: