When A Cube Has To Be Quarantined

One problem that companies frequently have with Essbase is the metadata and fact data modification process.  It’s basically binary.  That is, a cube reflects either the “before” or “after” state.  Between these two states there is a time gap, when the users are without access to either the “before” or “after” data.  Furthermore, if there are any problems with the “after” data, users cannot access the “before” data while the problems are being fixed – because it was destroyed in the process.

Oracle’s EPM development team has worked to reduce the gap between “states,” particularly in ASO where techniques are available to greatly reduce the impact as seen by the users.  Oracle’s product roadmaps indicate continued work in this area, and we look forward to seeing them in future releases.

Unfortunately, Oracle can address only the gap between the “before” and “after” states.  They cannot address logical or functional issues arising from external processes.  Limitations of the data source or of the ETL process may prevent a developer from taking advantage of all of the techniques and opportunities provided by Oracle.

Even when all of the meta and fact data exist to support on-the-fly cube updates, and new capabilities have been developed by Oracle, there still will be times when a transparently transformed cube should not be released or even retroactively recalled.  People are apt to make mistakes when modifying metadata, and no matter how many steps are added to the data governance process, be they manual or automated, occasionally the old cube will be better than the new.

Do you want every financial analyst in the company screaming at you?  One way to avoid this is never leaving the users in “data limbo.”  A four-cube rotation can come to the rescue.  Let’s assume the users are working with a cube that has an Essbase application name of Dog and an Essbase database name of Cat (written as Dog.Cat):

1. Dog.Cat:  This is the current “published” cube to which users are connected

To create our four-cube rotation, we would add the following cubes:

2. Dog_1.Cat:  This is the most recent “published” cube, kept as a fallback in case an audit failure is found too late
3. Dog_2.Cat:  This is the second-most recent “published” cube (a “belt and suspenders” strategy)
4. Dog_W.Cat:  W indicates that this is the Work-in-Process cube; this is where restructures and reloads will be performed.  Until the metadata modification cycle begins, this is identical to Dog.Cat

Anytime modifications could cause a failure, we must leave the users’ world alone.  At all times, the users should be able to cheerfully continue running their queries and reports against Dog.Cat.  A four-cube rotation enables this.

Instead of altering Dog.Cat, modifications should be performed against Dog_W.Cat, whatever those changes might be (metadata, fact data calculations, etc.).  Upon completion of modifications, the cube should be audited.  Hopefully, this audit is automated, but in some companies, it may be manual.  Actually, during the monthly-close cycle, some clients use this audit as an opportunity to not only check for process failures, but also to allow the team responsible for closing the books to run reports and verify that there isn’t “just one more JV” required before data is released to the rest of the company.

However it is accomplished, at some point the cube is ready to be “published.”  At this point, all access to the four cubes is cut off, the cubes are unloaded from Essbase, and the four cubes are rotated:  4 moves to 1; 1 to 2; 2 to 3; and 3 to 4.

By using a bit of an atypical process, the users will experience only a second or two of interruption.  They may have to sign in again, but other than that, few users even notice the interruption.  The secret is that all four cubes have identically named databases, and the cube as a whole does not get rotated.  No changes are made to the application.  As most Essbase developers know, the application is seen on the disk drive as a subdirectory under “…/app/” or in this case, “…/app/Dog” or “…/app/Dog_W,” etc.  The database is a subdirectory below that,  “…/app/Dog/Cat” or “…/app/Dog_W/Cat,” etc.

The second secret to making this work is that we don’t tell Essbase about this rotation.  We do it through the operating system, only moving the subdirectories.  It takes five steps:

1.   Del        …/app/Dog_2/Cat
2.   Move    …/app/Dog_1/Cat        …/app/Dog_2/Cat
3.   Move    …/app/Dog/Cat            …/app/Dog_1/Cat
4.   Move    …/app/Dog_W/Cat     …/app/Dog/Cat
5.   Copy     …/app/Dog/Cat           …/app/Dog_W/Cat

All of the security is based on the Application name, so there is no problem.  Now simply “load” the application and let the users back in.

There are some additional considerations, which I will address in my next blog:

• The copy step is the only one that takes any perceptible time at all.  I will present some alternatives that can be used if your cube is large.
• There are special cases to be considered, depending on whether there are multiple tablespaces (ASO) or multiple volumes (BSO).
• If the cube is part of a partition, one extra special-file rotation will be needed.

Finally, your ears may have perked up at the statement, “Anytime modifications could cause a failure….”   My next blogpost will expand on this topic, and you will see that ASO and BSO cubes pose differing challenges when trying to decide exactly which changes pose a risk of irrecoverable failure.

Until then, happy cubing!