OBIEE 12c: How To Change The Essbase JAgent Port

This document provides the steps to change the Essbase JAgent port in an OBIEE 12c environment

Unfortunately The System Administration Guide Changing Essbase Ports in Oracle Business Intelligence incorrectly lists OBIEE 11g steps.
Unpublished documentation bug 26801849 has been filed to correct the steps.

This port setting is defined in the essbase.cfg using the parameter AGENTPORT
The default value for this AGENTPORT is 9799, to change it please follow the steps below:

  1. Backup and edit the file on PROD environment:


  2. Change the line:

    AGENTPORT 9799


    AGENTPORT 9779 (i.e. – any free port of your choice)

  3. Save the file.
  4. Restart the OBIEE servers ( then




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.



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

On : version, Product Usage

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

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:

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.



Understanding Essbase Implied Shares And Their Effect On Planning Data Form Design


As in my last post we covered about Implied Shares concept and basics.

Now in this post let’s explain what Implied Shares are, why this Essbase feature exists and is enabled by default, and what the consequences are for Planning data form design.

When are Implied Shares Created?

An Implied Share is a relationship between a parent member and its child member in the Application outline. Implied Shares are an Essbase feature that will also affect Planning users since Planning applications are built on top of Essbase.

The purpose of Implied Shares is to reduce the block size in Essbase by avoiding the unnecessary duplication of data. Implied Sharing is enabled by default.

There are three scenarios where an Implied Share relationship will be created between a parent and its child.

  1. The parent has only one child
  2. The parent has only one child that consolidates to the parent (the other children of that parent being non-aggregating)
  3. The parent is of data type “Label Only”, in which case the parent will inherit the value of the first child, no matter what the aggregation settings of the children


Assume all members are set to data type “Store”

Scenario #1

Parent A
Child 1 (+)

Scenario #2

       Parent A
Child 1 (+)
Child 2 (~)
Child 3 (~)

In both of these scenarios, Essbase will automatically create an Implied Share relationship between Parent A and Child 1. This is done because the values of the parent and child in the above scenarios are always going to be the same, so storing two separate values in Essbase only increases block size unnecessarily.

When an implied share relationship is created, each implied member will assume the other member value. If a data value is entered for the parent, the child will assume the same value after a save (Planning Data Form) or after a lock, send and refresh (Excel add-in for Essbase).

If a data value is entered for the child, the parent will usually assume the same value after a save (Planning Data Form) or after a lock, send and refresh (Excel add-in for Essbase). There is one exception to this, explained below.

Unintended Effects on Planning Data Forms

Usually, the parent and child can be treated interchangeably. For example, if a Calculation Script or Essbase Load Rule is used to populate a parent or child in an Implied Share relationship, the other member will always assume the value of the member populated by the Calculation Script or Load Rule. The last value calculated or imported will “win”, and become the value for both members. It is possible to refer to either the parent or the child as a variable in a Calculation Script and the result will be the same.

However, this is one situation in which the behaviour described above can interact with the particular design of a web form to produce unexpected results. If both the parent and child members are displayed on the same Planning Data Form, data entered for the child may be discarded when the form is saved, leaving that cell in the form empty, or displaying the “#missing” null value. Alternatively, the cell may revert to an earlier value, discarding changes made since the last save.

This situation arises because of the way that a data form is saved. During a save operation, the form is read line by line from left to right, with the top line read first. Since most Planning applications are “bottom-up” applications, data is typically entered for the child member (as the parent member is read only). This typically leads to the following sequence of events:

  1. The data form displays the child member above the parent, on the data form. This is the default layout for a data form: the parent is displayed on the row below its children, indented.
  2. The user enters data for the child member, which was previously null
  3. The data form is saved. The save operation reads the form left-to-right and top-to-bottom. Therefore the child is saved first and the newly entered data submitted to Essbase.
  4. The save operation then reaches the parent, which is still null (an empty cell). Because of the Implied Share, this null value overwrites the value of the child member, effectively discarding the data entered in step 2.

How to Avoid the Side Effects of Implied Shares in Planning

There are several ways to work around the issue outline above.

I. Use the “Never Share” setting

Set the parent member to use the “Never Share” storage setting, rather than “Store”. This disables the implied share, so that the parent and child store their values separately.

A “Never Share” member differs from a “Store” member in one respect: a parent member set to “Store” will display the aggregated value of its children after a save (Planning Data Form), after a lock, send, and refresh (Excel add-in for Essbase), or after running an aggregation (running a Business Rule to aggregate the members on the form). A parent member set to “Never Share” will only display the aggregated value of its children after an aggregation is run.

It is usually advantageous for parents with single children to use the default storage type of “Store” (thus keeping the Implied Share relationship) as it reduces the block size and overall database size in Essbase, and improves calculation and aggregation times. Only use “Never Share” when actually required.

II. Add a Dummy Member

Add a dummy member to the outline as a second aggregating child. A dummy member need not be used on forms, but exists in the outline. This will disable implied sharing, since the parent member now has more than one aggregating child (see Examples section above).

Note that this workaround will not help if the parent is a “Label Only” member. “Label Only” parents have an Implied Share with their first child, no matter how many other aggregating children are present. The only way to disable implied sharing in such a scenario is to change the “Label Only” storage type.

III. Change the Design of the Planning Data Form

Do not include both parent and child on the same data form.

IV. Disable Implied Sharing Completely

Prior to version this could only be done on a server-wide basis, so that all applications on the Essbase server would be affected. Since disabling Implied Sharing will have a negative impact on performance and database size this should not be done lightly.

Disable Implied Sharing for the entire Essbase server (9.3.1 and earlier)

  1. Back up all Essbase data as a precaution
  2. Shut down Essbase (stop the service)
  3. Edit the ESSBASE.CFG file and add the following line:
    EVENT 29
  4. Start Essbase. Upon startup, Essbase internally eliminates the implied share optimization.
Note: do not use EVENT 29 with later versions! See the section below instead

Disable Implied Sharing on a per-application basis (Essbase and above)

  1. Back up Essbase data as a precaution
  2. Shut down Essbase (stop the service)
  3. Edit the ESSBASE.CFG file and add the following line:
    IMPLIED_SHARE [application name] FALSE

    This will disable implied shares for this application. Omit the square brackets when entering the real application name.
    If no application name is specified implied sharing will be disabled at the server level (the same effect as EVENT 29 in earlier versions)

  4. Start Essbase. Upon startup, Essbase internally eliminates the implied share optimization.

Essbase – Calculate Swap Space or Virtual Memory Usage

Each process (program) running on the server has the ability to lock for its exclusive use a chunk of swap space or virtual memory.

If too many processes lock too much space, the next attempted lock will fail, and that process will terminate abnormally with a memory allocation error.

Because there are so many variables involved, it is difficult to calculate or predict how much swap space or virtual memory will be the maximum amount needed.
We recommend that virtual memory or swap space be set between two and three times installed physical RAM as a reasonable guess.
For example, if 4 GB RAM is installed in the server, swap space or virtual memory should be set for a minimum of 8 GB, and 12 GB is preferred, if there is sufficient disk space available.

We also recommend that resource-intensive applications, such as relational databases, be moved to separate servers and not be co-located with Essbase.

Another important memory-related metric to track is the amount of memory consumed by each ESSSVR process.
Each ESSSVR process is a running Essbase application, and on 32bit Essbase, none can ever use more than about 2 GB of memory address space (4 GB on Solaris; 1.7 GB on HP-UX).
If an ESSSVR process tries to consume more than this amount, memory allocation errors and perhaps data corruption can occur.



Guidelines in case of memory allocation issues

  1. Reduce the number of databases defined for the application (to one only if possible).
  2. Within the database, reduce the amount of memory allocated for caches.
  3. Reduce the blocksize (recommended 8000 to 64000 bytes).
  4. Reduce the number of members in the outline.


Essbase error – Illegal Block Header !!!

Essbase normally maintains correct and synchronized data and index files. Trouble usually arises when Essbase applications are abnormally terminated. When an abnormal termination occurs at the wrong” point during processing, data or index files may not be written properly to disk, resulting in database corruption. This can happen for various reasons:

– a power failure or the server Machine is turned off abruptly
– the server operating system fails or is shut down
– an operator kills an Essbase task on the server
– some outside influence causes a fatal interruption of application processing
– a user attempts to access data within a previously corrupted database
– a bug in Essbase causes the application to crash

Essbase databases contain data files (PAG) and index files (IND). The data files store blocks of data values or cells. Index files identify where in the data files each block of data resides so that during data retrieval, load, or calculation, Essbase can extract the values for processing. Because these files are stored separately, they must remain completely synchronized, or else the index would not reference the correct data blocks.

Under normal circumstances, Essbase ensures these files are correct. Essbase employs a number of techniques internally to minimize the potential for corruption, including a “safe-write” methodology which uses space in the files to store both current and previous copies of changed data. In the event of an abnormal termination, Essbase can usually recover to a prior, known good point in time. Unfortunately, sometimes events occur which leave the files in an unsynchronized state.

If the data and index files are not synchronized, the database is corrupt. The index is the only means for interpreting the data files, so if the index is bad, the data is lost. There is no other utility which can read the data files and recover the data. This underscores the importance of having a good backup strategy.

Depending on the extent of the corruption, some portions of the database may still be accessible. This can be beneficial in recovery efforts, but it also means that users may be working with a corrupted database and not know it. Some time may pass before someone accesses the “bad” spot in the database and discovers the corruption. When this happens, the Essbase application process on the server will usually “crash” and produce an exception log (XCP file). The user will get a message indicating a network error because the server is no longer responding to the client.

In some cases, no one notices these Crash Events. The users simply reconnect, the database is reloaded, and they may continue in this fashion for a long time. The problem is compounded, and sometimes degrades the database to the point where it can no longer even start up. At this point, the only recourse is to recover the files from file system backup.

Here is a sampling of error messages which may indicate a corrupted Essbase database (other, similarly worded messages may also occur):

1006010 Invalid block header: Block’s numbers do not match
1006016 Invalid block header: Illegal block type
1070020 Out of disk space. Cannot create a new index file.
1070026 Corrupted Node Page in the B+tree. [%s] aborted
1005016 Unable to Read Information From Page File
1006002 Unable to Store Information In Page File
1006004 Unable to Read Information From Page File
1080009 Fatal Error Encountered. Please Restart the Server…
1070018 Invalid file handle [%s]. [%s] aborted.

Refer the below URL from Oracle for fix and possible root cause.

FIX and rootcause.


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, 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 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.


HPCM: Where Does Detailed Profitability Stores The Calculation Scripts Which It Generates

Both the Formula and the Volume Formula are included in the HPM_EXPD_DRIVER export view. This is the best way to view the driver information stored within the database.

There is no calculation scripts like in Essbase for detailed HPCM applications.

The calculation happens in the table view but cannot be reviewed like you do when using Essbase.

You can view the formulas as stated but not the calculation.


Location Alias in Planning and Budgeting Cloud Service (PBCS)

How to use Location Alias in Planning and Budgeting Cloud Service (PBCS)?

Currently this feature is not available in PBCS.

Workaround: Planning always creates default aliases when the application is created.

Planning refers to each of the cubes using the following aliases in case of Prod Instance:

  • Plan1 – _RevCube_
  • Plan2 – _PnlCube_
  • Plan3 – _BsCube_

You can refer to these location aliases from one plan type (cube) to refer to the another. For Example in Prod Instance from Plan2  you can refer to Plan1 using _RevCube_.

However, here are few considerations to note :

1) Xref/Xwrites when used in batch calculation, are non-performant and hence should be avoided if possible. Instead administrators can push the data from one plan type (cube) to another using Map Reporting Application.

2) In Planning, if you enable an account member for more than one plan type and chose Source plan type value as one of the cubes, then this value is available in the other cubes by means of a dynamic formula setup in the other cubes that use the location aliases described above to pull the value into the other cube. You should be able to use this functionality to refer to the values from different cubes of the same application


How can a physical partial clear be performed in ASO cube in PBCS?

Firstly MAXL is NOT an option in PBCS.

Following steps can be taken to clear the Physical Data.

Login to the Calculation Manager and

Expand the Planning application

Then select

Actions-> Database Properties ->


Expand the planning application > Right Click the DB -> Clear -> (All Data, Upper Level Blocks , Non input blocks , Dynamic Blocks and Empty Blocks  ).

This lets you type an MDX command and on completion, a message of successful clear is returned along with the number of cells cleared.

If run the same command again , get the double the number of cells cleared than last time. So it seems to be doing a logical clear and adding offset cells every time a partial clear is performed.

Per Development team, options are now available for Logical or Physical in partial clear for ASO on the Planning and Calculation Manager