Adventures in Groovy – Part 21: Real Time Data Movement (Getting REALLY Groovy)

Introduction

Before we jump in, there are a number of questions you are going to have at the conclusion of this article.  Please post comments and I will answer them, but keep in mind, this is an example.  Are there different ways to accomplish this?  You bet.  Should the data sync directly to the rFin database?  Probably not, as there are calculations in the fin database that likely need to happen.  This could be so complicated that nobody would follow it, so some liberties have been taken to simplify the explanation.  The hope is that you can take this, as it has all the pieces required, and modify, add pieces, alter others, and be able to create something that meets your needs.  This is a continuation of Part 18.  Please read that before you continue.

A Visual

Before moving into the steps of the process, the following diagram is an overview.

Step 1: Validating User Input and Executing Business Logic

Step one is Groovy validation on the input that is not relevant to the actual data movement and has been discussed in other articles.  This also runs any business logic on the data that has been changed.  The only difference between a Groovy and non-Groovy calculation is that the logic is isolated to execute on only the rows and columns that have changed.  This has also been discussed in previous articles and is not relevant to the topic of real time synchronization.

Step 2a: Isolating The Edited Rows

Isolating the edited rows to synchronize is not required, but it will significantly reduce the amount of time the data map / smart push takes.  If this step is skipped, hundreds, if not thousands, of products will be synchronized for no reason.  This will certainly create performance problems where there doesn’t need to be.  So, even though it isn’t required, it is HIGHLY recommended.  This will be discussed at a high level.  Much more detail on this topic can be read in Part 3 of this series.

Although the POV in the GP form has dimensions that aren’t in Fin, Company is.  This is the one parameter that is used all the way through this example.  Year, Version, and Scenario are all fixed.  The form is only for Budget updates.  This is not common, so replicating this would likely require some additional variables to store Scenario and Year.

// Get POV
String sCompany = operation.grid.getCellWithMembers().getMemberName("Company")
def sMaterialGroup = operation.grid.getCellWithMembers().getMemberName("Material_Group")
String sChannel = operation.grid.getCellWithMembers().getMemberName("Channel")

//Get a collection of all the products that have been edited
def lstVendors = []
operation.grid.dataCellIterator({DataCell cell -> cell.edited}).each{ 
 lstVendors.add(it.getMemberName("Vendor"))
}
//Convert the collection to a delimited string with quotes around the member names
String strVendors = """\"${lstVendors.unique().join('","')}\""""

Step 2b: Pushing The Changes To The GP Reporting Database

The next step of the process is to push the data (hopefully only the changed data) from the BSO database to the reporting database (GP to rGP in the diagram above).  This step is basically pushing the same level of data from a BSO to an ASO database for the products that have changed.  This can be done with a Smart Push or Data Map.  It can also be done with a data grid builder, but this is a simpler way to accomplish it.  A deep dive into the data grid builder is scheduled later in the series.

If you only want to further customize a push already on a form, then use a Smart Push.  Otherwise, a Data map is required.  There is a detailed explanation in Part 8 of the series and is worth reading.

Smart Push is used in this example.  I prefer to use Data Maps but it is helpful to see both.  A step later in the process will use a Data Map (push from Fin to rFin).

With this example, the Smart Push on the data form has all the appropriate overrides.  The only thing needed to be customizes is the list of products that have been changed.

// Check to see if the data map is on the form and that at least one product was updated
if(operation.grid.hasSmartPush("GP_SmartPush") && lstVendors)
 operation.grid.getSmartPush("GP_SmartPush").execute(["Vendor":strVendors,"Currency":'"USD","Local"'])

Why use a Smart Push or Data Map here?  Could you use the grid builder?  Absolutely.  Quite honestly, I don’t know which is faster, but I am going to test this in a later post and compare the performance.

Step 3 & 4: Synchronizing With Fin and rFin

This is where new methods will be introduced, and honestly, the most complicated part.  It is also the piece that completely changes the landscape and completes the circle on being able build real time reporting.  Since data is moving from a BSO to an ASO, there isn’t a pre-built solution/method to do this.  But, Groovy does open up the ability to simulate a retrieve, with a customized POV, and a submit.  At a high level, that is what these steps accomplish.  The POV from the form is used as a starting point and changed to a total vendor/channel/material group and retrieve the data from rGP (ASO so no consolidation is required), create another retrieve that is connected to the fin cube, copy the data at a total vendor/channel/material group from rGP to the fin cube grid, and submit it.

The following is Groovy Map, or Groovy Collection, that simply holds the translation between the accounts in the GP database and the accounts in the Fin database.  This is nothing proprietary to PBCS or the PBCS libraries.  If you are unfamiliar with these, explanations are easy to find by searching Google for “Groovy data maps.”

