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 :

 

Pimping my Ride

The other day, Edward Roske and I participated in a podcast hosted by Kevin McGinley and Stewart Bryson called Real Time BI. We spoke with them on integration of Essbase and OBIEE. It was a really great time. If you want to see what I really look like or now monotone I really am or the witless  banter between Edward and me, take a look at part one  on YouTube (http://youtu.be/wwTIml_b4mE) or iTunes (http://bit.ly/QhwuSq)!

Part 2 will be out soon. In addition to being informational, it is also somewhat entertaining.

How not to reverse engineer an Essbase cube to allow drill through

It has been awhile since I’ve posted. No apologies but I was busy getting ready for KScop13 (which was a great conference. Sorry if you missed it). Then I was a bit burned out and needed time to recover.  In this post, I’m going to take a little from my KScope presentation of Advanced Studio Tips and Tricks to hopefully help you. This will deal with reverse engineering a cube to get drill through functionality.

First why would you want to reverse engineer a cube?

• Cube already exists
• Want to add drill through capabilities
• Want to start migrating to Studio
• Want to have hierarchies available for building other cubes

So you can learn from my mistakes, I’ll discuss the wrong way to try to reverse engineer a cube.  I had a client that wanted to do this. I had recommended extracting the hierarchies from their existing cube and loading it into dimension tables. They wanted to try a different route.  Their target table had all of the level zero dimension members in the fact table. They wanted to see if we could just build the level zero members (since they would only drill through at level zero) from the fact table. The actual data load would be done outside of Studio, so there would be no change in that.

The first thing I tried was to create user defined tables that made fake dimensions tables. I used dummy parent names so it looked like a parent/child build.  I created the hierarchies and the Essbase model. In the Model properties, I told it to ignore shared members so they would not build the new relationship.

I encountered my first problem. I could not create the custom SQL in the drill through report. I fixed this problem by making the fake dimension hierarchies recursive.

Then I ran into my second problem I had a dimension (Scenario) that I created as a manually defined hierarchy. The deploy would not refresh the Essbase cube. So thinking swiftly,  I created it as a user defined table and joined to that “table”. That solved that issue (Or at least I thought it did)

This change  did allow me to deploy the cube, but I could not get the drill through intersections to work in my existing test cube. If I built a new cube with the dummy intersections, the drill through report would work. I figured out it was because of the "ignore share members” It was not actually creating the intersections in the cube it just ignored what I was trying to build.

Bummer. What this meant was I could not build the cube from just level zero members. I would need to have at least level 0 and level 1 members to build the dimension tables.

I reminded the client of my original suggestion on how to reverse engineer. They decided to take my suggestion. Basically we extract all the dimensions using the outline extractor from Applied Olap (you could also do it with ODI or other ways) then load the dimensions into the same database as the fact table exists in.

Once they are there, we can do our joins and normal Essbase Studio steps to update the cube and drill through reports.

This is going to be a busy rest of the year from me. I’ve already spoken at a Hyperion Solutions roadshow with Oracle (losing my voice before the final of my 4 presentations). I’m scheduled to speak at the ODTUG sponsored Sunday Symposium at Oracle Open World, Attend Oracle Ace Director meetings in Redwood city, and speak at 4-5 other events in the remainder of the year. This is in addition to trying to do real work. While I love to share information with you all,  my first love is being a technical resource and solving problems in the Essbase/Hyperion world. I do as much of that as I can. 

I’ll try to be more frequent in my blog posts. I think I might share more from my Studio tips and tricks next or perhaps some things from my Thinking outside the box optimizations session from Kscope. That session almost allowed me to beat out Edward Roske for best conference speaker. I bare no hard feelings toward Edward, He deserved to win the award, but I gave him a run for the money (ok wooden kaleidoscope).  Funny both our presentations were on optimization.

Till next time