Tim Tow

Subscribe to Tim Tow feed
Updated: 28 min 57 sec ago

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

Tue, 2016-05-31 14:54
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.

Categories: BI & Warehousing

Next Generation Outline Extractor 2.0.5.1073 released

Wed, 2016-01-06 23:21


In the last week or so, we placed an updated version of the Next Generation Outline Extractor on our website. This version provides support for some updated Essbase versions, including 11.1.2.4.002, 11.1.2.4.003, and 11.1.2.4.005. More importantly, it addresses a bug where alias names were improperly associated with parent members when using the MaxL extraction source.. This bug was reported to us by a number of users and we are glad we were able to address it. Here is a list of the issues that were addressed:

2015.11.23 - Issue 1401 - Resolved an issue where only one alias table is exported when using MaxL as the extract source.

2015.11.23 - Issue 1402 - Resolved an issue where extracts using MaxL input and having members specified with Unicode may print incorrect characters in the output.

2015.11.23 - Issue 1403 - Resolved an issue where aliases and udas may have been improperly placed on parent members.

Please contact our support team if you have any issues.

Categories: BI & Warehousing

Fundamentals of SQL Writeback in Dodeca

Mon, 2015-10-05 22:00
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.
Categories: BI & Warehousing

Next Generation Outline Extractor 2.0.4.887 Released

Sun, 2015-06-14 09:36
We recently released an updated version of the Next Generation Outline Extractor. This new version, 2.0.4.887, addresses three issues:


  • Fixed an issue where the username and password passed via the command line were improperly logged
  • Fixed an issue reading MaxL XML data sources when the alias or UDA contained xml encoded characters such as the ampersand (&) character.
  • Updated labels on the Input Source tab of the user interface to clarify their purpose.
Here is a screenshot showing the updated labeling.


Due to the architecture of the Oracle Essbase APIs, it is generally much faster to use the MaxL Outline XML extracts when processing an Essbase Outline extract.  The Next Generation Outline Extractor still uses the Essbase Java API during this extract, but it is able to minimize the number of calls.  The second option shown above, Extract and Process MaxL Outline XML, will automatically extract the Outline XML from the cube during the processing.  The third option shown, Use Previously Extracted MaxL Outline XML, uses (obviously) an Outline XML file that has already been extracted.

Thank you to everyone who reported issues or made suggestions as you help make this utility better!

Categories: BI & Warehousing

New OlapUnderground Utility - The Essbase Outline Viewer

Fri, 2015-05-08 08:54
Have you ever wanted your users to see an Essbase database outline structure, but had trouble giving them access to Essbase Administrative Services ("EAS") for fear that they may have access to change things they shouldn't change?  Maybe you didn't want to give them EAS and have to deal with the version of Java installed on their machine?  We have the solution to your problems.  After a bit of fits and starts, we have finally released the OlapUnderground Essbase Outline Viewer!

The OlapUnderground Essbase Outline Viewer is a read-only user interface built specifically to view the contents of an Essbase outline.  It is built in Microsoft .NET technology and should run on any Windows-based computer without any special downloads.  It also uses the MaxL outline xml format and, as your Hyperion administrators can create and distribute the outline xml file, end users do not need an Essbase login to view the outline.

Here is a screenshot of the Outline Viewer:

Click image to see it full-size

You can download the Essbase Outline Viewer from our website at http://www.appliedolap.com/resources/downloads/essbase-outline-viewer.  Let us know what you think!

Categories: BI & Warehousing

Next Generation Outline Extractor - New Version Available

Mon, 2015-02-16 14:42
Today we released a new version of the Next Generation Outline Extractor, version 2.0.3.769.  Here are the release notes from this new version:

Version 2.0.3.769 supports the following Essbase versions:

