ASO has gone through many upgrades in functionality over its relatively short life. When 9.3.1 released, it finally had write-back capability. With the release of System 11 came the ability to clear targeted portions of data. Now with the release of 11.1.2, you can even perform procedural calculations (yes, calc scripts have made the leap to ASO.)
But, what about all of us still stuck in the past, on Essbase 9.3.1?
While I won’t discuss procedural calculation today (there will be a later article to discuss this), I can help you clear out data in ASO. There are two primary ways to clear data in ASO prior to System 11: by a specific member (IE: clear the budget scenario), and targeted intersections (IE: clear the budget for January 2010). The focus of today’s discussion will be clearing data from a specific member. This method is far easier to implement and performs the clearing of data much faster. The targeted approach, which I will cover in a later article, requires report scripts, load rules and more time for the data to clear.
So how can I clear just my budget scenario you ask? It’s actually very simple, change the formula of the member. In ASO (9.3.1 and later), when you change a member formula, the outline is restructured and the input members storing data are converted to dynamic and all data is cleared. Once the data is cleared, you can change the member formula back to blank, save the outline and proceed to load your new budget data.
Now you may ask, this is great for a one-off clearing of data, but how can we make this happen automatically? I want to load my budget from a BSO cube daily, how can I automate the process?
I have created a quick tutorial below that walks through automating this process. This tutorial uses the sample ASO model that comes with Essbase to allow you to easily re-create the process for testing prior to creating the process for your own Essbase model.
STEP 1. Create a new text file in notepad that looks like the screenshot below. Save this file as ClearCurrentYearA.txt.
STEP 2. Delete the 0 at the end of the line and save a copy of this file as ClearCurrentYearB.txt.
STEP 3. Create a rules file to handle the new text files.
STEP 3.(a) Open one of the two files that were just created, it makes no difference which you choose.
STEP 3.(b) Change data file settings to use a comma instead of a tab delimiter.
STEP 3.(c) Change rule to dimension build fields so that we can see our field settings.
STEP 3.(d) Change dimension build settings for the Years dimension to parent/child and allow formula changes.
STEP 3.(e) Change the properties for field 1 to be the parent of the Years dimension.
STEP 3.(f) Change the properties for field 2 to be the child of the Years dimension.
STEP 3.(g) Change the properties for field 3 to be the formula for the Years dimension and check the box labeled Delete when the field is empty.
STEP 4. Create a new MaxL script that looks like the script below (also included in the zip file):
STEP 5. Create a batch file that calls the newly created MaxL script (also included in the zip file):
STEP 6. Execute the batch file and watch the data in the Current Year member disappear.
As you can see above, the batch/MaxL script causes two restructures to occur. After the first restructure, we see that there are two input cells converted and removed. The amount of time required will depend on the time required to perform a restructure on the outline. Once this is completed, you should have data in all members but the one you chose to clear.
