Adventures in Groovy – Part 28: The DataGridBuilders

Print
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.

  1. The DataGridDefinitionBuilder is used to read data.
  2. 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.

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.

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.

The rows are identical to the columns, so I won’t repeat the narrative above.

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.

Finally, load the grid, which is the equivalent of refreshing a Smart View template.

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.

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.

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.

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.

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.

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.

Finally, save the grid to submit the data to the database.

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!

 

Please follow and like us:
RSS
Facebook
PINTEREST
LinkedIn
 

8 Comments

  1. 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.

     
    • 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.

         
  2. 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.

       

Leave a Reply

Your email address will not be published. Required fields are marked *