This version uses SpreadsheetGear 2010 (6.0.3.190), NetAdvantage 2011, Volume 1 (11.1.20111.2042), and Aspose.Cells 7.0.3.0.
The release notes for this version contain the following sections:
ClickOnce Prep Utility
Dodeca-Essbase Server — Gzip Compression
Dodeca-Essbase Server — Miscellaneous
Dodeca-Essbase Server — Relational Outline Caching
Dodeca Server — Metadata Audit Log
Essbase Connection Metadata
Essbase Views
Excel View Wizards
Excel View Template Designers
Metadata Editors
Relational Views (SQL Excel and Essbase Excel)
Selectors
SQL Server JDBC Drivers
View Sharing (and Authentication Services)
Views
Workbook Script — Essbase MDX
Workbook Script Functions, Methods, Properties, and Event Links
ClickOnce Prep Utility
A new temporary digital signing certificate (dodeca_temp.pfx) is installed with this release, and expires on 12/31/2039. The previous certificate is set to expire on 11/20/2012. All installations that use the new certificate will be treated as new installations. This means that the client-side cache is not retained from the previous version. For instance, the size of the application window and the last used selector items are not restored from the previous version. (#1131)
Dodeca-Essbase Server — Gzip Compression
The dodeca-gzip-filter servlet filter compresses the xml stream returned from both the dodeca and dodeca-essbase services to reduce bandwidth, which can reduce the time it takes for transferring the results from the server to the client. The amount of compression varies depending on the nature and size of the response from the server. (#1118)
The dodeca-gzip-filter servlet filter code is contained within the dodeca and dodeca-essbase servers and is configured inside the WEB-INF\web.xml file for each respective server. The web.xml entries for the filter are:
<filter> <filter-name>dodeca-gzip-filter</filter-name> <filter-class>com.appliedolap.dodeca.server.sax.filters.gzip.GZipFilter</filter-class> </filter>
<filter-mapping> <filter-name>dodeca-gzip-filter</filter-name> <url-pattern>/</url-pattern> </filter-mapping>*
If multiple filters are specified and the dodeca-gzip-filter servlet filter is enabled, the dodeca-gzip-filter must be mapped first. If this filter is not mapped first and thus becomes the outermost servlet filter, other filters enabled on the server may receive gzipped data.
Though the gzipped content may reduce bandwidth requirements, use of the dodeca-gzip-filter may not result in optimal performance. The gzip algorithm adds overhead to the communication due to the time and resources required to compress and uncompress the data. To assist in computing the optimal performance, this version of Dodeca has a discrete gzip logger. The gzip logger is configured in the classes\log4j.properties file.
log4j.additivity.dodeca.service.gzip If the logger is set to DEBUG level, detailed gzip log information is written to the dodeca-essbase-gzip and dodeca-gzip log files. These logs contain the uncompressed size, compressed size, compression percentage, and time to compress. This information can help you determine if the time spent compressing the content, plus the time saved in data transfer, will result in better performance given your network speed.
Below is a sample of the log contents. The last row shows an Essbase ZoomIn transaction where the 16 Mb response is compressed to about 1.8 Mb for a compression ratio of 89.1%. The compression time, however, is about 3.9 seconds. If you take into account a similar amount of time for decompression, then it would make sense to enable gzip when the amount of time to transfer 16 Gb at your network speed is more than the amount of time to transfer 1.8 Gb at those speeds plus the estimated 7.8 seconds of processing time added for gzip compression and decompression.
Dodeca-Essbase Server — Miscellaneous
Essbase Cube Information — Relational cache logging
The Dodeca-Essbase service requires particular cube information to operate properly. The cube information is normally generated from the Essbase outline when the first user logs into the system and connects to a given cube. The information is then cached in server memory. Starting in version 6.0, the administrator has the option to generate this information in the background and to store it in a relational data store.
To make it easier to determine if the cube information is being generated dynamically or if it is being pulled from the relational cache, the logging for this functionality was split out into its own log file, the dodeca-essbase-cubeinfo.log file.
This is an example of a log entry for a cube info call that generated the cached cube information:
INFO|2012.09.24 13:26:59.243|dodeca.essbase.service.cubeinfo|"Generated item
Mustang.Sample.Basic and stored it in the MetadataCache (291 ms)"
This is an example of a log entry for a cube info call that retrieved cached information:
INFO|2012.09.24 13:43:42.142|dodeca.essbase.service.cubeinfo|"Found cubeinfo
item localhost.Sample.Basic in the relational cache and stored it in the MetadataCache;
CACHED_OUTLINE_ITEMS.HASHCODE=a3bb7ec1033aa451ad304f15eb8167dd1637d88c. (9 ms)
Note: This log can also be used to determine the performance implications of relational caching.
The log4j.properties file has a new section for configuring this log. The log messages will be output for the INFO level and below. (#1123)
Dodeca-Essbase Server — Relational Essbase Outline Caching
The relational Essbase outline caching functionality was introduced in Dodeca version 6.0.0.3106. The functionality provides the ability to store Essbase outlines represented by Dodeca Essbase connection objects in a relational database to enhance both performance and flexibility to Essbase member requests.
In the initial release of this functionality, in order to enable outline caching for a given Essbase database, an administrators was required to either add the outline caching information to the dodeca-essbase.properties file or, alternatively, to insert a record into a table in the Dodeca metadata store. This was sometimes difficult for administrators to do, so new functionality was implemented to make the configuration easier and to provide additional capabilities. This functionality includes the following:
Note: The new ExtendedCubeInfoEnabled property can also be used to decrease the startup time. Refer to the Essbase Connection Meatdata section for more information.
In the Essbase Connection Metadata Editor, each connection contains Relational Outline Caching properties:
*RelationalOutlineCachingSummaryOnly* — Controls whether the relational outline cache includes the outline details, which is the default, or only the outline summary-level information.
To change these properties, the *AdminPassword* and *AdminUsername* must also be specified.
The Dodeca-Essbase server must still be properly configured for relational
outline caching before these properties can be applied. Refer to Section Dodeca Essbase Server — Relational Outline Caching, Version 6.0.0.3106, for additional details. Steps 4 and 5 are no longer required. (#1108)
Dodeca Server — Metadata Audit Log
The dodeca metadata audit logging feature is now disabled by default. The metadata audit logging can, in certain cases, cause issues when loading metadata into an Oracle database using the Application Setup Utility. (#1094)
The metadata audit logging configuration property is located in the dodeca.properties file, which is installed with the Dodeca Framework in the staging\WEB-INF folder. To enable logging, change the value to true, and then use the ClickOnce Setup Utility to create the WAR file. Depending on the Java application server, the setting can also be modified after deployment.
Essbase Connection Metadata
The Essbase connection metadata contains a new property, ExtendedCubeInfoEnabled, which controls whether the extended cube information is returned when cube information is obtained for the database.
Cube information is obtained when an Essbase connection is used by the Dodeca client. Most of the cube information can be obtained without opening the outline, except for the following extended information: SmartLists (i.e. the text list strings and associated values for SmartLists) and the date format string.
Since opening an outline can be time consuming, setting the ExtendedCubeInformationEnabled property to False when the extended information is not needed avoids the overhead incurred when the outline is opened.
Although the SmartList and date format string are not currently used internally on the client, the date format string is used internally by the dodeca-essbase service in the data audit log functionality. If ExtendedCubeInformationEnabled is set to False and if date measures are updated, the data audit log tables may not accurately reflect the updated data. (#1125)
Essbase Views
Fixed issue: When retrieving data from an outline marked to allow duplicate member names, members that are not unique in the outline do not appear in the grid.
Resolution: Unique member names are represented in the grid using the member ID. For example, if a duplicate member exists that represents Rochester, New York, then the fully-qualified member ID, [New York].[Rochester], appears in the grid. (#1126)
Additional unique member name functionality is intended for a future release.
Excel View Wizards
The Excel, Essbase Excel, and SQL Excel View Wizards provide a step-by-step user-interace for creating, editing, and deploying Excel-based views. All the required view properties along with the most frequently used properties can be set. Upon completion, the wizard automatically creates the metadata objects that represent the view, the Excel binary artifact, and the workbook script, as needed. The wizard then presents several options, including adding the view to a view hierarchy, opening the view template designer, reviewing and editing all the view properties, and opening the view.
Opening the View Wizard
To create a new view using the wizard, open the wizard for the specific view type from the Admin menu.
To edit an existing view using the wizard, open the wizard from the View Selector ExplorerBar or View Selector Tree by clicking the Excel view with the right mouse button and then selecting the Open View Wizard option from the context menu.
To enable the ability to open the view wizard from the View Selector, use the Admin / Applications editor to set the View Selector’s AllowOpenExcelViewWizard property to True.
The Wizard User-Interface
The wizard dialog is presented as a sequence of pages that can be navigated using the navigation buttons (i.e Back, Next, Finish, etc.) or the navigation guide on the left, which is an ordered list of page links. The navigation guide also provides a visual means of indicating which page is the active page by displaying the active page link as bolded text.
The view wizards are modeless dialogs, which allow you to interact with the application while the dialog remains open.
The view wizard presented in the screenshots below is the Essbase Excel view wizard. The Excel and SQL Excel view wizards are identical to the Essbase Excel view wizard, with the exception of the three Essbase-specific pages: Essbase Connection Settings, Essbase Retrieve Settings, and Essbase Send Settings. In a future release, the SQL Excel view wizard will be enhanced to support the configuration of SQL Passthrough DataSet ranges.
The pages are generally self-explanatory, but the View Template Options page is described here to bring your attention to the choices available for identifying the Excel file to be used by the view.
The view template options include:
When one of the options is selected and the page associated with the option is displayed, another page link appears in the navigation guide below the View Template Options page link. In this example, the "Create an Excel file" option was selected. You can navigate to the page, but you can also change the option by navigating to the View Template Options and then select another option.
Once you have completed specifying the settings and are ready to save the view, click the Finish page link at the bottom of the navigation guide or click the Finish button on the Save and Sharing page.
When using the wizard to create a new view, the wizard does the following when Finish is clicked:
Displays the post-finish Options page.
The Edit View navigation button restarts the wizard in edit-mode.
The "Add the view to a hierarchy" option displays this page:
To add the view to a hierarchy, select the hierarchy from the dropdown list. Click and drag the view to the hierarchy and drop it at the location where you want to place the view. You can also use drag-and-drop to move and copy items within the hierarchy. Save the changes to the hierarchy by clicking the Commit button.
You can add the view to another hierarchy by repeating the steps.
Click the Options button to return to the Options page.
The "Edit view properties" option displays this page, which allows you to review and edit all of the view’s properties.
To save any changes, click the Commit button. Click the Options button to return to the Options page.
The View Template Designer, which is described in the following section, can be opened directly from the wizard.
The view can be previewed by opening the view directly from the wizard.
Excel View Tempate Designers
The Excel, SQL Excel, and Essbase Excel view types use an Excel file as the view template. The Excel file is stored in the metadata repository as a binary artifact, and associated with one or more views through the view metadata. Previously, all editing of the view template was performed in Excel. With this release, editing the view template is now supported within Dodeca.
The View Template Designers support many commonly used Excel editing tools, along with tools designed specifically for enabling the template to be used within Dodeca, such as inserting selector tokens and adding retrieve and send ranges for Essbase Excel views. The Essbase Excel View Template Designer also supports Essbase operations similar to the classic Essbase Excel add-in.
Essbase Excel View Template Designer
Excel and SQL Excel View Template Designer
The view template designer tab caption contains the ID and version of the Excel binary artifact that stores the view template, and the name of the Excel file.
Key view template designer features include the following:
View template versioning — The template can either be saved as the current version (that is displayed in the designer) or as a new version of the Excel binary artifact. To load a previous version of the template into the designer, select the version from the Template Versions dropdown list. Selecting the current version from the list allows you to discard any unsaved changes.
_It is possible to have multiple Excel files open concurrently. The Workbooks dropdown contains the list of all of the open workbooks, and can be used to change the active workbook. The active workbook has a checkmark to the left of the file name and designer tab caption also contains the file name. _
When the template is saved, the active workbook content is saved as the view template.
Helpful Hint
There may be times when you working in the designer and need to use Excel functionality to edit the template that is not supported by the designer. You can use the following steps to integrate Excel into the view template designer workflow:
Use the Save Template or Save Template as New Version tool to commit the view template to the metadata repository.
The designers support the following Excel tools:
Show — Show headings, gridlines, formulas
Unhide Rows and Columns and Restore Hidden Rows and Columns
The standard Excel Hide and Unhide Rows (and Columns) tools are available on the context menu. The Unhide Rows and Columns tool provides a convenient way to unhide all hidden rows and columns in the active worksheet. The tool is enabled when there are any rows or columns located within the range defined by $A$1 and the last used cell. When the tool is clicked, the rows and columns become unhidden. The designer retains the addresses of the unhidden rows and columns, which are used by the Restore Hidden Rows and Columns tool to "undo" the unhiding of the rows and columns.
Protect worksheet and contents of locked cells
Note that cell protection is set using the Format Cells tools.
The context menu contains the available Excel operations for the selected range.
Inserting selector tokens — One of the most common tasks performed when enabling a spreadsheet for use in Dodeca is to add selector tokens to cells as placeholders for point-of-view selections.
The Selector Tokens tool allows you select a cell (or multiple cells) that you want to set to the token for a particular selector.
Name Manager — Used to create, edit, delete, and find all names defined in the workbook.
The Name Manager is a modeless dialog, which allows you to interact with the application while the dialog remains open. To set the Refers to value, you can either key in the string or, for a range, select the range on the worksheet.
Essbase operations — The Essbase Excel View Template Designer supports the following Essbase operations and utilities:
The context menu contains the Essbase operations along with the available Excel operations for the selected range.
Essbase Range Editor — Used to create, edit, and delete Essbase retrieve and send ranges.
The Essbase Range Manager is a modeless dialog, which allows you to interact with the application while the dialog remains open. To set the Refers to value, you can either key in the string or, for a range, select the range on the worksheet.
The Name Manager can also be used to manage the Essbase ranges, but the Essbase Range Editor is designed to facilitate retrieve and send range creation, including the ability to associate a specific Essbase connection with the range in the template.
Metadata Editors
Resolution: The Commit button is disabled after a refresh is completed. (#1093)
Relational Views (SQL Excel and Essbase Excel)
Selectors
SQL Server JDBC Drivers
The SQL Server JDBC drivers shipped with the dodeca and dodeca-essbase servers have been upgraded to the latest version. The upgrade includes both the normal JDBC driver, sqljdbc.jar, which is for use with Java 1.5 servers and the Type 4 JDBC driver, sqljdbc4.jar, which is for use with Java 1.6 servers.
In addition, the sqljdbc4.jar has been made the default JDBC driver for both servers by renaming the sqljdbc.jar to sqljdbc.jar_use_for_java15. If your server uses Java 1.5, you must rename sqljdbc.jar_use_for_java15 to sqljdbc.jar and remove or rename the sqljdbc4.jar to have a different file extension. (#1121)
If you need more information or assistance related to the SQL Server JDBC drivers, contact support@appliedolap.com.
View Sharing (and Authentication Services)
In Version 6.2.0.3390*, the ability for a user to share a saved view was introduced. When sharing is enabled for a given view, a saved instance of the view can be shared by the owner with Noone or with Everyone. An application that is configured with an authentication service can control whether saved views can also be shared with users in specific roles and/or with specific users. In Version 6.2.0.3390, for the SQL-based authentication services, the roles/users included all the roles/users returned by a defined SQLPassthroughDataSet query. The functionality did not support the ability to restrict the roles and/or users that an individual authenticated user is allowed to share with.
*Refer to the Saved Views section of the Version 6.2.0.3390 release notes for more information about saved view sharing.
In version 6.3.0, the WindowsUserRolesFromSqlPassthroughDataSet and the EssbaseUserRolesFromSqlPassthroughDataSet support the ability to optionally filter the roles/users based on the authenticated user name. To support this capability, the following authentication properties were added: ** RolesToShareWithSQLPassthroughDataSetID — (Optional) The ID of the SQLPassthroughDataSet that defines the query used to obtain the roles with whom the authenticated user is allowed to share saved views. The roles are displayed in the User/Role dropdown list in the View Sharing dialog.
+ This setting is only required if you want to allow the user to select the role(s) with whom to share a saved view, which in effect shares the view with all users assigned the selected role.
+ To restrict the roles with whom the authenticated user can share views, the query’s SELECT statement can include a WHERE clause that contains the SqlPassthroughDataSet_UserNameVariable value within a condition.
UsersToShareWithSQLPassthroughDataSetID — (Optional) The ID of the SQLPassthroughDataSet that defines the query used to obtain the users with whom the authenticated user is allowed to share saved views. The users are displayed in the User/Role dropdown list in the View Sharing dialog.
This setting is only required if you want to allow the user to select the users(s) with whom to share a saved view.
To restrict the users with whom the authenticated user can share views, the query’s SELECT statement can include a WHERE clause that contains the SqlPassthroughDataSet_UserNameVariable value within a condition.
AllowSharingWithEveryone — Controls whether the authenticated user is allowed to share saved views with everyone.
By default, the user is allowed to share saved views with everyone. The property is typically set to False when the RolesToShareViewsWithSqlPassthroughDataSetID and/or UsersToShareViewsWithSqlPassthroughDataSetID restrict the roles and/or users with whom the user is allowed to share saved views.
SqlPassthroughDataSet_UserNameVariable — The string within the RolesForUserSqlPassthroughDataSetID query SELECT statement to be replaced with the name of the authenticated user. This allows the user name to be used in the SELECT statement WHERE clause to query for only the roles associated with the authenticated user.
The user name variable can also be used in the RolesToShareViewsWithSqlPassthroughDataSetID and UsersToShareViewsWithSqlPassthroughDataSetID query SELECT statements to restrict the roles and users with whom the authenticated user is allowed to share views.
This property was previously called RolesForUserSqlPassthroughDataSet_UserNameVariable. Although the name presented in the metadata editor has changed, any existing application metadata that specifies a value for this property is compatible with the 6.3.0 release. The property value will now be shown in the editor as the SqlPassthroughDataSet_UserNameVariable.
Here is a simple example that illustrates how to restrict the users and roles that the authenticated user is allowed to share with. For this example, let’s say that we want to allow the user to share a saved view with the roles to which the authenticated user is assigned and with the users who are also assigned to the same roles as the authenticated user.
The application is assigned the EssbaseUserRolesSqlPassthroughDataSet authentication service. Since we want to restrict the roles and users that the user is allowed to share with, the AllowSharingViewsWithEveryone property is set to False.
The *SqlPassthroughDataSet_UserNameVariable* setting is %USERNAME%.
This example uses a very simplified database model, where a single table contains a row for each mapping of a role to a user. A given user may be assigned to multiple roles.
Note: The Dodeca framework does not have any requirements related to the schema of the database used to store users and roles. The relational query can be defined as appropriate for the database within your environment.
To allow the user to share a saved view with only the roles to which the authenticated user is also assigned, the *RolesToShareWithSQLPassthroughDataSetID* is set to the same SQLPassthroughDataSetID as is the *RolesForUserSQLPassthroughDataSetID*. The Dodeca_User.RolesForUser SQLPassthroughDataSet query is defined as:
SELECT ROLE_NAME FROM USER_ROLE WHERE USER_NAME = '%USERNAME%'
So, when the user "amy" logs at startup, the '%USERNAME%' is replaced with 'amy', and the results include the following roles: BusinessAnalyst, Executive, FinancialAnalyst, and Manager. (Amy has a lot of responsibilities!)
When she shares a saved view, the list of available roles presented in the view sharing dialog includes the results of the same query based on the *RolesToShareWithSQLPassthroughDataSetID* setting.
To allow the user to share a saved view with the users who are also assigned to the same roles as the authenticated user, the *UsersToShareWithSQLPassthroughDataSetID* is assigned the Dodeca_User.UsersToShareViewsWith, which defines the following query:
SELECT DISTINCT USER_NAME FROM USER_ROLE
WHERE ROLE_NAME IN
(SELECT DISTINCT ROLE_NAME FROM USER_ROLE
WHERE USER_NAME = '%USERNAME%')
In this example, the results would include user2, user3, and user4.
The view sharing dialog allows the user to share with the roles returned by the RolesToShareWithSQLPassthroughDataSetID and the users returned by UsersToShareWithSQLPassthroughDataSetID.
Views
The use of multi-dimensional expressions (MDX) as a means of querying Essbase cubes has become more common and widespread due to its flexibility and efficiency. To allow Dodeca administrators/developers to leverage MDX for generating view content, a new workbook script method, EssbaseMdxQuery, and a new BuildRangeFromScript overload, EssbaseMdxQuery, are available with this release in order to support the ability to run a custom Essbase MDX query and write the results to a worksheet.
The following example uses the BuildRangeFromScript method’s EssbaseMdxQuery overload to retrieve the data at the top of the worksheet, and the EssbaseMdxQuery method to retrieve the data at the bottom of the worksheet.
For the data at the top, the view template contains the page and column fields, and the
BuildRangeFromScript is used to build the row field. The template contains the retrieve range Ess.Retrieve.Range.1. With the method’s Insert argument is set to TRUE, the row members returned by the MDX query are inserted into the retrieve range. The view’s build processing retrieves the data.
The MDX-specific BuildRangeFromScript arguments include the following:
HideRestrictedData — Controls whether data to which the user has no access is suppressed. By default, HideRestrictedData is TRUE; Restricted data is not returned.
BuildRowsOrColumns — Indicates whether row or columns are built from the query results.
The MDX query is tokenized to filter the results based on the point-of-view selection for measures and scenario.
SELECT \{Year.Children, Year} ON COLUMNS,
NON EMPTY CrossJoin(\{East.Children, East}, \{Colas.Children, Colas}) ON ROWS FROM SAMPLE.BASIC
The data at the bottom is generated using the EssbaseMdxQuery method. As specified in the workbook script, only the rows and columns are returned to the client. This is appropriate since the Dataless argument is set, by default, to FALSE, so the method is responsible for pushing the data to the sheet. With no retrieve range, the build processing does not attempt to retrieve the data.
The HideRestrictedData, IdentifierType, and RepeatRowMemberLabels arguments are the same as described above. Other MDX-specific arguments include the following:
Workbook Script Functions, Methods, Properties, and Event Links
Event Links
Fixed issue with the EditCellChanged, ListDataValidationCellChanged, and RangeChanged event links: The event properties (Row, Column, CellAddress, SheetIndex, and SheetName) are set based on the active worksheet and active cell instead of the worksheet that contains the cell or range that changed. (#1124)
Functions
New Function: HasExcelComment determines whether a cell has an Excel comment. If a cell contains an Excel comment. (#1103)
Function usage: @HasExcelComment([Address])
Function usage: @ExcelComment([Address])
New Function: TokenIsNullOrEmpty returns True/False indicating whether the specified has a null value or the value is an empty string. If the token does not exist, returns True. (#1105)
Function usage: @TokenIsNullOrEmpty(<TokenName>, [<UseTargetView>])
EssConnectionPropertyValue Function — Added the following Essbase cube properties that can be obtained using the EssConnectionPropertyValue function:
EssCube.CaseSensitive
EssCube.MemberTypeEnabled
EssCube.VaryingAttributeEnabled
EssCube.DateFormatString
EssCube.NonUniqueMemberNameEnabled
For example, @EssConnPVal(Sample.Basic, EssCube.DateFormatString) returns the cube’s DateFormatString. (#1113)
Methods
If the script returns data that contains dates and the dates need to be formatted on the sheet, then 1) the EnterDatesAsText argument value should be set to False, and 2) the appropriate cell number formats on the sheet should be set to Date. Please note that if the EnterDatesAsText argument value is set to False, but the appropriate cell number formats are not set to Date, the cells will contain the internal serial number representation of the date. (#1100)
SetOutlineLevel method — Added two overloads: ShowLevels and ShowDetail. (#1104)
ShowLevels — Shows rows and/or columns with an outline level that is less than or equal to the specified level. The overload-specific arguments include RowLevels and ColumnLevels. The leave the levels unchanged, specify nothing or 0. To collapse all levels, specify 1 as the ColumnLevels or RowLevels argument value.
ShowDetail — Shows/expands or hides/collapses the detail corresponding to the outline level of the row or column specified as the address.