Dodeca Version 6.3.0

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

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:

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

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:

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:

_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:

The designers support the following Excel tools:

The context menu contains the available Excel operations for the selected range.

The context menu contains the Essbase operations along with the available Excel operations for the selected range.

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)

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

Workbook Script — Essbase MDX

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:

SELECT \{Year.Children, Year} ON COLUMNS,

NON EMPTY CrossJoin(\{East.Children, East}, \{Colas.Children, Colas}) ON ROWS FROM SAMPLE.BASIC

WHERE (, )

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

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)