Using MDX for Generated Members in Essbase Reports

There are times when Essbase users may need to see an ad-hoc collection of members aggregated together in Essbase, and that isn’t always an easy task.  If it were an aggregation that is needed on a recurring basis, the Essbase administrator may add an alternate hierarchy to assist.  Other times, users might just create a spreadsheet with the desired members in different rows or columns and use Excel formulas to add them together.  In this blog post, I will cover a third option, the use of MDX to create dynamically-generated members, how to run them in Smart View, and how to make them much easier to use in Dodeca.

In order to illustrate how dynamically-generated members can be used, let’s consider an example using the Sample Basic database.  Here is a simple quarterly income statement query that I will use as the basis for this blog post:

SELECT
    {[Year].Children, Year} on COLUMNS,
    Hierarchize(Descendants([Profit]), POST) ON ROWS
FROM 
    Sample.Basic
WHERE 
    ([Market].[New York], [Product].[Colas], Actual)

The results from this simple query look like this:

This MDX is pretty straightforward, but what if you wanted to see how New York and Connecticut would look if they were combined?  This is the question that a generated member can return for you.

Generated members in MDX are created using the WITH MEMBER clause.  Moreover, the generated member can then be used anywhere a normal member can be used, even in a slicer dimension (or what we would call a ‘page field’ in the classic Essbase add-in or a point-of-view in Smart View).  Here is the query modified to use the new generated member:

WITH MEMBER
    [Market].[SelectedMarkets] AS 'SUM({[New York], [Connecticut]})'
SELECT
    {[Year].Children, Year} on COLUMNS,
    Hierarchize(Descendants([Profit]), POST) ON ROWS
FROM
    Sample.Basic
WHERE
    ([Market].[SelectedMarkets], Colas, Actual)

The results from this query look like this:

So far, so good, but there are a couple of things to note.  First, the member displayed in the POV is not a real member; that is to be expected.  This leads to the second thing in that you cannot refresh the query as an ad-hoc analysis; the dynamically generated member name will be replaced with the dimension member name in its place.

To go even further, what if you want to have multiple generated members?  In that case, the syntax is easy as you just continue with another MEMBER clause:

WITH MEMBER
    [Market].[SelectedMarkets] AS 'SUM({[New York], [Connecticut]})'
MEMBER
  [Product].[SelectedProducts] AS 'SUM({[Colas], [Grape]})'
SELECT
    {[Year].Children, Year} on COLUMNS,
    Hierarchize(Descendants([Profit]), POST) ON ROWS
FROM
    Sample.Basic
WHERE
    ([Market].[SelectedMarkets], [Product].[SelectedProducts], Actual)

The results of this query look like this:

The syntax for creating and using generated members is not that difficult, but there are a couple of things that make it a bit more difficult than it should be for end users to use this approach.

First, any time end users start having to deal with scripts of any kind, the level of complexity goes up exponentially.  As one of my mentors used to say, “The difference between zero lines of code and one line of code is much greater than the difference between one line of code and a hundred lines of code”.  In other words, it is hard to get users to deal with code of any kind.

Second, once an end user has to ‘write a line of code’, or script in this case, then they assume the responsibility for it being correct.  As there are differing levels of comfort and skill among users, the risk of error goes up.

Finally, when users use a script like the one used in this example, they have to type in the correct member names or, again, risk error. Here is the new MDX dialog in Smart View 11.1.2.5.720 showing where users type in the MDX including the member names.

To make it much easier for end users, Dodeca does a couple of things.  First, Dodeca developers can configure reports to use MDX without the end user ever having to know that MDX is powering the report ‘under-the-covers’.  Further, Dodeca has flexible Point-of-View selectors that allow the end user to simply pick which members they want to use in the query.

Dodeca report developers use tokens as a sort of substitution variables in the script.  The tokens are replaced in the script at run-time by the members selected by end users.  Here is the same script with tokens in place of the hard-coded values:

WITH MEMBER
  [Market].[SelectedMarkets] AS 'SUM({[T.Market]})'
MEMBER
    [Product].[SelectedProducts] AS 'SUM({[T.Product]})' 
SELECT
    {[Year].Children, Year} on COLUMNS,
    Hierarchize(Descendants([Profit]), POST) ON ROWS
FROM
    Sample.Basic
WHERE
    ([Market].[SelectedMarkets], [Product].[SelectedProducts], Actual)

The Dodeca Essbase Scripts editor has tools to help the report developer create and test MDX scripts.  Here are the Test Tokens available in the editor that allow developers to simulate the values plugged in by the Point-of-View selectors:

And the script itself in the scripts editor which has built-in testing facilities:

Finally, here is a Dodeca view that utilizes the tokenized MDX query and allows users to easily select the members they want dynamically aggregated and the report is produced without the risk of error.



Let me know if you would like to learn more about Dodeca and how it could help your company.

Kudos to the Dodeca Support Team!

Here at Applied OLAP, we have a great support team that we have tasked with doing their best to make sure our customers are happy with their Dodeca software.  On most days, they are answering emails and firing up impromptu support webcasts to help our customers with any questions they have.

Today, we got some great feedback from a customer after a popup support webcast.  Here is an email I received today from Edgardo Rodriguez at Wall Street law firm Sullivan and Cromwell (email used with permission):

Hi Tim – just wanted to drop you a quick line on the tech support I received this morning.  Kevin & Rodney were helping me with a headcount report.  Have to tell you – they were great. They helped me leverage some of their code and walked me through the finer points of using it under our scripts – all under an hour!  They also turned me on to using the SQL retrieve as a possible solution (which I will be using shortly).

I appreciate you support more than you know. 

Just thought you should know..

