Smart View: Smart List is not Visible when Alias Changed to Default – PBCS


Administrator developed a Smart View ad-hoc template to load the data. When Alias is set as None users are able to view smart list associated with account and also able to submit data (for BegBalance and Jan to Dec) however when the user changed alias to default, smart lists associated with account not showing the list.

This issue is identified as Bug 26476354 and fixed in PBCS version 17.08 as per DEV, but fix will not work with out implementing below steps manually:

1. A child of a root member m1 has a duplicate alias. For example:
assumptions alias is assigned to m1 and also to some other member under the tree.

2. In that case Planning should generate correct qualified name, that is:
[root_name].[m1] in case root member does not have an alias.
Or
[root_alias].[assumptions] in case root member has an alias.
Other combinations [root_name].[assumptions] or [root_alias].[ m1]  are invalid.

When the user has a saved xls worksheets with incorrect qualified names, they need to  manually update it to fix these names, using Member Selection as  without manual updates Planning server will not be able to resolve these names and will handle incorrectly qualified names as comments.

Thanks,

~KKT~

Advertisements

SmartView Issue – COM Addin Not Getting Added To Excel By Default


The Smart View Addin does not show up automatically when opening Excel.  However, once Excel is started, it can be loaded by manually enabling the addin from Excel Options.

Under Add-ins in Excel  the current load behavior of the particular add-in can be seen. The load behavior of the add-in should be set to “Load at Startup”.
It was showing as Unloaded.

To enable the Smart View addin to display when opening Excel:

1.  Open your registry editor. To do this, open your Windows Start menu and type “regedit”.

2.  Go to HKEY_CURRENT_USER\Software\Microsoft\Office\Excel\Addins\Hyperion.CommonAddin. Ensure the LoadBehavior is set to 3.

3.  If the LoadBehavior is anything other than “3” then follow these steps:

a.  Close Excel. Make sure the “excel.exe” process is not running in the Windows Task Manager.

b.  Open the registry editor again. Double-click the registry key to edit it.

c.  Change the Load Behavior to “3.” Close the registry editor.

d.  Re-open the registry editor to ensure “3” is still there.

4.  Open Excel. The Smart View add-in should now load automatically at startup.

IMP Note – A registry backup is required before making changes to the registry.

Thanks,

~KKT~

Saving Planning Webform / Smartview Results in Error : “There was an error during the save process”


On : 11.1.2.4.006 version, Product Usage

When attempting to submit data in Hyperion Planning web form ,
the following error occurs.

ERROR
———————–
There was an error during the save process

This is due to Essbase Exceeded block limit.

Was identified from the Essbase log with the below error Message:

Error(1024017)
Request [SpreadsheetOperation] from user [admin@Native Directory] was terminated since it exceeded the block limit

Remove the setting QRYGOVEXECBLK from Essbase.cfg file, restart service for Essbase to fix the issue.

Thanks,

~KKT~

How to Enable Smart View Logging?


Smartview logs we generally require for our audit purpose and some time to raise an issue with Oracle.

Follow below steps.

  1. Office 2003: Point to Hyperion -> Options
    Office 2007 and above: Choose the Options button in the SmartView ribbon
  2. SmartView versions prior to 11.1.2: Select the Display tab
    SmartView 11.1.2 and above: Select Advanced
  3. In the ‘Log Messages’ or ‘Logging’ section, select ‘Route Messages to File’
  4. Enter a valid filename, e.g. C:\SmartViewLog.txt
  5. Unless directed otherwise by Support, select Information. In SmartView 11.1.2 and above, this is in the Log Message Display drop-down menu.
  6. Choose OK.

Now reproduce the problem and any error messages. Close Excel and attach the file (e.g. C:\SmartViewLog.txt) to your SR. Afterwards, either disable logging or change the logging level to ‘Error’ (see step 5 above) to ensure good performance.

Smartview Essbase client support with SSL


Does Smartview Essbase client support SSL?

Yes it does.

If you want to use an Essbase client with SSL, then you can use the Smartview client which supports SSL.

You need to configure the SmartView client to connect via HTTPs to the Analytic Provider Services (APS).

APS will access Essbase on secure port.

Thanks,

~KKT~

Troubleshoot SmartView Performance Issues


Symptoms

