The Essbase API Can Sometimes Be Very Touchy

I am working on something this morning with the Java API and found something to be a bit touchy and, of course, thought I would share it in case someone else runs into something similar.  Specifically, I was using the IEssCube.queryMembers() method which very quickly returns member information.  This function is the Java version of the VB API EsbQueryDatabaseMembers and C API EssQueryDatabaseMembers functions.

Those API calls use a query language that is similar to, but different than, Essbase report scripting language.  In this scripting language, spacing matters.  Here is a screenshot of something that didn't work:

Note there is no space between the FORMAT and {MBRNAMES} clauses in the query.  I added a space and here is the result:

In this case, the API correctly returned the ancestors of Connecticut.

Lesson: Be aware that spaces can be important, in some places, when working with Essbase.

Little Nitpick on essxlvba.txt

I was working on answering an OTN question today (and creating a related blog post) and I came upon a little annoying thing in the Extended Spreadsheet Toolkit declarations file, essxlvba.txt.

I imported the file into VBA like I *used* to do years ago when I made a living writing Essbase Excel VBA code (and before I was fed up with it and started my own company to do things better). I used one of the essxlvba.txt constants, EssBottomLevel, in the following line:

v = EssVGetMemberInfo(Null, "Year", EssBottomLevel, True)

As always, I was using Option Explicit at the top of my module and it caught that this line of code would not compile. Why? Because EssBottomLevel was not defined. Of course, it was in the imported module:

Const EssChildLevel = 1
Const EssDescendentLevel = 2
Const EssBottomLevel = 3
Const EssSiblingLevel = 4
Const EssSameLevel = 5
Const EssSameGenerationLevel = 6
Const EssCalculationLevel = 7
Const EssParentLevel = 8
Const EssDimensionLevel = 9

What was the problem? In VBA, the above declaration limits the scope of the variable to that module. As I was using Option Explicit, that didn't present a problem to me as VBA alerted me to the issue. What about the (majority of?) VBA programmers who don't use Option Explicit? They, of course, would have a bug in their code and would have to search for the reason they didn't get 'bottom level' members returned.

The easy solution would be for Oracle to simply change the declaration to expand the scope; you can do this in your essxlvba.txt file today:

Public Const EssChildLevel = 1
Public Const EssDescendentLevel = 2
Public Const EssBottomLevel = 3
Public Const EssSiblingLevel = 4
Public Const EssSameLevel = 5
Public Const EssSameGenerationLevel = 6
Public Const EssCalculationLevel = 7
Public Const EssParentLevel = 8
Public Const EssDimensionLevel = 9

I wonder how many thousands of hours of lost productivity by hapless programmers can be attributed to this oversight?

Using EssCalc in Excel

There was a question on the Network54 board about our EssCalc component and how to use it in Excel. I didn't know anyone was still using the code, which was last touched in the VB5 days, but I thought the answer may make a good blog post.

EssCalc is a free utility that I wrote many years ago to show how to create and execute custom tokenized calculations from VB. It is, however, just as usable from Excel. To use it from Excel, follow these steps:

1. Download the code from the downloads area of our website at http://www.appliedolap.com/.
2. Extract the code to a directory.
3. In the VBA editor, import the file CEssCalc.cls into your project.
4. Confirm the API declarations in CEssCalc match those for your version of Essbase.
5. Import the file essxlvba.txt to get the Excel VBA declarations.
6. Instead of using the code in modMain to get a context handle (hCtx), use the Excel functionEssVGetHctxFromSheet.
7. Use the class to create/run a calc.

Here is the sample code that works on my system (including a calc script with a valid syntax):

Sub RunCalc()
Dim oCalc As New CEssCalc ''' calc object
Dim hCtx As Long

On Error GoTo ErrorHandler

''' get the hctx
hCtx = EssVGetHctxFromSheet(GetSheetname())

''' the context handle is required
oCalc.hCtx = hCtx

''' this is how you get a file off the server
'oCalc.CalcFile = "Test"

'oCalc.CalcFileLocal = "c:\temp\test.csc"