9.3.1
9.3.1.1
9.3.1.2
9.3.3
11.1.1
11.1.1.1
11.1.1.2
11.1.1.3
11.1.1.4
11.1.2
11.1.2.1
11.1.2.1.102
11.1.2.1.103
11.1.2.1.104
11.1.2.1.105
11.1.2.1.106
11.1.2.2
11.1.2.2.102
11.1.2.2.103
11.1.2.2.104
11.1.2.3
11.1.2.3.001
11.1.2.3.002
11.1.2.3.003
11.1.2.3.500
11.1.2.3.501
11.1.2.3.502
11.1.2.3.505
11.1.2.4

Issues resolved in version 2.0.3.769:

2015.02.15 - Issue 1355 - All Writers - Add functionality to replace all line feeds, carriage returns, tabs, and extraneous spaces in formulas

2015.02.13 - Issue 1354 - RelationalWriter - Changed the default database name from dodeca to extractor

2015.02.13 - Issue 1353 - RelationalWriter - Added CONSOLIDATION_TYPE_SYMBOL, SHARE_FLAG_SYMBOL, TIME_BALANCE, TIME_BALANCE_SYMBOL, TIME_BALANCE_SKIP, TIME_BALANCE_SKIP_SYMBOL, EXPENSE_FLAG, EXPENSE_FLAG_SYMBOL, TWO_PASS_FLAG, and TWO_PASS_FLAG_SYMBOL columns to the CACHED_OUTLINE_MEMBERS table

2015.02.13 - Issue 1352 - RelationalWriter - Added Server, Application, and Cube columns to the CACHED_OUTLINE_VERSIONS table

2015.02.13 - Issue 1351 - Fixed issue with LoadFileWriter where UDA column headers were incorrectly written in the form UDAS0,DimName instead of UDA0,DimName

In addition, a number of fixes, etc, were put into 2.0.2 and earlier releases and those releases went unannounced.  Those updates included the following items:

  1. There is no longer a default .properties file for the Extractor.  This will force a user to specify a .properties file.  (2.0.2.601)
  2. Removed the "/" character as a switch for command line arguments as it causes problems in Linux. (2.0.2.605)
  3. Fixed issue when combining MaxL input with relational output where a "not supported" error message would appear due to certain properties were not being read correctly from the XML file (2.0.2.601)
  4. Command line operations resulted in an error due to an improper attempt to interact with the GUI progress bar. (2.0.2.601)
  5. Shared members attributes where not be properly written resulting in a delimiter/column count mismatch. (2.0.2.625)
  6. Added encoding options where a user can choose between UTF-8 and ANSI encodings.  The Extractor will attempt to detect encoding from selected outline and, if the detected outline encoding is different from the user selected outline encoding, a warning message appears.
Categories: BI & Warehousing

Ephemeral Port Issue with Essbase Has Been Fixed!

Fri, 2015-02-13 09:24
The issue that has plagued a number of Essbase customers over the years related to running out of available ports has finally been fixed!

This issue, which often manifested itself with errors in the Essbase error 10420xx range, was caused by how the Essbase Java API communicated with the server. In essence, whenever a piece of information was needed, the Essbase Java API grabbed a port from the pool of available ports, did its business, and then released the port back to the pool. That doesn’t sound bad, but the problem occurs due to how Windows handles this pool of ports. Windows will put the port into a timeout status for a period of time before it makes the port available for reuse and the default timeout in Windows is 4 minutes! Further, the size of the available pool of ports is only about 16,000 ports in the later versions of Windows. That may sound like a lot of ports, but the speed of modern computers makes it possible, and even likely, that certain operations, such as the outline APIs, that call Essbase many, many times to get information would be subject to this issue. Frankly, we see this issue quite often with both VB and the Java Essbase Outline Extractors.

We brought this issue to the attention of the Java API team and assisted them by testing a prerelease version of the Java API jars. I am happy to report the fix was released with Essbase 11.1.2.3.502. In addition, there is a new essbase.properties setting that allows you to turn the optimization on or off:

olap.system.socketoptimization=false

