For many years, the conventional wisdom was that if your Essbase server was failing to start, you should run it in the foreground to see any messages that might be displayed to the console, but not logged into the Essbase.log file. With the pre-11.1.2.x versions, this was usually quite simple. In fact, if you had your environment variables set right, all you needed to do is type in ‘essbase’ at a command prompt regardless of what directory and your Essbase server would start before your eyes.

According to the Essbase Database Administrator’s Guide (dbag), it states that the ability to run Essbase in the foreground is no longer supported as of version 11.1.2.2.100. With the 11.1.2.x versions and their more complicated directory structure (some static files stored in …Oracle/Middleware/EPMSystem11R1/products…. and some files stored in …Oracle/Middleware/user_projects/epmsystem1/…); starting Essbase in the foreground can be challenging if you don’t use this simple hack.

If we open a command prompt and navigate to the …/Oracle/Middleware/EPMSystem11R1/products/Essbase/EssbaseServer/bin directory and type in ‘essbase’, you will likely see a message about an improper ESSBASEPATH, or maybe a message about the Locale.

In later versions, Essbase needs to reference files from different folders.  We have two options, we can either set up the proper environment variables or we can do what Oracle has done with their ESSCMD and ESSMSH executables.  I like to hijack the “startEsscmd.cmd” script that is installed with the Essbase Client in …/Oracle/Middleware/EPMSystem11R1/products/Essbase/EssbaseClient/bin directory.

Simply save a copy of the “startEsscmd.cmd” script as “startEssbase.cmd”.  Edit the new “startEssbase.cmd” script and change the “%ARBORPATH%\bin\ESSCMD.exe %*” line to “%ARBORPATH%\bin\ESSBASE.exe %*”. Then, save the startEssbase.cmd file and when you double-click it in Windows, Essbase will run in that window. Now you can use those old school Essbase commands in the foreground and rebuild the Essbase.sec file if absolutely necessary.

Of course, this is unsupported and with the advances in Essbase, it’s probably not even necessary since Essbase runs much better now than it did in the 9.3.x and early 11.1.1.x days.  The only time I have had to do this is when the Essbase.sec is corrupted beyond repair, but starting in version 11.1.2.2, I believe, Essbase tries to create a new Essbase.sec file automatically if it’s missing. It’s not very often that these steps are needed, but I wanted to document them just in case anyone is running on an old version out there. If you are on EPM version 11.1.2.2 or older, please consider an upgrade. I know a guy that can help with that.

With the release of Essbase 11.1.2.3.500, there was an interesting entry in the readme that caused some chatter on Network54.  The readme states:

The INPLACEDATAWRITE setting in essbase.cfg enables or disables in-place data writing.

Prior to this release, each time a data block was updated, it was written to a new disk location. With this release, for Exalytics, Essbase enables in-place data writing.

In-place data writing means that when updates occur, the data block can be written to the same location, as long as the compressed size of the data block fits in its original location on the disk.

In-place data writing can help reduce data fragmentation and lower the need for frequent restructuring of database. It also reduces the need for frequent index updates, resulting in improved performance.

Merely updating a single value of a cell in a single block causes that block to be written to the end of the page file? This made long-time developers scratch their heads because many of us believed that updating values in a block did not cause fragmentation. If submitting data causes fragmentation, imagine how bloated our Planning databases must be. No wonder we hear recommendations to restructure nightly during a planning cycle.

Glenn Schwartzberg said that yes, that’s how he understood it to work.  Edward Roske said there was no way submitting data cause fragmentation.  Cameron Lackpour didn’t believe it either until he did some testing and found that the ESSCMD GETDBSTATS command reported fragmentation after updating a single cell of Sample.Basic. Dan Pressman commented that he had seen the same behavior with compression turned off – BSO writes to the end of the .pag file when blocks are updated.

Test 1:

I tested this out on an 11.1.2.4.000, non-Exalytics sandbox. In Excel, I retrieved a single block of one of our sample cubes (not Sample.Basic, this was a larger demo cube). The page dimensions of my worksheet listed a single level-0 member from each Sparse dimension of the cube. Accounts were on my rows and January was my column dimension (both were level-0 Dense members).

With that sheet, I wrote a macro to do the following:

• Update each data cell of the grid to a randomly generated number
• Submit the data for the block back to Essbase
• Call a MaxL script to stop and restart the Essbase application to flush the cache
• Loop back to the top, iterating 250 times

While I ran the macro, I (im)patiently watched the database directory to see if the database’s essn.ind or essn.pag files would grow.  As we all know, if there is fragmentation that happens each time the block is submitted back to Essbase, the .pag file should see some growth – especially over 250 iterations. For complete transparency, this database is using the bitmap compression type that is the default for BSO applications.

After 250 iterations of changing the same block and stopping/restarting the application there was no essn.pag file growth. The GETDBSTATS results show the following:

• Average Clustering Ratio: 0.4431846
• Average Fragmentation Quotient: 0.8091218

That leads me to believe there is a little bit of fragmentation, so I cleared the cube and loaded in a level-0 extract of the data and ran a calculation.  After that, I ran GETDBSTATS again and found the following:

• Average Clustering Ratio: 0.5117247
• Average Fragmentation Quotient: 0.6745301

Test 2:

My next test was to do the same loop except I added in a quick aggregation step after the data was submitted. This time, as the macro ran, I could plainly see the essn.pag file growing every 5-6 loops of the code. The Average Clustering Ratio and Average Fragmentation Quotient also grew as the calculations ran.

What does all of this mean?

Conclusion:

My conclusion is that the documentation on INPLACEDATAWRITE is a little misleading. Essbase without the INPLACEDATAWRITE does NOT write a block to the end of the .pag file if a block is updated. Blocks may be updated all day long by submitting data to them without expanding your .pag files. Fragmentation is caused when a data block is brought into the calculator engine. This is where INPLACEDATAWRITE does its magic on Exalytics machines and allows those blocks to be compressed and returned to their original location in the .pag file, thus preventing fragmentation.

What about the Fragmentation Quotient? Well, in the DBAG, it mentions that the Fragmentation quotient can be high and not indicate a need to defragment since free space in the .pag is created in 8MB chunks. I observed this to be true during Test 2 where excess free space was added to the .pag file and the file would only grow after 5-6 loops of the submit data/calc/stop/start routine. My thought is that an Average Fragmentation Quotient less than 1 (<1%) is essentially no fragmentation at all. After a single calculation script I began to see that Average Fragmentation Quotient above 1 (1.884844). By nature Essbase has some degree of fragmentation all of the time as it’s .pag file is always a little bit bigger than the actual size of the data contained therein. As long as the .pag file isn’t growing and the Average Fragmentation Quotient is less than 1%, I’m counting that as no fragmentation by submitting data to a block.