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!
Kyle,
Sometimes it may become handy knowing that you can use a “DataGridBuilder” to read data too. You follow the same steps to build it (POV, Columns, Rows), but instead of passing values, use a null string. That would create the grid, but instead of submitting values, it will retrieve them once instanciated.
Thanks for sharing Luis.
Hi Kyle,
Can we use this approach to push data from one cube to another?
You can use it to move data between plan types in an application, but you can’t use it to move data between pods or between different independent applications. In working with development, the didn’t expect the excitement around making this available to us. Hopefully when more people latch on to this technology, they will open it up for more functionality.
Thanks Kyle.
We are trying to push data from Projects Module to FS module. The in built BR is not working the way we want. So was wondering if we could use the approach to push Project Expenses to FS.
If FS is another plan type (module, database, etc), or strategic modeling, yes.
Hi Kyle,
I was able to build data grid with the help of your blog series. Thanks for valuable resources. I was seeing an issue with the Datagrid builder. Its taking 8 minutes to load the grid for the particular intersection. I tried with different options with row/column/pov dimensions but could not able to get the best performance. Our projects, department members are more. Do you any issue with this script?
============================
DataGridDefinitionBuilder ProjectsBuilder = cube.dataGridDefinitionBuilder()
ProjectsBuilder.setSuppressMissingBlocks(true)
ProjectsBuilder.addPov([‘Entity’, ‘Site’, ‘Currency’, ‘Version’, ‘Scenario’, ‘Product’, ‘Customer’, ‘Plan Element’, ‘Account’, ‘Department’], [[sRTPSEntity], [sRTPSSite], [sCurrency], [sVersion], [sScenario], [sProduct], [sCustomer], [sAggPlanElement],[sAggAccounts2], [sAggDepartment] ])
ProjectsBuilder.addColumn([ ‘Years’, ‘Period’], [ [‘&OEP_RollingFcst’], [sPeriods]])
ProjectsBuilder.addRow([‘Projects’], [[sProjects]])
DataGridDefinition ProjectsGridDefinition = ProjectsBuilder.build()
DataGrid ProjectsDataGrid = cube.loadGrid(ProjectsGridDefinition, true)
ProjectsDataGrid.dataCellIterator().each {
if( !it.isMissing() ){
projectsList << it.getMemberName('Projects')
}
}
ProjectsDataGrid.close()
=========================================================
If you are connecting to BSO, it is definitely slower. If you are connecting to ASO, I would recommend finding the intersections where the data is, hold those members in arrays, and loop through a dimension with those arrays. The only time I have had run-times like that was when I created a currency conversion on hundreds of thousands of members. I think if you get creative, you should find a faster path.
very good and thnx for your post
in your post you are using DataGridBuilder for submitting data to target cube, but you are hardcoding values, that will not work in real time scenarios.
using DataGridDefinitionBuilder, after building grid, how can i push datagrid to aso cube? is it possible to do?
I would recommend you read the posts on real time data movement and watch the presentations on the topic that are posted.
Hi Kyle ,
Can you give some example of how to pass members dynamically from form in addRow or addPOV in DataGridBuilder ?
I am working on a training class that will be released in the coming weeks. Deep diving into this will be covered in the third module. Sign up at https://in2hyperion.podia.com/introtogroovyforepbcs to be updated on availability.
interested in your newsletter
Hi Kyle,
Is it possible to send data from BSO to ASO on the same application using this method?
Best regards
The short answer is yes. If you learn the system tables on the relational side you can edit them to accomplish this. It has been a while since I have used them since the cloud came out. I would start with the Hsp plan type table. I can’t say it enough but back up everything before you start messing with these tables.
Hello Kyle,
Can we use this method to send modified data from BSO to ASO?
Best regards
Yes, that is very possible.
I’m tried doing a push from an ASO to another ASO using grid builders and I could see that certain accounts with invalid intersections do not make it to the target ASO cube. Prior to setting this up, I had a DM rule that simply pushes without an error. Any idea?
you can submit the grid using using the parameter SYSTEM_USER. When you create the builder,
DataGridBuilder builder = cube.dataGridBuilder(“MM/DD/YYYY”, SYSTEM_USER)
The second parameter is an enum called ExecuteAsUser and the default is CURRENT_USER.
Hi Kyle,
is it possible to build a write grid from a resulting dataset. i got a map which consists of a data set as a result of some logic and i need to save that data to PBCS application/cube. i just want to know whether data grids can be built other than with out referring to webform grid.
thanks
Mahesh
It absolutely is possible. The source is irrelevant. As long as you format the grid builder correctly from the source information, you should be all good.
Thanks Kyle. it works as expected.
Hi,
I am trying to use DataGridBuilder to post values to the database.
when I statically pass values to addcolumn it works okay.
builder.addColumn(‘12356′,’37460’) — this works fine
but if I try to pass them from a list it does not work
def temp = [‘12356′,’37460’]
builder.addColumn(temp.join(‘, ‘)) — this does not work, ‘12356’,’37460′ is seen as string instead of comma separated values.
Could you please help me to pass the values from the list as parameters to the ‘addcolumn’ method?
Many thanks
Srikanth G
I have a great session on this in my training. You have to pass a array. The training goes through this in great detail and explains the javadocs so you know what to use in all the methods.
Thank you very much Kyle. Converting it to array fixed it for me.
My pleasure.
Hi Kyle,
we are using a groovy script to push data from WF to Fin cube. although it works fine, but our data size is pretty huge and in future data size constraints might hit us.
Wanted to check is there is a data size limit in posting data from one cube to another (making target grid in rule), not the data maps. I believe the web form size limitation would apply here as well, as we are trying to create a data grid. Please guide.
There is a size limit. I can’t remember what it is. I will find out. I posted it on customer connect. What I do is iterate through a dim or two. I will read at total dims except for one or two. I put those in the rows. Then I iterate through chunks of rows. I might get all the data for all the products. Then use collate on the list of products and rum x products at a time. This works fairly well. Make sure you try to do it in the fewest passes possible doing it with 100 products is way faster than doing it one product at a time.
Hi Kyle,
thanks for your blog. It is very helpfull for us.
I have a question for you: it is possible using DataGridBuilder function to define a list of elements (for ex using function @Relative(,0)) and using @Remove function for removes values or members in one list from another list?
Now we have problem to validate the following script.
buildert2.addRow([‘Controparte’, ‘Account’, ‘Dettaglio’], [[‘ILvl0Descendants(TotControparte)’], [‘ILvl0Descendants(3IA_MOL)’], [‘@Remove(ILvl0Descendants(TotDettaglio), “Dettaglio1”)’] ])
Any suggestion?
Thanks a lot.
Regards,
Antonio
I can’t say I have ever use REMOVE. If you use the Essbase REMOVE, you may not be able to use ILvl0Descendants. You may have to write it with @RELATIVE instead, or @LVLMBRS. If this works, please let us know so others will know, if you don’t mind.
[‘@REMOVE(@RELATIVE(“C180A-3200″,0),”Account_Default”)’]
It work !!!
Thanks…Antonio
Hi Kyle,
following the creation of a grid using the above instructions, is it possible to save the newly created form and make it searchable to users?
Thank you very much!
It is not possible.