It is our understanding that this optimization is turned on by default. I also checked the default essbase.properties files shipped with both Essbase 11.1.2.3.502 and 11.1.2.4 and did not see that setting in those files. It may be one of those settings that is there in case it messes something else up. The work of our own Jay Zuercher in our labs and searching Oracle Support seems to have confirmed that thought. There is apparently an issue where EIS drill-through reports don't work in Smart View if socket optimization is turned on. It is documented in Oracle Support Doc ID 1959533.1.

There is also another undocumented essbase.properties setting:

olap.server.socketIdleTime

According to Oracle development, this value defaults to 300 ms but there should be little need to ever change it. The only reason it is there is to tune socket optimization in case more than 2 sockets are used per Java API session.

Jay also tested the 11.1.2.4 version in our labs with the Next Generation Outline Extractor. With the default settings, one large test outline we have, "BigBad", with about 120,000 members in it, extracted in 1 minute and 50 seconds.  With socket optimization turned off, the same outline was only about 25% complete after 2 hours.   In summary, this fix will be very useful for a lot of Oracle customers.

Categories: BI & Warehousing

Essbase VB API is Officially Dead

Thu, 2015-02-12 11:06
It is with a sad heart that bring you the news that, as of Essbase 11.1.2.4, the Essbase VB API is officially dead.  I cut my teeth in Essbase working with that API way back in the mid-1990's, but the writing had been on the wall for some time.  Microsoft stopped supporting VB years ago, so it was only a matter of time before this time would come.  That being said, I haven't used the VB API for any new Essbase work since about 2001; the Essbase Java API is alive and growing so my efforts have been there.

Here is the official notification:













You can read the notification yourself in the Essbase 11.1.2.4 Readme.
Categories: BI & Warehousing

File Encoding in the Next Generation Outline Extractor

Tue, 2014-12-09 20:11
We had a couple of issues reported with the output of the Next Generation Outline Extractor where the exported file did not work properly as a load file. After some investigation, we found that the file encoding was incorrect. We were encoding the files using the Unicode/UTF-8 format. We chose this encoding so that we could write all characters in all languages, but we did not consider that UTF-8 is only valid for loading Unicode databases in Essbase.

To resolve this issue, we decided to add a configuration to the Next Generation Outline Extractor to allow users to select the file encoding. Here is a screenshot showing the new configuration setting.



As of yesterday, December 8, 2014, the updated Next Generation Outline Extractor is available on our website. The first version to feature this functionality is version 2.0.2.692. Version 2.0.2.692 is available for all Essbase versions from Essbase 9.3.1 forward. We are also happy to announce that his version of the Next Generation Outline Extractor is the first version to support the recently released Essbase 11.1.2.3.505.

If you encounter any issues with the Next Generation Outline Extractor, please don't hesitate to contact our support team at support@appliedolap.com.

Categories: BI & Warehousing

Are you ready for Windows 10? Dodeca Is Ready!

Wed, 2014-10-15 23:38
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?
Categories: BI & Warehousing

Kscope15 Abstract deadline is Wednesday, October 15!

Sat, 2014-10-11 14:08
Kscope15 abstract submission will close in just a few days and the ODTUG content teams want you!

I am sure there are many of you who have never done a presentation at a conference and may not feel comfortable speaking in public. Well, I have a secret for you, despite the fact that I won the Best Speaker Award at Kscope14, I share your discomfort with speaking. What works for me, however, is that I only speak about topics that I know very well. Do you have some area of expertise that you know really well? If you do, please share it! It will be fun and all of your peers who learn something new, from you, will appreciate it!

With that, take the next step and click here to submit session!
Categories: BI & Warehousing

Next Generation Outline Extractor Webcast - Oct 8th!

Fri, 2014-09-12 17:07
I am doing a repeat of my Kscope15 'Best Speaker' award-winning presentation as part of the ODTUG webcast series. Here is the official announcement from ODTUG:

Wednesday, October 8, 2014 12:00 PM - 1:00 PM EDT

Next Generation Essbase Outline Extractor Tips and Tricks
Tim Tow, Applied OLAP