Regards

Ed

So, here is a special call-out to our support team.  Way to go!

Announcing the Dodeca Spreadsheet Management System, Version 7 and the Dodeca Excel Add-In for Essbase

After 18 months of hard work, Applied OLAP is proud to announce the general availability of the Dodeca Spreadsheet Management System, version 7, and the all-new Dodeca Excel Add-In for Essbase.

The Dodeca Spreadsheet Management System provides customers the ability to automate spreadsheet functionality, reducing the risk of spreadsheet errors while increasing productivity.  It combines unprecedented ease-of-use for business users using spreadsheets for planning, budgeting, forecasting, reporting and analysis tasks.  It also provides a robust, programmable development environment enabling companies to create spreadsheet applications tailored to their specific needs.

The new Dodeca Excel Add-In for Essbase was created as a drop-in replacement for the classic Essbase add-in and is the world’s only Excel add-in focused exclusively on Essbase.  The new add-in supports the most common actions used by Essbase traditionalists, supports the corresponding VBA functions, and includes a built-in Excel ribbon.  Early adopters have also been impressed by the speed of retrieving data, commenting they found the Dodeca Excel Add-In as fast as, or even faster, than the classic Excel Add-In for Essbase.  It is supported for Excel 2010, 2013, and 2016 and for Essbase 9.3.1 and higher.




Dodeca 7 includes new features and functionality for security, selectors, logging, and enhanced workbook scripting.

The enhanced security features add the ability to more easily manage users, roles, and permissions to access a Dodeca application.  The new security features include:

• User Management – You can now track, monitor, and control user access to a Dodeca application and more easily monitor your Dodeca user base.  This feature enables customers to control individual user access to a specific application, enable users for admin access, and manage user mapping to roles while also tracking metrics such as the first, last, and count of user logins.  This feature also logs metrics on the users system to enable Dodeca administrators to more easily support their users.
  
  
  
  Here is an example of the metrics stored for each user record.
  
  

• User Roles – In addition to provisioning roles via Essbase, Microsoft Active Directory, or LDAP groups using Dodeca authentication services, you can now create your own groups directly in Dodeca and map them to users.  In addition, these new roles can be configured to be additive to the roles provided by other authentication services.  

• Application Session Timeout – You can now prevent users from keeping a Dodeca session open indefinitely by specifying an inactivity timeout or by specifying a designated shutdown time.
  
  

The new logging features provide the ability for customers to both easily track what their users are running in the system and assist our support team if and when support is needed.  The new logging features include:

• View Usage Logging – View usage is now automatically logged in the server.  The logs include not only identifying information for the view and the user, but also include performance information, error information, and tokens used in the view generation.
  
  

• Client-side Request and Response XML Logging – The XML traffic traveling between the client and the server may now be logged to a directory on the client machine.  This logging expands on the Server-side Request and Response XML Logging to make it easier to gather the XML traffic for a single user.  In turn, the XML captured can be used by our support team to easily replicate transactions for our developers if and when necessary.

The selector enhancements include improvements to both view selectors and member selectors.  The improvements include:

• View Selector Relational Hierarchy Generation – You may now populate the entire View Hierarchy, or alternatively, populate one or more branches, based on the results of a relational query.
  
  

• View Selector Hierarchy Item Access Filters – Previously, you could control the View Hierarchies available to a given user.  This new filter provides the ability to control which view hierarchy items are presented in the view selector based on the current user’s roles.
  
  

• Relational Treeview Point-of-View Selector List– You may now configure a hierarchy of point-of-view items based on the results of a relational query.
  
  

• Enhanced Essbase Treeview Point-of-View Selector List Expansion and Selection Filtering – You can now filter Essbase member content and selectable status based on one or more specified filters including generation number, level number, member name, alias, or shared status.

View editing enhancements make it even easier to create and modify Views in Dodeca:

• Enhanced SQLPassthroughDataSet Query Editing – You can now define token values to use for testing tokenized SQL queries in the Test Data Set utility.

• Improved Token Editor – You can now view and edit tokens and their values in an improved grid layout.
  
  

Workbook scripting functionality adds more flexibility to Dodeca via 108 events that provide the opportunity for customers to extend Dodeca, 116 configurable methods (actions that can be taken in response to the events), and 138 functions that provide extended information to the workbook script.

The new workbook script functionality includes:

• New Events
• BeforeBuildExecute - Allows a workbook script to cancel the build before the view is covered.
• BeforeRefreshExecute - Allows a workbook script to cancel the refresh before the view is covered.
• Shown - Raised when the view is initially shown.  The event is raised before the framework applies the view’s AutoBuildOnOpen property, which allows a workbook script to set the property dynamically.
• New Methods
• ExportToExcel – Provides the ability to export view and point-of-view information in an exported Excel file.  This information is used to regenerate the view upon Excel file import which enables off-line data entry in Excel with subsequent database updates in Dodeca.
• SetSelectorConfiguration - Provides the ability to add or remove a point-of-view selector to/from a view dynamically.
• Enhanced Methods
• CallWebService – Added a new RESTRequest overload, which allows web service calls to made to RESTful web services.
• SendEmail - Added a new ServletSMTP overload, which sends email from the Dodeca server rather than from the client.  This is useful in circumstances in which SMTP mail must come from an approved IP address.
• SetEntry – Added a new Added FormulaArray overload which allows Excel array formulas to be entered programmatically.
• SetFill - Added a new Clear overload, which clears the fill color and pattern from the specified range.
• New Functions
• ColumnWidth - Returns the column width based on the active cell or a given cell.
• RowHeight - Returns the row height based on the active cell or a given cell.
• DataPointHasCellNote - Returns a boolean indicating if the active or specified data cell has Essbase LRO cell notes associated with it.
• IsInCharacterRange - Returns a boolean indicating whether the specified string is limited to the specified character range.  This function can be used to detect multi-byte characters in a string.
• Enhanced Functions
• SheetCount - Added an optional IncludeHiddenSheets argument, which controls whether the returned count includes both visible and hidden sheets or only visible sheets.

