Recently, a very typical request from business asking for the YTD data export to a file for an external integration. Although it seemed like a very simple request, the challenges we ran into are described below.

1. YTD is a dynamic calc member
2. It is exclusively calculated in the ASO cube
3. It took forever to export a dynamic member using data management

We have decided to try with two options:

1. In the BSO cube, create a new store YTD member, then calculate and export the data.
2. Use the groovy exporter to export the periodic values as well as to export and update data to YTD.

Because I hadn’t yet looked at Groovy’s transformer function, we went with option 1. I recently looked at option 2, and I wanted to let you know how simple it was to make any changes to the data set.

Use Data Exporter

In one of our earlier post, we also leveraged data exporter. I wanted to export level 0 data to a file with only a few row filter conditions. As you can see from the example below, tranformPeriodcToYTD is a custom closure that helps us transform the data. Closures in Groovy must first be defined before they can be used.

cube.createDataExporter()
.setColumnMemberNames(periods)
.setRowFilterCriteria("BaseData","FY21","Plan","Working")
.setDataExportLevel(DataExportLevel.LEVEL0)
.exportDataToFile( "LevelYTDExport.csv", transformPeriodicToYTD)

Assume that transformPeriodToYTD receives the individual data components for each row and processes them one at a time.

Closure transformPeriodicToYTD = { RowData rowData ->	
	Map<String, String> transformedValues = [:]
	double ytd = 0.0      
	rowData.valuesMap.each { String member, String value ->
		if (value && value != '#Mi') {
			ytd += value.toDouble()        	
		}        
		transformedValues[(member)] = ytd.toString()
	}    
	[createRowData(rowData.rowTuple, rowData.columnMembers, transformedValues)]
}

Execution And Validation

Within a second, the script was executed. 1600 rows of data were converted and exported. The performance for the big data set is something I’m not sure about.

The file was generated in the inbox/outbox explorer.

Validation

This example can be used in numerous areas and is not just for YTD calculations. I hope you find this information useful.