EPM RADIANCE

EPM GROOVY – A Guide to Custom Scripting for Partial Data Clearing in ASO Cubes

·

Raja Stalvin

Most of the information in this post will be redundant for people who started working with Essbase prior to the cloud era. The Groovy API provides a mechanism for clearing partial data in ASO straight from Calculation Manager using the Groovy API. Two parameters must be given.

  1. Clear Data Region
  2. Logical or Physical clear

Logical Clear Vs Physical Clear

The data is not removed when using logical clear; instead, it is offset to zeros. Physical clear removes the data from the intersections. As a result, logical clear is always considerably faster than physical clear. We see #Missing (or I may say MISSING in MDX :)) because physical clear wipes off the data.

Define the intersections

It is indeed ASO, thus level zero intersections are needed. Any reference to higher level members, as far as I can tell, does not result in errors either. I’ve been using a basic crossjoin string generator function that produces nested cross joins from multiple sets from different dimensions. The behavior is mentioned in one of my previous post.

Groovy
Cube cube = operation.getApplication().getCube('ASOCube')
String mdxRegion = getCrossJoins([['[No Season]'], ['[FY20]'], ['[No Scenario]'], ['[No Version]'], ['[No Channel]'], ['[Day00]'], ['[No Product]'], ['MemberRange([Feb],[Jan])']])
Boolean physical = true
def startTime = currentTimeMillis()
try {
    cube.clearPartialData(mdxRegion, physical)
} catch (e) {
    println e.message
}
println "Total Time Elapsed : ${(currentTimeMillis() - startTime) / 1000} Secs."

String getCrossJoins(List<List<String>> essIntersection) {
    String crossJoinString
    if (essIntersection.size() > 1) {
        crossJoinString = essIntersection[1..-1].inject('{' + essIntersection[0].join(',') + '}') { concat, members -> "CrossJoin(" + concat + ',{' + members.join(',') + '})' }
    }
    return crossJoinString
}

Execution

It takes a long time to physically clear. I don’t have access to an on-premises instance to run the test on. I asked a few of my friends who use physical clear often. They also notified me of this. Let’s see if the performance is improved in the upcoming updates.

Logical Clear
Physical Clear