Smart View hangs on Studio Drill through issue

It is amazing at least to me,  that I have two posts in less than a month.

Recently I had both a client and another consultant in our firm with the same (or similar) issues. They had implemented Essbase Studio and were using Rill through reporting. In one case, when a number of users would try to retrieve from Smart View , Smart View would hang for all users. It would last a number of minutes before it would give an error message. If the users tried to retrieve again, they would get another message about “the prior request is still running”. The other message that would appear was “Decompression failed”. A third instance, Smart View would hang for exactly 4 minutes, then free up.  If the Drill through reports were turned off, Smart View performance went back to normal.

A number of things were attempted to try to remedy the situation including:

1. Turning off compression in APS Essbase.properties file

2. Updated the registry on the APS server changing the port timeout from 4 minutes to 30 seconds

Neither of these worked and I was befuddled (This is similar to Elmer fuddled but you don’t try to shoot rabbits). Since I was not working directly on this and the client had turned to Oracle support with no real help, my colleague continued to carve away at it. His email to me speaks better of it than I could, so here is his synopsis of what he tried.

“ So we got something to work… However, the answer makes me think of the chicken dance, where chickens are given food at random intervals and start to develop a pattern of behavior from what they had previously done when the reward arrived. And in solving for this problem, that is exactly what I was doing: dancing like a chicken.

As previously stated we thought it had something to do with the ports. I knew ports were refreshed every 4 minutes, so I thought if it takes 4 minutes after SmartView freezes to come back … the answer must have to do with the ports being used and not available. We timed it … and it took 4 minutes exactly every time. Thus like the chicken, I did a little dance.

So we increased the ports, frequency of port refresh … however it did not work. We then checked the ports that were being used and found only 144 were being used when it froze. I then acquired another little move to my chicken dance. I was starting to move.

We tried the following: •Web logic – EPM Managed Servers Tuning

• APS – Essbase Property File Settings

• APS – Logging.xml

• Essbase – Compact Outline

• Essbase Config File

• Java Heap Size

• IE – Timeout Settings / Registries

Then I realized that if I stopped the application and restarted it, that it would immediately become available. No waiting 4 minutes. I was pretty sure that perhaps if I changed something in the Essbase config file I could get it to work. Now I was really dancing.

I started to look for settings that had a 4 minute time out setting… could not find any. I found a setting called Serverthreads … I decided to try it. So the next morning I asked the administrator to restart the server so we could test it. He made one additional change to increase the logging detail. We went ahead and tried it.

It worked!!! Now all we had to do was verify that this was really the fix.

We removed the serverthreads setting and restarted services, and it still worked. Wow, that was strange. What had caused it to work, since it was still working after removing the change and restarting services? We would need to retrace all of our steps.

So then we removed the detail logging. To our surprise it now failed. Wow … I was really dancing now. We tested this again and found that it only worked if we set Essbase log file to show detail.

My guess is that there is some setting that we can adjust so that we do not always have to have detail logging. However, I have been dancing so hard that I think it is time to pass this dance on to Oracle support.

Like random droppings of a positive stimulus I had danced long into the night finding the right patterns to get my next little dropping of reinforcement.”

As for the solution …

What worked was:

In Provider Services :

D:\Applications\Oracle\Middleware\user_projects\domains\EPMSystem\config\fmwconfig\servers\AnalyticProviderServices0\Logging.xml

Original Entries :

  <logger name='' level='WARNING:1'>

  <logger name='oracle.EPMOHPS' level='WARNING:1' useParentHandlers='false'>

Modified Entries :

  <logger name='' level='TRACE:1'>

  <logger name='oracle.EPMOHPS' level='TRACE:1' useParentHandlers='false'>

“Why changing the logging level should have any impact … that I do not know!!  I wish I was smart enough to answer that.

We only stumbled upon this by dumb luck when the Oracle asked us to change the xml log so that we could send them the more informative log.  Like I said earlier … we were attempting things that made sense only to find that the thing that made no sense worked.”

So while I did not find the solution, one was figured out, If you run into this hopefully, you can benefit from our research.

 

On another un-related note, the second part of the Podcast Edward and I did with Kevin and Stewart is available. take a look, it was fun to do and I think informative. Here it is :