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.