PBCS Data Map / Smart PushHas Data volume Limits

IntroductionWhen moving data in PBCS with Data Maps or Smart Pushes, theyhave limits on how much data can be moved. The amount of datacan be seen in the logs, and look something like this.

Failure

Exporting data…Exported data file(s) size is: 207.1 MB.Push Data failed. Error: Exported data size of data map thatis being executed from groovy is more than permissibleamount: 100 MB.

Success

Exported data file(s) size is: 464.7 MB.EXPORT elapsed time: 39584IMPORTING – AppName: AreakFinTRANSFORM elapsed time: 63634IMPORTING elapsed time: 21166TOTAL elapsed time: 124553

Prior to the Feb, 2018 release, the following did not alwayshold true. If you are/were seeing inconsistencies, see BugReport: Push Data failed. It also includes information abouthow the data cap works, as it is different between Data Mapsand Smart Pushes, which is worth reading.

Data Movement Limits IdentifiedI got the following information from Oracle, and it is useful

if you are using the data movement functionality. When theseare developed, it is a good idea to evaluate the size and planfor growth. If the production data movements are nearing thethresholds, it is recommended to be proactive and try toreduce the POV that is used to move the data. If it can’t bereduced, one option is to split it into multiple pushes whichcan be done with Smart Pushes on the Data Form save, or withGroovy. Groovy also allows you to further condense the POV bydynamically changing the POV based on the cells edited, whichis the most productive and efficient way to handle these.

So, here is what was documented. The data limits imposed onthe movement methods are below.

There is not a cap when running a Data MapWhen executing the following, there is a cap of 100MB

Smart Push on a Data FormSmart Push via a Groovy CalculationData Map via a Groovy Calculation

SummaryIf you are not seeing this, I would recommend opening a ticketwith Oracle to resolve. I will be writing a post explaininghow to execute and override POVs in Smart Pushes and Data Mapswith a Groovy Calculation in the near future, so look for anarticle in my Adventures in Groovy series.

Adventures in Groovy – Part

7: Validating Run TimePrompts

IntroductionWhen developing applications in PBCS, there are times when weneed to request information from users. This is especiallyuseful in workforce application, but certainly not limited tothem. With Groovy, we can take validation to the next level. We can compare inputs to each other, we can limit input, andwe can now stop the execution of the calculation until theinputs are validated. The difference now is that we have theability to keep the prompts open, and force the user to eitherenter valid data, or cancel the request.

Validating Hire Date / Termination DateUse CaseLet’s start with a very simple example. Let us assume we areadding a new employee, and we are asking for a hire andtermination date. A real-life example would require moreoptions, like a name, title, union, and other requiredinputs. To simplify this tutorial, we are only entering thehire and termination dates to prove out the validation andshow functionality of the Groovy RTP validation logic.

When a user enters a termination date after a hire date andlaunches the rule, it validates and executes the rule.

When the job is opened in the job console, we see the RTPs and

the values entered, and the Job Status is selected, the logshows the values returned to Essbase.

When a user enters a termination date prior to a hire date andlaunches the rule, it an error is returned and the RTP windowdoesn’t go away. At this point, the user has to correct theerror, or cancel the execution of the business rule.

In this case, the log shows the business rule failed.

CodeThere are multiple objects that are used to accomplish RTPValidation. The code that processed the above action is thefollowing.[crayon-61b5b15eeb37e666458845/]

rtps objectCreating RTPs in Groovy was covered in the previous article. If you haven’t read that, it would be a good time to take alook, as it explains the basic of this object. Expanding onthe use of the object, we are using some additional methods. This object has many, including returning the input asboolean, double, strings, dates, members, and smart lists, toname a few. In this example, we are using getEssbaseValue,which returns the value sent to Essbase and stored. If therewas a need to compare date ranges, we could have used thegetDate, and expanded on this with the Groovy date functionsto get the days, months, or years between the entered values. In this simple example, we just want to make sure the termdate is greater than the hire date.

messageBundleThe first thing that is requires is to create a messageBundleand messageBundleLoader. These two objects work together tohold the errors, the error messages, and multiple languages,if required.

The messageBundle is a map that holds all the errors (name anddescription). In this example, we only have one error, butmore can be added and separated by commas. ThemessageBundleLoader holds all the messageBundle objects thatrepresent the different languages.

throwVetoExceptionWhen an exception is found, executing this method willinitiate an error and cause the RTP validations to fail. Thismethod requires the messageBundleLoader, and the error to bereturned to the user.

Other Use CasesBy now you are probably already thinking of other uses of

this. I can see limiting growth rates, confirmingcombinations of RTPs (like not allowing salaried people in aunion), ensuring that a new employee doesn’t have a hire dateprior to the current date, and probably hundreds of other waysto use this.

