Collate and Try catch in Groovy rules to speed up Smart Pushes

I was working at a client who uses a lot of Smart Pushes to move data to the reporting application and as you know Smart Push is really good at replicating the data using the form context.

Now you might ask me what about if we are using allocation and the members we are allocating to are not present in the webform. Here is where overrides come in (and of course, Groovy). 🙂

When Smart Push tries to move a lot of data, it fails. Well, it is not recommended to move a lot of data, you need to use data maps for that. However, data maps cannot be triggered by users (or you might have to create your own custom data maps. I’ll blog about that in a later blog post) and you are stuck with Smart Pushes.

Now, what makes a Smart Push fail. Well, I already stated that it is because of we trying to move a lot of data. Now how are you getting to that “lot of data”. Well, because I’m passing a large combination of members that make up the data.

Hmm, what if we can trigger multiple Smart Pushes still using the large member combination. If you think of this it is was Star Analytics (oh yes, in the earlier times, this was an option people used to use to get data from an ASO cube) was doing. To get to a large data set, split them to smaller sets and stitch them together at the end.

Now, how will we do that with Smart Push. Here comes Groovy and Collate to the rescue.

Collate – and how it works

What is collate?

Let’s say that you got 20 candies and I’m asking you to group them into 4 groups. Now I’m going to add another condition, you must have 6 candies if possible in all groups.

def listNumbers = [1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20]
listNumbers.collate(6).each{
    println it
}

That is exactly what collate does. It groups a group(list) of items into sub groups.

When I ran a collate on the list of numbers (20 of them) and asked it to group them by 6, I got 4 lists with the last one having only two items in it. The result of a collate operation is a List of Lists.

Now that we know the trick of splitting large sets into smaller, let’s go ahead and create that Smart Push Groovy script.

We need to first get the application and cube that we are working on. Since this rule is going to be executed from a form, I’m using operation to get the current application. (line2).

Once we have the application and cube, we can go get the dimensions. (line 5 and 6)

/*RTPS: {Market} {Product}*/
def app = operation.application
def cube = app.getCube("Basic")
def marketDim = app.getDimension("Market", cube)
def productDim = app.getDimension("Product", cube)

I’m going to execute a Smart Push for all the level 0 members of the RTP selected by the user. I can get the level 0 members using the getEvaluatedMembers command. Since that command is going to return a list of Members, I’m collecting each individual member using the fixValues command which gives me the correct Essbase name of each member in the list(unique names with double quotes).

def usrLvl0MarketMbrs = marketDim.getEvaluatedMembers("""ILvl0Descendants(${rtps.Market.toString()})""" as String, cube).collect{fixValues(it)}
def usrLvl0ProductMbrs = productDim.getEvaluatedMembers("""ILvl0Descendants(${rtps.Product.toString()})""" as String, cube).collect{fixValues(it)}

Now that we learned about collate, let’s use it to split the members into groups of 5. We need to get all combinations so I’m going to loop inside a loop as shown below.

usrLvl0MarketMbrs.collate(5).each{ mrktMbrs ->
	def essMrktMbrs = mrktMbrs.join(", ")
    usrLvl0ProductMbrs.collate(5).each{ prdMbrs ->
    	def essPrdMbrs = prdMbrs.join(", ")
        println essMrktMbrs
        println essPrdMbrs
    }
}

You might be wondering why is he printing the members, well that is me making sure that I’m not doing anything unnecessary.

Here is the output of that print statement.

As you can see, you get all combinations of markets and products. It’s time to introduce the Smart Push.

Groovy script with collate

All we are doing there is executing a Smart Push for all the combinations we saw in the previous screenshot. We are also saying use the native row suppression so that only the rows that got data is fetched.

/*RTPS: {Market} {Product}*/
def app = operation.application
def cube = app.getCube("Basic")
def marketDim = app.getDimension("Market", cube)
def productDim = app.getDimension("Product", cube)
def usrLvl0MarketMbrs = marketDim.getEvaluatedMembers("""ILvl0Descendants(${rtps.Market.toString()})""" as String, cube).collect{fixValues(it)}
def usrLvl0ProductMbrs = productDim.getEvaluatedMembers("""ILvl0Descendants(${rtps.Product.toString()})""" as String, cube).collect{fixValues(it)}
usrLvl0MarketMbrs.collate(5).each{ mrktMbrs ->
	def essMrktMbrs = mrktMbrs.join(", ")
    usrLvl0ProductMbrs.collate(5).each{ prdMbrs ->
    	def essPrdMbrs = prdMbrs.join(", ")
        operation.grid.getSmartPush("Push2ASO").execute(["Market":essMrktMbrs,"Product":essPrdMbrs], true)
    }
}

That is all well and good. However, what if that fails. That where catching an exception and working on the exception comes into play.

Try….Catch to the rescue

You can multiple exception using try {} catch {} blocks. In our case, we are looking for DataPushException. We should report every other one.

So here is how we’ll do that for the Smart Push operation.

try{
	operation.grid.getSmartPush("Push2ASO").execute(["Market":essMrktMbrs,"Product":essPrdMbrs], true)
} catch (oracle.epm.api.model.DataPushException e){
	mrktMbrs.collate(2).each{ mrktMbrs1 ->
		essMrktMbrs = mrktMbrs1.join(", ")
		prdMbrs.collate(2).each{ prdMbrs1 ->
			essPrdMbrs = prdMbrs1.join(", ")
			operation.grid.getSmartPush("Push2ASO").execute(["Market":essMrktMbrs,"Product":essPrdMbrs], true)
		}
	}
}

When the Smart Push fails with a DataPushException which is raised for large data sets we catching it. Afterwards we are reducing the set that reported the error into a further smaller set (5 to 2) and executing Smart Push on that smaller set.

Isn’t it wonderful to perform all this using a calc.

Here is the full script.

/*RTPS: {Market} {Product}*/
def app = operation.application
def cube = app.getCube("Basic")
def marketDim = app.getDimension("Market", cube)
def productDim = app.getDimension("Product", cube)
def usrLvl0MarketMbrs = marketDim.getEvaluatedMembers("""ILvl0Descendants(${rtps.Market.toString()})""" as String, cube).collect{fixValues(it)}
def usrLvl0ProductMbrs = productDim.getEvaluatedMembers("""ILvl0Descendants(${rtps.Product.toString()})""" as String, cube).collect{fixValues(it)}
usrLvl0MarketMbrs.collate(5).each{ mrktMbrs ->
	def essMrktMbrs = mrktMbrs.join(", ")
    usrLvl0ProductMbrs.collate(5).each{ prdMbrs ->
    	def essPrdMbrs = prdMbrs.join(", ")
		try{
			operation.grid.getSmartPush("Push2ASO").execute(["Market":essMrktMbrs,"Product":essPrdMbrs], true)
		} catch (oracle.epm.api.model.DataPushException e){
			mrktMbrs.collate(2).each{ mrktMbrs1 ->
				essMrktMbrs = mrktMbrs1.join(", ")
				prdMbrs.collate(2).each{ prdMbrs1 ->
					essPrdMbrs = prdMbrs1.join(", ")
					operation.grid.getSmartPush("Push2ASO").execute(["Market":essMrktMbrs,"Product":essPrdMbrs], true)
				}
			}
		}
    }
}