Smart View Private Connection Issue - Resolved!

I had managed to go for the past 5 months, since I moved to Windows 7 64-bit on my laptop with Essbase 11.1.2, without the need to connect to Smart View.  This week, I decided I wanted to test something in Smart View and compare it with Dodeca but, low and behold, I could not create a private connection with Smart View to save my life.   After some investigation, I found some nasty messages in my apsserver.log (or, more specifically, in C:\Oracle\Middleware\user_projects\domains\EPMSystem\servers\AnalyticProviderServices0\logs\apsserver.log):

[2011-04-16T20:34:42.184-05:00] [AnalyticProviderServices0] [ERROR] [] [oracle.EPMOHPS] [tid: 18] [userId: ] [ecid: 0000IxX6708EgKWzLwyGOA1De^CQ000001,0] [APP: APS#11.1.2.0] [[
java.lang.NullPointerException
 at com.hyperion.dsf.server.framework.BaseLogger.writeException(Unknown Source)
 at com.essbase.smartview.util.EssLogger.writeException(Unknown Source)
 at com.essbase.smartview.xml.XmlHandler.invoke(Unknown Source)
 at com.essbase.smartview.xml.XmlHandler.invoke(Unknown Source)
 at com.essbase.smartview.xml.XmlHandler.invoke(Unknown Source)
 at com.essbase.smartview.xml.EssbaseSmartView.doPost(Unknown Source)
 at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
 at javax.servlet.http.HttpServlet.service(HttpServlet.java:821)
...  blah, blah, blah!

I did a quick search of the web and found nothing relevant, so I turned to my friend and fellow ACE Cameron Lackpour..   Cameron had just finished working his way through the Oracle Knowledgebase notes on Smart View on Oracle support.  He came up with this nugget:

https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=1098634.1

It told me the cause of my ills was an incomplete configuration and gave me a couple of different methods to complete the configuration.  I chose to export and edit the datasources.xml file.

In the xml, I simply had to add my Essbase server name:

Once that was complete, I was able to create a private connection:

KScope Hotels

Yesterday, I found out during the ODTUG Board Meeting that the hotels for KScope11 are starting to fill up.   I will staying at the Renaissance Long Beach Hotel as most of the Hyperion focused events, including Hyperion Midnight Madness, will be there.  Many of the other Hyperion attendees will be there as well, so I am sure there will be many, many networking opportunities which can be just as valuable as the conference sessions themselves.  Does anyone remember the spirited games of Jenga at the hotel in Monterrey?  Jenga Joe, are you staying at the Renaissance?

EPM 11.1.2.1 on the cloud

11.1.2.1 has now been out a week and thus should not be news to anyone here.  One interesting thing different about this release, however, is the cloud instance that has popped up so that anyone can easily take advantage of giving 11.1.2.1 a test drive.

Emerging Solutions' John Booth has put an Amazon cloud instance online.  John has also worked hard on the cloud instance that will be used for hands-on labs at KScope 11.  For the latest details, see John's blog at http://www.metavero.com/index.php?option=com_content&view=article&id=51:epm-test-drive&catid=1:latest-news&Itemid=50.

If you have never used the Amazon cloud, Cameron Lackpour has posted a blog on how to get started with John's image.  See Cameron's blog post at http://camerons-blog-for-essbase-hackers.blogspot.com/2011/04/yeah-yeah-11121-is-here-so-what_12.html.

As for me, I am getting busy providing Dodeca support for 11.1.2.1.  It should be available either late this week or early next week.

Test, Test, Test

I am working on some of the relational code in our server and decided to test performance of a small function to see which alternative was faster.  The function tests if a string contains all whitespace and I thought one alternative may be much faster than the other. 

The first alternative uses regular expressions.  It does look more elegant, but I didn't look at the Java source code to see what it is actually doing.  Further, I don't use regular expressions much and thought I would take the opportunity to see if it would help me in my quest for performance.  Regular expressions can do some complex processing, so it makes sense they may not be real fast, but for some reason I thought this option may be better. 

The second alternative merely trims the string and evaluates whether the length of the trimmed string is zero (in which case, of course, the original string was all spaces).  This alternative creates a new String object each time it is called and thus I thought it may have some performance problems.

Here is the code:

public static void main(String[] args) {
  performanceTest("   ", 1000000, 1);
  performanceTest("   ", 1000000, 2);
  performanceTest(" bogus ", 1000000, 1);
  performanceTest(" bogus ", 1000000, 2);
}

static boolean isWhiteSpaces( String s ) {
  return s != null && s.matches("\\s*");
}

static boolean isWhiteSpaces2( String s ) {
  return s.trim().length() == 0;
}

public static void performanceTest(String s, int loops, int version) {
  long start = System.currentTimeMillis();

  for (int i = 0; i < loops; i++) {
    if (version == 1) {
      isWhiteSpaces(s);
    } else {
      isWhiteSpaces2(s);
    }
  }

  System.out.println("Total time for " + loops + 
                     " loops using algorithm #" + 
                     version + ": " + 
                 (System.currentTimeMillis() - start));
}

I was surprised at the difference in the performance:

Total time for 1000000 loops using algorithm #1: 1042
Total time for 1000000 loops using algorithm #2: 41
Total time for 1000000 loops using algorithm #1: 991
Total time for 1000000 loops using algorithm #2: 41

The second option, which trims the string, then compares the length, wins hands down.  Now I know which option I will use in my code.

New Dodeca Training Video Online: Creating an Essbase Excel View, Part 1

We have a new training video online that shows how to convert a simple Essbase-aware Excel template to a Dodeca report in minutes.


Adding the Essbase Retrieve Range

This 23 minute video, which is part 1 of 2, is posted on the Applied OLAP YouTube channel at http://www.youtube.com/user/appliedolap#p/c/5BF8FA7D57B7EF19/0/x4S6IxX45pM.  The first video covers basic Excel template preparartion concepts, such as retrieve ranges and tokens, importing the Excel template into Dodeca, creating the View object for configuration settings and deployment. 

The second video in the series, which will be posted soon, will continue building the view and will show how to make the template cascadable.

Enjoy!

Hacking EIS for Fun (and Profit)

I have been doing some cool work on our Dodeca Essbase server and am at the point where I am working on Linked Reporting Objects. Of course, one type of LRO is the EIS Drillthrough Report. It has been years since I had EIS running on my machine, so I decided to get it running to help my development efforts.

Needless to say, this is the first time I have installed EIS since I converted my laptop over to Essbase 11.1.2 running on Windows 7 64-bit. Everything seemed to be going along very smoothly building the EIS Sample until I ran the Create Sample menu entry in the EIS console. Error! And it was a very strange one at that:

Contact your AIS administrator because the Java runtime environment may not be set appropriately on the server machine.., IMPORTMODEL failed NULL.

After some quick research, I found I am not alone with this issue as there are a couple of posts on OTN on the subject. With a bit of searching, I found this item on support.oracle.com:

Contact your AIS administrator because the Java runtime environment may not be set appropriately on the server machine..." EIS XML Import /Export Fails on Unix [ID 1086979.1]

The symptoms listed include:

• Cannot use the Import/Export feature of Models from the AIS console.
• The XML import/export feature in 64bit EIS 11.1.2 on 64bit Windows 2008 doesn't work.
• When trying to import or export any OLAP model or metaoutline to a XML file, the following error occurs: "Error -1: Contact your EIS administrator because the Java runtime environment may not be set appropriately.."

I finally got it to work by going to a 32-bit Essbase 11.1.2 VM, configuring the TBC and TBC_MD DSN’s to point to the relational database on my 64-bit server, then running the Create Sample menu item on the 32-bit machine. The import worked and inserted the data into the tables on my 64-bit system.

Once this was complete, I opened the TBC metaoutline and tried the Verify menu item. OUCH! I remember hating this issue:

According to my friends in tech support, I needed to create the tables under the TBC username. In other words, wipe the databases and start over. It was painful enough getting to this point, so I decided to figure out why the TBC username was required.

I looked at the data in all of the tables of the TBC database and found some columns where the TBC prefix existed in the data.

As this is just a test system, I decided I would try to modify the data in my effort to get the EIS sample application to work. In the end, I had to update the ‘TBC’ string to ‘dbo’ in the following tables/columns:

• OA_INFO – PHYSICAL_TABLE
• OA_INFO – ATTRIBUTE_EX_RULE
• MO_INFO – MO_OWNER
• OM_INFO – MODEL_OWNER
• OVP_REL_INFO - PHY_TABLE_1
• OVP_REL_INFO - PHY_TABLE_2

Hopefully, I didn’t miss any of the changes I made as I put this list together after getting the Verify function to work. Once this was complete, the TBC sample loaded successfully. After this brief distraction, back to work on my Java code!

Dodeca 5.3.0.2646 Available for Download!

We have been busy working on some new functionality for our 5.3.0 release and have finally got it out the door!  This release features a number of new capabilities requested by customers along with other product improvements.  Our release notes contain an exhaustive list of the changes, but here are some of the highlights:

Support for PDF

We have added native capability to write data in a grid or Excel based Dodeca view directly to a PDF file.  The output PDF file is PDFA-1b compliant.  Additionally, the generated PDF file can be opened locally in a Dodeca WebBrowserView or externally in the default PDF viewer.   We have also added programmatic support for PDF by way of the SaveWorkbookAsPdf method (or "action") and the BeforeSaveAsPdf and AfterSaveAsPdf event links.

Dodeca SQL Excel View Rendered as PDF (click to enlarge)

Excel AddIn Mode

Dodeca was created to target the need for a bulletproof spreadsheet environment for planning, analysis and reporting on Essbase and relational data sources, but we have had requests to make Dodeca work even more like the classic Excel add-in.  We are happy to announce this new release features our new Excel AddIn mode.  This mode, which is implemented as an optional configuration setting on our Adhoc Essbase view type, allows an end user much more flexibility to choose their own Essbase connection, load their own Essbase-aware Excel workbooks from their hard drive and retrieve/drill just like they are using the classic addin (except without the bugs!)




Copy Comments

We have had many customers ask if they could copy their analytic commentary from one set of datapoints to another. For example, one customer wanted to copy their comments relating to January Forecast for their 25 assigned markets to the February Forecast. In response, we have added server-side support for copying comments. Additionally, we have added the CopyComments workbook script method/action to facilitate end users initiating the copy comment process.



CopyComments Workbook Script Method (click to enlarge)













Support for Adding and Removing UDAs

User Defined Attributes are often used to mark Essbase members for special usage in an Essbase application.  This new functionality provides a new workbook script method, SetUDA, with both AddToMember and RemoveFromMember overloads.  The workbook script method allows an adminstrator to include functionality in an application to add, or remove, one or more UDAs from one or more Essbase members in a single call to the server.  Of course, with any workbook script, the functionality may be attached to a toolbar button, menu item or context menu item to make it easy for users to complete the task.

Improvements Working with Stored Procedures

In a Dodeca SQLPassthroughDataSet object, the select, insert, delete, and update statements defined for the object can be defined to execute a stored procedure.  Here is an example of a typical stored procedure:

execute @argument1,@argument2,…

Inside Dodeca, we take the SQL statements, parse them, and create prepared statement objects.  In this process, we also modify insert SQL on the fly to eliminate columns for which there is a parameter defined,

but which do not have a parameter passed for the record. We did this to allow for database default column values to be returned after an insert statement. Nice feature and I had just bunches of fun writing that one! It works very well except, for stored procedures, arguments are typically required (vs. SQL which is very flexible). We have changed the behavior of an insert when we detect a stored procedure to not modify the SQL.

Other New Workbook Script Events

BeforeDataTableRangeAddRow / AfterDataTableRangeAddRow - Occurs before / after a row (or rows) is added to a DataTableRange either by a user adding/inserting a row(s) or when an empty row is automatically added.

BeforeDataTableRangeDeleteRow / AfterDataTableRangeDeleteRow - Occurs before / after a row (or rows) is deleted from a DataTableRange.

Other New Workbook Script Methods

SaveDataTableRangeRow supports saving the changes to a specified row within a DataTableRange. The row can be an added (or inserted) row or a modified existing row.

Other New Workbook Script Functions

The DataTableRangeRowHasChanges function returns True or False based whether a row within a DataTableRange contains unsaved changes, which includes a new row and a modified existing row.

The DataTableRangeRowState function determines whether a row was Added, Modified, or Unchanged.
This is a summary of some of the new things in the Dodeca 5.3.0 release.  We have worked hard to get this release out the door.  Next on our agenda, we expect to release a 5.3.1 version with some functionality that didn't meet the deadline for 5.3.0.  We are also working in the background on our next big release; we hope to start talking about the contents of that release in the not too distant future.