This post covers the basics of building a Planning app. Building a Planning application can be a straightforward process; however, there are some pitfalls to watch out for. One or two mistakes can lead to some major headaches in trying to decipher what went wrong. Whether a beginner or a seasoned vet looking for a quick refresher, this guide will outline the steps necessary to successfully create/deploy a Planning application. More...

No, your eyes aren’t playing tricks on you. The grid lines don’t align with the row headers. It is very slight on smaller forms, but forms with hundreds of rows compounds the issue. The further down the grid, the more of an issue the offset is.

 More...

It is possible for a database in Essbase to become corrupt.  This can be caused by server hangs, software glitches, and a variety of other reasons.  Although infrequent, if a database cannot be loaded for any reason, and it needs to be restored, the following actions can be a quick resolution.  Keep in mind that this will remove the data and it will need to be imported from a backup export.

Before performing this, verify that the database is not attempting to recover.  To determine if this is occuring, open the application log file.  If it states that it is recovering free space, be patient as it may correct itself. 

File Structure

Essbase has a simple file structure that it follows.  It can vary with each application depending on the options used.  The area to focus on for this process is below.  The application and database that is being restored would take the place of appname and dbname.

Hyperion\Products\Essbase\EssbaseServer\App\AppName\DbName

Restoring To A Usable State

In this directory, files with the following extensions will need to be removed.  This will delete all of the data  and temporary settings that are causing the application to function improperly.  It will NOT delete the database outline, calc scripts, load rules, or business rules. 

  • .ind (index files)
  • .pag (data files)
  • .esm (Essbase kernel file that manages pointers to data blocks, and contains control information that is used for database recovery)
  • .tct (Essbase database transaction control file that manages all commits of data and follows and maintains all transactions)

After these files are removed, verify that the application and database is functioning.  This can be done in Essbase Administration Services by starting the application.  If the application doesn’t start, more research will have to be performed. If the application loads, import the most recent data backup and run an aggregation.

There are a number of other possible file types in this directory.  Below is some information that may be helpful.

Audit Logs

  • .alg:  Spreadsheet audit historical information
  • .atx:  Spreadsheet audit transaction

Temporary Files

  • .ddm:  Temporary partitioning file
  • .ddn:  Temporary partitioning file
  • .esn:  Temporary Essbase kernel file
  • .esr:  Temporary database root file
  • .inn:  Temporary Essbase index file
  • .otm:  Temporary Essbase outline file
  • .otn:  Temporary Essbase outline file
  • .oto:  Temporary Essbase outline file
  • .pan:  Temporary Essbase database data (page) file
  • .tcu:  Temporary database transaction control file

Objects

  • .csc:  Essbase calculation script
  • .mxl:  MaxL script file (saved in Administration Services)
  • .otl:  Essbase outline file
  • .rep:  Essbase report script
  • .rul:  Essbase rules file
  • .scr:  Essbase ESSCMD script

Other

  • .apb:  Backup of application file
  • .app:  Application file, defining the name and location of the application and other application settings
  • .arc:  Archive file
  • .chg:  Outline synchronization change file
  • .db:  Database file, defining the name, location, and other database settings
  • .dbb:  Backup of database file
  • .ddb:  Partitioning definition file
  • .log:  Server or application log
  • .lro:  LRO file that is linked to a data cell
  • .lst:  Cascade table of contents or list of files to back up
  • .ocl:  Database change log
  • .ocn:  Incremental restructuring file
  • .oco:  Incremental restructuring file
  • .olb:  Backup of outline change log
  • .olg:  Outline change log
  • .sel:  Saved member select file
  • .trg:  Trigger definition file.XML (Extensible Markup Language) format
  • .txt:  Text file, such as a data file to load or a text document to link as a LRO used for database recovery
  • .xcp:  Exception error log
  • .xls:  Microsoft Excel file

Microsoft Excel does a great job of calculating only what is needed.  If automatic calculation is turned on (and is by default), it only calculates formulas that have changed since the last calculations.  If automatic calculation is turned off, F9 will accomplish the same thing. 

CTRL+F9 goes one step further and calculates formulas that have changed, as well as the formulas dependent on them.

CTRL+ALT+F9 calculates all formulas in the workbook, regardless of whether they changed since the last calculation.

When custom functions are used, Microsoft Excel doesn’t always know the dependencies because the function can reference cells outside those provided in the function arguments.   When this occurs, using CTRL+SHIFT+ALT+F9 is critical to ensure that all cells are calculated correctly.  This rechecks dependent cells and calculates all formulas regardless of whether they have changed since the last calculation.  This is the only way to ensure that all data is calculated.

When I am introduced to business segments that use Hyperion Essbase, I always get asked the same question: "Can you explain what sparse and dense mean?"  Although I agree that users don't HAVE to understand the concept, I contend that it is extremely valuable if they do.  It will not only help them become more efficient users, it goes a long way in helping them understand why something simple in Excel isn't always simple in Essbase.  If users understand what a block is, and what it represents, they have a much better experience with Essbase.

If you are a relational database developer or a spreadsheet user, you tend to view data in 2 dimensions.  An X and Y axis is equivalent to the rows and columns in your spreadsheet or database table.  Essbase is a little different in that it stores data in 3 dimensions, like a Rubik’s Cube, so it has a Z axis.  Essbase databases refer to these “Rubik’s Cubes” as blocks.  An Essbase database isn't one giant Rubik’s Cube; it could be millions of them.  The size and number of possible blocks a database has is determined by the sparse/dense configuration of the database.

An Essbase outline has a number of dimensions.  The number of dimensions can range in quantity and size, but each dimension is identified as a dense or sparse dimension.  The dense dimensions define how large each block will be in size (the number of rows, columns and the depth of the Z axis).  The sparse dimensions define the number of possible blocks the database may hold.  Assume the following scenario:  a database exists with 3 dense dimensions and 2 sparse dimensions.  The dense dimensions are as follows:

Net Income
  + Income
  + Expenses

Qtr 1
  + Jan
  + Feb
  + Mar

Version
  ~ Actual
  ~ Budget
  ~ Forecast

Remember, the dense dimensions define the size of blocks.  These dimensions would produce a block that looks like the image below.  Every block in the database would be the same.

For those more knowledgeable with Essbase design, this example assumes that no member is dynamically calculated or is tagged as a label to reduce complexity.

 

The sparse dimensions are below.

Total Product
 + Shirts
 + Pants

Total Region
 + North
 + South
 + East
 + West

The unique combinations of each sparse dimension has its own block.  There will be a block for Pants - North, one for Shirts - North, and so on.  Since there are 3 members in the Total Products dimension and 5 members in the Total Region dimension, there will be a total of 15 (3 x 5) blocks.  If a database has 5 sparse dimensions, all with 10 members, it would have a total possible number of blocks equal to 100,000 (10 x 10 x 10 x 10 x 10).  Below is a representation of the possible blocks for Shirts.