Skip to main content

Data & Intelligence

Surgical Essbase Recovery: How To Back Up Your Essbase Data

shutterstock_125192411_350Recovery of data in Essbase is sometimes confusing and time consuming depending on the type and frequency of backups previously taken. In this article, I’ll give you an idea of how you can back up your Essbase data for the purpose of restoring a slice of the database.

Scenario: I am running a daily export of level 0 data from my Essbase database. One of my Planners called and said he submitted this month’s data to last month’s point of view thus overwriting a Forecast version that was still valid.

Recovery Solution 1: My first option is to import my level 0 data export and notify the Planners that they need to start submitting data that was entered since that last backup I have. The problem with this scenario is not everyone who entered data may be available to re-enter data.

Recovery Solution 2: Create a copy of the database, load the last level 0 backup, export the point of view affected by the accidental submit then load that into my production database. This solution takes a bit longer to build because it involves the creation of a new Essbase application and database as well as calculation script development; however, we can surgically specify the data to restore based on the point of view of the process that broke it to begin with.

At the risk of insulting your intelligence, I must add: BACK UP YOUR DATABASE BEFORE YOU START. I will not start any surgical restore operation without getting a full backup of the database I’ll be impacting. This adds time to the beginning but can mean that I keep my job if I make a mistrake. Just grit your teeth and focus on the backup while the Planner is yelling in your ear. He can wait. It’s your job security not his if the restore is bad.

Data Intelligence - The Future of Big Data
The Future of Big Data

With some guidance, you can craft a data platform that is right for your organization’s needs and gets the most return from your data capital.

Get the Guide

Step 1: Determine the point of view. This solution cannot work without a specific point of view. The POV will usually be a fairly small slice limited to a single scenario and version. Document the point of view using exact member names and member selection functions. For example, the Planner violated data in the following POV:

  • Scenario: “Forecast”
  • Version: “Week 2”
  • Entity: “Texas”, “Louisiana”, “Florida”
  • Currency: “USD”
  • Period: “Jan” through “Dec” (months only)
  • Accounts: “Headcount”, “Gross Sales”

Step 2: Create a new Essbase application in EAS Console and call it, “Restore”. Make sure to specify the same Unicode option as the original.

Step 3: Copy the original database (minus data) to the new application.

Step 4: Load the last level 0 or full data export. Assuming the outline has not significantly changed, the data can be loaded without a load rule.

Step 5: Here’s where it gets interesting. Write a calculation script to export the slice of data that was broken by the Planner. Here’s an example based on the point of view described in step 1:

SET DATAEXPORTOPTIONS
{
/* Export level 0 only */
 DataExportLevel LEVEL0;
/* Let’s not export dynamically calculated data */
 DataExportDynamicCalc OFF;
/* Let’s not export stuff that’s not even there! */
 DataExportNonExistingBlocks OFF;
/* Export in a columnar format because we’ll need to
    create a load rule to import it in later */
 DataExportColFormat ON;
/* Pivot the 12 periods out as columnar data. */
 DataExportColHeader "Period";
/* Export the dimension names as a header row so we
    can identify each column in the load rule later. */
 DataExportDimHeader ON;
};
/* Fix on the point of view we identified in the first step. */
FIX ("Forecast", "Week 2", "Texas”, ”Louisiana”, ”Florida”,
 ”USD”, ”Jan”:”Dec”, ”Headcount”, ”Gross Sales”)
      /* Use the DATAEXPORT command to dump this POV to a flat file.
          I’m using a pipe (|) as a delimiter and #MI to represent
          missing data. */
       DATAEXPORT "file" "|" "E:\Restore\fixmyoops.txt" "#MI";
ENDFIX

Step 6: Review the data extracted in step 6 in a spreadsheet to make sure it’s what you want to recovery. If not, go back to step 1 and double-check your point of view.

Step 7: In the original application, create a load rule to import the data file created in step 5 into the database. DISCLAIMER: You really shouldn’t be loading data directly into production without testing the process and the result in a separate system.

Step 8: Run the data load using the data file created in step 6 and the rule created in step 7.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Cris Dunn

Cris Dunn is the manager of Perficient's EPM SupportNet practice which provides direct support for applications and infrastructure surrounding many organizations' EPM software implementations. He is also an Oracle University certified instructor and teaches everything around Essbase as well as the EPM installation and configuration classes. When he is not working, you will find Cris engaged in His church serving as the treasurer, musician, nursery worker, and, when necessary, toilet scrubber.

More from this Author

Follow Us
TwitterLinkedinFacebookYoutubeInstagram