If you would like to share an idea, please post a comment!

ConclusionBeing able to validate user input prior to executing acalculation and returning the input request to the user is huge step forward, and just another benefit of Groovycalculations. We can reduce the number of user errors andimprove the user experience.

Bug Report: Push Data failed.Error: Exported data sizeviolates permissible amount:100 MB

IntroductionData Map Error:

Push Data failed. Error: Exported data size of data map thatis being executed from groovy is more than permissible amount:100 MB.

If you are confused, join the club. The results areinconsistent as some data pushes are successful that are over

the 100MB limit. So, why the following error?

Exporting data…Exported data file(s) size is: 207.1 MB.Push Data failed. Error: Exported data size of data map thatis being executed from groovy is more than permissibleamount: 100 MB.

ClarificationA point of clarification for those of you who are new to datamaps and smart pushes. If you think they are the same thing,here is the clarification from Oracle, in my words.

A Data Map is any data map executed from the Data Maparea, whether it is through the UI, EPM Automate, or theREST API.A Smart Push is essentially any Data Map executed from aData Form.

Although they seem like the same function, they have differentlogical areas in execution. My understanding is that a DataMap should never hit a cap on memory. A Smart Push does havea cap. Not only that, the way it was explained to me is thatthere is a hard cap on how much memory Smart Pushes canconsume, and this is a global limit, not a limit per SmartPush. So, the reason you are experiencing inconsistentresults with Smart Pushes is quite simple. The more SmartPushes that are executed in a time window, the more memory isused. So, you may never have a problem in a Test, or atnight, but during UAT or in Prod, successful execution may beintermittent. The reason is when these are run periodically,that limit may never be reached. Run multiple times bymultiple people in short durations will cause the limit to beconsumed.

This bug only applies to Data Maps.

The ProblemThe same Data Map executed results in two different outcomes.

Failure

Exporting data…Exported data file(s) size is: 207.1 MB.Push Data failed. Error: Exported data size of data map thatis being executed from groovy is more than permissibleamount: 100 MB.

Success

Exported data file(s) size is: 464.7 MB.EXPORT elapsed time: 39584IMPORTING – AppName: AreakFinTRANSFORM elapsed time: 63634IMPORTING elapsed time: 21166TOTAL elapsed time: 124553

So, if there is a cap at 100MB, what gives? If you have seenthe following error, and wondered why the same Data Mapsometimes runs and sometimes fails, it is related to Bug27161430.

The FixAlthough support was difficult to navigate, I was lucky enoughto be at an Oracle session in Virginia and talked to adeveloper. He immediately requested the ticket number andsaid flat out, this is a problem. I don’t want to name names,so a huge thank you to an unidentified developer at Oracle forgiving me a few minutes and helping, because I don’t believeit would have been escalated to the development teamotherwise.

The ticket was updated yesterday, and the fix is slated to be

released in February. Although this is an internal bug, hereare the details.

Bug 27161430 – PBCS: EXPORTED DATA SIZE OF DATA MAP THAT ISBEING EXECUTED FROM GROOVY IS MORE

Adventures in Groovy – Part6: Converting a POV into aFix

IntroductionOne of the fundamental features of a Groovy calculation is theability to dynamically grab aspects of a grid, and getting thePOV is something that is required to dynamically generate anEssbase calculation. There are times when the entire POV isrequired, times when only members from specific dimensions areneeded, and situations where only the rows and columns of theedited cells are used to construct effective fix statements. All 3 of these situations will be discussed.

Use Case (Pulling POV Members)Many times, the Fix statement of a calculation being builtincludes all members in a data grid POV.

Code[crayon-61b5b15eebf0b560741304/]

Breaking It DownThe operation.grid.pov provides access to all the dimensionsin the POV. From that, dimension names, member names, andother properties that are useful, can be accessed. Whenfollowed by a *, it returns all the dimensions in the POV to alist. Using the essbaseMbrName instructs the function toreturn every member in the POV. The povMemberNames variablestores a list of all those values.

When building the calcScript variable,${povMemberNames.join(‘”, “‘)} will return the list,delineated with “,”. This would return something likemembername1″,”membername2″,”membername3. This obviously ismissing surrounding quotes, which is why it is embedded insidethe quotes.

Use Case (Pulling Selective DimensionMembers From The POV)Pulling one, or all of the dimension values in a POV, inindividual variables for later use, provides the ultimateflexibility. The following pulls all the members and storesthem in a unique variable. Then, any dimension in the POV canbe accessed. An easy way to accomplish this is to use thefind method of a List object to filter the elements.

Code[crayon-61b5b15eebf12550620940/]