For more information, we recommend you download and read the latest Dodeca release notes from the registered section of our website.  As always, you may also reach out to us via email or call us at
256.885.4371.

Fundamentals of SQL Writeback in Dodeca

One of the features of Dodeca is read-write functionality to SQL databases.  We often get questions as to how to write data back to a relational database, so I thought I would post a quick blog entry for our customers to reference.

This example will use a simple table structure in SQL Server though the concepts are the same when using Oracle, DB2, and most other relational databases.  The example will use a simple Dodeca connection to a JDBC database.  Here is the Dodeca SQL Connection object used for the connection.

The table I will use for this example was created with the following CREATE TABLE  statement.

CREATE TABLE [dbo].[Test](
[TestID] [int] IDENTITY(1,1) NOT NULL,
[TestCode] [nvarchar](50) NULL,
[TestName] [nvarchar](50) NULL,
  CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED 
  ([TestID] ASC)
)

First, I used the Dodeca SQL Excel View Wizard to create a simple view in Dodeca to retrieve the data into a spreadsheet.  The view, before setting up writeback capabilities, looks like this.

To make this view writeable, follow these steps.

1. Add the appropriate SQL insert, update, or delete statements to the Dodeca SQL Passthrough Dataset object.  The values to be replaced in the SQL statement must be specified using the notation @ColumnName where ColumnName is the column name, or column alias, of the column containing the data.
2. Add the column names of the primary key for the table to the PrimaryKey property of the SQL Passthrough DataSet object.
3. Depending on the database used, define the column names and their respective JDBC datatypes in the Columns property of the SQL Passthrough Dataset.  This mapping is optional for SQL Server because Dodeca can obtain the required information from the Microsoft JDBC driver, however, the Oracle and DB2 JDBC drivers do not provide this information and it must be entered by the developer.

For insert, update, and delete operations, Dodeca parses the SQL statement to read the parameters that use the @ indicator and creates a JDBC prepared statement to execute the statements.  The prepared statement format is very efficient as it compiles the SQL statement once and then executes it multiple times.  Each inserted row is also passed to the server during the transaction.  The values from each row are then used in conjunction with the prepared statement to perform the operation.

Here is the completed Query definition.

Next, modify the DataSetRanges property of the Dodeca View object and, to enable insert operations, set the AllowAddRow property to True.  Note that if you added update and/or delete SQL to your SQL Passthrough Dataset object, be sure to enable those operations on the worksheet via the AllowDeleteRow and AllowModifyRow properties.

Once this step is complete, you can run the Dodeca View, add a row, and press the Save button to save the record to the relational database.

The insert, update, and delete functionalities using plain SQL statements is limited to operations on a single table.  If you need to do updates on multiple tables, you must use stored procedures to accomplish the functionality.  You can call a stored procedure in Dodeca using syntax similar to the following example:

{call sp_InsertTest(@TestCode, @TestName)}

Dodeca customers can contact support for further information at support@appliedolap.com.

Are you ready for Windows 10? Dodeca Is Ready!

Microsoft recently released its first preview version of their next desktop operating system, Windows 10. In our business, we have seen many large companies just migrate to Windows 7 and the general consensus seems to be that Windows 8 is a 'consumer' operating system. In other words, it will be a while before you have to think about Windows 10, but it is our business to be thinking about this now. We have the Windows 10 preview downloaded and installed in our labs. We made a very smart decision years ago when we decided to write the Dodeca client layer in .NET technology because it works in Windows 10 unchanged!

We are ready. Will you be ready?

What's New in Dodeca 6.7.1?

Last week, we released Dodeca version 6.7.1.4340 which focuses on some new relational functionality. The major new features in 6.7.1 are:

• Concurrent SQL Query Execution
• Detailed SQL Timed Logging
• Query and Display PDF format
• Ability to Launch Local External Processes from Within Dodeca

Concurrent SQL Query Execution

Dodeca has a built-in SQLPassthroughDataSet object that supports queries to a relational database.  The SQLPassthroughDataSet functionality was engineered such that a SQLPassthroughDataSet object can include multiple queries that get executed and returned on a single trip to the server and several of our customers have taken great advantage of that functionality.  We have at least one customer, in fact, that has some SQLPassthroughDataSets that execute up to 20 queries in a single trip to the server.  The functionality was originally designed to run the queries sequentially, but in some cases it would be better to run the queries concurrently.  Because this is Dodeca, of course concurrent query execution is configurable at the SQLPassthroughDataSet level.

Detailed SQL Timed Logging

In Dodeca version 6.0, we added detailed timed logging for Essbase transactions.  In this version, we have added similar functionality for SQL transactions and have formatted the logs in pipe-delimited format so they can easily be loaded into Excel or into a database for further analysis.  The columns of the log include the log message level, timestamp, sequential transaction number, number of active threads, transaction GUID, username, action, description, and time to execute in milliseconds.

Below is an example of the log message.

Click to enlarge

Query and Display PDF format

The PDF View type now supports the ability to load the PDF directly from a relational table via a tokenized SQL statement.  This functionality will be very useful for those customers who have contextual information, such as invoice images, stored relationally and need a way to display that information.  We frequently see this requirement as the end result of a drill-through operation from either Essbase or relational reports.