//set account map
def acctMap = ['Regular_Cases':'Regular_Cases',
 'Net_Sales':'42001', 
 'Cost_of_Sales_without_Samples':'50001',
 'Gallonage_Tax':'50015', 
 'Depletion_Allowance_Manual_Chargeback':'56010', 
 'Gain_Loss_Inv_Reval':'50010',
 'Supplier_Commitments':'56055',
 'Supplier_Spend_Non_Committed':'56300',
 'Samples':'56092',
 'GP_NDF':'56230',
 'GP_BDF':'56200',
 'GP_Contract_Amortization':'56205',
 'Sample_Adjustment':'56090'
 ]

Now, let’s start in with the methods that have not been discussed in the Groovy Series.  The remaining process simply copies the data at total channel, total material group, and total vendor, to the Fin databases to No Cost Center, which is void in GP.

If you are familiar with creating Planning Data Forms, or you use Smart View to create adhoc reports, you will understand the concepts of creating grids with Groovy.  They include the page, column, and row definitions, all which have to be defined.  Once they are defined, well, that is all there is .  The script looks a little scary, but it is basically doing the things you do every day.

This first grid is our source grid.  It will connect to the rGP (ASO) database and retrieve the data to be moved to the Fin and rFin databases.

// Create variables that will hold the connection information
Cube lookupCube = operation.application.getCube("rGP")
DataGridDefinitionBuilder builder = lookupCube.dataGridDefinitionBuilder()

// Define the POV for the grid
builder.addPov(['Years', 'Scenario', 'Currency', 'Version', 'Company','Channel','Material_Group','Source','Vendor','View'], [['&v_PlanYear'], ['OEP_Plan'], ['Local'], ['OEP_Working'], [sCompany],['Tot_Channel'],['Total_Material_Group'],['Tot_Source'],['Tot_Vendor'],['MTD']])

// Define the columns
builder.addColumn(['Period'], [ ['ILvl0Descendants("YearTotal")'] ])

// Loop through the Groovy Map for the accounts to retrieve
for ( e in acctMap ) {
 builder.addRow(['Account'], [ [e.key] ]) 
}

// Initiate the grid
DataGridDefinition gridDefinition = builder.build()

// Load the data grid from the lookup cube 
DataGrid dataGrid = lookupCube.loadGrid(gridDefinition, false) 

// Store the source POV and rows to replicate in the destination grids (rFin and Fin) 
def povmbrs = dataGrid.pov 
def rowmbrs = dataGrid.rows 
def colmbrs = dataGrid.columns

Now that the source is ready to go, creating the objects/grids that connect to the destination databases is next, which are Fin and rFin.  It builds out the POV, columns, rows, and also loops through the cells in the source grid to get the data.  Almost every line is duplicated, so don’t get confused.  The reason is that the script is creating a grid to save to each of the fin databases.  To make it easier to see this, the duplicate items are in a different color.

// Create variables that will hold the connection information
Cube finCube = operation.application.getCube("Fin")
Cube rfinCube = operation.application.getCube("rFin")
DataGridBuilder finGrid = finCube.dataGridBuilder("MM/DD/YYYY")
DataGridBuilder rfinGrid = rfinCube.dataGridBuilder("MM/DD/YYYY")

// Define the POV for the grid
finGrid.addPov('&v_PlanYear','OEP_Plan','Local','OEP_Working',sCompany,'No_Center','GP_Model')
rfinGrid.addPov('&v_PlanYear','OEP_Plan','Local','OEP_Working',sCompany,'No_Center','GP_Model','MTD')

// Get the column from the source grid and define the column headers for the grid
def colnames = colmbrs[0]*.essbaseMbrName
String scolmbrs = "'" + colnames.join("', '") + "'"

finGrid.addColumn(colmbrs[0]*.essbaseMbrName as String[])
rfinGrid.addColumn(colmbrs[0]*.essbaseMbrName as String[])

// Build the rows by looping through the rows on the source grid, converting the accounts,
// and inserting the values from rGP (source)
dataGrid.dataCellIterator('Jan').each{ it ->
  def sAcct = "${acctMap.get(it.getMemberName('Account'))}"
  def sValues = []
  List addcells = new ArrayList()
  colmbrs[0].each{cName -> 
    sValues.add(it.crossDimCell(cName.essbaseMbrName).data) 
    addcells << it.crossDimCell(cName.essbaseMbrName).data
    }
  finGrid.addRow([acctMap.get(it.getMemberName('Account'))],addcells)
  rfinGrid.addRow([acctMap.get(it.getMemberName('Account'))],addcells)
  }

If you noticed slightly different methods (dataGridBuilder vs DataGridDefinitionBuilder), you have a keen eye.  Later discussions will go into detail on the differences, but the reason both are used in this example is because DataGridDefinitionBuilder allows the use of functions, like ILvl0Descendants, which was used so members were not hard coded.