Breaking It DownThe first line stores the entire POV collection. Thatvariable can be accessed and used like any Groovy collection. By using the find method, items in the collection can befiltered. povmbrs.find {it.dimName ==’Years’} will return theYears dimension object and one of the properties of thatobject is the essbaseMbrName. Using it will return an Essbase

friendly member name.

The “fixValues” method converts strings to “Fix friendly”strings that can be used in an Essbase calc script. Any ofthe following objects can be inserted into this method.

AttributeDimensionAttributeMemberDimensionMemberPeriodDimensionRtpValueYearDimension

The result is the dimension member name inside a fixstatement.

Rows/ColumnsSome of the real efficiencies with Groovy stem from the factthat the edited cells can now be determined and a Fixstatement can dynamically be generated to isolate longerrunning calculation on only the rows and columns that havechanged. In the following example, there are two methods toget the members.

Code[crayon-61b5b15eebf14697286840/]

Breaking It DownIf you are unclear about how to iterate through a grid, readAdventures in Groovy Part 3: Acting On Edited Cells. Insidethe dataCellIterator, the example assigns two list objectswith the respective members. Since this only touches thecells that have been edited, only the periods and productsthat have been updated will be stored in the respectivevariables.

You may be wondering if this is truly the most isolatedcombination of data. Theoretically, the same product maynot have the same edited months. You could further condensechanges in the Fix statement by looping through the rows andcreating a fix for every row. In this example, Period isdense, so doing this would not change the number of blocks. Depending on the application, taking this extra step mightprove more efficient. Rather than complicate this further,we are assuming this isn’t required.

Every required dimension (Account, Entity, Period, Scenario,Version, and Year) has its own method. cell.periodName returnsthe Period member of the cell. All the dimensions have thesame naming convention.

For custom dimensions, getMemberName can be used with therequired dimension passed as a parameter. If you wantconsistency, this method can also retrieve the 6 requireddimensions. cell.getmemberName(“DimensionName“), whereDimensionName is an actual dimension, returns the respectivemember in that dimension.

Just like the previous example, add the variable to the Fixstatement with a join and it returns the delimited list ofmembers that have been edited.

“${periods.join(‘”, “‘)}” returns the list of Periodsthat have edited cells, and“${products.join(‘”, “‘)}” returns the rows (orproducts) that have been edited.

Wrapping UpOne last step that can be added, and should be, is the checkto see if any cells have been modified by evaluating the sizeof one of the lists that is created during the griditeration. After the iteration is completed, the followingcan be added to accomplish this. If there are no cells

edited, the calculation is stopped at the return line andnothing is sent back to Planning/Essbase to execute.[crayon-61b5b15eebf16259278382/]Joining the examples above, the fix would look like this.[crayon-61b5b15eebf1f706048456/]

ConclusionIf you implement this, you will likely see huge performanceimprovements. In the client production applications, I haveimplemented this in, I see at least a 95% improvement inperformance. This same logic can be used for Data Maps anddata movements from ASO to BSO (which we will cover later)

Adventures in Groovy – Part

5: Accessing SubstitutionVariables

IntroductionAccessing Substitution Variables is critical in mostcalculations, and accessing them in Groovy is a little morecomplex than it needs to be with not having an API to getthem. Since the SubstitutionVariable is not available, thereare a couple ways to get them. The precursor to this post isthree-fold.

Read the Bug Report: Groovy SubstitutionVariable1.Class Not Functioning post on Jan 8, 2018 regarding theSubstitutionVariable class availability.Thanks to Abhi for providing a great alternative.2.It may be helpful to read Adventures in Groovy Part 4:3.Run Time Prompts to understand how to access RTPs in aGroovy calculation.

In my bug report above, I suggested grabbing them via a hiddencolumn or row from a form. A reader suggested a another wayto do this, and I think it is a better way to accomplish it. Rather than grabbing the substitution variable by adding it tothe form and hiding the column/row from the user, Abhiprovided a much cleaner approach to working around not havingaccess to the SubstitutionVariable class by using hidden RTPs.

Create Run Time Prompts to AccessSubstitution VariablesAssume the following 3 variables are required in businessrules. Create a new RTP for each. The naming convention isirrelevant, but should be considered and be consistent foreasy reference in the business rules. In this read, I have

assumed there isn’t an existing RTP with the defaults set to asubstitution variable. Even if there is, it might bebeneficial to create ones specifically for this need so futurechanges don’t impact the values.

Name: subVar_CurMonthType: MemberDimension: PeriodDefault Value: &v_CurMonthRTP Text: N/A

Name: subVar_CurYearType: MemberDimension: PeriodDefault Value: &v_CurYearRTP Text: N/A

Name: subVar_BudYearType: MemberDimension: PeriodDefault Value: &v_BudYearRTP Text: N/A

