Adventures in Groovy – Part 28: The DataGridBuilders
Gridbuilders are the link to moving data from BSO to ASO, and ASO to ASO. Gridbuilders can also be used to save calculated results that are executed directly in Groovy. If it jumped into your head, yes, you can completely bypass the Essbase calculation engine. I have a coworker that has done exactly that – everything in Groovy and had all the forms connected directly to ASO! There is an example of this in my presentation at KScope, Last Minute ODTUG Planning Sourvenirs. Before we get too far ahead, back to the basics.
Welcome to Grids
Creating grids is the same as creating a Smart View report. They have POVs, column and row headers, and of course, the data grid.
Gridbuilders come in two forms.
- The DataGridDefinitionBuilder is used to read data.
- The DataGridBuilder is used to submit data.
The later can use functions like ILvl0Descendants and can suppress missing rows. Both can return status information about the volume of data the grid retrieves/submits.
The DataGridDefinitionBuilder
The first grid discussed will be the reader grid. This grid can be used in processes to get currency tables, read data to be used in calculations, or make decisions on the logic to execute. It can also be used to be the source of data moved to another location or plan type. Try not to make this too complicated. It really is exactly the same as a Smart View adhoc retrieve.
The first step is to create a connection to the cube, or plan type, and initiate the GridDefinitionBuilder object.
// Setup the connection to the plan type
Cube cube = operation.application.getCube("Plan1")
//Create the grid definition builder
DataGridDefinitionBuilder builder = cube.dataGridDefinitionBuilder
Now that the object is created and connected to a source, the next step is to setup the grid. This can be done in any order that makes sense to you because it isn’t used until the grid is built. Personally, I start with the POV. If you understand collections, particularly lists, this will seem pretty simple. If you don’t know what Groovy lists are, read Part 27: Understanding Collections. Creating the POV requires two parameters. The first is the list of dimensions. The second is the list of members in the corresponding dimensions. The first parameter is one list (a comma delimited list inside brackets). The second parameter is a list of lists. I will explain why a little later. Each sub-list has only one member, but it is still a list. The format of this is [[member1],[member2]]. So, it is a comma delimited list of embedded lists. It is important to understand this. If you are just replicating this example, you are doing yourself an injustice because you will struggle when you start throwing in variables and trying to create efficient ways to create these based off of a POV of the grid the user interacts with.
// Add the POV – Dimensions and a collection of the members in those dimensions builder.addPov(['Years', 'Scenario', 'Currency', 'Period', 'Version', 'Entity’], [['FY16'], ['Current'], ['Local'], ['BegBalance'], ['BU Version_1'], ['No Entity’]])
The next thing I do is create the columns. Again, understanding lists is critical to become efficient with these classes and methods. I will sound like a broken record, but I wish I had somebody tell me this 6 months ago. The column requires the same two parameters in the same formats. The first is a list of the dimensions. The second is a list of the lists of members associated to those dimensions. The difference from the POV is that multiple members can be added to each dimension. It might be more clear why the second parameter is a list of lists, now. If it wasn’t, distinguishing which members related to which dimensions would be a jumbled mess with just a comma delimited list of members. Just like with Smart View, columns can have multiple headers.
// Add the columns – 2 parameters, collection of dimensions and // collection of collections of members in those dimensions builder.addColumn(['Years', 'Account’], [['Children(Years)'],['Min Salary','Max Salary','Min Bonus','Max Bonus']])
The rows are identical to the columns, so I won’t repeat the narrative above.
// Add rows no data - in this class as it is retrieving data
builder.addRow(['Grades'], [ ['ILvl0Descendants("Grades")']])
Before we proceed, the addColumn and addRow methods can be replicated multiple times (you know this as segments in developing data forms).
Now that the grid is constructed, execute the build method to create the object with all its properties.
// Build the grid DataGridDefinition gridDefinition = builder.build()
Finally, load the grid, which is the equivalent of refreshing a Smart View template.
// Load a data grid from the specified grid definition and cube DataGrid dataGrid = cube.loadGrid(gridDefinition, false)
At this point, the grid acts just like a data form. All the methods and classes are available just as they are on forms. These grids can be iterated, just like web forms.
The DataGridBuilder
The DataGridBuilder is the class used to submit data to the database. The construction and use is similar to the DefinitionBuilder, except the use of functions is not relevant, and rather than loading data to the grid, the data is saved to the database.
// Setup the connection to the plan type
Cube cube = operation.application.getCube("Plan1")
DataGridBuilder builder = cube.dataGridBuilder("MM/DD/YYYY")
What we are creating below would look like this if it was done in Smart View.
Setting up the POV is similar to above, except this time there is no requirement to pass a list. Also, the identification of the dimensions is not necessary. All that is needed is a comma delimited list of members.
// Setup the grid POV, Rows, and Columns
builder.addPov('Salary', 'Current', 'Local', 'BU Version_1’)
Setting up the columns is also slightly different. The dimensions are again missing and the members are all that is required. Also, there is a line for each dimension rather than passing it in lists as multiple parameters. This is exactly the same as setting up a Smart View template. Each column has to be set. The example below has 3 columns made up of the year and period dimensions.
builder.addColumn('FY16', 'FY16', 'FY16’)
builder.addColumn('Jan', 'Feb', 'Mar’)
The rows are similar in that there is a slight difference from the DefinitionBuilder, but it does require two parameters. Each is a list. The first includes the members of the dimensions in the row headers. The second are the corresponding values to the column headers above. The following adds the members from the department and employee, and sets the values for Jan through March of FY16.
// Add rows to the grid builder.addRow(['Department 1', 'Employee 1'], [30000, 30000, 30000]) builder.addRow(['Department 5', 'Employee 2'], [40000, 40000, 40000]) builder.addRow(['Department 1', 'Employee 3'], [30000, 30000, 30000])
Once the grid is laid out, and this can be done before the grid is defined, a status object is created to hold the results of the submission of data.
// Create a status class to hold the results DataGridBuilder.Status status = new DataGridBuilder.Status()
At this point, the grid can be built. The difference with this object is that the status object is passed. That status object can be used to evaluate if records will be submitted and if any will be rejected.
// Build the grid – basically a refresh/retrieve
DataGrid grid = builder.build(status)
println("Total number of cells accepted: status.numAcceptedCells")
println("Total number of cells rejected: status.numRejectedCells")
println("First 100 rejected cells: status.cellsRejected")
Finally, save the grid to submit the data to the database.
// Save the data to the cube cube.saveGrid(grid)
Finish Up
This all seems pretty simple. I can’t say it enough. Spend some time understanding Groovy collections, especially how to manipulate them. I guarantee the time you spend will be time well spent. I will be posting another article on how to diagnose issues and troubleshoot grid errors, whether it be grids that won’t return results, or grids that won’t save data. Hope this was valuable!