Recovery of data in Essbase is sometimes confusing and time consuming depending on the type and frequency of backups previously taken. In this short article, I’ll give you an idea of how you can restore a slice of data based on the most famous Essbase backup, THE DATA EXPORT. I’m assuming that you know how to create a calculation script and data load rule. If I’m assuming too much, please consider attending some training.
Scenario: I am running a daily export of level 0 data from my Essbase database using a MAXL script. One of our 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 problems with this scenario is not everyone who entered data may be available to re-enter data and I’ll have to calculate the whole database. This is kind of like going to the dentist to have your teeth cleaned but wake up in the hospital with an organ transplanted. Well, maybe I am exaggerating a little.
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 goes bad. On a side note, this noisy planner may very well be the same person that “lights up his mobile device in the middle of a dimly lit theater.”
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” (or “Bob”, or anything else for that matter. I won’t look over your shoulder). 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. OBVIOUS BUT NECESSARY 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.
There you go. Have a nice day.
