June 17, 2010

This version of the framework requires an upgrade to the dodeca-essbase service.

Please contact support@appliedolap.com to request a compatible version(s) of the servlet for the installed version(s) of Essbase.

This version uses SpreadsheetGear 5.1.1.188 and NetAdvantage 10.1.20101.1007.

The release notes for this build contain the following sections:

Binary Artifact Metadata Editor

ClickOnce Deployment

ClickOnce Prep Utility

Comments

Culture Settings for Essbase Views, Excel Views, and SQL Excel Views

dodeca-essbase Servlet

Essbase Clustering

Essbase Connections

Excel Formatting Tools

Essbase Performance Logging

Essbase Selector Tree

Essbase Send Tool

Essbase Views

Excel Essbase View

JDBC Test Utility

Metadata Editors

Relational Database Support

Request and Response Logging

Save Layout as Binary Artifact Tool

Script Essbase View

SSL Support

Workbook Scripting

Binary Artifact Metadata Editor

ClickOnce Deployment

Resolution: The underlying cause appears to be that the file version stamp was not being updated on the Dodeca dlls. This was by design in order to accommodate our plug-in architecture. As a result, we slightly modified the design and put automated processes in place to update the file version stamp with each build.

The file versions of the Dodeca assemblies now reflect the build number. (#788)

ClickOnce Prep Utility

The elimination of the requirement for the .NET 2.0 SDK addresses an issue encountered on 64 bit machines (i.e. Windows 7, Windows 2003, etc.) that have the 32 bit .NET 2.0 SDK installed. Attempting to build the deployment would result in the error: "The installation directory for the .NET Framework 2.0 SDK was not found" and the build would stop. This prevented an administrator from deploying Dodeca from a 64 bit desktop. (#741 and #811)

Comments

Culture Settings for Essbase Views, Excel Views, and SQL Excel Views

Added a CulturePolicy property, which controls how a view’s regional settings are obtained. A view can be configured to either use 1) the regional settings that are defined for a specific culture, or 2) the regional settings that are defined for the culture of the user’s Windows environment. (#822, #826)

When the CulturePolicy is set to UseSpecifiedCulture, the culture specified by the CultureName property is used to get the regional settings. When set to UseWindowsCulture, the culture of the local Windows operating system is used.

By default, the CulturePolicy is set to UseSpecifiedCulture and the CultureName is set to "English (United States)".

If a view is to be used by users in different countries, then configuring the view to obtain the regional settings from a user’s Windows operating system is preferable. The formatting used in the view’s spreadsheet as well as the argument separator used in Excel functions will be consistent with what a user is accustomed to when using Excel.

The regional settings affect the following:

In Excel, function arguments in formulas are separated by the character defined as the List Separator in the Regional Options settings for the local Windows environment. The character is typically a comma or a semicolon. Excel automatically converts the argument separator in an Excel file to the list separator in effect when the file is opened.

In Dodeca, a view’s culture setting determines the list separator that is used for functions in Excel formulas for the view. The list separator that is defined for the view’s culture, such as English (United States) or French (France), is handled by the view in the same way that Excel handles the list separator defined by the local system’s regional settings.

Any formulas defined in the view’s Excel template are automatically converted to use the list separator defined for the view’s culture as the function argument separator.

Any formulas entered by the user into the view’s spreadsheet must use the view culture’s list separator. Any Excel formulas in the workbook scripts that are used by the view must also use the view culture’s list separator as the function argument separator.

In Excel, the symbols and separators used in cell number formats are defined by values in the Regional Options settings for the local Windows environment. Number formats can include any of the following: the decimals used in numeric, currency, and percent values; the strings used to separate groups of digits to the left of the decimal in numeric, currency, and percent values; the currency symbol; the negative sign symbol; the string used to separate the day, month, and year in a date value; and the string used to separate the hour, minutes, and second in a time value.

In Dodeca, a view’s culture setting determines the regional settings, and therefore the symbols and separators used in cell number formats for the view. Any cell number formats defined in the view’s Excel template are automatically converted to the appropriate equivalent formats for the view’s culture. Any custom number formats assigned to cells in a workbook script that is used by the view must use the symbols and separators that are appropriate for the view’s culture.

For Essbase views, the culture-specific formatting used for Essbase numeric data values is based on the view’s culture. For instance, the data value 1,234.56 in the English (United States) culture is 1 234,56 the French (France) culture.

How to Create a Culture-Neutral Workbook Script

When the CulturePolicy is set to UseWindowsCulture, it is important to create workbook scripts that are not written for a specific culture, meaning that the scripts do not use culture-specific symbols, separators, etc.

For example, instead of using a comma as the argument separator in the Address formula: =Address(@LRow(), @LCol())

Use the @Sep() function, which is replaced at runtime with the list separator for the view’s culture: =Address(@LRow()@Sep()@LCol())

For example, the SetNumberFormat() method can be used to set the number format for cells within a range. A number format string that uses decimals and thousands separators that are appropriate for the English (United States) culture is:

_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)

