Oracle Developer Tools User Group Call for Papers (Act fast!)

Sorry I haven't had time to post anything for the last couple of weeks. I plan to start a series on how to install the Hyperion 9.3.1 Suite when I can get enough time to do the proper job of it. I have been working 'half-days' (12 hours, get it?), however, for the past couple of months and haven't had much time for anything. I did, however, force myself to go fly my airplane on Saturday. You know it hasn't been flying enough when, for the second time in a row, the battery was low and the plane needed to get a jump start. It had been a couple of weeks since it had been flown and I thought I had better make sure the battery was charged up before I fly over to Atlanta on Wednesday (tentatively) to go to the Hyperion User Group meeting. I bet you didn't know you could jump-start an airplane, did you?

I did, however, get an interersting email over the weekend from some of the officers in the the Oracle Developer Tools User Group ("ODTUG"). They have decided to pursue a Hyperion technology track at the annual Kaleidoscope 2008 conference scheduled for New Orleans June 15 to June 19. Hyperion-oriented people may want to attend the conference to see the latest on both Essbase technologies and OBIEE. This conference appears as though it will have content that has been been missing, for the most part, from the Hyperion 'Solutions' conferences over the past few years where there seemed to be slanted a bit more towards marketing than technical. Information for the Kaleidoscope conference can be found at

http://www.odtugkaleidoscope.com/


Although submissions for the ODTUG conference have been closed for a while, they just this past weekend reopened submissions for Hyperion related content. Submissions are scheduled to end February 16th. You can enter your submissions at

http://www.prairiesystemsgroup.com/odtug_hyperion_abstracts.html


I hope that many Hyperion experts are willing to share their knowledge in New Orleans.

Why you can't use SQL to query Essbase

Over the years, I have seen many people ask why you can't use SQL to query Essbase and I have given it a bunch of thought. I saw a similar post today on OTN and decided to finally put it into writing.

Keep in mind that, despite the fact that I have been doing Essbase full time for the last 12 years or so, I did a bunch of SQL stuff before that. In fact, I spent a bunch of time back when I was an 'accountant' working as a technical lead on an Oracle Financials implementation. So here goes with the explanation.

Essbase is not accessible via SQL as the structure of a cube is quite different than that of a table.

If you think about what SQL does, it retrieves a dynamically determined set of rows each of which are specified by a defined set of columns. Conversely, when retrieving data from a cube (in the simplest case), you are retrieving a dynamically determined set of rows and a dynamically determined set of columns filtered by a 'slice' which are determined by "page fields" or headers (which is very comparable, conceptually to a SQL where clause). In the more complex case, you are retrieving sets of sets of dynamically determined rows defined by sets of sets of dynamically determined columns.

Another way of visualizing it would be to think about how you would structure SQL to select a range of *cells* in an series of spreadsheets, say range "C64:M400". That isn't too hard when you think about a single worksheet, but how about when there are 78 worksheets in your workbook that you need the same cells from? What if there were separate subdirectories on your server with the same workbook for all 563 sales centers in your company and you needed the same cells from all of those? Well, at this point we are essentially talking about querying 4 dimensions.. What if your company tracked 12 dimension? What would the SQL look like to get those results?

As you can see, the structure of multidimensional data doesn't lend itself very well to SQL. That being said, there is a 'SQL-like' language that was built to do multidimensional queries, "MDX". Here is a sample MDX query from the 9.3.1 APS java sample code:

SELECT
Union(CrossJoin({[Sales], [Profit]}, {[Actual], [Budget]}),
Union(CrossJoin([Total Expenses].Children, {[Actual]}),
{([Opening Inventory], [Variance]), ([Additions], [Variance %])}))
ON COLUMNS,
CrossJoin([200].Children, {[East], [West]} ) DIMENSION PROPERTIES [Product].Ounces
ON ROWS
FROM Sample.Basic
WHERE {[Jan]}";

MDX query functionality is exposed in the Hyperion/Oracle products primarily via the C and Java APIs and via web services using XML for Analysis ("XMLA"). I did not find any MDX related functionality in the VB API declarations file; that is probably due to the large number of structures and API calls that would have to be defined/supported combined with the fact that VB has a sunset that is fast approaching.

New Hyperion Blog Link

I have just put a new Hyperion blog link in my 'Cool Links'. It is the 'Look Smarter' blog written by my friend Edward Roske at interRel Consulting in Dallas. Edward has been working with Essbase since 'the world was flat', so to speak, and is also the author of the only book that has ever been published about Essbase. He also has the coolest office I have ever been in at 'The Rangers Ballpark in Arlington'; his office overlooks the outfield and, on the day I was there, the Rangers were playing the Detroit Tigers.

Edward's blog has some great technical information, particularly on the future of Hyperion products, and is entertaining as well. The blog is published at http://looksmarter.blogspot.com/

Advanced Security Manager

Oracle recently took down the HDN website and not all of the content, including third party downloads, is currently available on the OTN site. Of course, someone posted on OTN their need to get ahold of the OlapUnderground Advanced Security Manager. In response, I got in contact with the OlapUnderground team and they have sent me the Advanced Security Manager (along with the source code).. We are considering taking over development and management on the ASM but, in the meantime, I have made the Advanced Security Manager files available on our website:

http://www.appliedolap.com/olapunderground/downloads/temp/secmon1.zip

I will leave this download, as received from the OlapUnderground team, on our website until it is determined if it will be available on OTN or until another home is found for it. If we decide to take on management of the ASM development, we will create a separate page for it on our website.

I guess the question is this. How many people actually still use the Advanced Security Manager? Should we spend any cycles working it at all? Note: I have promised not to release the source code unless it is cleaned up a bit. Thoughts anyone?

Hyperion Online Docs now integrated into the Oracle site

In responding to a question on the Oracle Technical Network (OTN) this morning, I found the Hyperion online documentation has now been integrated into the Oracle website. It also appears to be very well organized. In fact, I am going to bookmark this site as soon as I finish this post.

The docs are online at http://download.oracle.com/docs/cd/E10530_01/doc/index.htm.

How to configure Tomcat, as delivered with Analytic Provider Services, to capture stdout

By default, the Analytic Provider Services (“APS”) service does not log standard out. Unfortunately, Java developers frequently use standard out to log information (particularly error information). If this information is critical to you, the logs need to be configured.

After doing a bit of digging, I was able to determine that logging is enabled by configuration parameters in the Windows registry. Specifically, the following parameters need to be added to the registry to configure the service:

• StdError


• StdOutput

The following screenshot shows how I configured these two settings to enable the standard out logging in APS 9.3.0. Remember that you must restart the service for these settings to take effect.


(Click image to enlarge)

When I installed System 9.3.1, standard out logging again stopped working. Upon further investigation, I found the names and location of the settings had changed. On the plus side, the settings actually did already exist but were set to ‘nul’ by default.

I modified the settings and added a path to the SysErrFile and SysOutFile parameters as shown below.

(Click image to enlarge)

If you find any other strange things with APS service logging, please email me at timtow@appliedolap.com and I will add the information to this document.

I have made this content downloadable as a pdf document here.

Essbase API error fix - (geeky)

Edit: If you are running Windows 2008, Windows 7, Windows Vista (maybe) and later operating systems, see this link for updated information: http://timtows-hyperion-blog.blogspot.com/2011/05/essbase-api-ephemeral-port.html.

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