Copy version and Push data to ASO using Groovy 4

I guess you all might have implemented a reporting application along with the planning application. If you are not, please start doing that from now on.

When you have got a reporting application, you’ll always want both (planning and reporting) to be in sync. Smart Push (on Planning Cloud) lets you sync the data in real-time.

I would also like to point out that you do not use Smart Push if you are moving a large amount of data. You can use data maps to do so. You might ask, “They both move data, so why would it be different?”

Yes, you are right; they both transfer data. However, they both use different mechanisms to move data. Data map uses an export data and import approach, while Smart Push uses a GridAPI approach.

Now that we know we are dealing with a reporting application and data movement let’s get to business.

Here is what I was trying to do. I’m letting the superuser copy data from one version to another. Keep in mind that this is using a DATACOPY script vs. the Copy Version feature.

The user has selected an Entity, and the script is using @RELATIVE function and copying the data.

Now once the copy gets done, I would like to execute a DATA Map with the destination version so that the data is in sync.

Easy Peasy, write a groovy script with member overrides and execute the data map. Hmm, maybe not so.

Let’s look at the groovy rule. I wrote the rule and concatenated Lvl0Descendants with the entity and custom dimension selections.

Arghh, an error!! What is going on?

If you’ve looked at the Data maps, it won’t let you choose a dynamic member from a sparse dimension in the source definition.

With Hybrid in play, this is unavoidable. That is the point of Hybrid right, sparse dynamic parent members.

What should we do now? You are in the groovy world of awesomeness. Your imagination is your limit.

I created a script that expands Level 0 Descendants, then use the resulting members for Data map execution.

Groovy code

/*RTPS: {EntityList}*/
/*RTPS: {ToVersion}*/
/*RTPS: {CustomDimList}*/
/*RTPS: {ToYear}*/

//Defining the variables and storing the RTPs
String sRTPToVersion
String sRTPToYear
/* Get application */
def app=operation.application
/* Get Cube from application by providing the cube name */
def cube=app.getCube("CUBENAME")
/* Get the dimension from application by providing the dimension name */
def customDimDim=app.getDimension("DIMNAME", cube)
def entityDim=app.getDimension("Entity", cube)
/* Get all the members using evaluation and Planning functions */
def usrCustomDimMbrs = customDimDim.getEvaluatedMembers("Lvl0Descendants(${rtps.CustomDimList.toString()})" as String, cube).collect{fixValues(it)}
def usrEntityMbrs = entityDim.getEvaluatedMembers("Lvl0Descendants(${rtps.EntityList.toString()})" as String, cube).collect{fixValues(it)}
sRTPToVersion = rtps.ToVersion.toString()
sRTPToYear = rtps.ToYear.toString()
/* Join the List as a comma seperated string */
def expandedEntityMbrs = usrsEntityMbrs.join(", ")
def expandedCustomDimMbrs = usrCustomDimMbrs.join(", ")
//Data map Rule
operation.application.getDataMap("SmartPushAdhoc").execute(["Scenario":"Budget","Version":sRTPToVersion,"Years":sRTPToYear,"Entity":expandedEntityMbrs,"CustomDim":expandedCustomDimMbrs],true)

println("""The following Entity details were moved to reporting cube: 
		Lvl0Descendants(${rtps.EntityList.toString()})""")
println("""The following Version details were moved to reporting cube:
		${sRTPToVersion}""")
println("""The following CustomDim details were moved to reporting cube: 
		Lvl0Descendants(${rtps.CustomDimList.toString()})""")

We need the application, cube, and dimension information (lines 12, 15, and 18) to make this work. You could ask me that when the user picks an RTP, shouldn’t the RTP be having all this information.

Groovy doesn’t know what the RTP is when it comes to the groovy engine; it treats the RTP value as a string. You need to tell groovy that RTP1 is from Account, RTP2 is from Entity, so on and so forth.

That is why we need application and cube for evaluating (or expanding in this case) the member function (line 22).

The getEvaluatedMembers function returns a List of Member Class, from that list of members, you need to get the name of the members. This is where you can use the “collect” groovy function to get properties from a collection and create another object.

Line 22 is looping through all the Member class items and collecting the Essbase member names as another list of string.

Use a join to convert the list to a comma-separated string and off you go.

Line 33 does the Data map execute operation.

Update 11/18/2019

I did reach out to Venu (the brains behind Data Maps) on the issue with selecting Dynamic parents, and he provided a clever workaround.

The trick is to make Data Map think that a parent is Stored. To do so, you can set the Default Storage as Never Share and change the individual Plan Type storage to Dynamic Calc (which is needed for Hybrid). I didn’t think about that one. I’m still going to leave the Groovy code so that someone else can make use of it.