November 22, 2013
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 2012 (7.0.5.140), NetAdvantage 2011, Volume 1 (11.1.20111.2042), Aspose.Cells 7.0.3.0, and Syncfusion Essential Studio 11.2035.0.25.
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:
Application - Fixes
Application - View Selector Tooltips
Essbase Authentication Services
Essbase Excel View - Fixes
Essbase Issue: Shared Members Treated as Duplicate Members
Excel Page Setup Dialog
Excel (and Essbase Excel) Views - Fixes
Excel (and Essbase Excel) View Template Designer
Mac OS X Release Directory
Metadata Property Search Utility
PDF View
SQLPassthroughDataSet - Fixes
SQLPassthroughDataSet - Parameterized SQL Select Statements
View Hierarchy Metadata Editor - Find Item/View
Workbook Scripting - Event Links, Functions, Methods
XCopy Release Directory
Application - Fixes
Fixed Issue: When the application’s *CheckForMetadataUpdatesFrequencyPolicy* property is set to CheckForMetadataUpdatesOnViewOpen or CheckForMetadataUpdatesOnSessionStartUp, the following error is encountered on startup when any metadata instance ID contains an ampersand (&): (#1223)
Unable to generate or load the checksum list. The application will set the CheckForMetadataUpdatesFrequencyPolicy to CheckForMetadataUpdatesOnUse, which requests the checksum for each metadata instance when used. Unable to construct and deserialize checksum list from file "C:\Users\kongsy\AppData\Local\Apps\2.0\Data\8N98E08O.V1T\3O3HX79J.B57\sfa6…app_b17044c700843c6f_0006.0005_a1483a986c6b6297\Data\sfa.CHECKSUM_LIST.CHECKSUM_LIST.1". There is an error in XML document (1, 10473). An error occurred while parsing EntityName. Line 1, position 10473.
Application — View Selector Tooltips
The ability to configure the contents of the tooltip displayed when a mouse is over a view or a saved view in the View Selector ExplorerBar and View Selector Tree is now supported. The ViewSelectorItemToolTipPolicy setting options include the view name, the saved name, the description, and the saved description. By default, the view name or the saved name, for a saved view, is displayed. (#1235)
For the screenshot below, the view selector is configured to show both the Saved Name and Saved Description for (saved) views.
Note: For a saved view, the shared status is automatically added to the tooltip and is not a part of the description.
Essbase Authentication Services
An authentication Essbase database is no longer required. In previous versions of Dodeca, the EssbaseUserRolesSqlPassthroughDataSet and EssbaseUserRolesFromGroupNames authentication services required the existence of a single Essbase database to use for authenticating users. To avoid the necessity of creating and maintaining a database that might not otherwise be needed, the authentication services can now be configured to only validate the credentials specified by the user and not actually attempt to connect to a database. The ValidateCredentialsOnly is the new authentication service property that controls this behavior. By default, the value is False, which ensures that the behavior of existing applications, which use either one of the Essbase authentication services, is the same as with previous releases. (#1233)
Note: When ValidateCredentialsOnly is set to True, the EssbaseConnectionID is still required. The authentication service uses the Essbase connection to obtain the ServletPath and the (Essbase) Server settings. The Application and Database settings are ignored.
- Resolved the performance issue encountered with the EssbaseUserRolesFromGroupNames authentication service
When many groups exist, using the EssbaseUserRolesFromGroupNames authentication service was causing a prolonged delay at startup. (#1236)
Essbase Excel View - Fixes
- Fixed Issue: When the DataCellDoubleClickPolicy is set to OpenViewForDrillthrough, an "Object reference is not set to an instance of an object" error is encountered when a contributing datapoint member represents a dynamic time series and uses the period-to-date format, such as Y-T-D(Q3). (#1227)
Essbase Issue: Shared Members Treated as Duplicate Members
Starting in (a patch to) Essbase 11.1.2.2 and in Essbase in 11.1.2.3, Essbase treats shared members as non-unique (i.e. duplicate) members. Shared members are returned by the database with the fully-qualified member name format typically reserved for duplicate members. In prior versions of Essbase, non-unique members were possible only when the database outline was enabled to allow duplicate member names and when the member names were actually non-unique.
To address this issue, changes were made to the Dodeca Essbase service and the SmartClient to accommodate the existence of duplicate member names, including shared members that are treated by Essbase as non-unique. For an ad hoc operation, the fully qualified member names are not displayed in the grid, but are retained in memory to allow for subsequent Essbase operations. In an Essbase Excel view, the non-unique member names for both duplicate members and shared members (that are treated by Essbase as non-unique members) within the retrieve range must be fully qualified to allow for Essbase to recognize the members. (#1225)
The changes will provide the foundation for a more complete support of duplicate member names in a future release of Dodeca. For instance, we intend to support the ability to control whether or not the fully qualified member names are displayed on the sheet.
Excel Page Setup Dialog
The Page Setup dialog that was included with previous releases has been replaced with a new user-interface that more closely resembles the Excel Page Setup dialog. The Dodeca Page Setup dialog is resizable and modeless. (#1237)
The Page and Margins tabs are identical to the Excel Page Setup dialog.
The Header/Footer tab is similar to the Excel version.
The header/footer functionality provided by SpreadsheetGear does not include the formatting and element enhancements introduced in Excel 2007 and Excel 2013; therefore, the Page Setup dialog only supports the creation of headers and footers that are compatible with Excel 2003, which includes the following capabilities:
- The specification of a font face and size, bolding, italics, and underlining for a given header or footer section.
- The insertion of an element that dynamically prints the date, time, document name, sheet name, page number, or total number of pages in a header/footer section.
The following Excel 2007 and 2013 formatting and elements are not supported by SpreadsheetGear:
- Double underlining, superscript, subscript, and strikethrough.
- The insertion of the file path or a picture.
- The use of one or more colors within a header/footer section.
- The use of different formatting within a header or footer section, such as Hello World.
When unsupported formatting and elements are detected, the embedded codes are displayed in red. The unsupported format and element codes may be retained for a non-edited section, but will be shown as text when printed from within Dodeca.
The highlighted codes can be removed by clicking the "Remove--" button:
Typically, when a view is designed to allow the user to print from within a Dodeca application, any unsupported codes should be removed from the view template by the application administrator/developer, since these codes will appear in the printed document.
The following header/footer options are also not supported by SpreadsheetGear, and are not included in the Page Setup dialog:
- Different odd and even pages
- Different first page
- Scale with document
- Align with page margins
The Sheet tab is similar to the Excel Page Setup dialog:
The Page Setup dialog is modeless, which allows the user to interact with the view while the dialog is open. The Print Area and Print Titles addresses can be either manually entered or selected on the sheet. When clicked, the button at the right of each field resizes the dialog to allow easier access for selecting the range on the sheet.
SpreadsheetGear does not support the Comments or "Cell errors as" page setup options.
Excel (and Essbase Excel) Views - Fixes
Fixed Issue: When a cell is right clicked while the cell is in edit mode, an (unexpected) context menu is displayed, as shown below.
Resolution: The menu is the default edit menu automatically opened by the SpreadsheetGear control. The opening of the menu has been suppressed in Dodeca. (#1226)
Excel (and Essbase Excel) View Template Designer
- Enhancement: Group/Ungroup and Freeze/Unfreeze tools have been added to the Template Designer so that Excel Grouping and Freeze Panes can be added to the template in Dodeca without requiring changes to be made in Excel. (#1151)
- Fixed Issue: The Name Manager generates an "Out of memory" error when a named range references an entire sheet. (#1212)
- Fixed Issue: When a sheet name is numeric, the Name Manager displays the "Do you want to apply changes" prompt unnecessarily. (#1211)
Mac OS X Release Directory
The Dodeca Framework installation now includes a precompiled "Dodeca.app" deployment for running the Dodeca Smart Client on Mac OS X operating systems. This application file is contained in a ZIP archive located in the "\os x" directory of the Dodeca Framework Installation. Before running the application on a Mac, the "Dodeca.app" file must first be configured to connect to the appropriate metadata servlet, tenant, and application by updating the "Info.plist" file under the "Dodeca.app\Contents\" directory. The "Program Flags" key must be updated to represent the required command line arguments for the Dodeca Smart Client in the following form:
"/p http://<servername>:_<port>_/dodeca/metadata-service /t <tenant> /a <application>"
Detailed instructions for configuring and deploying the Dodeca Smart Client on a Mac can be found in the "Dodeca OS X Installation Guide.pdf" located in the "\os x" directory or by contacting Applied OLAP support.
Metadata Property Search Utility
The metadata property search utility supports the ability to search non-default metadata property values across specific or all metadata types.
The utility is accessed from the Admin menu:
In the example below, the utility is being used in the Sample application to find occurrences of the [T.Product] token across all metadata types. The search results indicate that the token is associated with two selectors; is used by four SQLPassthroughDataSet queries; is used by several views in the CaptionAfterBuild property, as a view token, in a couple of report scripts (i.e. ReportSpec property), and in a CascadeSheetNamePattern; and, is used by several workbook scripts to add a token or in a BuildRangeFromScript method.
Each result includes the metadata type, the ID of the metadata instance, the name of the property that contains the search string, and the full property value in which the match was found. The ID value contains a link, which (when clicked) opens or activates the appropriate metadata editor window and, depending on the editor, selects the property or launchs the editor’s search capability.
The value tooltip displays the property string:
_The "Search Metadata Properties" dialog is modeless, which allows you to interact with the application, including the metadata editors, without closing the dialog. _
With the Application, Essbase Connection, Selector, Selector List, SQL Connection, SQLPassthrough DataSet, View, and View Property Set types, the property is selected in the metadata editor. With the Hierarchy and Workbook Script types, the metadata editor’s Find is automatically launched. For the Toolbars Configuration type, the toolbars configuration that contains the property is selected; however, the property is not.
An option to Use Regular Expression syntax is supported.
PDF View
In previous releases, the Open In PDF Viewer tool displayed a view as a PDF in a new window within the application workspace by creating a Web Browser view, which displays the PDF using the Internet Explorer-compatible (ActiveX) PDF reader plugin installed on the client machine.
Since Dodeca runs as a 64-bit application on 64-bit machines (i.e. Windows 7 x64), and since there is no 64-bit ActiveX PDF reader plugin control, the PDF opened in an external viewer (i.e. outside the Dodeca application) in this environment.
To address this issue and to remove the dependency on there being an Internet Explorer compatible PDF reader plugin control installed on the local client, we are introducing a PDF view type with this release, which uses a built-in PDF reader control. (#1234)
In this and subsequent releases, the Open In PDF Viewer tool displays a view as a PDF in a new window within the application workspace by creating a PDF view that displays the PDF.
Note: If you wish to use the previous Open In PDF Viewer tool that opens the PDF in a Web Browser view, the tool key has been changed to "OpenInLocalPdfWebBrowserView".
PDF views can also be created with the Views metadata editor. The PDF view type has two properties that determine the PDF to be displayed: URL and PDFBinaryArtifact. URL is the location of either a local or internet PDF resource. PDFBinaryArtifact specifies a PDF that has been imported into Dodeca as a Binary Artifact, using the new Binary Artifact Type: PDF.
If both a PDFBinaryArtifact and a URL are specified, PDFBinaryArtifact takes precedence. The PDF View Standard and PDF View Standard All toolbars configurations are now included in the "metadata_starter_kit.zip," so that an appropriate standard toolbars configuration for the PDF view type can be chosen when creating new PDF Views.
By default, if no ViewToolbarsConfigurationID is set for a PDF View, the PDF View Standard toolbars configuration will be used.
Note: The PDF View Standard toolbars configuration does not contain a "Build View" button. It is recommended that the AutoBuildOnOpen property is set to True when using the PDF View Standard toolbars configuration.
SQLPassthroughDataSet — Fixes
- Fixed Issue: When a SQLPassthroughDataSet query’s InsertSQL statement specifies a stored procedure, the stored procedure is only being executed on the server when the InsertSQL statement starts with a (case-insensitive) execute or call. However, the SQL syntax for many JDBC drivers requires for the SQL statement to be escaped with curly brackets, for example \{call spInsert('argA')}.
Resolution: The algorithm was modified to detect the keywords execute and call with or without the prepended curly bracket, with any number of spaces before the curly bracket, and/or between the curly bracket and the keyword, and at least one space after the keyword. (#941)
The following examples are now supported:
execute xyz('abc')
\{ execute xyz('abc')}
\{ execute xyz('abc') }
call xyz('abc')
\{ call xyz('abc')}
\{ call xyz('abc') }
In version 6.2.3.3425, a new Query setting, called SQLStandardizationPolicy, was added and can be set to either FullStandardization, which is the default, or to NoStandardization. This controls whether the SQL statements are standardized with respect to spaces (and spacing) within the statements. The full standardization helps to prevent syntax errors related to spacing, which may be generated by the JDBC drivers. (Refer to the version 6.2.3.3425 section for further details.)
A couple of issues with the policy have been fixed:
\1) The full standardization was being performed even when the policy was set to NoStandardization.
\2) The standardization was being performed after token replacement. This becomes an issue in cases where the token value is altered by the full standardization. This is best illustrated by way of an example WHERE clause:
"--WHERE Name = '[T.Name]' " and the token value is "Doe,John"
After token replacement and standardization, there WHERE clause becomes:
"--WHERE personName = 'Doe, John'", which contains a space between the "," and "J"
This can result in incomplete and inaccurate query results.
Resolution: When the SQLStandardizationPolicy is set to FullStandardization, the standardization is performed before token replacement. This fix does not require any changes to existing metadata for which these issues have been encountered. (#1238)
SQLPassthroughDataSet — Parameterized SQL Select Statements
The parameterized prepared SELECT statement capability was requested by a customer in order to leverage performance optimizations gained through the caching of parameterized statements in DB2. This capability may not be beneficial in all environments. (#1228)
Overview
The SQLPassthroughDataSet functionality supports the concept of tokenized select statements. When a tokenized select statement is used, the Dodeca client substitutes the tokens with the appropriate token values before passing the query to the server. The dodeca server creates a prepared statement and executes the query through the JDBC driver.
Alternatively, with a parameterized select statement, the Dodeca server substitutes the parameters with the parameter values provided by the client. The substitution is performed after the statement is prepared.
In most cases, the approach of using tokenized statements is adequate and appropriate; however, there are specialized cases where using a parameterized prepared statement on the server may be preferable. When SQL requires significant time and resources to analyze and prepare for execution, the resources of the database server can potentially be better leveraged by using parameterized prepared statements. In particular, some database servers support the ability to cache prepared statements for execution by other users.
*The use of parameterized select statements is an option to be considered within the context of the capabilities of the database server. Any existing tokenized select statements will continue to work the same as in previous versions of Dodeca, and are the best approach in the majority of cases. *
How to convert a SELECT statement from tokenized to parameterized
- From the Query.DataTableInfo.Columns Editor, map the column to the appropriate data type.
In the Query.SelectSQL Editor, replace the token, along with any surrounding quotes, with the parameter notation.
A JDBC driver may require specialized syntax for a SELECT statement to be used as a parameterized prepared statement.
- From the Query.DataTableInfo.SelectSQLParameters Editor, add a parameter definition and specify the token as the parameter value.
At runtime, the client replaces the token with the token value and sends the token value as the parameter value to the server.
IMPORTANT
- The name of the parameter in the select statement, in the parameter definition, and in the column/data type mapping is case-sensitive.
- If the parameter represents a string column, the parameter is not enclosed by quotes in the select statement. Instead, the parameter value, which is typically a token, can be enclosed in quotes, although the enclosing quotes are not required.
The following table indicates the correct format for a single value and for values within an IN clause as determined by the data type:
| Data Type | Value format | Value format for IN clause |
|---|---|---|
CHAR, VARCHAR, LONGVARCHAR, NCLOB, NCHAR, NVARCHAR, NLONGVARCHAR, NCLOB | sample or 'sample' | 'sample1', 'sample2' |
BIT, TINYINT, SMALLINT, INTEGER, BIGINT | 1 | 1, 2, 3 |
NUMERIC, DECIMAL, FLOAT, REAL, DOUBLE | 1.23 or 4 | 1.23, 4.56, 7, 8.9 |
DATE, TIME | 2013-12-31T12:00:00 | 2013-12-31T12:00:00, 2012-12-31T13:25:00 |
BOOLEAN Note: Boolean values are evaluated in a case-insensitive manner. | TRUE or FALSE or T or F or 1 or 0 | TRUE, FALSE or True, faLse or T, F or 1, 0 |
TIMESTAMP | Database dependent. | Not supported. |
View Hierarchy Metadata Editor — Find Item/View
The new Find feature supports the ability to search a hierarchy for views and/or items based on a whole or partial match of a specified string with the ID and/or name. A case-sensitive or insensitive search can be performed.
The Find dialog is modeless, which allows you to interact with the application while the dialog is open. If there are multiple hierarchies, you can activate a different hierarchy and continue with the same search criteria. So, for example, you may want to find the occurrences of a view that needs to be removed from one or more hierarchies. After finding and removing the view from one hierarchy, you can select another hierarchy and continue without having to close the dialog or reenter the search string. (#1240)
Workbook Scripting — Event Links, Functions, Methods
Event Links
- New Event Link — The SQLExceptionOccurred event link allows a workbook script to be notified when a SQL exception occurs. This enables the workbook script to perform custom logic, such as error recovery, within the context of the exception. (#1239)
The event link supports the following properties, which can be accessed using the @EPVal(<event property name>) function:
- ErrorCode — Error code, if applicable.
- Message — Exception error message, which is the message returned by the server/JDBC driver/database.
- Details — Exception details, which provides a descriptive context on the client in which the exception occurred, along with the error message.
- ServerStackTrace — The string representation of the frames on the call stack of the server at the time the exception was thrown.
- ClientStackTrace — The string representation of the frames on the call stack of the client at the time the exception was thrown.
In the following example, the workbook script logs the SQL exception to a local file on the client.
The SQLExceptionOccurred event can be cancelled by calling the CancelEvent method. The effect of cancelling the event depends on the context of the exception. For instance, if the SQL exception occurrs when the view is being built and the workbook script cancels the event, the error is not reported to the user in the view’s status bar, nor is the status bar updated. When a workbook script cancels the event, it is responsible for resetting the status and progress text using the SetViewStatus and SetProgressText methods.
Methods
- Modified EssbaseRetrieve method: Added an EssbaseConnectionID argument to the Adhoc overload, which more easily allows the workbook script to specify a connection other than the view’s connection for the adhoc retrieval. (#1229)
- Fixed Issue: In some cases, the EssbaseZoomIn method generates the error "Unable to 'ZoomIn' on sheet '<sheetname>'. Cell ranges must be passed to [EssGBeginZoomIn]." when the sheet contains multiple retrieve ranges. (#1232)
- Fixed Issue: The SetConditionalFormat method’s FillColor specified argument value is ignored. (#1213)
XCopy Release Directory
The Dodeca Framework installation now includes a preconfigured xcopy release directory for running the Dodeca Smart Client as an XCopy deployment. Before running the application as an XCopy deployment, the xcopy release directory must be "copied" to a location accessible by end-users. Then, a shortcut to the "AppliedOLAP.Dodeca.SmartClient.exe" can be created with the required command line arguments for the Dodeca Smart Client in the following form:
"/p http://<servername>:_<port>_/dodeca/metadata-service /t <tenant> /a <application>"
Detailed instructions for configuring and deploying the Dodeca Smart Client as an XCopy deployment can be found in the Dodeca Installation Guide under the section titled "Local Client Installation and Configuration" or by contacting Applied OLAP support.