Ability to Launch Local External Processes from Within Dodeca

Certain Dodeca customers store data files for non-financial systems in relational data stores and use Dodeca as a central access point.  The new ability to launch a local process from within Dodeca is implemented as a Dodeca Workbook Script method which provides a great deal of flexibility in how the process is launched.

The new 6.7.1 functionality follows closely on the 6.7.0 release that introduces proxy server support and new MSAD and LDAP authentication services.  If you are interested in seeing all of the changes in Dodeca, highly detailed Dodeca release notes are available on our website at http://www.appliedolap.com/resources/downloads/dodeca-technical-docs.

Smart View Internals: Exploring the Plumbing of Smart View

Ever wonder how Smart View stores the information it needs inside an Excel file?  I thought I would take a look to see what I could learn.  First, I created this simple multi-grid retrieve in Smart View.

Note that the file was saved in the Excel 2007 and higher format (with an .xlsx file extension).  Did you know that the xlsx format is really just a specialized zip file?  Seriously.  It is a zip file containing various files that are primarily in xml format.  I saved the workbook, added the .zip extension to the filename, and opened it in WinRar.  Here is what I found.

I opened the xl folder to find a series of files and folders.

Next, I opened the worksheets folder to see what was in there.  Of course, it is a directory of xml files containing the contents of the worksheets.

My Essbase retrieves were on the sheet named Sheet1, so let’s take a look at what is in the sheet1.xml file.   The xml is quite large, so I can’t show all of it here, but needless to say, there is a bunch of information in the file.  The cell contents are only one of the things in the file.  Here is an excerpt that shows the contents of row 5 of the spreadsheet.

This is interesting as it shows the numbers but not the member name.  What is the deal with that?  I noticed there is an attribute, ‘t’, on that node.  I am guessing that the attribute t=”s” means the cell type is a string.  I had noticed that in one of the zip file screenshots, there was a file named sharedStrings.xml.  Hmm...  I took a look at that file and guess what I found?

That’s right!  The 5th item, assuming you start counting at zero like all good programmers do, is Profit.   That number corresponds perfectly with the value specified in the xml for cell B5, which was five (circled in blue in the xml file above).   OK, so when are we going to get to Smart View stuff?  The answer is pretty quick.  I continued looking at sheet1.xml and found these nodes near the bottom.

Hmm, custom properties that contain the name Hyperion?  Bingo!  There were a number of custom property files in the xml file.  Let’s focus on those.

Custom property #1 is identified by the name CellIDs.  The corresponding file, customProperty1.bin, contained only the empty xml node <root />.  Apparently there aren’t any CellIDs in this workbook.

Custom property #2 is identified by the name ConnName.  The file customProperty2.bin contains the string ‘Sample Basic’ which is the name of my connection.

Custom property #3 is named ConnPOV but it appears to contain the connection details in xml format.  Here is an excerpt of the xml.

Custom property #4 is named HyperionPOVXML and the corresponding file contains xml which lines up with the page fields I have in my worksheet.

What is interesting about the POV xml is that I have two different retrieves that both have working POV selectors which are both implemented as list-type data validations in Excel.  I don’t know what happens internally if I save different values for the POV.

Custom property #5 is labeled HyperionXML.  It appears to contain the information about the Essbase retrieval, but it doesn't appear to be the actual retrieval xml because it doesn't contain the numeric data.  My guess is that this xml is used to track what is on the worksheet from a Hyperion standpoint.

There is a lot of information in this simple xml stream, but the most interesting information is contained in the slice element.  Below is a close-up of contents in the slice.

The slice covers 6 rows and 7 columns for a total of 42 cells.  It is interesting that the Smart View team chose to serialize their XML in this manner for a couple of reasons.  First, the pipe delimited format means that every cell must be represented regardless of whether it has a value or not.  This really isn’t too much of a problem unless your spreadsheet range is pretty sparse.  The second thing about this format is that the xml itself is easy and fast to parse, but the resulting strings need to be parsed again to be usable.  For example, the vals node will get split into an array containing 42 elements.  The code must then loop the 42 elements and process them individually.  The other nodes, such as the status, contain other pieces of information about the grid.  The status codes appear to be cell attributes returned by Essbase; these attributes are used to apply formatting to cells in the same way the Excel add-in UseStyles would apply formatting.  There are a couple of things to take away:

1. In addition to the data on the worksheet itself, there is potentially *a lot* of information stored under the covers in a Smart View file.
2. String parsing is a computation-intensive operation and can hurt performance.  Multiply that workload by 8 because, depending on the operation and perhaps the provider, all 8 xml nodes above may need to be parsed.

In addition, the number of rows and columns shown in the slice may be important when you are looking at performance.  Smart View must look at the worksheet to determine the size of the range to read in order to send it to Essbase.  In the case of a non-multi-grid retrieve, the range may not be known and, as a result, the grid may be sized based on the UsedRange of the worksheet.  In our work with Dodeca, we have found that workbooks converted from the older xls format to the newer xlsx format, which support a larger number of cells, may have the UsedRange flagged internally to be 65,536 rows by 256 columns.  One culprit appears to be formatting applied to the sheet in a haphazard fashion.  In Dodeca, this resulted in a minor issue which resulted in a larger memory allocation on the server.   Based on the format of the Smart View xml, as compared to the more efficient design of the Dodeca xml format, if this were to happen in Smart View it may cause a larger issue due to the number of cells that would need to be parsed and processed.  Disclaimer: I did not attempt to replicate this issue in Smart View but rather is an educated guess based on my experience with spreadsheet behavior.