Typically, the problem will arise only with large spreadsheets containing large numbers of SmartView cells. It may only occur when Refresh All is executed but not when worksheets are refreshed individually, or it may only occur at times of high load such as month end. It is more likely to arise with SmartView functions than Adhoc, because Adhocs are more efficient than functions. Such symptoms as these would point to a performance issue rather than a bug. However, if IIS logs show Deadlock detected, this is probably sign of a bug, where SmartView crashes IIS on the web server. Symptoms here would be SmartView becoming unresponsive and new users being unable to log on for a while. Restarting the web server resolves. This situation is outside the scope of this document.

Support

Once an actual bug has been eliminated, the issue must be treated as a performance problem. This lies outside the remit of your contract with Oracle Product Support. You may decide to contact a consultant or to try to tune the system yourself. Please understand that Support can only give you general and generic guidance in this. We will not read logs or make recommendations.

Background

SmartView communicates over HTTP using .NET. This just means that requests and responses are sent in a standard XML format that is tucked in the HTTP headers. The mechanism is the same as when your internet browser requests a file or submits a form (simplification). A standard Microsoft component that is part of Internet Explorer is used for this.

There are three components (four if you include the network) in a SmartView refresh:

  • the client
  • the web server
  • the HFM application server

It is easiest to troubleshoot when they are on separate machines. Running SmartView on the server to see whether it works better is a useful test to see what effect the network is having, but doesn’t show any other bottlenecks. If you run Task Manager on these 3 machines showing the Performance tab, you will see initial activity on the client as Excel clears the cells and the request is converted into XML. Then you see the web server gets busy as it receives and parses the request. Then the application server is busy as it sends the data, then the web server is busy as it compiles the results into XML again, and finally the client parses the XML, pushes the data into cells and runs an Excel calculation. This last stage is the longest of all. Overall, it is typical to see the client doing 90-95% of the work, so client performance is a very important factor. Look out for Excel’s memory usage increasing and RAM becoming in short supply. Often, user dissatisfaction with refresh times can be alleviated by installing more RAM or providing a faster workstation.

The situation we are dealing with here, however, is when the chain of events described above is interrupted. In this case, you will see the client or the web server wait in vain for a response from the next machine in the chain. The case of invalid XML is similar, but the response is truncated so that the XML is not well-formed when it arrives. XML consists of matching pairs of tags and is not well-formed when a closing tag is missing. This can be caused by certain network settings such as packet size (see later). The end result in either case is the same: as Excel cleared the cells before sending the request, cells are left blank or with zeroes.

The task now is to determine which particular component is failing.

Client

Check the HTTP timeout in the Windows registry. Example:

[HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Internet Settings]\
“ReceiveTimeout”=dword:00dbba00
“KeepAliveTimeout”=dword:002BF20
“ServerInfoTimeout”=dword:002BF20

The default time-out limit is five minutes for versions 4.0 and 4.01 and is 60 minutes for versions 5.x and 6. Also, the default time-out limit is 30 seconds for Windows Internet Explorer 7 and 8. To change the settings, add the above keys. The above values are shown in hexadecimal, not decimal and correspond to 180,000 milliseconds (3 minutes) and 240 minutes for the receive timeout.

For more information about client timeout settings see following articles:
How to change the default keep-alive time-out value in Internet Explorer – http://support.microsoft.com/kb/813827
Internet Explorer error “connection timed out” when server does not respond – http://support.microsoft.com/kb/181050

You must determine an adequate timeout limit for your environment and largest workbook by trial and error or experience. Once these changes are applied, my recommendation is to reboot the client machine.

Web server

Look for errors in logs showing that the web app didn’t respond/disconnected or timed out waiting for another application (i.e. the HFM application server). Eliminate load balancers etc. during testing in order to isolate the problem and to ensure that the user always goes to the same web server. Otherwise, logs can be hard to interpret. See also the IIS Tuning Guide for IIS settings. We cannot recommend particular settings, as each client must establish the optimal settings for their own environment after performance testing. If necessary, engage the services of a suitably qualified consultant.

Application Server

