Friday, November 20, 2020

Hey Why Does My New SmartView Keep Timing Out?

Oracle SmartView for Office still needs the Windows Registry keys it has been wanting since... the very beginning really.  It does not matter if we're talking Hyperion / Oracle EPM on-premises or Oracle EPM Cloud.

If your user has upgraded to a new Windows 10 machine and is complaining about SmartView timeout issues, the culprit is almost always the Windows Registry.  This is what a non-tuned user's workstation looks like, from my point of view:

The registry key names you see in this example screenshot are shorthand for the full registry key name.

These registry keys and suggested values were taken directly out of a series of Oracle Knowledge Base articles that have been available for a very long time.

Here we're looking at two areas of focus: network and MS Office animations.

Network of course is the true silver bullet here.  Get the keys applied, reboot the user's workstation, and the problem is nearly always solved.

The animation keys help with weird flickering issues occasionally reported by SmartView users.  I haven't heard any of these complaints recently, as the flickering problem is very Office version-specific.  I've written about this issue here before.  If your user has the on-premises version of Office 365, they are likely OK and wouldn't have seen the issue.  It still wouldn't hurt to have the keys, though.

So here's the script that generates the above screenshot.  Within this very simple script -- nothing proprietary here! -- you can see the full key names and values expected.

 

echo ------------------------
echo KeepAliveTimeout should be set to dword:2bf20
echo Here is the current value:
reg query "HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Internet Settings" /v KeepAliveTimeout

echo ------------------------
echo ServerInfoTimeout should be set to dword:2bf20
echo Here is the current value:
reg query "HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Internet Settings" /v ServerInfoTimeout

echo ------------------------
echo ReceiveTimeout should be set to dword:75300
echo Here is the current value:
reg query "HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Internet Settings" /v ReceiveTimeout

echo ------------------------
echo DisableWindowTransitionsOnAddinTaskPanes should be set to dword:1
echo Here is the current value:
reg query HKEY_CURRENT_USER\Software\Microsoft\Office\15.0\Common\Toolbars /v DisableWindowTransitionsOnAddinTaskPanes

echo ------------------------
echo DisableAnimations should be set to dword:1
echo Here is the current value:
reg query HKEY_CURRENT_USER\Software\Microsoft\Office\15.0\Common\Graphics /v DisableAnimations

echo All done checking!
pause

One final point...  #MISSING suppression is handled on the end-user's workstation, not on the Essbase server (on-premises or Cloud - it does not matter).  If user requests a massive grid in either ad-hoc mode or by opening a Planning form in SmartView, the Essbase server is going to process it.  Essbase ships the complete grid back to SmartView and lets the chips fall where they may.  (The communication flow is more complex than this - I'm just trying to keep this post simple)

I mention this because I've seen some users attempt to "data mine" Essbase.  The mindset here is "give me everything and I'll sift through it in a linked sheet or in MS Access".  In my view, Essbase & Planning were never intended to be used this way.

When a user requests a grid containing a half million or more possible cells, it does not matter what you do to either the user's Windows Registry, the essbase.cfg file (only if you're on-premises), or tweaks to the cube's dense/sparse settings.  SmartView will remain locked up until the entire grid is fetched and processed.  I've noticed grid retrieval performance starts to get ugly when we're looking at 300,000 possible cells and higher.

Where on-premises is concerned, massive grid retrievals can lead to "OutOfMemoryException" errors in either Planning or Analytic Provider Services, depending upon the type of SmartView connection used.   You can bet these errors also happen in Oracle EPM Cloud, since the underlying technology is the same, but the Oracle WebLogic logs aren't exposed to us in the Cloud.  Bumping up the on-premises "-Xmx" registry keys for Planning & APS will provide some relief (e.g. changing from the 4GB default to 8GB), but all we're really doing here is throwing more RAM at the problem; it won't speed anything up.

The underlying issue to address, unfortunately, is end-user behavior.  Usually it is only 1-3 people within the organization who are trying to datamine the entire cube, and you'll typically find them in FP&A.  If you're in Oracle EPM Cloud, use EPMAutomate to grab the daily audit logs and that should help you identify who is pulling down large grids.  On-premises has different ways EPM Detectives such as yourself can probe and find who is bringing the system to its knees. 

So apply the keys recommended above, reboot the workstation, and keep retrieval slice sizes within reason.