Oracle EPM - Getting Groovy with Smart Pushes
Smart Pushes are the bread and butter of any modern Oracle EPM deployment. Most setups nowadays have multiple cubes passing data around many times a day. Smart Pushes let you make the updates "live" by tying the execution to a Save action on a form, and you can even define all the intersections you would need to push. Say, for example, that you have a driver-based revenue model. If a user updates one driver, it may impact multiple different intersections in the rest of the model that you would then want to push to your target cube (generally an ASO cube).
My biggest pet peeves with Smart Pushes are:
In building out a new revenue model, I decided I wanted to try and take a crack at building a more dynamic, Groovified means of executing Smart Pushes. This will summarize my results!
First thing's first, in Groovy you can execute a Smart Push with the following logic:
operation.grid.getSmartPush(pushName).execute(optMbrOverride,true)
This command requires the name of the Smart Push (well, really the Data Map being used as a Smart Push) from the form in question. It also allows for an optional member override parameter (more on that later) and an optional database suppression boolean operator (also optional, but defaults to false, I recommend making it true). The first problem you might notice with this approach is that we need the name of the Smart Push passed as a parameter. Hardly dynamic if we hardcode it, right? Don't worry, I have a solution for that as well :)
Let's start with issue number 4 in my list, as that will actually give us some of the more advanced Groovy logic to play with. A Smart Push failing due to pushing too much data can be resolved through using try-catch mechanics and the ability to collate (i.e. break apart) a list. Essentially, what we will do is try executing the Smart Push, but if it fails, catch the error message, collate the Smart Push based on a dimension, and try again. We can loop this as many times as we would want, though I'd recommend no more than three times.
So, how to do that? Well, I want to make this a reusable I can basically copy-paste around my Groovy rules, so I am going to make my own function. Let's start with:
def SmartPushTryCatch(String pushName, Map<String,String> mbrOverride = [:], String collateDim = "", int collateBatch = 0){}
Note our parameters here. As shown above, we must have the Smart Push name, so that is a required parameter. Next, we have an optional parameter of a member override map. It is optional because I have the default value assigned (the = [:] part). Then, we have the optional dimension to collate our list on (default is blank) and the optional size of the batches we will create (default is 0). We will insert the rest of our code in the {}.
When we use a try-catch construction, generally at the end of our looping we want to display an error message, via a throwVetoException() method. To do this, we must create a messageBundle and store the message in it. While we're at it, we'll also add two variables to help with logging, a counter variable and a flag. We do that this way:
//****** Build message bundle so Groovy can throw an error after 3 failures
def msg = messageBundle(["SmartPushError":"Smart Push failed after 3 attempts, please try again later"])
def mbl = messageBundleLoader(["en":msg])
Number SmartPushCount
Boolean SmartPushSuccess
Ok, we've started to lay the framework. Now, we need a new variable, collateMbrs, that will be used to store our broken apart list. Additionally, we want to add a check that looks to see if the collateBatch parameter is greater than 0 and that the collateDim parameter is one that is in the mbrOverride map. that looks like this:
def collateMbr
if (collateBatch > 0? && mbrOverride.containsKey(collateDim)) {}
Ok, now the hard part. When we passed the mbrOverride map, it is in the format of [Dim : Mbrs]. To build our collate list, we need to get just the Mbrs part and break it out into a list that we can collate. Groovy is finicky about the object types in this situation, but basically we are getting the Mbrs (which is a Key object type), converting it to a string, splitting that string on the commas, and adding it to a list object that we can then later use the collate() method on. If that sounds like a lot.... it kinda is. Here's what it looks like:
List<String> collateMbr = new ArrayList<String>(Arrays.asList(mbrOverride.get(collateDim).toString().split(',')))
Once we do that, we have a list of members to break apart if the Smart Push is too big! Now we can get into the push itself! I'll explain this first then post a single big block of code, as it is kinda hard to break out. So we'll start with our new collateMbr list and tell it to collate based on the batch size from the parameters. This will break our list up into smaller lists of that size. We'll then join that smaller list into a string I call collateMbrs which we can put back into our mbrOverride map and try to execute the push. If the push errors our, we can catch the error, wait a few seconds (optional really), and try breaking the list up into smaller groups to try again. Here we go:
领英推荐
collateMbr.collate(collateBatch).each{ mbrs ->
SmartPushCount = 0
collateMbrs = mbrs.join(',')
mbrOverride.put(collateDim, collateMbrs)
try {
SmartPushSuccess = true
SmartPushCount = SmartPushCount.next()
operation.grid.getSmartPush(pushName).execute(mbrOverride,true)
}
catch (oracle.epm.api.model.DataPushException e1){
SmartPushSuccess = false
SmartPushCount = SmartPushCount.next()
println "Smart Push failed on attempt $SmartPushCount ... waiting 3 seconds and trying again."
sleep(3000)
mbrs.collate(collateBatch/2).each{ mbrs2 ->?
collateMbrs = mbrs2.join(",")
mbrOverride.put(collateDim, collateMbrs)
try {
SmartPushSuccess = true
operation.grid.getSmartPush(pushName).execute(mbrOverride, true)
}
catch (oracle.epm.api.model.DataPushException e2){
SmartPushSuccess = false
SmartPushCount = SmartPushCount.next()
println "Smart Push failed on attempt $SmartPushCount ... waiting 3 seconds and trying again."
sleep(3000)
try {
SmartPushSuccess = true
operation.grid.getSmartPush(pushName).execute(mbrOverride, true)
}
catch (oracle.epm.api.model.DataPushException e3){
SmartPushSuccess = false
println "Smart Push failed on attempt $SmartPushCount "
throwVetoException(mbl,"SmartPushError")
}
}
}
}
if (SmartPushSuccess == true){println "Smart Push completed after $SmartPushCount attempt(s)"}
}?
Note the part before the second try{} block where I break up the collateBatch in half. The result of this operation must be an integer, so make sure you either give the collateBatch an even number, or you add in some extra handling for that.
What if you want to have a Smart Push that isn't collated? Well, that's where the if statement from before comes in handy. We can add an else at the end and do a simpler try-catch loop that doesn't worry about collating.
else
//****** Normal Start Smart Push try-catch block ******
try {
SmartPushSuccess = true
SmartPushCount = 1
if (mbrOverride.size() == 0)
{operation.grid.getSmartPush(pushName).execute(true)}
if (mbrOverride.size() > 0)
{operation.grid.getSmartPush(pushName).execute(mbrOverride, true)}
}
catch (oracle.epm.api.model.DataPushException e4){
SmartPushSuccess = false
SmartPushCount = 2
println "Smart Push failed on first attempt, waiting 3 seconds and trying again."
sleep(3000)
try {
SmartPushSuccess = true
if (mbrOverride.size() == 0)
{operation.grid.getSmartPush(pushName).execute(true)}
if (mbrOverride.size() > 0)
{operation.grid.getSmartPush(pushName).execute(mbrOverride, true)}
}
catch (oracle.epm.api.model.DataPushException e5){
SmartPushSuccess = false
SmartPushCount = 3
println "Smart Push failed on second attempt, waiting 3 seconds and trying again."
sleep(3000)
try {
SmartPushSuccess = true
if (mbrOverride.size() == 0)
{operation.grid.getSmartPush(pushName).execute(true)}
if (mbrOverride.size() > 0)
{operation.grid.getSmartPush(pushName).execute(mbrOverride, true)}
}
catch (oracle.epm.api.model.DataPushException e6){
SmartPushSuccess = false
println "Smart Push failed on third attempt."
throwVetoException(mbl,"SmartPushError")
}
}
}
if(SmartPushSuccess == true){println "Smart Push completed after $SmartPushCount attempt(s)"}
}
Don't worry, I'll put the entire script at the end of the post. Plus, we're almost there!
So we've now addressed issues 3 and 4 on my complaints list, and have this pretty SmartPushTryCatch() function we can leverage. Now, I want to make the execution not require me to hardcode in the name of the push every time. I do that through another custom function that will get all of the Smart Pushes mounted to the form and, in the event of multiple, iterate through them. Here's the full function at once since its way shorter (we did the hard part already!)
def ExecuteSmartPush(LinkedHashMap<String,String> overrideLinkedMap = [:], String dimCollate = "", int batchCollate = 0)
def pushList = operation.grid.getSmartPushes()
? HashMap overrideMap = new HashMap(overrideLinkedMap)
if (pushList.size() > 0){
println "There are a total of ${pushList.size()} SmartPush(es) to execute: $pushList"
? ? if (overrideMap.size() != 0){
? ? String overrideString
? ? ? for (dimKey in overrideMap.keySet()){
? ? ? ? overrideString += "Dimension: $dimKey \nMember(s): ${overrideMap.get(dimKey)} \n"
? ? ? }
? ? ? println "Overrides are: \n $overrideString"
? ? }
? ? for (push in pushList){
? ? ? def startTime = currentTimeMillis()
? ? ? println "Executing Smart Push $push"
SmartPushTryCatch(push.toString(), overrideMap, dimCollate, batchCollate)
? ? ? def elapsedTime=(currentTimeMillis()-startTime)/1000
? ? ? println "Total elapsed time for Smart Push execution: $elapsedTime sec"
}
}
? else {
? ? println "No Smart Pushes assigned to form, no data will be pushed."
? }
}
Notice this function has similar parameters to the one above, this is intentional. It is missing the pushName because this function will actually lookup the push names for us based on what is assigned to the grid. Also, you can kind of ignore the whole LinkedMap/HashMap stuff going on... more Groovy object type mumbo jumbo. I added in some extra logging as well that I find helpful, but you can parse that out if you want.
So now we have a smooth way to execute our Smart Pushes! But we still need to handle issues 1 and 2 from my list. Issue 1, making Smart Pushes more dynamic, is pretty easy. All we need to do is use Groovy to build a list of the members that were modified, convert into a clean string, and then pass them into our new ExecuteSmartPush function. Note that you will have to decide what dimension you will check for. This will be based on your business requirements and form design, but for this example I went with Entity. I won't go too much into the details here, but the basic construction would be something like this:
def ChangedList = [] //create a blank list
/*populate the list with modified member names for a given dimension, in this case, entity*/
operation.grid.dataCellIterator({DataCell cell -> cell.edited}).each{
ChangedList << it.getMemberName("Entity")
}
String SEntity = ChangedList.collect{"\"$it\""}.sort().unique().join(",").replace("[","").replace("]","").replace(",\"Entity\"","")
ExecuteSmartPush(["Entity":SEntity], "Entity", 100)
So, that solves the dynamic-ness problem. But what about issue 2, needing to define the intersections on every form? This might not seem as big a deal, but if there are incremental updates to a model, you will have to remember to update every single Smart Push definition, which can be tedious. My little trick here isn't the fanciest, but I like it... use substitution variables! You can store the needed intersections in the subvars and reference them in the rule. The downside is subvars have a character limit of 255, so if you have a lot of long member names, or just a lot of members, you may need to do multiple subvars. My approach is one subvar per dimension per model. This does add a decent amount of subvars, but they will all be right there in a convenient list for you to update. To get a subvar into groovy, you do this:
Cube lookupCube = operation.application.getCube()
String subVar1 = lookupCube.getSubstitutionVariableValue(subvarName).toString()
Then, once you have your subvars all brought in and converted to strings, you can pass them all at once to the Smart Push. It will make the command long, but it works and is consistent!
ExecuteSmartPush(["Account":SAcct,"Category":SCat,"Currency":SCurr,"Data_Type":SDataType,"Entity":SEntity,"Scenario":SScenario,"Version":"Working","Years":SYear,"Period":SPeriod], "Entity", 100)
So that's that! Hopefully this helped someone or gave them some ideas! Oh and as promised, here are both of the custom functions. I put them in a Script object so I can just insert the script into the Groovy rule so it only takes up one line :)
def SmartPushTryCatch(String pushName, Map<String,String> mbrOverride = [:], String collateDim = "", int collateBatch = 0){
def msg = messageBundle(["SmartPushError":"Smart Push failed after 3 attempts, please try again later"])
? def mbl = messageBundleLoader(["en":msg])
? Number SmartPushCount
Boolean SmartPushSuccess
def collateMbrs
? if (collateBatch > 0? && mbrOverride.containsKey(collateDim)) {
List<String> collateMbr = new ArrayList<String>(Arrays.asList(mbrOverride.get(collateDim).toString().split(',')))
? ? println "Smart Push will be collated using the $collateDim dimension with a batch size of $collateBatch"
? ? println "There are ${collateMbr.size()} members to push for the $collateDim dimension.? Given the batch size of $collateBatch, there will be at least ${Math.ceil((double)collateMbr.size().div(collateBatch))} push(es)."
collateMbr.collate(collateBatch).each{ mbrs ->?
SmartPushCount = 0
collateMbrs = mbrs.join(',')
mbrOverride.put(collateDim, collateMbrs)
try {
SmartPushSuccess = true
SmartPushCount = SmartPushCount.next()
operation.grid.getSmartPush(pushName).execute(mbrOverride,true)
}
catch (oracle.epm.api.model.DataPushException e1){
SmartPushSuccess = false
SmartPushCount = SmartPushCount.next()
println "Smart Push failed on attempt $SmartPushCount ... waiting 3 seconds and trying again."
sleep(3000)
mbrs.collate(collateBatch/2).each{ mbrs2 ->?
collateMbrs = mbrs2.join(",")
mbrOverride.put(collateDim, collateMbrs)
try {
SmartPushSuccess = true
operation.grid.getSmartPush(pushName).execute(mbrOverride, true)
}
catch (oracle.epm.api.model.DataPushException e2){
SmartPushSuccess = false
SmartPushCount = SmartPushCount.next()
println "Smart Push failed on attempt $SmartPushCount ... waiting 3 seconds and trying again."
sleep(3000)
try {
SmartPushSuccess = true
operation.grid.getSmartPush(pushName).execute(mbrOverride, true)
}
catch (oracle.epm.api.model.DataPushException e3){
SmartPushSuccess = false
println "Smart Push failed on attempt $SmartPushCount "
throwVetoException(mbl,"SmartPushError")
}
}
}
}
if (SmartPushSuccess == true){println "Smart Push completed after $SmartPushCount attempt(s)"}
}
}
else {
try {
SmartPushSuccess = true
SmartPushCount = 1
if (mbrOverride.size() == 0)
{operation.grid.getSmartPush(pushName).execute(true)}
if (mbrOverride.size() > 0)
{operation.grid.getSmartPush(pushName).execute(mbrOverride, true)}
}
catch (oracle.epm.api.model.DataPushException e4){
SmartPushSuccess = false
SmartPushCount = 2
println "Smart Push failed on first attempt, waiting 3 seconds and trying again."
sleep(3000)
try {
SmartPushSuccess = true
if (mbrOverride.size() == 0)
{operation.grid.getSmartPush(pushName).execute(true)}
if (mbrOverride.size() > 0)
{operation.grid.getSmartPush(pushName).execute(mbrOverride, true)}
}
catch (oracle.epm.api.model.DataPushException e5){
SmartPushSuccess = false
SmartPushCount = 3
println "Smart Push failed on second attempt, waiting 3 seconds and trying again."
sleep(3000)
try {
SmartPushSuccess = true
if (mbrOverride.size() == 0)
{operation.grid.getSmartPush(pushName).execute(true)}
if (mbrOverride.size() > 0)
{operation.grid.getSmartPush(pushName).execute(mbrOverride, true)}
}
catch (oracle.epm.api.model.DataPushException e6){
SmartPushSuccess = false
println "Smart Push failed on third attempt."
throwVetoException(mbl,"SmartPushError")
}
}
}
if(SmartPushSuccess == true){println "Smart Push completed after $SmartPushCount attempt(s)"}
}
}
def ExecuteSmartPush(LinkedHashMap<String,String> overrideLinkedMap = [:], String dimCollate = "", int batchCollate = 0){
def pushList = operation.grid.getSmartPushes()
? HashMap overrideMap = new HashMap(overrideLinkedMap)
if (pushList.size() > 0){
println "There are a total of ${pushList.size()} SmartPush(es) to execute: $pushList"
? ? ?if (overrideMap.size() != 0){
? ? ? ?String overrideString
? ? ? ?for (dimKey in overrideMap.keySet()){
? ? ? ? ?overrideString += "Dimension: $dimKey \nMember(s): ${overrideMap.get(dimKey)} \n"
? ? ? ?}
? ? ? ?println "Overrides are: \n $overrideString"
? ? ?}
? ? ?for (push in pushList){
? ? ? ?def startTime = currentTimeMillis()
? ? ? ?println "Executing Smart Push $push"
SmartPushTryCatch(push.toString(), overrideMap, dimCollate, batchCollate)
? ? ? ?def elapsedTime=(currentTimeMillis()-startTime)/1000
? ? ? ?println "Total elapsed time for Smart Push execution: $elapsedTime sec"
}
}
? else {
? ? println "No Smart Pushes assigned to form, no data will be pushed."
? }
}
Strategic Finance Supervisor at Marathon Petroleum Corporation
2 年Yes! Let's get Groovy with Oracle Smart Pushes!