First of all, I must state that this table and I have major issues. It does not play well with others, especially if something gets out of whack. (of course there’s a story)
A few weeks ago, something happened in one of my client’s environment, and we couldn’t run any WFP rules. I kept getting the following error:
Essbase Error(1023040): msg from remote site [[Fri Nov 2 14:44:15 2018]localhost/HE_EPBCS/OEP_WFSC/epm_default_cloud_admin/Error(1024009) Member HSP_ID_6666 specified for external reference formula does not exist]
I exported the data at level 0, re-imported, and then I would get:
Essbase Error(1023040): msg from remote site [[Fri Nov 2 14:44:15 2018]localhost/HE_EPBCS/OEP_WFSC/epm_default_cloud_admin/Error(1024009) Member HSP_ID_6667 specified for external reference formula does not exist]
I then dubbed these the “devil codes”.
1) for all of the 6s and
2) because they are evil. Pure evil.
Here’s the skinny with EPBCS WFP, if you load Smart List information via Essbase, Essbase converts it to a number and you can kiss your WFP application good-bye.
Here’s what I mean. This is the Planning format to load your workforce data:
SUTA, FUTA, Start Month, Pay Type, those are all Smart Lists in Planning, with textual values. Essbase assigns them a number behind the scenes so it knows how to calculate the values dependent on the lists. It uses the SL_Aliases table to store the numberic values.
When you load your data file, you have two options, Planning or Essbase:
If your file is in PLANNING FORMAT AND has Smart Lists, YOU MUST use the Planning Source Type. There is a known bug (Bug 28891402), that says if you load your planning Smart Lists to Essbase, it will change the super secretive SL_Aliase for that member to a value, and any rules that need that Smart List will no longer work.
If you go to Navigator, Compensation Planning, the very bottom form is called Identify Invalid Data. (I have a love/hate relationship with this as well) This form allows for you to find those members that have numeric instead of textual values so you can clean it up those values.
BUT, here’s the catch. I was not able to clear out the smart list numeric values at the upper level members. I was able to manually fix the issue at level 0 intersections, but not at Total_Entity.
So, what did I do? (I’m glad you asked).
First, this is why it’s so important to have 7 days of backups. If we had had this implemented, we would’ve been able to restore rather quickly. What we found was all of the data in WFP was corrupted. I exported all level 0 data from the other databases. The client had files with current data that he needed loaded to the system. Luckily, he did not need historical forecast data. We had a clean application snapshot, so we fired that sucker up WITHOUT the data. Then we loaded the data files via Planning, and the level 0 export from the other cubes and boom! No more devil errors.
We aren’t even sure how the files ever got loaded via Essbase, but we have learned to never even consider even LOOKING at that little Essbase bubble. Essbase bubble could equal the devil codes.
Good luck!
~Sum
PS. The client has now implmented 7 days of backsup. (hallelujer!)
EPMSxSW 