Fix statements... The most essential concept to understand when writing calc scripts in Essbase. I am going to be talking a little bit about best practices around fix statements and what not to do with them. I will be referencing the Sample.Basic database that we all love so much just to keep the code simple.
Below is a high level screenshot of the Sample.Basic outline that we will reference for this discussion:
So, lets get right into why you came here... ...the fun examples of what not to do. Below is an example of what our fictional Essbase developer, "Jimmy", has done. Even though the syntax validates and the calc functions, for the most part, as it should, lets discuss what is wrong with it. I will show an example at the end describing how you should structure this clear script efficiently and effectively.
fix(jan,feb,mar);
fix(actual);
fix("100-10");
fix("new york");
fix(sales,cogs);
clearblock all;
endfix;
endfix;
endfix;
endfix;
endfix;
#1: "The Zipper" or "Pyramid"
I am sure the first thing you notice about this example is that Jimmy has nested all of his FIX statements separately, calling out a member from each dimension in order to clear a select set of blocks.
This method may look nice to some, but to an experienced Essbase developer, this is not nice. In fact, this is actually a less efficient way of clearing these blocks. By opening a new fix statement for every dimension he is actually slowing down the calculation unnecessarily. You can simply put all of the members in one statement to make this much more efficient.
#2: "Manic Semicolons"
Jimmy has added more sloppiness to his calc script. The Manic Semicolon happens more often than you would think. Some developers, like Jimmy, do not realize that a FIX statement does not need a semicolon. This really doesn't hurt performance, but it sure does make Jimmy look like he does not understand what he is doing.
#3: "Unrestrained Members"
The last thing you ever want to do is leave any member unrestrained like Jimmy has done here. Members should always be contained within quotes. This may just sound like a pet peeve, but it can save headaches down the road. You will notice that "New York" and "100-10" are contained in quotes. This is not by accident. These members would cause the calc script to fail validation if they did not have these protective quotes around them. The reason for this is because New York has a space in the member name and the system will try to resolve it as two separate members, "New" and "York". The product 100-10 also would fail validation because the system assumes this is the number 100 minus 10 or 90. No good...
#4: "Underutilized Functions"
Jimmy is what I would qualify as a caveman coder, similar to the "guy" that unplugs a lamp to turn it off when he could have just flipped the switch on the wall. You will notice in his first FIX Statement, he is trying to include the months in the first quarter of the year in his script. Lucky for him, there is an easy to use function called "@CHILDREN" that he can use instead of hard coding the first 3 months. I realize that this is not an extreme case, but it is a good idea to get in the habit of thinking about your calc scripts in this dynamic way. The syntax that should be used in this case is @CHILDREN("Qtr1") which will automatically grab Jan, Feb, and Mar.
#5: No Respect for Next Person
Clearly Jimmy has no respect for the next developer that will be modifying this script in the future. You can tell this from 2 key facts, proper capitalization not used and the lack of comments.
Proper capitalization may not seem very important, but it really improves the overall readability of the script syntax. It really lets the developer after you know that you took your time with the script. Some guidelines for properly capitalizing items within year script are as follows. Member names should always match the case of how they are stored in your outline. Functions, like FIX and @CHILDREN should be in a caps to make them easier to read.
Comments, need I say more? Commenting code is something that every programmer should have ingrained in their coding techniques. This not only helps the developer after you figure out what the script is doing, but helps you debug as you are developing. Another thing to add to the script is a header comment that describes which script you are looking at, what is is suppose to do, when it was written, who wrote it, and what the high level process is of the calc script. This will save many headaches down the road.
#6: Overruled Outline Order
Looks like Jimmy has done it again... His nested FIX statements are not in outline order. This order does not affect the script from a technical standpoint, but it sure does make it easier to figure out which members are from each dimension (see #5 above). What Jimmy should have done is put all of his members in one FIX statement and group them by dimension. You will notice in the best practice example below, this has been organized properly.
How Should It Really Look?
After coaching Jimmy through my best practices, he re-wrote his script and gave it back to me. Much better! What do you think?
/************************************************
Script Name: CLR_EX.csc
Script Purpose: This script will clear Actual Sales and COGS for New York for the "100-10" product
Date Created: 2012-06-21
Created By: Jimmy Smith (ACME Consulting)
Process:
1. Clear data
************************************************/
FIX(
@CHILDREN("Qtr1"), /* Year */
"Sales", "COGS", /* Measure */
"100-10", /* Product */
"New York", /* Market */
"Actual" /* Scenario */
)
CLEARBLOCK ALL;
ENDFIX /* Year, Measure, Product, Market, Scenario */
Please note that the best practices discussed here are the ones that I have collected over the years that that I personally believe are the best ones. There is no law of the land in this space, but these are the best practices that I drive on all of my projects.