Note: The Dodeca xml format does not need to contain information for cells that are blank.  This format reduces the size and the processing cycles necessary to complete the task.  In addition, when we originally designed Dodeca, we tested a format similar to the one used today by Smart View and found it to be slower and less efficient.

Considering all of this information, I believe the xml format would be difficult for the Smart View team to change at this point as it would cause compatibility issues with previously created workbooks.  Further, this discussion should give some visibility to the fact that the Smart View team faces an on-going challenge to maintain compatibility between different versions of Smart View considering that different versions distributed on desktops and different versions of the internal formats that customers may have stored in their existing Excel files.  I don’t envy their job there.

After looking at all of this, I was curious to see what the xml string would look like on a large retrieve, so I opened up Smart View, connected to Sample Basic and drilled to the bottom of the 4 largest dimensions.  The resulting sheet contained nearly 159,000 rows of data.  Interestingly enough, when I looked at the contents of customProperty5.bin inside that xlsx file, the contents were compressed.  It occurred to be a bit strange to me as the xlsx file format is already compressed, but after thinking about it for a minute it makes sense as the old xls file format probably did not automatically compress content, so compression was there primarily to compress the content when saved in the xls file format.

Custom property #6 is labeled NameConnectionMap.  The corresponding property file contains xml that appears to map the range names in the workbook to the actual grid and the connection.

Custom property #7 is labeled POVPosition. The file customProperty7.bin contains the number 4 followed by a NUL character.  Frankly, I have no idea what position 4 means.

Moving on to custom property #8 which is labeled SheetHasParityContent.  This file contains the number 1 followed by a NUL character.  This is obviously a boolean flag that tells the Smart View code that new features, such as support for multiple grids, are present in this file.

Custom property #9 is labeled SheetOptions.  The corresponding file, customProperty9.bin, contains an xml stream that (obviously) contains the Hyperion options for the sheet.

Custom property #10 is labeled ShowPOV and appears to contain a simple Boolean flag much like that in custom property #8.

Finally, custom property #11 is labeled USER_FORMATTING and may not be related to Smart View.

I did look through some of the other files in the .zip and found a few other references to Smart View, but I did not see anything significant.

So, now that we have completed an overview of what is contained in one, very simple, multi-grid file, what have we learned?

1. There is a bunch of stuff stored under the covers when you save a Smart View retrieve as an Excel file.
2. With the reported performance issues in certain situations with Smart View, you should now have an idea of where to look to resolve Smart View issues in your environment.

There are a number of files I did not cover in this overview that could also cause performance issues.  For example, Oracle support handled one case where they found over 60,000 Excel styles in the file.  Smart View uses Excel Styles when it applies automatic formatting to member and data cells.  When there are that many styles in the workbook, however, it is logical that Excel would have a lot of overhead searching through its internal list of Style objects to find the right one.  Accordingly, there is a styles.xml file that contains custom styles.  If you have a bunch of Style objects, you could delete the internal styles.xml file.

Note: Be sure to make a copy of your original workbook before you mess with the internal structures.  There is a possibility that you may mess it up and lose everything you have in the workbook. Further, Oracle does not support people going under-the-covers and messing with the workbook, so don’t even bring it up to support if you mess something up.

Wow, that should give you some idea of what may be going on behind the scenes with Smart View.  Even with the experience I have designing and writing the Dodeca web services that talk to Essbase, I wouldn't say that I have a deep understanding of how the information in a Smart View workbook really works.  However, one thing is for certain;  Dodeca does not put stuff like this in your Excel files.  It may be interesting to hear what you find when you explore the internals of your workbooks.

Dodeca 6.6.0.4194 Now Available for Download!

This past Friday, November 22nd, we completed our work on the newest version of the Dodeca Spreadsheet Management System and made Dodeca 6.6.0.4194 available for download from our website.  This blog entry is a sneak peek at some of the new features in version 6.6, as well as 6.5, which was released to select customers with specific functionality requests.  There are a few features that are particularly useful for end users, so let’s start there.

More Excel Support

Dodeca has always been strong on Excel version support and this version delivers even more Excel functionality.  Internally, we use the SpreadsheetGear control, which does a very good job with Excel compatibility.  This version of Dodeca integrates a new version of SpreadsheetGear that now has support for 398 Excel functions including the new SUMIFS, COUNTIFS, and CELL functions.

Excel Page Setup Dialog

The new version of Dodeca includes our implementation of the Excel Page Setup Dialog which makes it easy for users to customize the printing of Dodeca views that are based on Excel templates.  Note that for report developers, the Excel Page Setup has also been included in the Dodeca Template Designer.

New PDF View Type

Customers who use PDF files in their environments will like the new PDF View Type.  In previous releases of Dodeca, PDF documents displayed in Dodeca opened in an embedded web browser control.  Beginning in this version, Dodeca includes a dedicated PDF View type that uses a specialized PDF control.

View Selector Tooltips

Finally, users will like the new View Selector tooltips which optionally display the name and the description of a report as a tooltip.

Performance

Performance is one of those things that users always appreciate, so we have added a new setting that can significantly improve performance in some circumstances.  Dodeca has a well-defined set of configuration objects that are stored on the server and we were even awarded a patent recently for the unique aspects of our metadata design.  That being said, depending on how you implement reports and templates, there is the possibility of having many queries issued to the server to check for configuration updates.  In a few instances, we saw that optimizing the query traffic could be beneficial, so we have implemented the new CheckForMetadataUpdatesFrequencyPolicy property.  This property, which is controlled by the Dodeca administrator, tells Dodeca whether we should check the server for updates before any object is used, as was previously the case, only when a view opens, or only when the Dodeca session begins.  We believe the latter case will be very useful when Dodeca is deployed in production as objects configured in production often do not change during the workday and, thus, network traffic can be optimized using this setting.  The screenshot below shows where the administrator can control the update frequency.