HFM application server performance is very dependent on whether the data is already in RAM or not. If  the first refresh fails but the second one succeeds, this is probably a sign that the memory cache needed to be populated, which had been done during the first refresh. HFM loads whole subcubes (Scenario, Year, Value and Entity) into cache, so requesting large number of accounts in the same subcube quickly returns results, but if large number of entities is requested the results are returned slowly as the server runs out of available RAM and starts unloading subcubes to make room for more. The HFM event log (HsvEventLog.log) shows this happening, but don’t forget that the messages you see are a normal consequence of the way HFM works and are not a problem unless you see a hugely elevated number of them during the period the refresh occurs. Another sign would be page file thrashing. If there are several servers in a cluster, use sticky sessions in HFM to make sure tests always go the same server, or hit one server directly rather than using the cluster.

Solutions here involve installing more RAM on the server and changing the worksheet design to restrict the number of subcubes accessed. A separate consolidation server, so that reads are not competing with intensive consolidations and calculations, would be a good thing.

Network

To find out if packets get fragmented, use
         ping -l size -f

To send a big packet with the ‘don’t fragment’  flag. E.g.
        ping -l 1024 -f oracle.com

If you see a reply like Reply from 141.146.8.66: bytes=1024 time=200ms TTL=245 then the size you specified is below the packet limit of the network. But if you use, for example, 2000 in place of 1024, and you get Packet needs to be fragmented but DF set, then you know you have exceeded the limit. Combine this investigation with a Fiddler trace (https://www.fiddler2.com/fiddler2/) to see what size data messages are being sent and received.

Troubleshooting Guidance

  1. During troubleshooting process, fix your environment so that it is predictable. Register a single HFM application server and connect to that. Use a single web server and don’t go through Apache or any other redirector. Make sure we know which machine to go to for logs. This would be a temporary measure and it doesn’t stop the other users using the full, load balanced system so long as it doesn’t involve your server.
  2. As far as possible keep other users from using it during testing, unless you are specifically testing for performance under load. We want to see what activity is due to SmartView and not random other jobs.
  3. Use PerfMon on all three machines: client, web server and application server.
  4. Clear Logs before testing and note the times of each test so the logs can be correlated.
  5. Log CPU activity, memory usage, network throughput, page faults, thread count etc. I’m no expert on this so get advice from someone who is.
  6. In addition to the performance logs, from the IIS Server get:
    a. HTTP.SYS Error Log – %windir%\System32\LogFiles\HTTPERR (Default location; configurable)
    b. IIS Website Log – %windir%\System32\LogFiles\W3SVC# (Default location; configurable)
    c. Event Log (both System and Application) in EVT or EVTX and TXT (tab separated) formats
  7. From HFM Application server get:
    a. HsvEventLog.log (in <HFM install>\Server working Folder)
    b. Event Log (both System and Application) in EVT and TXT (tab separated) formats
  8. Be aware of all timeout settings in your environment (i.e. IIS & the network) and time your tests. This can show whether it is an ASP timeout, script timeout, client side timeout or whatever. Note that the Web Session Timeout setting for SmartView in HFM’s Server and Web Configuration is a separate issue from IIS timeouts. Web Session Timeout controls the length of idle time before SmartView makes the user log in again. Default 20 mins.
  9. Run Fiddler traces to directly observe the XML and check for HTTP failures.

SmartView Refresh All WorkSheets Issue In 11.1.2.2.310


Issue in Smart View 11.1.2.2.310 version when using the Refresh all Worksheets feature.

Open a workbook with multiple Smart View retrieves. Click the Refresh all Worksheets button.

The keyboard freezes within Excel.
Trying to reposition the active cell using the keyboard will fail. This includes any use of the arrow keys, Page Up, Page Down, Home, End, etc.
Pressing [Esc] does not fix this issue.  One can use the mouse to change the active cell, however after doing so, your keyboard still does not function properly.
After clicking the arrow keys a couple of times, a small popup will appear in the upper left-hand corner.

This issue has been fixed in 11.1.2.5.200 Patch 18077081

 

Download Information:
This patch is available via My Oracle Support.

1. Log into the My Oracle Support portal.
2. Choose the Patches & Updates tab.
3. Select Patch Name or Number from the drop-down box.
4. Perform a search for Patch:18077081 for your corresponding OS platform.
5. The patch file includes both installers and the readme.

Thanks,

~KKT~

Smartview Tab Disappears From Excel


Hi,

I came across this issue many times and address many times to my users how to fix this issue. Just thought of documenting for everyone.

Goal – How to enable the Smart View tab if it disappears from Excel

To fix:

  1. Open Excel as a System Administrator.
  2. Go to Excel Options ->Select Add-ins
  3. Under the Drop Down selection for “Manage” ->Select COM-ADDINS and Click on Go
  4. Check if the “Load Behavior” of the Oracle Smart view for Office is “Load at Startup”.
  5. If the status is unloaded, remove the existing COM Addin andrRe-add the HsAddin.dll from <Drive>:\Oracle\SmartView\bin directory.

Step by step Video for reference. Smartview Excel Fix

Thanks,

~KKT~

Smart View Error “Provider Is not Shared Services Provider” When Using Shared or Private Connections


When using shared or private connections in SmartView the following error message is displayed:

Provider is not Shared Services Provider.

There are two possible causes for this issue:

  • Internet explorer timeout
  • Internet explorer proxy settings where the server needs to be added to the exception list

To prevent timeout issues, add modify Windows Registry on the client workstation where Smart View is installed:

  1. Open Windows Registry editor.
  2. Navigate to
    [HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Internet Settings]
  3. Add the following values:
    “ReceiveTimeout”=dword:001b7740
    “KeepAliveTimeout”=dword:001b7740
    “ServerInfoTimeout”=dword:001b7740

If issue is related to proxy settings do the following:

  1. Open Internet Explorer
  2. Open menu Tools > Internet Options > Connections > LAN Settings > Proxy server > Advanced
  3. Add the server to the proxy exception list and save changes.
  4. Close IE and reopen

Thanks,

~KKT~

Smartview Concept- Point of View, Adhoc Grids and Functions


There is often confusion about what Point of View Manager in Smartview is for, so let me explain a few points.

  1. Firstly, POV Manager sets the connection’s Background Point of View. This POV is used primarily for SmartView functions.
    An HsGetValue function for HFM, for example, when created using Function Builder, specifies all 12 dimensions. However, it is possible — and for performance reasons, often preferable — to omit some dimensions. The function will assume the member set in the Background POV for omitted dimensions.
  2. Saving a POV by dragging it to a worksheet in POV Manager does not set that Point of View for the sheet — remember, Points of View are related to the Connection, not a particular worksheet.
    When you save a POV in this way, a copy of it is saved in the worksheet. It will be saved in the Excel file when you save the workbook and it can be retrieved in future sessions by dragging it from the sheet back to the Active POV within POV Manager.
  3. Once you have created an Adhoc Query or Free Form Query, the grid has its own Point of View, represented on the POV Toolbar. For Essbase, Planning, and HFM prior to 11.1.2.1, the starting point for this Point of View is the Background Point of View. (For HFM the starting point is the user
  4.  which is retrieved from the server.) Once the Adhoc has been created, the grid is independent of the Background POV. You must make any future changes to it via the POV Toolbar.

1. To create a new Adhoc Query with specific Point of View (Essbase, Planning)
Firstly set the Point of View in POV Manager (in Active). No need to drag or save the Point of View.
Click Hyperion -> Refresh to create the new Adhoc grid. The starting Point of View will match the one you set in POV Manager.

2. To change the Point of View of an existing Adhoc Query
Use the POV Toolbar relating to the grid.

3. To save a Point of View for re-use
1. Set up the desired Point of View in POV Manager
2. Drag the Active POV into an open worksheet in the left-hand pane
3. The Point of View will now be saved along with the workbook.
To use it in the future, after opening the workbook and connecting, drag it from the worksheet to the Active folder in the left-hand pane of POV Manager.

4. To set a default Point of View (to avoid having to set the POV every time you start SmartView)

Note: from 11.1.2.1 it is not longer necessary to do this, as the user POV is stored on the server and retrieved upon connection.

First save the Point of View into Personal.xls as described in 3 above.
On opening Excel and connecting, copy and paste this Point of View from Personal.xls to Active in POV Manager.

If you do not see Personal.xls listed in POV Manager:
1. Point to Tools -> Macro -> Record New Macro…
2. In the Record Macro dialog box, select ‘Personal Macro Workbook’ in the drop-down menu
3. Choose OK
4. Choose Tools -> Macro -> Stop Recording.
Personal.xls will now be listed in POV Manager.

When you close Excel, you may be prompted to save the Personal Macro workbook. Choose Yes to ensure your Default Point of View is retained.

 

Do Revert in case of any queries.