Groovy Lab – ASO Data Clear – Groovy & REST API

Posted by Jun on August 20, 2022

In an ASO reporting cube, it is important to properly clear data before pushing or copying data. ASO does not work as a BSO cube which has the block concept. In a BSO cube, copying a #missing value is common. But in an ASO cube, it will do nothing when it comes to copying the NULL value. That being said, if you try to copy NULL / #missing to clear data, the exiting value will not be cleared as you expected.

There are multiple ways clear data in an ASO cube, let’s quickly review a few common Planning and Essbase features. Then we will jump into the fun Groovy rules.

Planning features

Data Map Clear

When pushing data from BSO to ASO, there is always an option to let you select clear before push or not. This is a straightforward way to clear ASO when using the data map feature.

Data Clear Job

Setting up data clear jobs would allow us to clear all, all aggregations or partial data. This is MDX-related behind the scenes.

Database Level Clear

ASO data clear can also be achieved via database level clear.

Business Rules – Designer View

Multiplying by 0 is another way to clear data. Remember to merge the data slices after the clear to remove the 0s.

Groovy Rules

Now is the time to check the Groovy way. Using the Groovy rule gives us more flexibility to control what should be cleared, how should be cleared, and when should be cleared.

Traditional Groovy

Groovy provides a clear method to use for clearing ASO cube data.

clearPartialData(java.lang.String mdxRegion, boolean isPhysical)

The simple syntax is

operation.getApplication().getCube(‘CubeName’).clearPartialData(“MDX”,true)

For example, we would like to clear workforce planning, and actual, final data. Let’s prepare the MDX query first.

Crossjoin(Crossjoin(Crossjoin(Crossjoin(Crossjoin(Crossjoin({Descendants([OWP_Workforce Planning – Accounts], [OWP_Workforce Planning – Accounts].dimension.Levels(0))},{Descendants([Entity], [Entity].dimension.Levels(0))}),{Descendants([YearTotal], [YearTotal].dimension.Levels(0))}),{[Final]}),{Descendants([OWP_Total Employees], [OWP_Total Employees].dimension.Levels(0))}),{[FY22]}),{[OEP_Actual]})

Then add this MDX region to the Groovy rule.

operation.getApplication().getCube(‘EPBCS’).clearPartialData(“Crossjoin(Crossjoin(Crossjoin(Crossjoin(Crossjoin(Crossjoin({Descendants([OWP_Workforce Planning – Accounts], [OWP_Workforce Planning – Accounts].dimension.Levels(0))},{Descendants([Entity], [Entity].dimension.Levels(0))}),{Descendants([YearTotal], [YearTotal].dimension.Levels(0))}),{[Final]}),{Descendants([OWP_Total Employees], [OWP_Total Employees].dimension.Levels(0))}),{[FY22]}),{[OEP_Actual]})”,true)

In calculation manager, it will display the following.

Groovy + REST API

Using Groovy and REST API together will give us a more developer-friendly way to control the data clear process.

First, we will need to create a data-clear job.

The use the following URL and payload to make an API call.

{“jobType”:”CLEAR_CUBE”, “jobName”:”ASOClear”}

For example, we can add more details in the payload to overwrite what has been defined in the clear job.

try{

HttpResponse<String> jsonResponse = conFin.post(“/rest/v3/applications/EPBCS/jobs”)

.header(“Content-Type”, “application/json”)

.body(

“””

{

“jobType”: “Clear Cube”,

“jobName”: “Clear REP”,

“parameters”: {

“cube”:”OEP_REP “,

“members”:”Jan”,

“clearSupportingDetails”:false,

“clearComments”:false,

“clearAttachments”:false,

“clearPhysicalOnEssbase”:true

}

“””).asString();

}