Though users will like these features, we have put a lot of new things in for the people who create Dodeca views and those who administer the system.  Let’s start with something that we think all Dodeca admins will use frequently.

Metadata Property Search Utility

As our customers continue to expand their use of Dodeca, the number of objects they create in the Dodeca environment continues to grow.  In fact, we now have customers who have thousands of different objects that they manage in their Dodeca environments.  The Metadata Property Search Utility will help these users tremendously.

This utility allows the administrator to enter a search string and locate every object in our system that contains that string.  Once a property is located, there is a hyperlink that will navigate to the given object and automatically select the relevant property.  This dialog is modeless, which means you can navigate to any of the located items without closing the dialog.

Note: this version does not search the contents of Excel files in the system.

Essbase Authentication Services

In the past, when administrators wished to use an Essbase Authentication service to validate a login against Essbase and automatically obtain Dodeca roles based on the Essbase user’s group memberships, they had to use an Essbase connection where all users had access to the Essbase application and database.  The new ValidateCredentialsOnly property on both of the built-in Essbase Authentication services now flags the service to check login credentials at the server-level only, eliminating the need for users to have access to a specific Essbase database.

New Template Designer Tools

Prior to Dodeca 6.x, all template editing was performed directly in Excel.  Since that time, however, most template design functionality has been replicated in the Dodeca Template Designer, and we think it is preferable due to the speed and ease of use with which users can update templates stored in the Dodeca repository.  We have added a couple of new features to the Template Designer in this version.  The first tool is the Group/Ungroup tool that allows designers to easily apply Excel grouping to rows and/or columns within the template.   The second new tool is the Freeze/Unfreeze tool that is used to freeze rows and/or columns in place for scrolling.

Parameterized SQL Select Statements

Since we introduced the SQLPassthroughDataSet object in the Dodeca 5.x series, we have always supported the idea of tokenized select statements.  In other words, the SQL could be written so that point-of-view selections made by users could be used directly in the select statement.  In a related fashion, we introduced the concept of parameterized insert, update, and delete statements in the same version.  While parameterized statements are similar in concept to tokenized statements, there is one important distinction under the covers.  In Dodeca, parameterized statements are parsed and converted into prepared statements that can be used multiple times and results in more efficient use of server resources.  The parameterized select statement was introduced in this version of Dodeca in order for customers using certain databases that cache the prepared statement to realize improved server efficiency on their select statements.

Workbook Script Formula Editor Improvements

We have also been working hard to improve extensibility for developers using Workbook Scripts within Dodeca.  In this release, our work focused on the Workbook Script Formula Editor.  The first thing we added here is color coding that automatically detects and distinguishes Excel functions, Workbook Script functions, and Dodeca tokens.  In the new version, Excel functions are displayed in green, Dodeca functions and parentheses are displayed in blue, and tokens are displayed in ochre.   Here is an example.

In addition, we have implemented auto-complete for both Excel and Dodeca functions.

New SQLException Event

Version 6.6 of Dodeca introduces a new SQLException event that provides the ability for application developers to customize the behavior when a SQL Exception is encountered.

XCopy Release Directory

Beginning in version 6.6, the Dodeca Framework installation includes a pre-configured directory intended for customers who prefer to distribute their client via XCopy deployment instead using Microsoft ClickOnce distribution.  The XCopy deployment directory is also for use by those customers who use Citrix for deployment.

Mac OS X Release Directory

The Dodeca Framework installation now includes a pre-compiled Dodeca.app deployment for customers who wish to run the Dodeca Smart Client on Mac OS X operating systems.  What that means is that Dodeca now runs on a Mac without the need for any special Windows emulators.  Dodeca does not require Excel to run on the Mac (nor does it require Excel to run on Windows for that matter), so you can certainly save your company significant licensing fees by choosing Dodeca for your solution. 

In short, you can see we continue to work hard to deliver functionality for Dodeca customers.  As always, the Dodeca Release Notes provide detailed explanations of all new and updated Dodeca features.  As of today, we have decided to make the Release Notes and other technical documents available for download to non-Dodeca customers.  If you are curious about all of the things Dodeca can do, and if you aren't afraid to dig into the details, you can now download our 389 page cumulative Release Notes document from the Dodeca Technical Documents section of our website.  

Save $250 on Kscope14 Registration Now!

If you work in the Essbase, Oracle EPM, or Oracle BI world, *the* place to be every June is the annual Kscope conference.  Registration is open for the next conference, Kscope14, coming next June in Seattle, WA.  If you are not currently a full ODTUG member, let me tell you how you can save $250 on the $1650 registration fee.

There are two steps you have to take to "save big".  First, become a full member of ODTUG for $99 and enjoy all of the benefits, including access to a members-only presentations library, throughout the year.  Next, register for Kscope14 and you are eligible for the members-only price of $1500 for a savings of $150.  While you are registering, simply use the code AOLAP to get an additional $100 discount!

My company, Applied OLAP, is one of top-tier Platinum Sponsors of Kscope14 and I will be there.  I hope to see you at the conference and, if you were able to save some money by using our exclusive AOLAP code, be sure to stop our booth, say hello, and learn how the Dodeca Spreadsheet Management System can help your company reduce spreadsheet risk, increase spreadsheet accuracy, and reduce costs.

EPM 11.1.2.3 Available for Download

Oracle Enterprise Performance Management 11.1.2.3 became available over the weekend, so let the rush of upgrading begin!