''' This is how you create a script in code
With oCalc
.AddLine "FIX(""T.MARKET"", ""T.PRODUCT"",""T.SCENARIO"") ", True
.AddLine " CALC DIM(""Measures""); "
.AddLine "ENDFIX;"
End With

''' this is how you show the calc string
MsgBox oCalc.CalcString

''' set the process state check to 2 seconds
oCalc.Interval = 2000

''' this how you replace one token
oCalc.ReplaceToken "T.MARKET", "New York"

''' replace a bunch of tokens (if they exist)
Dim cTokens As Collection
Dim cReplacements As Collection

Set cTokens = New Collection
Set cReplacements = New Collection

cTokens.Add "T.PRODUCT"
cTokens.Add "T.SCENARIO"
cReplacements.Add "Cola", "T.PRODUCT"
cReplacements.Add "Budget", "T.SCENARIO"

oCalc.ReplaceTokens cTokens, cReplacements

''' see the calc string again with the rest of the tokens replaced
MsgBox oCalc.CalcString

Exit Sub

ErrorHandler:
MsgBox Err.Description
End Sub


''' get the sheetname in Essbase required format
Function GetSheetname(Optional oSheet As Worksheet)
If oSheet Is Nothing Then
Set oSheet = ActiveSheet
End If

GetSheetname = "[" & oSheet.Parent.Name & "]" & oSheet.Name
End Function

What I realized after looking at this code is how old it actually is.. I wrote this code over 10 years ago as I recognize portions of it from our ActiveOLAP for Essbase 1.0 product which shipped in 1999.

By the way, to do this same thing in our Dodeca product takes zero lines of code. We have a feature called Workbook Script which would allow you to attach one or more tokenized calc scripts to an event. For example, you could attach the calc scripts to the 'WorkbookAfterSend' event which would cause Dodeca to automatically replace the tokens in the script and run the calc whenever a user presses the Send button (and after all Send Ranges in the workbook have been successfully sent to the Essbase server). In other words, Dodeca makes running a custom Essbase calculation much easier.

One Good Reason to Stick with Supported Versions

I have been using Essbase for over 14 years now and have got it to run just about anywhere. Sometimes it took a few tweaks and sometimes, such as in getting it to run on Windows 7, is quite a bit more involved. Still, I got it working.

Recently, there was a post on the Network 54 board about a problem getting EAS 11.1.1.3 running on Windows 2000 Advanced Server:

http://www.network54.com/Forum/58296/message/1253831592/Essbase+11.1.1.3+configuration+issue

Typically, I wouldn't expect this to be a problem but it was. We did a bit of research and found that one of the dlls, jmaxl.dll, called a function in the operating system that apparently did not exist in Windows 2000. When you see this, there is basically nothing you can do.

Last week, we saw a similar issue with 11.1.1.3 while doing QA on the Olap Underground utilities. We found the Essbase VB API for 11.1.1.3 does not appear to initialize properly on Windows 2000. What that means to Olap Underground users is that those products will no longer work on Windows 2000 if they upgrade to 11.1.1.3.

Essbase 11 Formatted Values, Text and Date Measures

There was a question on the Network54 recently about formatted values in Essbase 11, so I thought I would post a little info from 'under the covers' with formatted values and how they relate to Text Measures and Dates.

First, Essbase does support formatted values in Essbase 11. You can see in this screenshot from EAS, the format string is a property of the member.

The format string is in MDX format and there seems to be no valid examples in the docs of how to actually do a format.  Thanks to the beauty of the internet and the hard work of Venkat at RittmanMead Consulting, I found an example on their blog.  In the screenshot below, you will see the result of the formatted value:

Under the covers, there is a setting at the API level that determines if the formatted values are retrieved from the database.  Uses of Smart View control this setting using the 'Format String' option in the Options dialog.

The same setting also governs whether Date and Text Measures are returned as either the formatted values or as numbers (which are what are stored in the database).   Here is a screenshot of how the Java API returns formatted values with both the formatted values returned and the the formatted values not returned:








The value circled in yellow is a formatted value; note that the number below is the numeric value (which can also be returned in the same retrieve at the API level).  The value circled in green is a Date measure; I don't think my date values are valid though.  It seems limited that the earliest date supported is January 1, 1970 (and I think I used Excel dates to fill the cell which starts at January 1, 1900.

The values circled in red and blue are Text Measures.  Text Measures get their values from Smart Lists.  Here is the EAS editor showing my simple Smart List:















OK, so it says 'Text Lists' in the dialog, but if you look at the API level, you see Smart Lists:

Now you probably know more about Formatted Values, Text and Date Measures than you ever cared to know.

Essbase API - Ephemeral Port Issues/Resources

One of the most popular posts on my blog has been the post where I showed how to open up the number of ports available to Essbase. This is necessary as the Essbase API is quite chatty and, when combined with the small number of available dynamic ports and the long default timeout on these ports in Windows, running out of ports has become an issue.  Most users see this when running many, many repetitive API calls against Essbase. 

One example is when users run the Essbase Outline Extractor against a large database.   Back in the old days when the Essbase API was designed, it probably wasn’t much of a consideration.  These days, however, with million member outlines and really fast computers, it is very easy to run out of ports.

We are currently testing some new Dodeca functionality we plan to introduce this summer that optionally performs some intensive caching of the Essbase outline.  Of course, we ran into the port issue very quickly and, in the course of looking at how the ports have changed in the new versions of Windows, found a couple of interesting links to pass along.

First, I found a nice link that explains how dynamic ports work.  Better yet, this link covers dynamic ports on non-Windows operating systems.  You can see this link at http://www.ncftp.com/ncftpd/doc/misc/ephemeral_ports.html.   This link also talks about the new, larger default dynamic port range in Windows Vista, Windows 7 and Windows Server 2008 which now feature a default range of 16,384 ports.

I also found a link to a Microsoft KnowledgeBase article that talks about the new defaults and shows ways to both display, and dynamically increase, the range of ports.  The KnowledgeBase article is located at http://support.microsoft.com/kb/929851/.   I ran the commands listed there on my Windows 7 laptop:

C:\Users\timt>netsh int ipv4 show dynamicport tcp
Protocol tcp Dynamic Port Range
---------------------------------
Start Port      : 49152
Number of Ports : 16384

I then increased the number of ports dynamically:

C:\Users\timt>netsh int ipv4 set dynamic tcp start=5000 num=60536
Ok.

C:\Users\timt>netsh int ipv4 show dynamicport tcp
Protocol tcp Dynamic Port Range
---------------------------------
Start Port      : 5000
Number of Ports : 60536

The changes appear to permanent.  I rebooted my machine and found the new configuration remained.

Kaleidoscope 2010 - Java API Session Slides / Source Posted

As promised, I have posted the slides and source code examples from my Introduction to Development with the Essbase Java API session at Kaleidoscope 2010.  There is a readme file that gives you the basics of how to run them from the command line.  If you are serious about learning the Essbase Java API, I strongly recommend you download one of the free Java IDE's available.  My favorites are:

• IntelliJ
• Oracle JDeveloper
• Eclipse

You can download the files from the Blog-Content section of the Applied OLAP website at http://www.appliedolap.com/downloads.

Essbase API for Unicode Applications

Recently we had a user report that the OlapUnderground Essbase Outline Extractor, which we now maintain and distribute, does not work with Unicode outlines. I had never tried working with a Unicode outline but I had my assistant take a look at it and sure enough, it didn't work. Upon further investigation, I found the ESB_INIT_T structure has a new parameter to handle Unicode (UTF-8) encoding:

' Essbase ESB Initialization Structure
Type ESB_INIT_T
Version As Long
MaxHandles As Integer
LocalPath As String * ESB_PATHLEN
MessageFile As String * ESB_PATHLEN
HelpFile As String * ESB_PATHLEN
ClientError As Integer
ErrorStack As Integer
usApiType As Integer
vbCallbackFuncAddress As Long
End Type

The usApiType parameter takes one of two constants as a parameter:

Global Const ESB_API_NONUNICODE = &H2
Global Const ESB_API_UTF8 = &H3

If you don't pass a value, the default value internally must be non-unicode as the ESB_API_NONUNICODE value is non-zero value but non-unicode applications work.

In the Outline Extractor, there is a catch-22 in the code. The EsbInit function gets called before the application can know whether the outline being targeted is from a Unicode application. We have added a checkbox to the user interface so a user can indicate if the outline being targeted from a Unicode application.

We still have one issue to work through before the Outline Extractor functions with Unicode. Visual Basic 6 does not write strings in UTF-8 encoding but rather uses ASCII encoding. Writing the strings produced by native VB6 to a file writes the wrong encoding and thus certain characters change. We still need to update the file writing algorithm to properly write the strings before we release it.

Most of my programming these days is in Java; encoding seems to be much easier in Java. Further, I do not believe a parameter like usApiType is necessary in the Essbase Java API.

Kaleidoscope 2010 Review

Kaleidoscope 2010 wrapped up a week ago Thursday and, not surprisingly, I haven’t had time to write about it until now.  Here is a summary of some of the highlights.

Community Service

The Community Service project this year provided labor to the Ronald H Brown Middle School in northeast DC.  The school is was named for a US Secretary of Commerce who was killed in a plane crash in 1996 in Croatia.  The ODTUG crew worked on refurbishing a playground, did landscaping and sorted books in the library.   Joe Aultman from AutoTrader, Jeff Sims from Tethsys and I worked on the basketball courts which literally had no lines or layout when we started.  Cameron Lackpour and my son Billy worked in the library and Whitney, my daughter, worked on the hopscotch and four-square areas with an EPM consultant I had never met before, Jessica Cordova.  Here is a picture (thanks to Larissa Stamey from ODTUG and Wayne Van Sluys for the picture).

For what it is worth, I placed the basketball in the center of the circle for this picture (and laid out the lines and painted much of the blue court.  I am standing on left end just in front of my daughter, Whitney.  My son, Billy, who labored for weeks on the Windows 7 installation blog, is standing just in front of the guy holding the letter 'B'.

Sunday Symposium

The Sunday EPM Symposium is one of the unique things about Kaleidoscope as it is the only forum where customers and consultants can meet the Hyperion development teams face-to-face.  There was some great exchanges of information but, of course, as Oracle was talking about futures, everything presented came with heavy disclaimers.

During the Symposium last year, the product manager from Smart View held an impromptu ‘tell me why you like the classic add-in better than Smart View’ session that lasted nearly his entire allotted time.  From his presentation this year, it is very apparent that he listened to the feedback.  The Smart View team is working hard on classic add-in parity and are they are starting to get Smart View much closer to the point where it will gain more widespread acceptance.

Keynote / General Session

The General Session had a huge announcement for us Hyperion people as one of our own, Edward Roske from interRel, is the Conference Chair for Kaleidoscope 2011 in Long Beach.  Edward has worked very hard on the previous conferences and has thus earned his way to a very tough job.  For us Hyperion people, however, having a Hyperion person as the Conference Chair shows the importance of Hyperion in the ODTUG world.   I expect that next years conference will have even more Hyperion content and will be even more of a can’t miss event.

The keynote was by Lee Rainie, Director of the Pew Research Center's Internet & American Life Project.  Lee discussed some very interesting facts gathered from their research.  One interesting trend he discussed is ‘lifelogging’ where people document everything they do on-line.  I instantly recognized my daughter follows this tendency and, if fact, we always kid her about her natural ability to take pictures of herself with her iPhone for her Facebook page.  I encourage you to read the findings at http://www.pewinternet.org/Presentations/2010/Jun/The-Future-of-the-Internet--National-Geographic.aspx. This presentation features the same content contained in the keynote

Sessions
I normally don’t get the opportunity to go to many sessions other than my own and this year was no exception.  I did get to attend the Smart View sessions on Thursday and they were all very good and thought provoking.

I gave three presentations in DC as well.  The first session was a vendor session which highlighted the features and benefits of Dodeca.  It was well attended and we gave away a bunch of Dodeca T-Shirts at the end.

The second session was a new presentation intended to educate Oracle database developers about Essbase and where it fits into the enterprise.  This was a fun topic and I think it is becoming more relevant with each passing day as Essbase gets integrated deeper into the Oracle stack.

The last session was a fill-in session on the Java API.  My friend Jason Jones was scheduled to do the session but, at the last minute, was unable to attend.  This session was the most fun for me as I really dig into Essbase at the API level and foster new understanding of how Essbase works ‘under the covers’ to even experienced Essbase people.   A number of people of have asked for the sample code from this session; I will make it available on our website soon.

Overall / The Future

This Kaleidoscope was the best one yet but I still expect it to be bigger and better next year, but there is still a lot of work to do.  Many people, including me, still long for the days of the Hyperion Solutions conference.  The Solutions conference had the advantage of being run by the vendor, so the marketing department could devote many dollars to make sure it was ‘the place to be’ for Hyperion people.

In the Oracle world, the comparable conference is Oracle Open World but, as Hyperion is a small percentage of Oracle as a whole, Open World will never have the Hyperion focus that Solutions had or that Kaleidoscope has today.  Kaleidoscope, on the other hand, is run by the Oracle Development Tools User Group which means that it is completely controlled, and funded, by the users.  That means you can make difference on the direction of this conference.  To help make Kaleidoscope even better, I plan to run for the ODTUG Board of Directors in order to provide Hyperion users a voice on the governing body.  Elections are coming up in the fall and paid members are eligible to vote.  I encourage everyone to join, participate and please vote for me for the Board of Directors!

Essbase Outline Performance Testing

I posted a blog entry last week about getting member information in the Essbase API and made a comment about how opening an Essbase outline can be slow.  We have seen anecdotal evidence over the years that outlines created in EIS/Essbase Studio seem to open more slowly which, incidentally, led us to write metadata caching into our Dodeca-Essbase service years ago.  If I remember correctly, the Java API developers told me back then that opening the outline copies the outline file to the client machine, so some of the performance problem may be due to the file size that must be passed across the network; this is the same with the C and VB APIs.  Based on these things, I decided to do some testing to try and get to the bottom of it (and perhaps help our friends at Oracle understand how the APIs are used out here 'in the wild' so they can better optimize the operations).

For my test, I wrote a Java method to open an outline and output the time it takes to complete the action.  I then wrote code to call the method 5 times for each of four cubes/databases to make sure I was getting consistent timings.  The testing was done completely on my laptop with the Java code, Essbase 11.1.1.0 and APS 11.1.1.0 all running on the same machine.  I picked these four cubes for different reasons.  The four cubes are:

• Sample.Basic.  I picked this cube as everyone has it and it can provide a comparison baseline.  The filesize for the test was 9.1 Mb.

• ASOSamp.Sample.  I picked this cube as it gave me an ASO comparison baseline with 17,711 members in 14 dimensions.  The filesize for the test was 5.2 Mb.

• Big1.Big1.  I picked this (renamed) customer cube as it is a very large BSO outline, built with build rules, with 337,272 members in 6 dimensions including 45,985 Accounts and 331,226 entities.  The filesize for the test was 64.1 Mb.

• zzz.zzz.  I picked this (renamed) customer cube as it is an average ASO cube built by Essbase Studio with 55,284 members in 11 dimensions.  The filesize for the test was 133 Mb.

Here are the results of my test summarized in a pivot table; click on the graphic to view the entire sheet.

I found the zzz.zzz outline was, by far, the slowest to open.  When compared to the ASOSamp baseline outline, it took approximately 9 times longer to open zzz.zzz despite the fact that it has only 3 times more members.  Big1.Big1, which has 6 times more members than zzz.zzz, opened in just over 50% of the time.  Based on my tests, it appears the filesize is a major factor in the performance and that the outline built with Essbase Studio is significantly larger than the outline built with build rules.

So, how does outline performance affect you?  Other than the obvious wait times in EAS, there may be some things that are not as obvious. The two most glaring examples are the inability to get all of the available information about associated attributes and the inability to get member comments.  In any case, wouldn't it be great if all member queries were equal and outlines opened really fast?

Essbase API error fix - (geeky)

Here is something we came across while working on issues reported by a few olapunderground Outline Extractor users. The symptoms they were seeing is that the ancestor members were not being returned for some of the members in large outlines. I have also made this content downloadable as a pdf document here.

Essbase error 1042006 when API tries to do too many connections from one machine in quick succession

Note: this document was originally obtained from Hyperion tech support sources and has been edited to remove identifying information for the customer, etc and to place context into the document. We also included notes from our internal testing with the olapunderground Outline Extractor tool. If you have other comments about this document which others may find helpful, please let us know and we will try to incorporate them into this document.
– Tim Tow (timtow@appliedolap.com)

Occasionally, we have heard of the Essbase API failing when it tries to do too many connections in quick succession on the Windows operating system. One of the more frequent places we have seen this occurring is in the olapunderground Outline Extractor which does literally thousands to hundreds of thousands of calls (or more) to the API to get outline information. The typical scenario seen in that product is that parent member information may be missing for members in the extract file.

The problem relates to the port numbers used on the client. Those ports are ephemeral (“briefly used”) port numbers. The Windows default for the TcpTimedWaitDelay is 240 seconds (valid values are 30-300) and for the MaxUserPort is 5000 (valid values are 5,000-65,534). The default values essentially limit the number of ephemeral ports available and the API runs out of ports to use for the connection. Adjustment of the MaxUserPort and TcpTimedWaitDelay settings in the Windows Registry may fix the error. Other alternatives to solving this issue include modifying the API code to avoid a massive number of calls to the application in a short period of time. For member manipulation calls, for example, you may try to get the outline to the client machine and then lookup the attributes of the members using a local copy of the outline.

The values of these 2 settings determine how many connections can open on the client side and how long those connections last. You can examine how many client ports are in a TIME_WAIT state by using the Netstat tool on the client computer. Run the Netstat tool with the -n flag and count the number of client sockets to your Server IP address that are in a TIME_WAIT state. Note that the MaxUserPort and TcpTimedWaitDelay settings are applicable only for a client computer that is rapidly opening and closing connections to a remote computer.

When you use the TCP/IP protocol to open a connection to a computer that is running Essbase, the underlying network library opens a TCP/IP socket to the that computer. When it opens this socket, the network library does not enable the SO_REUSEADDR TCP/IP socket option. Note that the Essbase network library specifically does not enable the SO_REUSEADDR TCP/IP socket option for security reasons. When SO_REUSEADDR is enabled, a malicious user can hijack a client port to Essbase and use the credentials that the client supplies to gain access to the computer that is running Essbase. By default, because the network library does not enable the SO_REUSEADDR socket option, every time you open and close a socket through the network library on the client side, the socket enters a TIME_WAIT state for four minutes (240 seconds using the default TcpTimedWaitDelay). If you are rapidly opening and closing connections over TCP/IP , you are rapidly opening and closing TCP/IP sockets. In other words, each connection has one TCP/IP socket. If you rapidly open and close approximately 4000 sockets in less than 240 seconds, you will reach the default maximum setting for client anonymous ports, and new socket connection attempts fail until the existing set of TIME_WAIT sockets times out.

In our testing with the olapunderground Outline Extractor, we noted the following items while debugging a reported issue and testing these registry adjustments:

• We replicated the problem on Windows XP but, despite some limited efforts, did not replicate the issue on Windows 2000; we did not try to replicate the issue on Windows 2003. Our tests were limited to a single outline submitted by an Outline Extractor user.
  
• The registry key ‘MaxUserPort’ did not appear to exist by default in the Windows XP registry. We had to create it and, in our test case, a value of 12000 solved the issue. It seems logical, however, that the processor speed of the client machine, combined with the code path of the actual API code, could have a tremendous effect on whether the number of ports becomes an issue.
  
• After changing this registry key, we needed to reboot XP for the new setting to take effect.

Note: the below was apparently provided by a Hyperion engineer; the name has been removed to maintain keep the identity anonymous. This section is quite technical and goes into the background of how the ports work.

Registered Ports, ports between 1024 and 49151, are listed by the IANA and on most systems can be used by applications or programs executed by users. Table C.2 specifies the port used by the server process as its contact port. The IANA registers uses of these ports as a convenience to the Internet community. To the extent possible, these same port assignments are used with UDP. The Registered Ports are in the numerical range of 1024-49151. The Registered Ports between 1024 and 5000 are also referred to as the Ephemeral Ports. At least on Windows, the TCP stack (OS) re-uses these ports internally on every socket connection cycling from 1024...5000 and wrapping around to 1024 again. This could lead to some interesting problems if sockets are opened and close very quickly as there is usually a time delay before that port is made available again...

Second, the number of user-accessible ephemeral ports that can be used to source outbound connections is configurable with the MaxUserPort registry entry (HKLM\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters). By default, when an application requests any socket from the system to use for an outbound call, a port between the values of 1024 and 5000 is supplied. You can use the MaxUserPort registry entry to set the value of the highest port number to be used for outbound connections. For example, setting this value to 10000 would make approximately 9000 user ports available for outbound connections. For more details, see RFC 793. See also the MaxFreeTcbs and MaxHashTableSize registry settings (HKLM\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters).

Below are also excerpts from Microsoft website and the links for your references:

• TcpTimedWaitDelay
  
  Determines the time that must elapse before TCP can release a closed connection and reuse its resources. This interval between closure and release is known as the TIME_WAIT state or 2MSL state. During this time, the connection can be reopened at much less cost to the client and server than establishing a new connection.
  
  Registry key=HKLM\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters
  Data type=REG_DWORD
  Default value=0xF0 (240 seconds = 4 minutes)
  Valid values=0x1E to 0x12C (30 to 300 seconds)

• MaxUserPort
  
  Determines the highest port number TCP can assign when an application requests an available user port from the system. Typically, ephemeral ports (those used briefly) are allocated to port numbers 1024 through 5000.
  
  Registry key=HKLM\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters
  Data type= REG_DWORD
  Default value=5000
  Valid values= 5,000 to 65,534 (port numbers)
  
  For more information, see the following link: http://www.microsoft.com/windows2000/techinfo/reskit/en-us/default.asp?url=/windows2000/techinfo/reskit/en-us/regentry/58811.asp
  

When Is A Member Not A Member?

When is a member not a member? Sometimes in the Essbase Java API, that's when. Depending on how you obtained your IEssMember object instance, it may only partially describe the member. What I mean by that is that is that some properties are not available, and are thus either blank/null or throw an Exception when you try to access them. This is why it can be frustrating to work with the Essbase API. For what it is worth, this problem is not limited to the Essbase Java API but is also a present in the C and VB APIs as well. In this blog, I will focus on the Java API but for those of you using the C and VB APIs, see if you can spot the corresponding API calls in those languages.

To start, let's look at 3 common ways to get an IEssMember object in the Essbase Java API:

• IEssCube.getMember()
• IEssMemberSelection.executeQuery()
• IEssCubeOutline.findMember()

Each of these methods have their advantages and their disadvantages. The IEssCube.getMember() method is fast but doesn't return all properties. IEssMemberSelection.queryMembers() allows you to find multiple members based on relationships or wildcard searches but, again, doesn't return all of the properties; it also will cause hanging Essbase connections if you don't properly close the IEssMemberSelection object. IEssCubeOutline.findMember() gives you basically all of the information but requires that you open the outline. Opening the outline can be terribly slow if you have a large outline or, in our experience, if the outline is built using EIS or Essbase Studio. The fact that there are multiple ways to get the member information can certainly be frustrating.

One example of frustration occurred this spring when we decided to add attribute information to the MemberTips we optionally display in a Dodeca member selector. We uncommented our server code that calls the IEssMember.getAssociatedAttributes() method and were not surprised that this method threw an Exception. We have gone to great lengths to make Dodeca performant and, for the most part, don't open the outline. As a test, however, we decided to open the outline only when the query was returning this information. As soon as we tested this on one of our larger customer outlines with 385,000 members in the Entities dimension, we knew this approach would be much too slow to put into production. Upon further testing, we found that we could get 2 of 4 attribute-related properties without opening the outline and that is what we have now implemented.

Dodeca MemberTips

This summer, I have my lucky intern working on a research project with Essbase member information with the Java API. The first thing I had him do was to write some Java code that used reflection to attempt to find which properties were available to each method of obtaining an IEssMember object. Here is a summary of that work using 11.1.1.3 run against Sample Basic using 100-10 as the target member. An 'x' in the cell indicates that the property value appears to have been returned properly; a blank indicates either an error occurred or, perhaps, that the method didn't work due to member 100-10 not being a dimension root member, etc.

MethodName	Cube	Member Selection	Outline
getAggregationLevelUsage	x	x	x
getAssociatedAttributes			x
getAttributeAssocLevel	x	x	x
getAttributeMemberDataType	x	x	x
getAttributeValue	x	x	x
getChildCount		x	x
getChildMembers			x
getConsolidationType	x	x	x
getCountChildMembers	x	x	x
getCountOfDupMemberNameInDim*
getCurrencyCategoryOrName		x	x
getCurrencyConversionType		x	x
getDescription	x	x	x
getDimensionCategory		x	x
getDimensionName	x	x	x
getDimensionNumber	x	x	x
getDimensionSolveOrder	x	x	x
getDimensionStorageCategory		x	x
getDimensionStorageType		x	x
getFirstChildMemberName	x	x	x
getFormatString	x	x
getFormula	x	x	x
getGenerationNumber	x	x	x
getHierarchyType*		x	x
getLastFormula	x	x	x
getLevelNumber	x	x	x
getLinkedAttributeAttachLevel	x	x	x
getMemberComment			x
getMemberId	x	x	x
getMemberNumber	x	x	x
getMemberType*
getName	x	x	x
getNextSiblingMemberName	x	x	x
getOriginalMemberName	x	x	x
getParentMemberName	x	x	x
getPreviousSiblingMemberName	x	x	x
getPropertyDataTypes	x	x	x
getPropertyModes	x	x	x
getPropertyNames	x	x	x
getRelatedMemberNames			x
getRoot	x	x	x
getShareOption		x	x
getSmartList*
getSolveOrder	x	x	x
getTimeBalanceOption		x	x
getTimeBalanceSkipOption		x	x
getUDAs		x	x
getUniqueName		x	x
isAttributesAssociated	x	x	x
isClientCachingEnabled	x	x	x
isDimensionRootMember	x	x	x
isExpenseMember		x	x
isFlowType	x	x	x
isIndependentDim	x	x	x
isInitialized	x	x	x
isMemberNameUniqueWithinDim*
isNameUnique		x	x
isRelationalDescendantPresent	x	x	x
isRelationalPartitionEnabled	x	x	x
isTwoPassCalculationMember		x	x

* Indicated results may be due to the member tested, 100-10, instead of the availability of the information exposed by the IEssMember.

As it appears that basically every property value is available when the outline is open, my intern is now working on prototyping a faster methodology for getting member information that I engineered. I will discuss this methodology once we have it implemented and shipping in Dodeca.

Speaking of Dodeca, we have been hard at work on Dodeca 5.0 and are now at the beta 2 milestone (which explains the sparseness of my blogging). I plan to start blogging on the numerous new features of Dodeca 5.0, along with continuing the Dodeca architecture overview I started in the fall, within the next few days.

Little Nitpick on VBA - Continued

Here is a comment on was posted on my 'nitpick' blog post by 'Jared'; I thought it would be better as a separate post:

Speaking of lost productivity...one thing that has thrown me off in VBA (with Essbase or not) is the syntax for calling functions and procedures--when to use parentheses or not. For example, every Essbase VBA programmer has used EssVRetrieve, probably in the format:

dim sts as long

'Retrieve data from the current sheet
sts=EssVRetrieve(Null, Null, 1)

The function runs and its return value is assigned to the variable sts.

Now, in some cases, I want to call functions and I don't care about a return code. Or, I want to call a procedure (which you cannot assign to value, of course). But if you do this:

EssVRetrieve(Null,Null,1)

...or this, for example:

CheckCellValue(Activesheet.Name,
5, FALSE)

...you'll get a syntax error. And since function syntax in most documentation includes the parentheses, you may have a hard time figuring out the error. Even the auto-complete in the VBA editor seems to indicate that the parentheses are OK: type a left paren after the
function/sub, and it will list the arguments for you, as if everything is just fine.

Unfortunately, it isn't. :) So the rule is this: only use parens with a function call if you are assigning a value to the function; and if it's a sub procedure, you never use parens. So you would have:

EssVRetrieve Null,Null,1 'Retrieve data, don't worry about return code

...or:

CheckCellValue Activesheet.Name, 5, FALSE 'Call "CheckCellValue" proc, passing parms

Hope this isn't too much of a derail, but it just came to mind...

Not too much of a derail at all Jared as I think it will be useful information for a number of blog readers.