Wednesday, May 4, 2011

Hyperion Planning Physical Disk Out Of Space (Case Study)

I was troubleshooting a client whose server's hard disk was almost full.  The server hosted a Planning (version 4) and an essbase server (version 7.1.3) - but I believe this can be done on any version of planning or essbase server.  The essbase cube was a sparse one, and in order to defragment it, I need to export the database and reimport it back.  After 95 hour the essbase database console states ASCII Backup : [Some Number] and still generating the numbers. The file output is only 3752 KB. The total IND is around 24 GB and the PAG is about 71.1 GB. The server is not in archive mode, and I tried restarting the essbase server beforehand.

Then I restarted the system (the windows 2000 server), and I tried to validate the essbase data. After 16 hour, it's getting me nowhere, and then I stopped the validation.

Then I  "refresh"ed the planning outline unto the essbase outline. I tried an incremental / full refresh, but it failed showing insufficient disk space error (my client actually added a member on the dimension before contacting me). I added a 200 GB external disk registered as E Drive. The PAG and IND files are located on the D Drive (100 GB, with the files around 95 GB we got about 10 GB Free). The Essbase otl, csc and rule files are located on the C Drive.

Exporting only level 0 data is not an option.   I've seen some script using level 1 or level 2 data for calculation to populate the level 0 data. But exporting level 0 data turns out to be successfull albeit very slow.

The system was a production system (and still is), and at the time there were no back up.

That's the case. 

Here is the solution which work on my problem :

Since the very beginning this wasn't any Hyperion Applications problem.  This was an OS layer problem.  So I turned off all Hyperion Applications Services (Tomcat, Planning & such).  I copied all the content of D Drive to E Drive (same structure different Drive Letter).  Remember that D Drive is 100 GB (with only 10 GB Free) and E Drive is an empty 200 GB Disk.  Then I switched the Drive Letter from D to E (done in Windows Server - Any Windows Server, [even Windows XP] have this capability).  So suddenly I have a Disk with 110 GB Free.  I restart the machine - just to be safe.  Then I export the essbase cube again.  The export took 7 whole days to complete.

After 7 Days, I emptied the old cube and reimported the data back.  I did a full refresh from the planning - to ensure dimension consistency between essbase and planning outlines, and it worked like charm.

Afterwards I emptied the 100 GB disk, and copied all binary structure (along the new small PAG & IND files) from the 200 GB disk to the 100 GB disk.  Then switched back the drive letter.  (Remember to turn off all application services beforehand and restart the machine the afterwards).  Now you got a denser data and a faster working application.

Another solution, albeit a more risky solution is to expand/span the Drive - Any Windows Server, [even Windows XP] have this capability.  So you could actually turn the D Drive into a 300 GB Logical Disk.  But since the 200 GB isn't gonna stay on this machine permanently, and I need to return the disk to unspanned mode, I would not chance the solution.  I have little confidence with data consistency spanned through out multiple physical disks.  (If either one of the disk failed, the data can't be recovered anymore).

[WARNING] This solution is not supported by Oracle but could solve the problem.  (Since this is an OS Layer problem, not an Application [Oracle/Hyperion] Problem)

[MORE INFO] I considered splitting the data from the Essbase Application unto different disks a good implementation habit.   The PAG and IND files are located on the D Drive & the Essbase otl, csc and rule files are located on the C Drive.