The equivalent format string for the French (France) culture is:

_($* # ##0,00_);_($* (# ##0,00);_($* "-"??_);_(@_)

The equivalent format string to use in a culture-neutral script uses the @NSEP() and @NDEC() functions:

_($* #@NSEP()##0@NDEC()00_);_($* (#@NSEP()##0@NDEC()00);_($*"-"??_);_(@_)

Caveats:

*This version of the framework requires an upgrade to the dodeca-essbase service. *

Please contact support@appliedolap.com to request a compatible version(s) of the servlet for the installed version(s) of Essbase.

This release includes new support for Essbase server clustering (#806). Prior to Essbase 9.3.0, support for Essbase clustering was built into the Essbase Deployment Services/Analytic High Availability Services server infrastructure. Starting in Analytic Provider Services ("APS") version 9.3.0, Essbase clusters may be defined in APS, but APS no longer implements clustering functionality.

The clustering functionality in the dodeca-essbase servlet leverages APS for the cluster definitions. Cluster definitions are initialized by the Dodeca Cluster Manager upon the first connection to Essbase. Changes to the cluster definitions are not recognized until the dodeca-essbase server is restarted.

Clustering is supported for both stateful and stateless Essbase connections and is implemented using a round-robin algorithm. Additionally, stateful connections may optionally be configured to return an exception to the client if the cluster item to which it is connected becomes unavailable.

When cluster items are removed from service, they may be handled differently from the dodeca-essbase servlet based on their configuration. The servlet configuration allows the administrator to configure the Essbase error numbers which may occur when connecting to component nodes on the cluster. Error numbers can be configured to handle node failures in three different ways:

The Essbase error numbers and how they are handled, the timeout period and the interval between timeout checks are configurable in the dodeca-essbase web.xml file. The Timeout period is configured using the clusterItemTimeoutInterval init-param and the check interval is configured using the clusterItemCheckInterval. The error numbers are configured via the clustering.retryOnSameSvcComp.errorCodes, clustering.markSvcCompUnavailableTemporarily.errorCodes, and clustering.markSvcCompUnavailablePermanently.errorCodes init-params. Here is a screenshot from the web.xml:

Essbase Connections also need to be configured to use a cluster. The ClusterName property signifies the cluster to use for obtaining server, application and database information. The NotifyOnStatefulClusterNodeFailure property is used to signify a failure of a node on a stateful connection will notify the Dodeca client so appropriate action can be taken. The example connection show below is configured to use the cluster named 'ProductionCluster' with stateful transactions.

Essbase Connections

Resolution: Updated the storage mechanism to use the Triple DES encryption algorithm whenever the username and password are stored.

Note: Many customer deployments do not use a stored username/password, commonly known as a phantom username/password combination, in their deployments. Other customers use this mechanism to allow end users access to data using their own credentials, but conduct all send/calculate operations using a phantom username/password. The internal code change was implemented such that current customer deployments will work using their existing phantom username/password combinations. We do recommend that customers edit these connections, make a change, then commit the updated Essbase connection to the server. The updated EssConnection will automatically encrypt the username and password. (#775)

Excel Formatting Tools

Essbase Performance Logging

Added an Essbase Performance logging capability to the Dodeca-Essbase service. The performance logging functionality stores performance metrics for all, or selected, Essbase transactions executed on the Dodeca-Essbase service. The metrics are stored in the Dodeca relational metadata database in the ESSBASE_PERFORMANCE_LOG table.

The ESSBASE_PERFORMANCE_LOG table is defined in SQL Server DDL as:

create table ESSBASE_PERFORMANCE_LOG ( PERFORMANCE_LOG_RECORD_NUMBER numeric(19,0) identity not null, TENANT varchar(50) null, APP_SERVER_PROFILE varchar(255) null, SERVER varchar(50) null, APPLICATION varchar(50) null, CUBE varchar(50) null, ESSBASE_USER_ID varchar(50) null, WORKSTATION_USER_ID varchar(50) null, WORKSTATION_ID varchar(50) null, TIMESTAMP varchar(25) null, SEQUENCE_NUMBER numeric(19,0) null, THREAD_COUNT int null, OPERATION varchar(50) null, DURATION int null, REQUEST_GRID_SIZE int null, RESPONSE_GRID_SIZE int null, REQUEST_CELL_COUNT int null, RESPONSE_CELL_COUNT int null, MEMBER_COUNT int null, primary key (PERFORMANCE_LOG_RECORD_NUMBER) )

You can generate the DDL in the syntax of your installed database by typing the metadata-service address, for example, 'http://mustang:13080/dodeca/metadata-service' into a browser, then selecting the Generate DDL option. If you are upgrading from a previous version of Dodeca, you can run the DDL to create just this table against your database.

Note: If you are upgrading from a previous version of Dodeca and do not update the database schema to add the new table, you must edit the dodeca.properties file and set the dodeca.essbase.performance.log.enabled property to false.

The fields stored in the data are:

The grid sizes and cell counts will normally contain non-zero numbers, and the member count will contain zero, for most data retrieval operations. The grid sizes and cell counts will contain zero, and the member count will normally contain non-zero numbers, for most member query operations.

The performance logging functionality is comprised of two components: the servlet filter and the client properties. The servlet filter for the Dodeca-Essbase service is configurable via parameters in the web.xml file. The performance logging filter requires no mandatory parameter configurations to function properly. Optional parameters, their purpose and guidance for usage are documented in the filter section of the web.xml file.

A client must also be configured for performance logging to work. Performance logging is specific to a single Essbase connection and is configured in one of two ways depending on whether logging should be enabled for all clients or for only the current client and session.

*Warning*: Logging can quickly generate a large number of records in the ESSBASE_PERFORMANCE_LOG table. Performance logging should only be enabled on an as needed basis.

To enable Essbase performance logging for the current client during the current session only, use the Utilities → Log Essbase Performance tool, which expands to show a list of the Essbase connection ID’s. Performance logging can be enabled or disabled on a per connection basis.

To enable Essbase performance logging for all clients, use the Essbase Connections metadata editor to set the new DoEssbasePerformanceLogging property. When set to True, all clients signal the Dodeca-Essbase service to gather performance information for this connection and pass it to the Dodeca service for relational storage.

Essbase Selector Tree

By default, the tooltip contains the member name, alias, generation, level, and whether or not the member is shared. The content can also contain the UDA’s associated with the member, the member comment, formula, consolidation operation, and associated attributes. The size of the tooltip as well as the amount of time required to display the tooltip are both a factor of the size of the content. The possible number of UDA’s and associated attributes as well as the length of the comment and the formula should be taken into account when deciding whether or not to include any of these options. You can specify the member information displayed by the tooltip by checking and unchecking options.

The format used for an attribute is "<attribute dimension name>: <attribute name>".

The associated attributes are also displayed in the Member Information dialog.

Essbase Send Tool

Excel Essbase View

Resolution: The error was changed to the following: "Unable to build the view. Unable to do cascading. No CascadeSources are defined." (#765)

JDBC Test Utility

The JDBC Test Utility is used to test and validate the hibernate.properties settings for a given database. After the database is selected from the Database Platform dropdown list, the related settings are loaded from the properties file. The settings can be tested and edited, as necessary, and then written back to the file by selecting "Update hibernate.properties" from the Tools menu. (#812)

Note: The utility requires that the Java environment is installed and that the appropriate JDBC drivers are also available.

Metadata Editors

Paste Type Options Tool

The following paste type options are now supported: All, Formulas, Values, Formats, Comments, Validation, All Except Borders, Column Widths, Formulas and Number Formats, and Values and Number Formats.

The new paste type options include: Comments, Validation, All Except Borders, and Column Widths.

The PastePolicy property, which is supported for all Essbase views, generic Excel views, and SQL Excel views, controls the paste type used when the Ctrl+V keys are pressed to initiate a paste operation. The property is also used to set the default paste type option in the Paste tool. When the clipboard does not contain Excel data*, all options except the Values option are disabled and the Values option is automatically used to paste text data. When the clipboard contains Excel data, the option is set to either the previously selected option or the default. (#824, #825)

*Data copied from Excel (or from the SpreadsheetGear WorkbookView grid control) uses the Excel file format, which is called BIFF (Binary Interchange File Format).

To add the new options to an existing menu that already contains the Paste Options tool, perform the following steps.

Relational Database Support

This release includes enhanced support for displaying and updating relational data.

A new view type, SQL Excel, as well as the Essbase Excel view type supports the ability to display relational data as rows in a spreadsheet. The data can also be edited in the spreadsheet, and then written back to the database. The view content can contain data from multiple tables within a single database or from multiple databases. The databases can be the same or different types, such as Oracle and SQL Server, as well as different versions of the same database type.

The following capabilities are supported:

For an Essbase Excel view, additional capabilities include:

The introduction of the SQL Excel view type expands the Excel-based view types to three, including the generic Excel, Essbase Excel, and SQL Excel view types. The following table shows the key functionality supported by these view types:

Request and Response Logging

Added Request and Response logging to the Dodeca and Dodeca-Essbase services. The logging functionality captures all, or selected, xml transactions between the Smart Client and server tiers. The transactions are then stored in files in a given directory on the server. The files may then be used to better understand how the Dodeca tiers communicate with each other and are very useful in debugging operations.

*Warning*: The logging capability is not intended to be used at all times but rather on an as needed basis to capture the information entering and leaving the server primarily for debugging services. Logging can quickly generate a large number of files.

The logging functionality is comprised of two components: the servlet filter and the client properties. The servlet filter for each service is configurable via parameters in the web.xml file for the respective service. The output.directory parameter must be configured to enable logging as it specifies the directory where the xml files will be written on the server. If the output.directory is not properly configured, or the directory does not exist, logging will be disabled until the parameter is corrected and the service is restarted. The parameters, their purpose and guidance for usage are documented in the filter section of the web.xml file.

The client must also be configured for logging to work. An administrator can enable or disable logging for an application and/or for individual Essbase connections for all clients or for only the local client. A client is configured in one of two ways depending on whether logging should be enabled for all clients or for only the current client and session.

To enable request/response application logging (i.e. the Dodeca service) for the current client during the current session only, use the Utilities → Log Application Request and Response XML tool.

To enable request/response application logging for all clients, use the Applications metadata editor to set the new DoRequestResponseLogging property. When set to True, all clients signal the Dodeca service to log requests and responses.

Essbase request/response logging is specific to a single Essbase.

To enable Essbase request/response logging for the current client during the current session only, use the Utilities → Log Essbase Request and Response XML tool, which expands to show a list of the Essbase connection ID’s. Request/response logging can be enabled or disabled on a per connection basis.

To enable Essbase request/response logging for all clients, use the Essbase Connections metadata editor to set the new DoRequestResponseLogging property. When set to True, all clients signal the Dodeca-Essbase service log requests and responses.

Save Layout as Binary Artifact Tool

Script Essbase View

SSL Support

Workbook Scripting

Many customers are interested in creating dynamic reports with formulas that compute the totals across multiple levels. This functionality is normally accomplished using dynamic ranges computed using the Excel OFFSET function to compute ranges used in the SUMIF function to conditionally summarize data. The most difficult part of accomplishing these dynamic formulas is determining the number of rows in a section. This task is made easier using the @SectionBreakRowOrColumnCount() function.

In the spreadsheet below, the number in cell A14 represents the number of rows since the last level 1 item in column B or the top of the range if no previous level 1 items exist in the range being evaluated in column B.

The @SectionBreakRowOrColumnCount() function takes as arguments 1) the range to search up or left for the previous item, 2) the starting cell to search in the range, 3) the range containing the value to search, and 4) a flag indicating whether to evaluate row or column values.

In the example shown, the function that calculated the value '5' in cell A14 as called within workbook script with the following parameters:

@SectionBreakRowOrColumnCount(Built.Rows, @ACell(), @ACell(), "row")

which resolved to the following ranges:

@SectionBreakRowOrColumnCount($B$9:$B$33, $B$14, $B$14, "row")

The value was then placed into cell A14 using the SetEntry workbook script method. (#819)

The ExecuteQueryAsynchronous argument controls whether the specified SQLPassthroughDataSet’s query is executed asynchronously. When set to TRUE, the query is processed on a background thread. If the query is configured to limit the number of rows returned at a time (using the query’s IncrementalMaxRow setting) and the total number of rows generated by the query exceeds the limit, then the results are processed incrementally. As the results are received, the data is copied to the view, which occurs on the main UI thread.

The progress text in the status bar indicates when the view is waiting for results, "Retrieving view data", and when results are being copied to the view, "Populating view data". The status icon is animated when the view is waiting for results. The progress bar at the right of the status bar visually represents the percentage complete.

During processing, the view’s toolbars and selectors are disabled. When the view is waiting for query results, the application’s UI can be accessed, which allows other views to be activated, opened, built, etc.

In the workbook script, the calling procedure waits until the BuildRangeFromScript is complete, meaning when all the query results have been copied to the view, before executing any remaining methods in the procedure.

The ExecuteQueryAsynchronous allows the processing to be optimized and designed appropriately based on the nature and requirements of the view. (#820)

This method runs the specified Essbase report script and copies the results to a sheet.

The ServerBased overload supports a ReportScript argument that specifies the name of a report script on the server. The TextBased overload supports a ReportSpec argument that specifies the report script.

When dodeca-essbase service executes the report, it automatically performs a retrieve on the report results and applies the view’s or the specified Essbase option settings. To avoid unnecessarily generating the data with the report, the <preview tag should be specified in the report script.

In addition to the standard method arguments, both overloads also support the following arguments:

*DoTokenReplacement*

Optional. Indicates whether token replacement should be performed on the script before it is run.

*StartCell*

Optional. A defined range name or a range reference that designates where to copy the results. If none is specified, A1 is used as the start cell.

*OutputRangeName*

Optional. If specified, a defined range is created that contains the report results. Typically, the name uses the retrieve range naming convention, such as Ess.Retrieve.Range.1; however, the implications of doing so should be taken into consideration when writing the script. For instance, if this method is called in the AfterWorkbookOpen event link, setting the NavigateWithoutData argument to True, and setting the view’s RetrievePolicy to RetrieveRanges allows the default retrieve logic to retrieve the data based on the member layout generated by the script.

*SynchronizeCharts*

Optional. Indicates whether to synchronize the chart, if any, with the output range data.

*ConnectionID, Username, and Password*

Optional. Allows an Essbase connection other than the view’s connection to be specified. By default, the Essbase connection associated with the view is used.

And, the relevant EssProperties settings are supported, including AliasTableName, EnableHybridAnalysis, Indentation, MissingLabel, NavigateWithoutData, NoAccessLabel, RepreatMemberLabels, RetrieveUpdateMode, SuppressMissingRows, SuppressUnderscoreCharacters, SuppressZeroRows, UseAliases, and UseMembernamesAndAliasesForRows The default value for each of these settings is derived from the view’s corresponding runtime property. (#776)

When the ConnectionID argument value is specified that refers to an Essbase connection that has a Username and a Password explicitly defined for the connection, the EssbaseRunCalc method does not use the specified credentials. The credentials used for the view’s connection are used instead.

Resolution: These methods were modified to use the phantom Username and Password, when available, instead of defaulting to the view connection’s Username and Password. (#772)

These methods can be used when multiple operations need to be performed on the same connection. They are intended for use only with Essbase connections that are configured as stateful (i.e. the Stateless property is set to False.) The workbook script can open the connection by calling the EssbaseConnect method, then call the appropriate Essbase methods to perform the operations and specify the connection using the ConnectionID argument, and then call the EssbaseDisconnect method to close the connection. (#801)

Dodeca Framework