The Next Generation Outline Extractor is the follow-up to the classic OlapUnderground Essbase Outline Extractor used by thousands of Essbase customers. This session, which was the highest-rated session at Kscope15 in Seattle, explores some of the new capabilities of the Next Generation Outline Extractor, including command line operations exported directly to a relational database. Attend this session to learn how to leverage this free utility in your company.
Make you you sign up to join my on October 8th; you can register here!.
Categories: BI & Warehousing

What's New in Dodeca 6.7.1?

Mon, 2014-03-24 11:28
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 ExecutionDodeca 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 LoggingIn 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 formatThe 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 DodecaCertain 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.

Categories: BI & Warehousing

Meet the Oracle ACE Directors Panel - January 9 - Seattle

Thu, 2013-12-19 08:27

I will be in Seattle on Thursday, January 9th for the Meet the Oracle ACE Directors Panel.  It is at the Sheraton Seattle from 4 - 6 pm and will feature several other ACE Directors including Martin D'Souza, Kellyn Pot'Vin, Tim Gorman, and my longtime friend and collaborator, Cameron Lackpour.  

Come see and the panel and stay for the Happy Hour; the beer will be on me!


Categories: BI & Warehousing

Smart View Internals: Exploring the Plumbing of Smart View

Fri, 2013-12-13 17:46
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.
Categories: BI & Warehousing

Dodeca 6.6.0.4194 Now Available for Download!

Mon, 2013-11-25 18:23
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.  



Categories: BI & Warehousing

Save $250 on Kscope14 Registration Now!

Thu, 2013-11-21 17:17
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.
Categories: BI & Warehousing

Hyperion 11.1.2.3 Update - Kscope14 Street Team Meetup in Seattle - November 8th

Fri, 2013-11-01 20:54
If you are in or near Seattle on Friday, November 8th, don't miss the opportunity to see the new features of the Hyperion 11.1.2.3 release.  Oracle representatives are teaming together with the Kscope14 Street Team to bring you this 2-hour mini-session at the Fred Hutchison Cancer Research Center.  The session starts at 3 pm sharp and concludes with networking and a happy hour.  For details and to RSVP, visit http://kscope14.com/events/seattle-street-team.

I won't be able to make it to this event, but I wish I *could* be there.  I can't think of a better way to kick off a weekend!

Categories: BI & Warehousing

The New AppliedOLAP.com / Free Tools Access

Sat, 2013-09-28 18:53
My team has been hard at work getting our new website out the door, and we feel that it will make for a better experience all-around for our users and the users of the free OLAPUnderground utilities.  To help make the experience better, we have implemented a new download process.  Previously, users who wished to download files selected the files they wished to download and then provided their email address.  Our server then sent an email with temporary links for the download.  The email process sometimes caused issues with email filters or incorrect emails, so we decided to work to provide a better experience by using an off-the-shelf software release component.  As a result of this change, we are now requiring users to have a user account to download software.  Fortunately, the user account is free and you can create your own account in about a minute.

On the new site, all of the free downloads are available from the 'Downloads' section under 'Resources.'


If you are not logged in and you attempt to access the 'Downloads' section of the site, you will be prompted to login or register:


If you already have a www.appliedolap.com account, you can go ahead and login using your existing credentials, but if you don't, simply click the 'Register' button and fill out the registration form that pops up.


Once you have filled out the required information and pressed 'Register', a confirmation e-mail will be sent to the address provided on the registration form.


Click 'OK' and check your inbox for the confirmation e-mail.  Once you click the activation link, all you need to do is login to the site and you will have full access to the 'Downloads' section.  If you are a Dodeca user, we will provide you access to the 'Dodeca' downloads page, but as a free registered user, you will have access to the OLAPUnderground tools, as well as the Next Generation Outline Extractor.

Note:  Your Applied OLAP account username will always be the
e-mail address you used when you registered your account.


Go back to the 'Downloads' section after you log in to access the OLAPUnderground Utilities, as well as the Next Generation Outline Extractor.