Here at Applied OLAP, we have already started our work with Essbase 11.1.2.3.  We have added the new version to our build list and have started testing Dodeca with Essbase 11.1.2.3.  We will make the 11.1.2.3 version of our server available to our customers within a few days.

One interesting fix that we have seen so far is that EPM 11.1.2.3 uses an upgraded version of Java, Java 1.6 update 35.   The previous EPM version, 11.1.2.2, shipped with Java 1.6 update 29 which contained a bug related to the SQL Server JDBC Driver.  The issue, known as the BEAST vulnerability, caused SQL Server JDBC connections to hang unless you made a fix to the Windows registry.  I, for one, am glad that issue is resolved.

Dodeca Dashboard Screenshot

One of our customers recently sent us a screenshot of a dashboard they developed in Dodeca. This application, which is deployed to over 1,000 Essbase users worldwide, allows users to easily analyze over 250,000 SKU-level products by customer and by business unit. The application, which was implemented to replace a failed attempt at forecasting within SAP, uses Essbase and went live in less than 20% of the time that was put into the SAP system.

We think it looks great.  How about you?

Happy Birthday Applied OLAP

Last Monday, we reached a milestone at Applied OLAP as it was the 15th anniversary of our incorporation.  I founded Applied OLAP on March 18, 1998, because I was not satisfied with the status quo of Essbase reporting at the time which was, of course, the classic Excel add-in.  And, despite the fact that I was one of the original five Microsoft Excel MVP's in the world, I was frustrated by inherent limitations in the add-in which meant limitations to what users could do with the spreadsheet.   So, being the totally unreasonable* person that I am, I quit my job in order to start a company focused on making it easier for users to leverage Essbase.  Flash forward 15 years and now we have Dodeca!

*One of my favorite quotes is from the playwright, George Bernard Shaw:

"The reasonable man adapts himself to the world; the unreasonable one persists in trying to adapt the world to himself. Therefore, all progress depends on the unreasonable man." 

Am I unreasonable?  Why yes.  Yes I am!

We Don't Make Essbase, We Make Essbase Better!

We have always claimed that Essbase was better with Dodeca, but until now we had only anecdotal evidence about how our customers get more return on their Essbase investment using Dodeca.  Now we have something a bit more concrete.  Recently, the annual BI Survey, which bills itself as the “world's largest vendor-independent survey of Business Intelligence and Performance Management users”, published the results of their annual survey, The BI Survey 12.  As in past years, we asked our customers to participate in the survey and, as a result, we earned a mention in the survey.  More importantly, we are thrilled by how we were mentioned.

One particular question in the survey asks customers how well they achieved their goals with their selected product.  In the past, Essbase has not scored well on this metric but, this year, for the first time ever, Essbase attained the highest score in goal achievement among all the products in the BI Survey.

• Better than Microsoft Analysis Services?  Yes!  
• Better than SAP BW?  Yes!  
• Better than Cognos?  Yes!   
• Better than Microstrategy?  Yes!  
• Better than TM1 / Tableau / QlikTech???   Yes! Yes! Yes!   
• Even better than OBIEE?   Yep!  

The analysis provided in the survey provides clues as to why the Goal Achievement Index for Essbase was the highest of all 25 products reviewed:

"This year for the first time we have a BI Giant with the highest goal achievement level. Oracle Essbase which has had quite low goal achievement scores in previous years, performs very well this year.  The sample includes more answers this year from companies that use Essbase in conjunction with front-end tools like Dodeca. These seem to increase the ratings for Essbase compared to ratings from users that only use the Excel Add-in."

That’s right.  Dodeca appears to be the reason that more customers achieve their goals with Essbase than with any other product.  If you are still using one of the Excel add-ins as your interface to Essbase, what are you missing?  Contact us today to learn more about how you can make Essbase better at your company using Dodeca and don't forget to ask about our no-cost evaluation program!

We have just released some exciting innovations in our most recent release.  Click here to read about the newest innovations inside the Dodeca Spreadsheet Management System.

Announcing Kscope13 Platinum Sponsor – Applied OLAP and Dodeca!

We are happy to announce that Applied OLAP and our flagship product, The Dodeca Spreadsheet Management System, have signed up as a Platinum Sponsor for the Kscope13 conference in New Orleans.  We believe that the conference, which runs from June 25 to June 28, is not only the best forum for Hyperion related content, but it is also the best place for Hyperion related vendors to show their products.

There are several events at Kscope13 that you should not miss.  The first event is the annual ODTUG Community Service Day on Saturday, June 24.  This year, ODTUG volunteers will be working to improve a school with a new coat of paint, landscaping and playground improvements.  The community service project is also a good time to meet others who share your interests.  I have made a number of new friends at these projects over the years.

The next can’t miss event is the Sunday Symposium.  The Symposiums are one of the only times during the year that you can get direct contact with the Oracle development team.   One of the cool things about the symposium is that Oracle not only talks about the new things they are working on over the next year, but they also spend a lot of time listening to the wishes of customers.

Last week, ODTUG announced the identity of the keynote speaker and, frankly, there have been many conferences where the keynote just didn’t interest me.  This conference will be different.  The story of the Kscope13 keynote speaker, Doc Hendley, is amazing and inspirational.  Doc is the founder of Wine To Water, an organization who is working to bring sustainable, clean fresh water to parts of the world that desperately need water.  I watched his TEDx Asheville presentation on YouTube and all I can say is this..  WOW!.  I can’t wait to meet Doc in New Orleans.

