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~

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


Dears,

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

Examples:

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

 

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 11.1.2.3.608.2346 and Calculation Manager 11.1.2.3.608.011

 

Essbase – Encrypt Passwords using MaxL Scripts


First of all we need to generate random public and private encryption keys using the following command:

essmsh –gk > myKeys.txt

The resulting myKeys.txt will contain the keys, you should keep this file somewhere safe, here are the contents:

Public Key for Encryption: 25891,2909413183
Private Key for Decryption: 2520849883,2909413183

Then we also need a script to convert, e.g. Finance.mxl, contents as follows:

login ‘admin’ ‘password’ on ‘severname’;
spool on to ‘E:\CalcConsole\Logs\Finance.log’;
import database DFinance.Finance data connect as ‘admin’ identified by ‘password’ using server rules_file PLSQL on error write to ‘E:\CalcConsole\Logs\PLSQL.err’;
execute calculation DFinance.Finance.Night;
spool off;
Exit;

To encrypt the script we use the public key like this:

essmsh –E Finance.mxl 25891,2909413183

Which generates a file called Finance.mxls (note the ‘s’ on the end of the file extension which I suppose stands for “secure”), this no longer has clear-text passwords, contents as follows:

login $key 106005741293930722520707386301 $key 0404020362185807397114384020618985408471 on ‘mdcdev003’;
spool on to ‘E:\CalcConsole\Logs\Finance.log’;
import database DFinance.Finance data connect as $key 106005741293930722520707386301 identified by $key 0404020362185807397114384020618985408471 using server rules_file PLSQL on error write to ‘E:\CalcConsole\Logs\PLSQL.err’;
execute calculation DFinance.Finance.Night;
spool off;
Exit;

The last step is to actually run the script, we need to use the private key to do this:

essmsh –D Finance.mxls 2520849883,2909413183

The theory behind public/private key encryption is that because both keys are required to generate the actual password, and a potential hacker never has access to both the keys, they will not be able to “crack” the password.

Thanks,

~KKT~

How To Successfully Install The Oracle EPM System 11.1.2.2.0 Essbase In A Windows Server 64bit


Where can I get the Installer and the Assemblies to install Essbase 11.1.2.2.0?

To obtain the installer, go to the Oracle Software Delivery Cloud (https://edelivery.oracle.com/) and follow the next steps:

Log in to the download site
Select the Product Pack: Oracle Enterprise Performance Management System
Select the Platform: Microsoft Windows (64-bit) or the Platform of your choice
Push the Go button
Click on the link “Oracle Enterprise Performance Management (11.1.2.2.0) Media Pack for Microsoft Windows (64-bit)”

What do I need to download from the Oracle Software Delivery Cloud site?
Note: The part numbers listed in the example below are for the Windows 64-Bit Platform only, the part number will vary depending on the platform.

V31875-01 EPM System Release 11.1.2.2.0 for Microsoft Windows (64-bit) Part 1
V31876-01 EPM System Release 11.1.2.2.0 for Microsoft Windows (64-bit) Part 2
V31922-01 EPM System Release 11.1.2.2.0 for Microsoft Windows (64-bit) Part 3
V31877-01 EPM System Release 11.1.2.2.0 for Microsoft Windows (64-bit) Part 4
V32795-01 EPM System Release 11.1.2.2.0 for Microsoft Windows (64-bit) Part 5
V31879-01 EPM System Release 11.1.2.2.0 for Microsoft Windows (64-bit) Part 6
V31880-01 EPM System Release 11.1.2.2.0 for Microsoft Windows (64-bit) Part 7
V32789-01 EPM System Release 11.1.2.2.0 Client Installers for Microsoft Windows
V31881-01 EPM System Release 11.1.2.2.0 for Microsoft Windows (64-bit) Oracle HTTP Server

Optional Deliverables (Recommended)
V36699-01 EPM System Release 11.1.2.2.0 Installation Documents and Readmes

On your server, create a directory where the compressed files are going to be stored: (i.e. [Drive]:\EPM11R2_ZIPS )
Download the files to the designated target directory.

What am I going to do with all the compressed files?

Uncompress all the files, starting with the Installer, to a directory whose name does not contain any spaces. You could create a directory at the root level (i.e. C:\EPM11R2_Installer)
When done, ensure that the “assemblies” directory is populated with the files you uncompressed.
How do I run the installer?

To run the installer, go to the installer directory ( i.e. C:\EPM11R2_Installer ) and run the following command: installTool.cmd, this will start a console that will launch the Oracle Universal Installer.

What do I select during the installation?

After you pass the initial panels, select the New Installation. This gives you the ability to select the components by product.
For Essbase, check all the options that you would like to install.
Continue with the installation.
What do I need to configure after the installation?

You need to configure Shared Services (Foundation, EPMA and Calc Manager) and the Essbase products that you installed.
Ensure that you have the Relational Database information ready before you start the configuration phase.
How can I test if Essbase is working?

You could connect to the Essbase server via EAS, ESSCMD or MaxL to ensure that it is fully operational.
You could also check the Essbase.log and ensure that the correct initialization took place.

let me know in case of any more queries
Thanks,
~KKT~