How to Write Data using Groovy Data Grid?Builder

How to use Groovy Data Grid Builder to save data directly to the target cube

In my previous post, I’ve covered examples of reading data from a particular cube / intersection. Now let’s look at how we can write data to a cube / intersection.

There are two ways to write data back to the cube from a groovy script:

• producing an Essbase script dynamically
• using the DataGridBuilder to write the data directly to the target cube or intersection.

We’ll explore the DataGridBuilder in this post and I’ll cover the dynamic Essbase script generation in later posts.

Data Grid?Builder

The DataGridBuilder is the class used to save data to the cube.

First, we create the DataGridBuilder object

Cube cube = operation.application.getCube("OEP_FS")
DataGridBuilder builder = cube.dataGridBuilder("MM/DD/YYYY")        

With the class object, we then define the grid against which we would be saving the data. This requires the POV, the rows, and the columns.

When setting up the POV, the identification of the dimensions is not necessary. The addPov function just needs a comma-delimited list of members.

The columns are set up by each dimension by providing the member for each column of the grid from that particular dimension. The dimensions are again missing and only the member names are required. So effectively, we would need to call the addColumn function once for each dimension and give a list of members for each column. This is exactly how you would do for a Smart View grid.

As an example, if you needed the Jan, Feb, and Mar periods for the years FY23 and FY24, then you’d set it up with two calls to addColumn.

builder.addColumn('FY23', 'FY23', 'FY23’, 'FY24', 'FY24', 'FY24')
builder.addColumn('Jan', 'Feb', 'Mar’, 'Jan', 'Feb', 'Mar')        

When defining the rows, we would also need to provide the data that will be saved. When creating rows, we need to give two lists to the addRow function. The first is a list of all the row dimension members. In our case, we have just one which is the account dimension. If you have more than one, then provide a member from each dimension making sure that the members are listed in a consistent sequence (the same sequence as the dimensions). The second parameter is a list of values where the total count must be equal to the number of columns.

builder.addPov(rtps.Scenario.member.name, rtps.Version.member.name, rtps.Entity.member.name, rtps.Currency.member.name, rtps.Years.member.name, 'OFS_Direct Input', 'No Product', 'No Market')
builder.addColumn('Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec')
builder.addRow(['OFS_Marketing Events'], [25000, 20000, 30000, 24000, 26000, 28000, 32000, 30000, 28000, 26000, 24000, 22000])
builder.addRow(['OFS_Marketing Campaigns'], [12000, 10000, 15000, 12000, 13000, 14000, 16000, 15000, 14000, 13000, 12000, 11000])
builder.addRow(['OFS_Advertising and Promotion'], [30000, 24000, 26000, 28000, 25000, 20000, 30000, 24000, 15000, 12000, 13000, 14000])
builder.addRow(['OFS_Other Marketing'], [1000, 1000, 1000, 1000, 1000, 1000, 1000, 1000, 1000, 1000, 1000, 1000])        

One important thing to note is that the setPov, addColumn, and addRow functions all take Strings as parameters. When passing the parameters you’ll need to ensure that it is of type String and not Member objects.

Note: I am taking a few RTPs to capture the intersection from the form. This can easily be done by getting this from the grid attached to the operation, but this way the rule can run without being connected to a form.

The data being pushed is just a Collection / List of hard-coded values, but you can of course arrive at the data that needs to be saved based on specific calculation logic.

DataGridBuilder.Status status = new DataGridBuilder.Status()
builder.build(status).withCloseable { grid ->
 println("Total number of cells accepted: $status.numAcceptedCells")
 println("Total number of cells rejected: $status.numRejectedCells")
 println("First 100 rejected cells: $status.cellsRejected")

 // Save the data to the cube
 cube.saveGrid(grid)
}        

After the grid layout definition is complete, a status object is created to hold the results of the submission of data. The status object is passed to the build function. The call to saveGrid saves the data to the cube.

We’re also printing out how many cells were accepted vs rejected.

Data Grid Builder Example on the?Form

I will attach this to the expense direct entry form used here in the example.

Once the rule is triggered (I’ve kept it as a manual trigger), we can see the results on the form.

The DataGridBuilder class can be used to save data to the cube and is a flexible and efficient way to write data to an Essbase cube or intersection.?

By using the DataGridBuilder class, we can easily push data to the cube, which can be especially useful in situations where we need to write back data after performing a calculation in a Groovy script.