We hope you find the new Applied OLAP website informative and easy-to-use.  While you are there, be sure to check out all of the new information about the Dodeca Spreadsheet Management System and sign up for a free, personalized web demo so you can learn how Dodeca can help you.
Categories: BI & Warehousing

Oracle Open World 2013 General Session: Empowering Modern Enterprise Performance Management

Wed, 2013-09-25 16:23
Here at Oracle Open World 2013, I attended the general session entitled "Empowering Modern Enterprise Performance Management" which was led by Balaji Yelamanchili, Oracle Senior Vice President for Analytics and Performance Management Products.  His keynote focused on the development priorities within the Oracle EPM group.  Those priorities are:

  • Continued Investment in Existing Modules
  • EPM Applications in the Cloud
  • Continued Innovation in New Modules

There are a number of things planned in the area of continued investment in existing modules.  The investment areas include:

  • HFM platform work to make HFM cross platform and to improve performance.  In addition, these changes allow HFM to run on Exalytics which puts massive computing power under HFM.  For example, testing of HFM on Exalytics has shown that virtual close becomes a possibility even in complex consolidations.  One test that had 31,000 accounts, 2,000 legal entities and 66 currencies showed a reduction of consolidation time from 7+ hours down to less than 30 minutes.  This type of performance increase, if borne out in the real world, could have a real effect on how and when companies do their consolidations.
  • Optimization of Hyperion Planning and Hyperion Profitability and Cost Management for Exalytics.  This scalability allows for implementations that spread further in the company (i.e. more users), but also allow companies to consolidate their applications onto fewer physical machines.
  • Packaged OBI Analytics for Hyperion Planning.  The packaged analytics make it easier and faster to deploy Hyperion Planning with an added benefit of reduced consulting costs.

Oracle is continuing its move into the cloud arena with 2 services.

  • The Oracle Planning and Budgeting Cloud Service ("OPBCS") has been in customer and partner preview for a few months now and is nearly general availability.  Oracle is stressing that this isn't just moving the software to a managed service running on the cloud, but rather is a fundamental new paradigm for planning that focuses on self-serve models that have not existing since the inception of the classic, on-premise Hyperion Planning.  Because OPBCS must run without the need for IT interaction, users must be able to quickly and easily configure what they need from the service.  That being said, Oracle is taking a very conservative strategic approach with their Cloud Planning application as they are not trying to displace traditional on-premise Planning applications and move them to the cloud.  Rather, they are emphasizing the cloud for use in quick-to-deploy departmental applications or as a development platform that can be used to build an application for on-premise deployment.  Oracle expects to make the OPBCS available later this calendar year.
  • A new service that was announced at Open World 2013 is the new Financial Reporting Cloud Service.  Based on the slides and the comments made during the presentation, this isn't simply moving the classic Hyperion Financial Reporting application to the cloud, but rather they have imagined and created an application that appears to be targeted for statutory reporting.  How it appears to work is that the developer/administrator creates a framework for the reporting package which may be, for example, a corporate annual report and can assign responsibilities for different parts of the report to team members.  The editing of different components can be completed either via refreshing of data from the corporate data stores for items like a balance sheet or income statement, or edited in Word for things like the management discussion.  The product tracks workflow and approvals and, when complete, produces a PDF of the completed document.  It looks quite different than what we see today in HFR.
For innovation in new modules, Oracle is focused pretty heavily on mobile.  As of now, the focus on mobile has been on information consumption but Oracle plans to work towards full user functionality in the future.  They are using the 90/90 rule as a guide for their mobile effort by focusing on the functionality used by 90% of users 90% of the time.  In addition, they have created some purpose-specific applications such as the new EPM Workflow Mobile App that was created specifically for approval workflows for Planning and Financial Close.

Oracle has been working hard on the EPM Suite, but notice I didn't really say anything about my beloved Essbase.  Balaji did not say much about Essbase in his session, but rather left that for the Essbase Roadmap session that I also attended.  I will work on a blog post on that session soon.

Categories: BI & Warehousing

Pages