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