Finally, last time Kscope was in New Orleans, there was a Wednesday night Hyperion social event that wound its way down Bourbon Street.  There were about 22 of us that made that journey; this year I expect that number to be in the hundreds (and that is *after* the traditional Wednesday night Kscope “special event”, the just announced party at Blaine Kern’s Mardi Gras World.

As a vendor, I hope you get to spend time in the vendor hall and visit all of the vendors to learn about their products and services.  As a self-funded user group, ODTUG relies on these vendors help provide the funding to put on a great conference plus you may find some tools to help you in your job.  If your company uses spreadsheets, I would be especially happy to tell you about the benefits our customers realize using The Dodeca Spreadsheet Management System.
 
As a Platinum Sponsor, we can also help you save money on your Kscope13 registration.  Use the special code AOLAP when you register for Kscope13 and save $100 on your registration.  You can learn more about Kscope13 and register for the conference at www.kscope13.com.

See you there!

The Dodeca Spreadsheet Management System, Version 6.3, Has Been Released!

We have been very busy at work on a new version of Dodeca and were working so hard, in fact, that we missed our marketing opportunity of the century.  Of course, I am talking about December 12th or “12/12/12” which is also known as, of course, “Dodeca Day”.  Oh well, we have 99 years, 11 months to plan for the next one!

Not all is lost, however, because after months of hard work, we have released the Dodeca Spreadsheet Management System, version 6.3.0.3696.  The new version of Dodeca is very exciting as we have worked very hard to make it easier and faster to create and publish dynamic spreadsheet content.  This new capability is provided by the new Dodeca View Wizard and the new View Designer.  First, let’s take a look at the View Wizard.

The View Wizard is used to create or modify a Dodeca View which is the Dodeca version of a report or input form.  In all previous versions of Dodeca, the system administrators used a three step process to create and deploy a view:

1. Create a report template in Excel and import it into Dodeca using the Binary Artifacts Metadata Editor form.
2. Create a View object to link together the Excel template with Essbase and/or SQL Connection objects, and set Essbase properties and other settings.  These settings were set in the View Metadata Editor form.
3. Deploy the new View on a View Hierarchy object in the View Hierarchy Metadata Editor form.

When we originally had the vision for the View Wizard, our intent was that it would help developers who wanted to learn Dodeca as they wouldn’t have to learn these three separate forms just to publish a simple report.  We also thought that, once someone learned the ins and outs of the product, they would return to using the metadata editor forms.  What we have found, however, is that the View Wizard makes it so much easier that, even for me, *I* haven’t created a new view using the old ‘3-step process’ since I first had the View Wizard available to me in alpha form.

The View Wizard takes a step-by-step approach to creating the new view.  The first step is to choose the type of view that you would like to create.  Dodeca supports creating static views from Excel files or dynamic, data-driven views from Essbase and/or SQL data sources.

Choosing a specific wizard determines which properties are available on the view object.   Once you choose the type of view, the wizard then walks you through the steps.  The first step lets you assign a name to the new View.

Next, you specify the Excel file you would like to use or specify if you would like to create a view using a new Excel file.

In my example, I have selected a file from my desktop to bring into, and publish with, Dodeca.

The next step is to choose the member selectors you would like to filter the view.

The member selectors are highly configurable in Dodeca, but for this example I will let Dodeca use the default selectors, which use the Essbase hierarchy tree from which users can make selections.
Next, I will choose user interface settings including the toolbars and whether or not Dodeca displays the Excel formula bar.

In my case, I let Dodeca choose the default toolbars for this view type, and then I selected the checkbox that tells Dodeca to display the formula bar.
Next, I will skip the AutoBuild on Open step and jump to the Essbase Connection Settings.  When I initialized my Dodeca environment, I ran the Dodeca Essbase Connection wizard and chose the Essbase databases I wanted to include in my application.  In this step, I will choose which Essbase connection to use for this specific view.

In the next step, I will specify that Dodeca retrieve data into a specified range and will specify the format for #Missing and #NoAccess labels.   Dodeca has the ability to use a specified range name where the data will be retrieved.  This capability uses normal Excel range names which can be created dynamically within the spreadsheet.

My next step is to specify that this view be associated with a workbook script.  Workbook script is the typical way that customers customize and tailor view creation or behavior based on their unique requirements.   It works using a paradigm of events and related actions, or methods, which dynamically change either the spreadsheet or the Dodeca environment.  In my case, I don’t plan to do anything with the workbook script, yet, but I decided to add one just in case I want it later.

Finally, I am just going to press the Finish button in the navigation panel on the left and all of the necessary objects will be created behind the scenes and stored in the Dodeca server.   At this point, I have several options.

I need to make a couple of modifications to my Excel template.  Remember that I had picked three member selectors in an earlier step in the View Wizard, so I need to tell Dodeca where on the spreadsheet to place the member values selected by the user.   I also had specified that the data will be retrieved based on a named range, so I need to create that named range.   In all previous releases of Dodeca, this work would be done in Excel but that is no longer the case.  The new View Designer allows you to edit the Excel template directly within Dodeca.  First I will place the tokens that indicate where the selector values will be placed.

Next, I will specify the range where I want to limit the Essbase retrieve.

Finally, I saved the template back to the Dodeca server and tested the output by opening the view in preview mode directly from the wizard.

Since I liked how the view looked in preview mode, I decided to go ahead and deploy the view by placing the view in a view hierarchy and, using drag and drop, I dropped the new view on the hierarchy.

Once I placed the view in the hierarchy and committed it to the server, the view was now deployed and available to users on my server.

So, now you have seen an introduction to some very useful things in the new version of Dodeca and I hope you have the chance to use them soon. Soon, I will do a deeper dive post on the View Designer and explore the savings in both time and effort our customers are seeing when using this new component.

If you are not currently a Dodeca customer and would like to try it out, go to our Contact Us page on the Applied OLAP website and ask about an evaluation copy of Dodeca.