The argument could be made that there is no reason to push the data to rFin since later in the process it will be replicated.  I would not argue with that rational.  However, for educational purposes, the push to rFin here will include USD and Local currency.  The push later will only include USD.  So, there is some replication that could be removed in a production application.

//Create a status object to hold the status of the operations
DataGridBuilder.Status status = new DataGridBuilder.Status()
DataGridBuilder.Status rstatus = new DataGridBuilder.Status()

//Initiate the grids connected to Fin and rFin with the status object
DataGrid grid = finGrid.build(status)
DataGrid rgrid = rfinGrid.build(rstatus)

// The print lines that send information to the log are not required, 
// but showing the status is helpful in troubleshooting and monitoring
// performance
println("Total number of cells accepted: $status.numAcceptedCells")
println("Total number of cells rejected: $status.numRejectedCells")
println("First 100 rejected cells: $status.cellsRejected")

// Save/Submit the form to Fin
finCube.saveGrid(grid)

// Additional information sent to the log
println("Total number of cells accepted: $rstatus.numAcceptedCells")
println("Total number of cells rejected: $rstatus.numRejectedCells")
println("First 100 rejected cells: $rstatus.cellsRejected")

// Save/Submit the form to rFin
rfinCube.saveGrid(rgrid)

Step 5: Executing and Synchronizing Fin Logic

This is by far the simplest part of the entire process.  This piece doesn’t have to be a Groovy calculation, honestly.  In this situation, the Company can be grabbed from the form POV.  That said, I like the ability to log things from a Groovy Calculation, so I have done so in this example.  Why is currency calculated here and not in GP?  Great question.  Ah…this is just an example.  This could be replaced with any logic.

This is the simple part...execute the business rules

String sCompany = operation.grid.getCellWithMembers().getMemberName("Company")
StringBuilder essCalc = StringBuilder.newInstance()
essCalc <<"""
FIX(&v_PlanYear,"OEP_Plan",$sCompany,"No_Center","GP_Model")
 %Script(name:="FIN_Currency",application:="BreakFin",plantype:="Fin")
ENDFIX
"""

println essCalc
return essCalc

After any specific business logic is executed, the last step is to push the data to rFin.  Rather than use a Smart Push like above, this time a Data Map will be used.  My preference is to use Data Maps.  Once the parameters are understood, I think it is easier just to pass all the overrides in a generic Data Map.  Otherwise, the overrides are managed in multiple.  I certainly can’t argue performance, simplicity, or other benefits for one method over another.  It is completely a preference.

//Declare string variables to house POV members
String sCompany = '"' + operation.grid.pov.find{it.dimName =='Company'}.essbaseMbrName + '"'
//Execute datamap
operation.application.getDataMap("Fin Form Push").execute(["Company":sCompany,"Scenario":"OEP_Plan","Version":"OEP_Working","Years":"&v_BudYear","Source":"GP_Model","Currency":"USD","Account":'ILvl0Descendants("Account")',"Cost_Center":"No_Center"],true)

Performance

I have presented this concept 3 times to about 300 people.  I always get this question.

OK, you change one product and it is fast.  What happens if you change all of them?

To be completely transparent, pieces of this are not much faster, but the move from the detailed cube to the summary cube (GP to fin/rFin in this example) is lightning fast and makes no difference whether 1 or 30K products are changes.  In a real world situation, planners don’t change every line every time.

Here is a summary of what I experienced.  The first 4 are changes made at a lev0 of channel and material group.  The second 4 are done at the top of those dimensions.  The calculation of the business logic changes for obvious reasons.  The push of the changed data changes for the same reason.  It is simply a question of volume.  The synchronization to the reporting consolidated cubes is not impacted.  It doesn’t matter whether 1 or 30k products are changed because the data moving from the rGP cube is the same because it is pushing at a total.

* All times are in seconds

Conclusion

The reason I looked into Groovy was because of this example/client.  The logic on the form was relatively complicated and included allocations, then based on the changes there were spreads through the months and additional adjustments to make sure impacts to revenue were impacted properly.  The form save was taking minutes, and was unacceptable, for obvious reasons. The Smart Push was taking too long and had to be run in background, and would error due to size periodically.  That gave me the idea of the push on save to the consolidated financial cube, and voila!

This introduces some incredible functionality that can change the landscape of what we can do with PBCS.  Much of this has been discussed in previous pieces of this series, but the addition of moving consolidated data (without running a consolidation) throughout the applications is brand new.  There are some more things to the script that could be done to even speed it up a little (only moving edited accounts and months, for example).  In the near future, we will jump into this code at a more granular level and explain, line by line, what everything is doing.  We will also optimize this example further.  Look forward to that in the coming weeks.  For now, a solid example is available for you to use.