Business Rule InclusionInside the business rule, the following convention is requiredto add the variables.[crayon-61b5b15eec704778074956/]Set all the RTPs in the Variables tab to set to hidden so theuser isn’t prompted for these. Now, the substitutionvariables can be referenced.[crayon-61b5b15eec70e212217620/]

ConclusionSince these are likely to be used in many rules, it would bebeneficial to add these to a script and embed that script intothe rules that need to access these. Any new variable that

needs to be included can be added to the script, and all thebusiness rules would then have access to them. There are anumber of ways to do this with Groovy calculations, but thesimplest way is to embed it like a non Groovy business rule. This can be dragged from the left pane, or entered manually. The syntax is[crayon-61b5b15eec710934323247/]If and when Oracle releases the class that provides directaccess to sub vars, expect it to be documented here.

Easy Way to Randomize Data inPBCS

IntroductionWhen an application is used to demonstrate sensitiveinformation, a training class includes people that shouldn’tsee live data, or security is being tested, often times usingreal data is not an option. I have written PowerShell scriptsand .NET applications to randomize data from Essbase exports,as well as Custom Defined Functions to randomize withcalculations. PBCS just made it much easier. Using oneEPMAutomate command all the data in all the applications canbe randomized. We don’t have the ability to control what ischange, which would be a nice addition as I always excludedanything greater than 0 and less than or equal to 1 in aneffort to exclude rates and things like a boolean true/false,or headcount. But, the ability to do it in one command ismighty appreciated!

Masking Data in PBCSIn a recent update to EPM Automate, Oracle introduced a newmethod – maskData. It can’t get any easier to use. Once youlog in to EPM Automate, simply run maskData.[crayon-61b5b15eecd94067547555/]When this is executed, it will ask you to confirm that youwant to randomize the data in the application. If you want tobypass the confirmation, add -f after maskData and it willforce the command to run without confirmation.

Thank You Mr. ObviousBe very careful using this! Make sure you are logged on totest, and not production!

Bug Report: GroovySubstitutionVariable ClassNot FunctioningIf you have jumped into Groovy Calculations, one of the thingsyou likely would try to do is grab a value for a sub var. Hopefully, you haven’t spent too much time before readingthis. I wasted a ton of time trying to get this to workbefore I opened a ticket with Oracle. This class is NOTavailable yet and was inadvertently included in the publicdocs at https://docs.oracle.com/cloud/latest/epm-common/GROOV/. The development team told me they are going toremove it from the API docs.

Without it, the best way I have found to get this value is byadding it to a grid and pulling the dimension value from that

column/row. For example, if your periods are in the columnsand you are need the value of a substitution variable thatholds the current month, add the substitution variable to thefirst column for the variable that holds the current month ofactuals, and hide the column so the users are not confusedwith its purpose/location. If you make use ofgetCellWithMember, and don’t pass it any parameters, it willpull the top left cell in the grid, even if it is hidden. Since this is a period member, use the getPeriodName method. If it is a custom dimension, the getMemberName will providewhat you need.

Here is an example. The grid’s first column is hidden, andthe period is set the substitution variable that representsthe last month of actuals for the year.[crayon-61b5b15eed48a682911064/]The sCurMonth variable can be used where needed in the Groovycalculation to obtain the substitution variable value.

Adventures in Groovy – Part4: Run Time Prompts

IntroductionGroovy provides a very easy way to interact with the user viarun time prompts, or RTPs. These can be linked to dimensions,members, and input. One of the huge benefits of getting RTPsin Groovy is that the result can be validated, and thecalculation can be cancelled if they don’t validate (we willtouch on this in a future post).

The SolutionThis is one of the easier things to do with a Groovycalculation. There are two things required. First, theGroovy calculation must prompt the user to select a value. This is done by doing the following.[crayon-61b5b15eeda9e447116451/]At any point in the script after the above, the value can beused. If it is going to be used multiple times, it might beeasier to set a variable. Regardless of the approach, thevalue can be referenced using the rtps object as follows.[crayon-61b5b15eedaa4832065930/]That is all that is required!

ConclusionBeyond the obvious uses of an RTP, I have started using thesefor a number of other reasons.

On global forms where multiple values may be changedthroughout a short period of time and execute longrunning calculations, like allocations, I have seenbenefits of prompting a user with a yes/no smartlistRTP. If the user has more changes, they may not need toexecute the calculation after every save. This givesthem the option.If there is a requirement where some prompts aredependent on other prompts, using RTPs in Groovy givesyou the flexibility to validate the combination. Forexample, if an employee is set to hourly with a VPtitle, the prompts can be validated and returned to theuser as invalid combinations before the prompts areremoved from user view.