I am sure most of us are familiar with the conventional data export from Essbase using the DATAEXPORT option. However, we cannot perform a precondition to export only the required data. But this doesn’t allow us to perform a conditional check to see whether we are exporting valid data or not. In short, we cannot run an IF condition in a DATAEXPORT method. We can discuss in detail how Groovy can help us verify the data before exporting it to a file. We are using Groovy to create the grid and export it to a file in the inbox and outbox directory. This option cannot be used for a very large set of data.

Data Export without condition check:

The EPM Groovy API provides a direct method to export the data using the exportDataToFile method. The function comes with an optional file type extension. If none is provided, it exports as a CSV file. The exported file format is not the same as the Outline Load utility’s exported file format.

Specify Grid Members:

def povDims = [ 'Scenarios', 'Versions', 'Channel','Metrics','Years']
def povMbrs = [['No Scenario'], ['No Version'], ['No Channel'],['Sales Retail'],['FY20']]
def colDims = ['Periods']
def colMbrs = [ ['BegBalance'] ] 
def rowDims = ['Products']
def rowMbrs = [ ['ILVL0DESCENDANTS("Total Product")'] ]

Add the members to Grid Definition Builder:

Cube cube = operation.application.getCube("MFPPlan")
DataGridDefinitionBuilder builder = cube.dataGridDefinitionBuilder()
builder.addPov(povDims, povMbrs)
builder.addColumn(colDims, colMbrs)
builder.addRow(rowDims, rowMbrs)
DataGridDefinition gridDefinition = builder.build()

Export the data

cube.loadGrid(gridDefinition, false).withCloseable { grid ->
	operation.exportDataToFile(grid, 'fullGridExport.csv')
}

Verify the file from Inbox/outbox explorer

Data Export with condition check:

We have about six products which have been exported from the system with various ranges of data. I am going to filter only the products where the data is greater than 2000. As we have already created a definition builder in the previous step, I am going to reuse it to build my filtered grid.

Initialize filtered Grid:

DataGridDefinitionBuilder filtBuilder = cube.dataGridDefinitionBuilder()
filtBuilder.addPov(povDims, povMbrs)
filtBuilder.addColumn(colDims, colMbrs)

Add only the rows which are true for the condition

cube.loadGrid(gridDefinition, false).withCloseable { grid ->	
    def filteredRows = grid.rows.findAll{it.data.findAll{it.data > 2000}}
    filteredRows.each{	
    	def rowMembers = it.headers*.collect{DataGrid.HeaderCell mbr -> mbr.MbrName}
    	filtBuilder.addRow(rowDims, rowMembers)    
	}     
}

Export the data to File

DataGridDefinition filtGridDefinition = filtBuilder.build()
cube.loadGrid(filtGridDefinition, false).withCloseable { grid ->
	operation.exportDataToFile(grid, 'filteredGridExport.csv')
}

Verify the file from Inbox/outbox explorer