What do you think?




Adventures in Groovy – Part 8: Customizing Data Maps and Smart Pushes

Introduction

Groovy has the ability to execute Data Maps and Smart Pushes.  Data Maps are objects that define the data movement between applications and are accessible globally.  Smart Pushes are Data Maps that are attached to a Data Form and can override the dimensions settings.  They are largely the same thing, but defined separately inside of PBCS.

So, why execute these in Groovy rather than assign to a form and call it a day?

  1. The data push can be customized to push only data that has changed, improving performance.
  2. When a form is saved, all calculations are executed first, and the Smart pushes are executed after all calculations are finished.  If Groovy is used and the data push is done inside a Business Rule, the order of operation can be a
    1. calculation
    2. data push
    3. calculation
    4. data push
    5. etc
  3. Since the Smart Push has a global limit of memory that can be used, and multiple people are running them through form saves, it is critical to make it as small as possible to reduce the probability of hitting that limit and increasing the odds of the Smart Pushes failing.

To date, I see the advantage of running a Smart Push (a Data Map on a form) in that most of the dimensions are already overridden and less overrides are required in the Groovy logic.  There is no difference in performance or the size of what can be pushed between the two when executed from a Groovy calculation.  The advantage of using a generic Data Map is that there is less effort in defining the form level Smart Pushes, and once one Groovy calculation is written to override all dimensions from a Data Map, it can be replicated easily to all required forms.

To understand the memory issues and explanation of how it differs from Data Maps and Smart Pushes, see PBCS Data Map / Smart Push Has Data volume Limits.

Data Map

Executing a Data Map is very simple and can be done in one line of code.

operation.application.getDataMap("Data Map Name").execute(true)

Calling execute() on a DataMap would execute the named Data Map (with no customization) and clearing the target location before pushing data.  Changing the true to false, or removing it, would remove the clear and leave the data as is.

To override the the dimension settings and increase or decrease the scope of what is used, the member names need to be further defined.  Every dimension’s scope can be changed, or just the ones that require a change in scope can be altered.  The following changes the scope for the account, scenario, version, and Entity dimensions.

operation.application.getDataMap("Data Map Name").execute(["Account":"Net Income, Income, Expense", "Scenario":"Budget", "Version":"Working", "Entity":"East Region"], true)

Smart Push

The Smart Push works exactly the same, except the object referenced is a Smart Push, and is obtained through the grid object, not the application.  Again, the likelihood that the Smart Push is further scoped is high, so it is reasonable that the dimensional parameters would be fewer as many of them change based on the POV selected.

operation.grid.getSmartPush("Smart Push Name").execute(["Account":"Min Bonus, Min Salary"])

One additional option is to define a Smart Push from a Data Map in the Groovy script.

operation.application.getDataMap("Data Map Name").createSmartPush().execute(["Account":"Min Bonus, Min Salary"])

Error Trapping

When these are written, it is likely that the Smart Pushes and Data Maps exist.  One extra step to ensure the calculation doesn’t fail is to verify their existence.  For Smart Pushes, verify that it is attached to the form.

//Data Map Example
if(operation.application.hasDataMap("Data Map Name"))
  operation.application.getDataMap("Data Map Name").execute(true)
//Smart Push Example
if(operation.grid.hasSmartPush("Smart Push Name"))
  operation.grid.getSmartPush("Smart Push Name").execute()

Conclusion

Creating variables to use in these functions to change the scope to only the rows and columns that have been edited, the calculation would look like this.  This is where I see the biggest bang for  your buck.  To understand more about using the grid iterator, read Adventures in Groovy Part 3: Acting On Edited Cells.  When a grid has hundreds of rows, only pushing the data that has been edited can make a huge difference in performance.

// Setup the variables to store the list of edited vendors and periods
def lstVendors = []
def lstPeriods = []
// Iterate through only the cells that were changed and create a list of changed vendors and periods
operation.grid.dataCellIterator({DataCell cell -> cell.edited}).each{ 
 lstVendors.add(it.getMemberName("Vendor"))
 lstPeriods.add(it.getMemberName("Period"))
}
// Convert the lists to a comma delimited string with quotes surrounding the members
String strVendors = """\"${lstVendors.unique().join('","')}\""""
String strPeriods = """\"${lstPeriods.unique().join('","')}\""""
// Execute the Smart Push with a change in scope to Vendor and Period
if(operation.grid.hasSmartPush("GP_SmartPush") && lstVendors)
 operation.grid.getSmartPush("GP_SmartPush").execute(["Vendor":strVendors,"Period":strPeriods]

With any luck, you will see significant improvement, unless they change every month of every row!