Why I created a blog

Its been many years since I first created this blog. It has remained true to Essbase and related information over those years. Hopefully it has answered questions and given you insight over those years. I will continue to provide my observations and comments on the ever changing world of EPM. Don't be surprised if the scope of the blog changes and brings in other Hyperion topics.


Thursday, December 14, 2017

And I quote

Sometimes it is the little things that get you and frustrate you. A few weeks ago, I had a colleague contact me. She was frustrated because she was trying to get a calc script to work and kept getting an annoying error. Below is the text of what she was trying to do.  (I copied it from the email)

Fix(“IT Costs”)

OFS_Calculated = Headcount->”Driver Input”->BegBalace * “IT Costs”->”Driver Rate”>BegBalance;

EndFix

I’m using the following code to identify the Account member that the Smart List ID represents:

@MEMBER(@CONCATENATE(“HSP_ID_”, @Name(@hspnumtostring("BegBalance"->"Driver Type"))))

When I run the rule, I get the “attempt to cross a null member in function [@X]” error. When I hardcode the smart list value for the driver account I want, the rule runs successfully:

I suggested we do an on-line meeting to figure it out. We tried a few things and it looked like the syntax was correct. Then the light went on.  I asked why she was using intelligent quotes.  Intelligent quotes are the ones that bend inward or outward instead of straight up and down ". She told me she had cut and pasted it from a blog she found the syntax on. Yea that's a problem. Essbase does not consider intelligent quotes to be quotes. They have a different ascii value.  I had her change the quotes to straight up and down and the code worked perfectly.   What was s simple issue took a long time to figure out.
 
The morals of the story,
1. Look for the simple issues before you make it harder than it is
2. Remember when you cut and paste, things can be weird. If you have an issue, retype the example yourself in the editor so you know that there are not weird special characters.
 
I've seen similar issues with pasting data values or member names from excel, and word. So look at that first.

Defeating the Phantom Value Menace

Even old dogs can learn new tricks. I was recently teaching a calc script class and was discussing the Clearblock command. I mentioned the options,  All, Upper, NonInput, Dynamic and Empty and was describing what each one did. At this point, one of the students; I'll call her Fred, asked if this would solve her issue. She described the issue, their automation process rebuilds the dimensions and in doing so, quite often moves members from one parent to another. Often all of the children are moved from one parent making that parent now a level 0 member. The member would retain the data and therefor double count it. This is an issue that is called phantom data,  I and other have known about for a long time and I never had a good way to deal with it. She asked if the NonInput option would get rid of the data?  I gave her a great reply "I have no idea. It should work, but I've never tried it". I told her I would test it and get back to her.

I tested it yesterday and it works.  Here are the steps I did to test it. If anyone wants to shoot holes, I am not against it, I am happy for you to tell me what I'm missing.

I took a copy of sample basic and exported the level 0 data. Typically one would just use the calcdata.txt file to load, but that loads all levels so everything would be an input value. I then cleared the database, reloaded the level 0 extract and ran the default calc.  As you can see the result is as expected.  I have data for all of the intersections



Next I went into the outline and removed the children of Colas (100)

 
I saved the outline retaining all data. I then refreshed the data in my spreadsheet.


As you can see, I have phantom data in Colas and the retrieval no longer recognizes its children.

I created the following calc script. Notice I fixed on level 0 members of my sparse dimensions. This way the script would run quickly and only impact the those members that are newly designated level 0 members (by removing their children).  I did the agg at the end so it would propagate the "Fix" up to the ancestors.

     SET UPDATECALC  OFF;
            SET AGGMISSG ON;

      FIX(@relative("Product",0),@relative("Market",0))
           CLEARBLOCK  NONINPUT ;
      ENDFIX

       agg("Product","Market");

I ran the script and verified the results

IT WORKED !!!! Colas data was removed but not any other.  I shared the results with "Fred" and mentioned that were I doing this, I would add this calc right after my  dimension build to clear out the members before a user had a chance to submit data there.

There you have it, a problem I have been struggling with for years, easily solved but a simple question. Thanks "Fred".  Others may have stumbled upon this before, but I've never seen anyone post it.