Adventures in Groovy – Part 40: Eliminating Data Sources With The Groovy Calendar Class

I am currently working on a migration of on-premise to cloud project (going GREAT by the way).  One of the things we are working on is the change with the data integration.  One of the processes loads the number of working days to the application from a source that provides it.  “Why not use Groovy,” I ask?  It turns out to be a great question.

There are two concepts to cover.  A few lines of Groovy using a calendar class and the gridbuilder to save the data.  This could also be a dynamically generated calculation that updates the data.  Since this is used on an ASO cube, the example below uses the gridbuilder option.

The Code Explained

This example will have a run time prompt for year, will calculate the values for each month in the year selected and store them to the appropriate account.  The prompt and the variables required are declared here.

/*RTPS {rtpYear} */
int iYear = rtps.rtpYear.getEnteredValue().toString().substring(2).toInteger() + 2000
def sYear = rtps.rtpYear.getEnteredValue().toString()
Def values = []
Calendar calendar = GregorianCalendar.instance

Once this is setup, the calendar class will be used to identify the working days.  Working days here is defined as any weekday in the month.  These days are used to calculate payroll accruals and monthly averages.  Holidays are not considered in the calculations that use this.

The following uses the calendar class to create a list variable that will be passed to the gridbuilder later in the script.

for (int currentMonth = 1; currentMonth <= 12; currentMonth++) {
  Calendar startCal = new GregorianCalendar(iYear, currentMonth, calendar.getActualMinimum(GregorianCalendar.DAY_OF_MONTH))
  Calendar endCal = new GregorianCalendar(iYear, currentMonth, calendar.getActualMaximum(GregorianCalendar.DAY_OF_MONTH))
  int workDays = 0
  startCal.upto(endCal) {Calendar it ->
    if( (it[Calendar.DAY_OF_WEEK]).toString().toInteger() > 1 && it[Calendar.DAY_OF_WEEK].toString().toInteger() <7 )
       workDays += 1
     }
    def sMonth = Date.parse( 'MM', "$currentMonth" ).format( 'MMMM' )
   values << workDays
   println "for the month of $sMonth we have $workDays working days."
}

At this point, a list object has 12 values, one for each month.  This will be loaded to a specific POV that won’t change.  If the requirement was more dynamic, this example could certainly be expanded to account for it.  The last step is to store the data back to the database.  There are many examples on the gribuilder in previous articles, so it won’t be explained in detail.

Cube cube = operation.application.getCube("Plan1")
DataGridBuilder builder = cube.getDataGridBuilder("MM/DD/YYYY")
builder.addPov($sYear, 'Local', 'Working', 'Plan', 'No Entity')
builder.addColumn('Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug’, 'Sep', 'Oct', 'Nov', 'Dec’ )
builder.addRow(['Working Days'], values )
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)
 }

The Results

The log will provide some descent information.  This can be expanded for POV, user info, the time it took to process, but as it is, this is what the log would produce.  Remember, the security of the user that runs this is used.  If this is for a forecast, for example, and the start month is April, the accepted cells would only be nine.  3 cells would be rejected because they are not writable.

for the month of January we have 21 working days.
for the month of February we have 21 working days.
for the month of March we have 22 working days.
for the month of April we have 22 working days.
for the month of May we have 20 working days.
for the month of June we have 22 working days.
for the month of July we have 22 working days.
for the month of August we have 21 working days.
for the month of September we have 22 working days.
for the month of October we have 21 working days.
for the month of November we have 21 working days.
for the month of December we have 22 working days.

Total number of cells accepted: 12
Total number of cells rejected: 0

The Calendar Class

Although not required in this example, there are all kinds of things that this can be used for.  Have you ever needed to calculate the week of the year?  The day of the week?  How about the days between two dates?  You likely have come across these things for WFP or CapEx at least.  The calendar object itself has a ton of useful cases and the object that is returned is basically an array of information.  If the object is sent to the log with a println, all the values are exposed.  Of course, you can always google it, but it looks like this.

java.util.GregorianCalendar[time=-62130585600000,areFieldsSet=true,areAllFieldsSet=true,lenient=true,zone=sun.util.calendar.ZoneInfo[id="UTC",offset=0,dstSavings=0,useDaylight=false,transitions=0,lastRule=null],firstDayOfWeek=1,minimalDaysInFirstWeek=1,ERA=1,YEAR=1,MONTH=2,WEEK_OF_YEAR=10,WEEK_OF_MONTH=1,DAY_OF_MONTH=2,DAY_OF_YEAR=61,DAY_OF_WEEK=4,DAY_OF_WEEK_IN_MONTH=1,AM_PM=0,HOUR=0,HOUR_OF_DAY=0,MINUTE=0,SECOND=0,MILLISECOND=0,ZONE_OFFSET=0,DST_OFFSET=0]

It would take pages to explain and provide examples of all that can be done, which won’t be in this post.  Here are some ideas of uses.

  • use copyWith to duplicate an instance
  • use the format method to format the date as a month, year, long date, etc.
  • use minus to subract two calendar dates from each other (datediff)
  • use next and previous to increment days, months, or years
  • use set to set a specific date

Last Call

Will this change the world?  No.  Is it useful?  To some, absolutely.  The goal here is to just provide some information on how to use the calendar class and give you some ideas of what it could be used for.  Manipulating dates and time has always been a challenge in Essbase.   It is better now with some of the custom functions that have been exposed (and thank goodness they did this on the cloud or we wouldn’t be able to register custom functions).  But, performance, complexity, rolling through the same blocks multiple times, all can be minimized with the ability to calculate this outside of Essbase, pass it to a calculation, let Groovy do what it is good at, and let Essbase handle what its strengths are.

To read about more uses, Google “Groovy Calendar” and take a look at all the methods it has – pretty useful stuff.

You have any other thoughts?  Post a comment.  You know  we would love to hear from you.




Adventures in Groovy – Part 39: First Common Parent

I can’t tell you how many times I have been at a client and they wanted to replicate eliminations at the first common parent like HFM, or some other consolidations system.  Maybe there is a good way to do this, but I could never find an efficient and effective way to accomplish it with Essbase calculations.  Well, that is history.  This is yet another example of how to solve an historically complex problem very simply.

What Is The First Common Parent

Eliminations functionality addresses the posting of inter-company eliminations in scenarios where a full legal consolidation model is not required, such as within a standard financial model.  An example would be eliminating sales to another entity inside the organization so the total sales of the organization is not inflating the real sales of the organization.  This is typically done at the first node that consolidates the two entities, or first common parent.  In the example below, which will be used in the code below, we need to find the first common parent to do the eliminations for entity S253 and S592.  The hierarchy below shows that the first parent of these two members is Mountain Division.  This is the entity that will calculate and hold the eliminated sales.

Surprisingly Easy With Groovy

The Groovy classes available to us have the ability to query metadata.  This allows a calculation to return all kind of things, like the ancestors of members.  Groovy takes over the rest by comparing the arrays returned.

For this example, the calculation prompts for two members.  In an real-world example, these would likely be defined in the calculation, or maybe with UDAs or attributes.  The prompts in this example, C1 and C2, are run time prompts connected to the hierarchy above.  Once the members are defined, the next step is to query the ancestors of each of the two members.

First, a connection to the application that has the dimension and members is defined.  Once that is done, a dimension object is created that is used to execute the queries.  Since we need ancestors, we use IAncestors.

/*RTPS:{C1} {C2}*/
Cube cube = operation.application.getCube("Fin")
Dimension companyDim = operation.application.getDimension("Company")
List companyOne = companyDim.getEvaluatedMembers("IAncestors(${rtps.C1.toString()})", cube) 
List companyTwo = companyDim.getEvaluatedMembers("IAncestors(${rtps.C2.toString()})", cube)

companyOne returns an array with the following values.

[S253, AZ_Stores, Mountain_Div, West_Reg, US_Stores, Tot_Company]

companyTwo returns an array with these values.

[S592, MT_Stores, Mountain_Div, West_Reg, US_Stores, Tot_Company]

The hard part, if you consider that hard, is over.  Now that the two arrays are defined, a snazzy Groovy method are used.  The intersect method will return the common elements of two lists.

The order of the elements returned by the PBCS classes is ordered from the bottom of the hierarchy to the top.  The first element would be the first common parent!  This example doesn’t illustrate it but this would work for staggered hierarchies just the same.

List commonParents = companyOne.intersect(companyTwo)
println "First common parent for ${rtps.C1.toString()} and ${rtps.C2.toString()} is ${commonParents[0]}"

The println results in the following message to the job console.

First common parent for "S253" and "S592" is Mountain_Div

That is it boys and girls.  In 6 lines of scripting (and it could be less as some variables are introduced to clearly articulate the process and methods), the first common parent is identified.  The most difficult part would be the business logic to accomplish the actual business requirement.

All Done

Now that you know how to get the first common parent, this can be used to dynamically create the appropriate Essbase calculations to provide all the functionality that is needed.  If you like this, share it with the community.  Post comments if you have any questions.  I love getting feedback.




Adventures in Groovy – Part 31: Benefits of Running Logic in Groovy (Bypass Essbase)

Say what? An application where no calculations are performed with the Essbase calculation engine?

Business logic, currency, and every other calculation can be performed within Groovy outside of Essbase. I don’t know how I feel about this yet, but the thought of building this in calculations on an ASO cube is intriguing. The elimination of consolidating BSO cubes and data movements with Smart Pushes is also interesting. Some of my coworkers have built entire applications this way with great success and speed. I wanted to replicate this on a small scale to identify the pros and cons of this methodology, and here is what I found.

In my adventures, I found that performance was similar. Sometimes it was quicker, sometimes a little slower. I did not do large allocations, but I have done account calculations and currency conversion. I have done it on form save as well as globally. Complexity varies. If you are a Groovy expert, it likely seems less complex. If you are an Essbase developer, it might seem more complicated. Code can be reused by developing functions and placing them in scripts, just like Essbase scripts can be embedded into rules.

One thing I did find appealing for a user is that change history is complete! If you have dabbled with this, you will know that the change history only includes edited data by a user. So, calculated accounts don’t show any history. When the calculations are done in Groovy, the results are stored in a grid, and saved to the database. This mimics a user updating the values and reflects in the change history. This, I think, is pretty cool.

Another benefit is that calculated data in a grid is reflective of the inputs. With prompts, or when data isn’t validated, the form shows the results of the calculations before the data is saved in the form. I have been asked by many if it is possible to show the results real time before save (like an adhoc form with formulas built in Excel). This is now possible if you get creative.

Before we jump into the code, it might make sense to understand what dimensions/members are in the POV.

Code Introduction

The following calculation is replicating what an Essbase calculation is performing.

  1. Performs simple math to calculation revenue and costs – mostly simple multiplication and division
  2. Looks up the currency rates for the Entity selected and applies the exchange rate to the account if it is identified as an account that should be converted (ignores statistical accounts, for example)
  3. Converts the account from ProdRev to the representative account in the P&L plan type.
  4. Submits the calculated results to both the ProdRev BSO and ASO (in a real world situation, you may decide to exclude the BSO cube and connect the form and write the data directly to the ASO cube)

As previously mentioned, there are a couple way to do this. This method runs post save and pulls the necessary data from the cube and created two GridBuilders to submit the calculated accounts back to the databases. It would likely be more productive to pull the data from the grid the user entered it, assuming there were no other data points that were needed to calculate all the accounts. This would reduce the performance by one retrieve. This also connects to the BSO cube, and this may not even be in play if all the calculations were executed in Groovy and the forms connected to the ASO cube. So, I welcome all questions and constructive feedback, but understand this is one way to do this and it will be used to demonstrate the functionality. It doesn’t necessarily represent the best way to architect the plan types.

The data entry form is below. Some of the dimensions are hidden in the POV or Page Header. The columns just include the Period dimension. The rows, and this is important to understand because of the way the code loops, contain the vendors and the accounts.

Explaining the Code

On with the show! The first piece includes a couple functions that will be referenced in loops. I find repetitive logic like this placed in function to be more readable, but it certainly isn’t required.

// This function returns the POV member with or without quotes
// This is one operation I want to look into as it may not be required or may be executed better
def getPovMbr(member, boolean quotes = false) {
  if(quotes == true)
    return '"' + operation.grid.pov.find{it.dimName==member}.essbaseMbrName + '"'
  else
    return operation.grid.pov.find{it.dimName==member}.essbaseMbrName
}
// This function returns a 1 if the value is 0.  The primary use of this is currency
// exchange.  In this example, the planners didn't enter 1 for a USD to USD conversion, 
// so somewhere a 1 needed to be assumed.
def checkZero(double dVal=0) {
  if(dVal == 0) {
    return 1
  }
  else {
    return dVal
  }
}
// This returns a true or false as to whether the account has a UDA identifying that 
// account as one that either does or doesn't get converted.
boolean convertCurrency(def account)
{
  Dimension AccountDim = operation.application.getDimension("Account")
  Member AccountMbr = AccountDim.getMember(account)
  def memberProps = AccountMbr.toMap()
  if(memberProps['UDA'].toString().contains('IgnoreCurrencyConversion'))
    return false
  else
    return true
}

The next piece is setting each POV member in a variable. If you have read some of my other posts, there are better ways to do this. For the sake of simplicity and trying to explain other concepts, I have chosen to do it this way.

// Set each POV to its own variable
String sCompany = operation.grid.getCellWithMembers().getMemberName("Company")
String sVersion = operation.grid.getCellWithMembers().getMemberName("Version")
String sScenario = operation.grid.getCellWithMembers().getMemberName("Scenario")
String sYear = operation.grid.getCellWithMembers().getMemberName("Years")
String sMaterialGroup = operation.grid.getCellWithMembers().getMemberName("Material_Group")
String sChannel = operation.grid.getCellWithMembers().getMemberName("Channel")
String sSource = operation.grid.getCellWithMembers().getMemberName("Source")

At this point, the administrative stuff is out of the way. The first thing this example does functional is to get the currency exchange rates for the month of the Entity selected. This would be a little more complex if it were converting income statement and balance sheet accounts because it would need to distinguish the difference so the correct rate (average or ending) was applied.

// Define the map to hold the appropriate rate.  This will be a map that has an entry for each month, with the value equal to 
// the conversion rate - Jan:.878, Feb:.899, ...
def currencyRates = [:]
// Build currency for the company by retrieving the correct POV from the plan type
Cube lookupCube = operation.application.getCube("ProfRev")
DataGridDefinitionBuilder builder = lookupCube.dataGridDefinitionBuilder()
builder.addPov(['Years', 'Scenario', 'Version','Channel','Material_Group','Source','Vendor','Currency','Account'],[[sYear],[sScenario],[sVersion],['No_Channel'],['No_Material_Group'],['Input'],['No_Vendor_Assigned'],['Local'],['End_C_Rate']])
builder.addColumn(['Period'], [ ['ILvl0Descendants("YearTotal")']])
builder.addRow(['Company'], [ [sCompany] ])
DataGridDefinition gridDefinition = builder.build()

// Load the data grid from the lookupCube to the Map
lookupCube.loadGrid(gridDefinition, false).withCloseable { dataGrid ->
  dataGrid.dataCellIterator().each{ rate ->
    currencyRates.put(rate.getMemberName('Period'),checkZero(rate.data))
  }
}

Now that we have a map of the currency rates, the next piece will create the grids that will submit the calculated results back to the cubes. Remember, in this example, the data is being entered to a plan type that is a BSO cube. This will submit the results back to the BSO cube, but also to the corresponding reporting (ASO) cube. Since this is done post save (and we will assume post Smart Push), the data needs to be update in both places. Is this the best and most efficient way to do this? Probably not. Again, take it for what it is – an education on bypassing the Essbase calculations!

// Get list of vendors that are in the rows
def listVendors = operation.grid.rows.headers*.essbaseMbrName.collect {vendor, account -> return vendor}.unique()

// Create a list of calculated members that need to be in the grid
def listAccounts = ["Regular_Cases","Net_Sales","prodRev_Level_1","Cost_of_Sales_without_Samples","prodRev_Level_2","Depletion_Allowance_Manual_Chargeback"]

//prodRev Grid Setup
Cube prodRevCube = operation.application.getCube("prodRev")
Cube rprodRevCube = operation.application.getCube("rprodRev")

DataGridBuilder prodRevGrid = prodRevCube.dataGridBuilder("MM/DD/YYYY")
DataGridBuilder rprodRevGrid = rprodRevCube.dataGridBuilder("MM/DD/YYYY")

prodRevGrid.addPov(sYear,sScenario,sVersion,sChannel,sMaterialGroup,sSource,sCompany)
rprodRevGrid.addPov(sYear,sScenario,sVersion,sChannel,sMaterialGroup,sSource,sCompany,'MTD')
prodRevGrid.addColumn('Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec')
rprodRevGrid.addColumn('Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec')

The next piece is replicating what would be in a typical Essbase calculation. This is the business logic and currency conversion.

// Define the currencies to be updated and sent back to the database
def lstCurrency = ["Local","USD"]
// loop through the grid the user entered their updates.  This will loop through each vendor, then loop
// through each account for each vendor
listVendors.each{ vendor ->
  listAccounts.each{ account ->
    // Create two variables to hold the calculated results for local and USD
    def sValues = []
    def sValuesUSD = []
    // Create two variables to hold all the monthly calculated results to be added to the grid to submit
    List addcells = new ArrayList()
    List addcellsUSD = new ArrayList() 
    // Run for 12 months - this would likely need to be dynamic for a form that included 
    // the option for forecast and plan
    ['Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'].each{cMonth ->
    // Calculate all the accounts
    double setValue
      switch (account) {
         case "Net_Sales":
           setValue = operation.grid.getCellWithMembers("Avg_Price/Case_Inp",cMonth.toString(),vendor.toString()).data * operation.grid.getCellWithMembers("Regular_Cases",cMonth.toString(),vendor.toString()).data
           break
         case "prodRev_Level_1":
           double netSales = operation.grid.getCellWithMembers("Avg_Price/Case_Inp",cMonth.toString(),vendor.toString()).data * operation.grid.getCellWithMembers("Regular_Cases",cMonth.toString(),vendor.toString()).data
           setValue = netSales * operation.grid.getCellWithMembers("prodRev_1_%_Inp",cMonth.toString(),vendor.toString()).data
           break
         case "Cost_of_Sales_without_Samples":
           double netSales = operation.grid.getCellWithMembers("Avg_Price/Case_Inp",cMonth.toString(),vendor.toString()).data * operation.grid.getCellWithMembers("Regular_Cases",cMonth.toString(),vendor.toString()).data
           double prodRev1 = netSales * operation.grid.getCellWithMembers("prodRev_1_%_Inp",cMonth.toString(),vendor.toString()).data
           setValue = netSales - prodRev1
           break
         case "prodRev_Level_2":
           double netSales = operation.grid.getCellWithMembers("Avg_Price/Case_Inp",cMonth.toString(),vendor.toString()).data * operation.grid.getCellWithMembers("Regular_Cases",cMonth.toString(),vendor.toString()).data
           setValue = netSales * operation.grid.getCellWithMembers("prodRev_2_%_Inp",cMonth.toString(),vendor.toString()).data
           break
         case "Depletion_Allowance_Manual_Chargeback":
           double netSales = operation.grid.getCellWithMembers("Avg_Price/Case_Inp",cMonth.toString(),vendor.toString()).data * operation.grid.getCellWithMembers("Regular_Cases",cMonth.toString(),vendor.toString()).data
           double prodRev1 = netSales * operation.grid.getCellWithMembers("prodRev_1_%_Inp",cMonth.toString(),vendor.toString()).data
           double prodRev2 = netSales * operation.grid.getCellWithMembers("prodRev_2_%_Inp",cMonth.toString(),vendor.toString()).data
           setValue = netSales - prodRev1
           break
         default:
           setValue = operation.grid.getCellWithMembers(account.toString(),cMonth.toString(),vendor.toString()).data
           break
      }
      // Update the variables that will be used to create the grid rows
      sValues.add(setValue)
      addcells << setValue
      // Convert Currency if account should be converted
      if(convertCurrency(account) == true){
        sValuesUSD.add(currencyRates[cMonth].toString().toDouble() * setValue)
        addcellsUSD << currencyRates[cMonth].toString().toDouble() * setValue
      }
      else
      {
        sValuesUSD.add(setValue)
        addcellsUSD << setValue
      }
    }
    // After all 12 months are traversed calculated, add local and USD to the grid
    prodRevGrid.addRow([account.toString(),vendor.toString(),'Local'],addcells)
    prodRevGrid.addRow([account.toString(),vendor.toString(),'USD'],addcellsUSD)
    rprodRevGrid.addRow([account.toString(),vendor.toString(),'Local'],addcells)
    rprodRevGrid.addRow([account.toString(),vendor.toString(),'USD'],addcellsUSD)
  }
}

The last piece is to submit the grids. The following will write to the log the number of cells accepted and rejected.

// After all vendors and account have been calculated, submit the grid to the respective database
DataGridBuilder.Status status = new DataGridBuilder.Status()
DataGridBuilder.Status rstatus = new DataGridBuilder.Status()
DataGrid grid = prodRevGrid.build(status)
DataGrid rgrid = rprodRevGrid.build(rstatus)
println("Total number of cells accepted: $status.numAcceptedCells")
println("Total number of cells rejected: $status.numRejectedCells")
println("First 100 rejected cells: $status.cellsRejected")
prodRevCube.saveGrid(grid) 
println("Total number of cells accepted: $rstatus.numAcceptedCells")
println("Total number of cells rejected: $rstatus.numRejectedCells")
println("First 100 rejected cells: $rstatus.cellsRejected")
rprodRevCube.saveGrid(rgrid)

So…

Why Do this?

This would add complexity for those who don’t know Groovy.  It is a completely different thought process. But there are benefits.

  • Elimination of the BSO application creates a simpler model.
  • There is no requirement of data synchronization between the BSO and ASO cube.
  • Users will see cell history for all data that was changed, not just the data changed by a user directly.

That is not to say there aren’t drawbacks, because there are.  I am sure if you are experienced, you are already asking questions about clearing data, large allocations, and several other necessary components.

Is It Worth It?

Honestly, I don’t know yet. This is a complete paradigm shift in our world. The thought of calculating everything in Groovy and not having a BSO plan type is just completely foreign to me. The exploration of this option and documenting here surely proves my interest is peaked! Large allocations seem to be slower than BSO Essbase calculations. Everything else seems to be as quick or quicker. I think in the short term, having both gives us the best of both worlds, even with the additional need to synchronize the data between the two. Long term? Who knows. I never thought I would even be entertaining it, so I am not closing my mind to the possibility.

What do you think?

 




Adventures in Groovy – Part 27: Understanding Collections

Because so many methods in PBCS require parameters that are maps and lists, it is very important to your productivity and effectiveness to understand, use, and be able to manipulate collections.  Collections include lists, maps, and ranges.  These are based on Java collection types and include a ton of useful methods.  This article will cover lists and maps.

  • Lists are objects that are embedded in brackets and separated by commas, like [Jan, Feb, Mar].
  • Maps are also known as associative arrays and are similar to lists.  Each element is separated by a colon, like  [Revenue:678979, Expense:34387].

Not to jump too far ahead, but these objects aren’t limited to holding strings.  They can hold numeric values, lists inside of lists, maps inside of lists, or really almost any combination of these objects.

Time Well Spent

There is a lot of information in this post.  Before you page down, or read a little and get bored, I highly recommend you invest the time and understand the content.  The feedback from everybody that I gave this information to prior to this post said it made a huge impact and really cleared up a lot of questions they had.

A Little About Lists and Maps And Why Understanding It Is Important

Lists and Maps are used in a number of places that are frequently used in Groovy calculations.  Gridbuilder and DataMap objects are dependent on these objects.  You may be building the POV by embedding strings into what is actually a list without even knowing it. I have done this in previous examples to keep it simple and explainable.

String sYear = '"' + operation.grid.pov.find{it.dimName =='Years'}.essbaseMbrName + '"'
String sScenario = '"' + operation.grid.pov.find{it.dimName =='Scenario'}.essbaseMbrName + '"'
String sCompany = operation.grid.getCellWithMembers().getMemberName("Company")
String sChannel = '"' + operation.grid.pov.find{it.dimName =='Channel'}.essbaseMbrName + '"'
String sMaterialGroup = '"' + operation.grid.pov.find{it.dimName =='Material_Group'}.essbaseMbrName + '"'

operation.application.getDataMap("[name of map]").execute(["Company":sCompany,"Channel":sChannel,"Material_Group":sMaterialGroup,"Vendor":sVendor,"Scenario":sScenario,"Version":"OEP_Working","Years":sYear,"Source":"NBF_Source","Currency":"Local,USD","Account":accountPush],true)

builder.addPov(['Years', 'Scenario', 'Version', 'Company','Channel','Material_Group','Source','Vendor','View'], [sYear.tokenize(), sScenario.tokenize(), ['OEP_Working'], [sCompany],['Tot_Channel'],['Total_Material_Group'],['Tot_Source'],['Tot_Vendor'],['MTD']])

Since the POV and DataMap include lists and maps, building them can also be done by pulling the grid POV, which is a map.   Lists can be extracted from the map.  There are some additional members that are required that are not in the Web Form, so a few dimensions have to be added.

Hopefully the following example will emphasize the importance of understanding these objects and the methods associated to them.  The above is not a ton of work, but replicating this hundreds of times and having the foresight to think about how functions can be reused  highlights why understanding lists, and understanding Groovy, will help you provide solutions that are more manageable to maintain.  The following replicates what was above in a more effective and readable way, in my opinion.  It may seem more complex, but give it a chance.  I think you will find it easier once you understand it, and less work to develop Groovy calculations.

Example Setup

Assume the user changes Units for Jan, Feb, and Mar for NBF1 in the Data Form below.  Net Sales is impacted as it is calculated.  The business rule that runs calculates currency, so that will also have to be included in the DataMap and GridBuilder.

The POV for the form looks like this.

Replicating The DataMap Using A Groovy Map Object

Executing a Data Map uses a Groovy map built from the Data Form POV.  Currency has to be altered.  The edited rows and columns are appended.  For a complete explanation of how to get the edited cells, read Part 3: Acting On Edited Cells.

// Create a map from the data form POV
def povMap = [:]
operation.grid.pov.each{povMap[$it.dimName] = $it.essbaseMbrName}
// Update Currency to include Local AND USD 
povMap['Currency'] = 'Local,USD' 
// Add the vendor and accounts that have been changed 
povMap2.put('Period', sPeriods)
povMap2.put('Account', accountPush)
povMap2.put ('Vendor', sVendor)
// Execute the data map with all the dimensional overrides
operation.application.getDataMap("[name of generic map]").execute(povMap,true)

Let’s walk through the results of the script.  The povMap when created from the Data Form starts with

  • [Version:OEP_Working,
  • Source:NBF_Source,
  • Currency:Local,
  • Company:BILB,
  • Years:FY18,
  • Scenario:OEP_Plan,
  • Material_Group:mI03,
  • Channel:c01]

Once this map is created, there are some edits made.

  1. Currency is changed from Local to Local, USD.
  2. Periods is added as Jan, Feb, Mar.
  3. Account is added as Units, Net_Sales.
  4. Vendors is added as NBF1.

This method requires fewer lines, is much easier to repeat, is easier to read, and can be reused for other functions.

Replicating The GridBuilder POV Using A Groovy Map Object

Assume the data is now going to be replicated to another cube using a GridBuilder.  In the example available in Part 21: Real Time Data Movement (Getting REALLY Groovy), the data is moved to a P&L cube at a summary level and doesn’t include the channel, material group, and vendor dimensions.  A new Groovy map can be created (which we will do here), or we can alter the existing one above.  The source grid would then need to pull data at the total of all these dimensions.  Currency and Period need to be removed as they are in the columns.  Account will be removed and placed in the rows.

def povGridMap = [:]
operation.grid.pov.each{povGridMap[it.dimName] = it.essbaseMbrName}

// Remove dimensions that will be in the rows and columns
povGridMap.remove('Currency')

// Add View dimiension
povGridMap['View'] = 'MTD'

// Change the dimensions that need to pull totals
povGridMap['Channel'] = 'Total_Channel'
povGridMap['Material_Group'] = 'Total_Material_Group' 
povGridMap['Vendor'] = 'Total_Vendor' // Another way to add a new element to the map

builder.addPov(povGridMap.keySet() as List, povGridMap.values().collect{[it]} as List)
// the reason this requires the as List notation is because the addPOV method requires 
// the parameter to be a list type
// From the Java Docs:
// void setPov(java.util.List<java.lang.String> dimensions,
//             java.util.List<java.util.List<java.lang.String>> members)

Let’s walk through the results of the script.  The povMap when created from the Data Form starts with

  • [Version:OEP_Working,
  • Source:NBF_Source,
  • Currency:Local,
  • Company:BILB,
  • Years:FY18,
  • Scenario:OEP_Plan,
  • Material_Group:mI03,
  • Channel:c01]

The edits are then made and the result is

  • [Version:OEP_Working,
  • Source:NBF_Source,
  • Company:BILB,
  • Years:FY18,
  • Scenario:OEP_Plan,
  • Material_Group:Total_Material_Group,
  • Channel:Total_Channel,
  • Vendor:Total_Vendor]

Moving On

Hopefully the difference in the two strategies inspires you to learn a little more about the objects and methods below.

The remainder of this article shows examples that will be extremely useful, and some that will just give you some insight to what is possible.

Lists

Creating And Editing Lists

Lists can be created and referenced a number of ways.  Empty lists can be created.

def emptyList = []

Lists can be created by adding the elements.

Def q1List = [‘Jan’,’Feb’,’Mar’]

Lists can be altered by adding new elements.

def q2List = [‘Apr’]
q2List << (‘May’)
q2List.add(‘Jun’)

or

q2List.addAll(['Apr', 'May', 'Jun'])

They can even be added at specific places.

def q2List = ['Apr']
q2List << ('Jun')
q2List.put(1,'May') // which places it after Apr and before Jun

Elements can be edited based on location.

q2List.set(1,'May') // second item
q2List[-2]'May' // go back 2 from end
q2List[1]'May' / second item

Elements can be removed from lists.

q2List << (‘Jul’)
q2List.remove('Jul')

or

q2List.removeIf{it == 'Jul'}

or

 ['a','b','c','b','b'] - ['b','c'] // result is ['a']
Iterating Lists

Iterating on elements of a list is usually done by using one of the following.

  • each
  • eachWithIndex (same as each but include the index of the element)
['Jan','Feb','Mar'].each { month ->
    println "Item: $month"
}
// With the index of the element
['Jan','Feb','Mar'].eachWithIndex { month, index ->
    println "Item: $month is index $index"
}

The list can be altered while iterating, too.  This doesn’t update the list.  It only produces a new list.

println [1, 2, 3].collect { it * 2 }  //would produce [2, 4, 6]
Manipulating lists
Filtering and searching

There are tons of filtering and searching options.  The following are examples, but it isn’t even close to an exhaustive list.  Hopefully it will inspire some thought as to how you can use these methods.

// find 1st element matching criteria
[1, 2, 3].find { it > 1 } // results in 2

// find all elements matching criteria
[1, 2, 3].findAll { it > 1 } // results in [2, 3]

// find index of 1st element matching criteria
['a', 'b', 'c', 'd', 'e'].findIndexOf {it in ['c', 'e', 'g']} // results in 2

['a', 'b', 'c', 'd', 'c'].indexOf('c') // index returned (2)
['a', 'b', 'c', 'd', 'c'].indexOf('z') // index -1 means value not in list
['a', 'b', 'c', 'd', 'c'].lastIndexOf('c') // 4 is returned

[1, 2, 3].every { it < 5 }               // returns true if all elements match the predicate

![1, 2, 3].every { it < 3 }

[1, 2, 3].any { it > 2 }                 // returns true if any element matches the predicate

![1, 2, 3].any { it > 3 }

[1, 2, 3, 4, 5, 6].sum() == 21                // sum anything with a plus() method
['a', 'b', 'c', 'd', 'e'].sum() // 'abcde'
[['a', 'b'], ['c', 'd']].sum() // ['a', 'b', 'c', 'd']

// an initial value can be provided
[].sum(1000) //  1000

[1, 2, 3].sum(1000) //  1006

[1, 2, 3].join('-') //  '1-2-3'

def list = [9, 4, 2, 10, 5]
list.max() // 10
list.min() // 2

// we can also compare single characters, as anything comparable
assert ['x', 'y', 'a', 'z'].min() // 'a'

// we can use a closure to specify the sorting behavior
def list2 = ['abc', 'z', 'xyzuvw', 'Hello', '321']
list2.max { it.size() } //  'xyzuvw'
list2.min { it.size() } //  'z'

['a','b','c'].contains('a')      // true

[1,3,4].containsAll([1,4])       // true

[1,2,3,3,3,3,4,5].count(3)  // 4
Sorting

Groovy offers a variety of options to sort lists, from using closures to comparators.

[6, 3, 9, 2, 7, 1, 5].sort() // [1, 2, 3, 5, 6, 7, 9]
def list = ['abc', 'z', 'xyzuvw', 'Hello', '321']
list.sort {it.size()} // ['z', 'abc', '321', 'Hello', 'xyzuvw']

def list2 = [7, 4, -6, -1, 11, 2, 3, -9, 5, -13]

Maps

Literals

In Groovy, maps (also known as associative arrays) can be created using the map literal syntax: [:].  Maps are use to create associations between two or more elements.  They can be used to lookup values like currency rates, map accounts from one plan type to another, and a host of other things.  Many of the API methods require maps to be passed.

def map = [name: 'Gromit', likes: 'cheese', id: 1234]
map.get('name')   //  'Gromit'
map.get('id')   // 1234
map['name']   // 'Gromit'
map['id'] == 1234

def emptyMap = [:]
emptyMap.size()   // 0
emptyMap.put("foo", 5)
emptyMap.size()   // 1
emptyMap.get("foo")   // 5
Map Notation

Maps also act like joins so you can use the property notation to get/set items inside the Map as long as the keys are strings which are valid Groovy identifiers:

def map = [name: 'Gromit', likes: 'cheese', id: 1234]
map.name   // 'Gromit'  -    can be used instead of map.get('name')
map.id   // 1234

def emptyMap = [:]
emptyMap.size()   // 0
emptyMap.foo = 5
emptyMap.size()   // 1
emptyMap.foo   // 5
Iterating Maps

Maps makes use of the each and eachWithIndex methods to itarate through maps, just like Lists.

def map = [
        Bob  : 42,
        Alice: 54,
        Max  : 33
]

// Loop through each item
map.each { entry ->
    println "Name: $entry.key Age: $entry.value"
}

// add an index
map.eachWithIndex { entry, i ->
    println "$i - Name: $entry.key Age: $entry.value"
}

// Alternatively you can use key and value directly
map.each { key, value ->
    println "Name: $key Age: $value"
}

// Key, value and i as the index in the map
map.eachWithIndex { key, value, i ->
    println "$i - Name: $key Age: $value"

Manipulating Maps

Adding/Removing Elements

Adding an element to a map can be done either using the put method, the subscript operator or using putAll.

def defaults = [1: 'a', 2: 'b', 3: 'c', 4: 'd']
def overrides = [2: 'z', 5: 'x', 13: 'x']

def result = new LinkedHashMap(defaults)
result.put(15, 't')
result[17] = 'u'
result.putAll(overrides) // [1: 'a', 2: 'z', 3: 'c', 4: 'd', 5: 'x', 13: 'x', 15: 't', 17: 'u']

Removing all the elements of a map can be done by calling the clear method:

def m = [1:'a', 2:'b']
println m.get(1) //  'a'
m.clear()
Filtering and searching

It is useful to search and filter maps, and here are some examples of this functionality.

def people = [
    1: [name:'Bob', age: 32, gender: 'M'],
    2: [name:'Johnny', age: 36, gender: 'M'],
    3: [name:'Claire', age: 21, gender: 'F'],
    4: [name:'Amy', age: 54, gender:'F']
]

def bob = people.find { it.value.name == 'Bob' } // find a single entry
def females = people.findAll { it.value.gender == 'F' }

// both return entries, but you can use collect to retrieve the ages for example
def ageOfBob = bob.value.age
def agesOfFemales = females.collect {
    it.value.age
}
// ageOfBob == 32, agesOfFemales == [21,54]

def agesOfMales = people.findAll { id, person -> person.gender == 'M'}.collect { id, person ->
    person.age
}
// agesOfMales == [32, 36]

// `any` returns true if any entry matches the predicate
people.any { id, person -> person.age == 54 }

Conclusion

There is a lot of information and examples here.  What I hope you walk away with is an understanding of what a list and a map is, most importantly.  Understanding how to use these in the API is really important, and manipulating them will save you a ton of time and unnecessary code.




Adventures in Groovy – Part 12: Learning and Testing Groovy Outside of PBCS

Introduction

For people that are new to Groovy/Java, testing functions that Groovy provides can be a tedious and time consuming process.  Learning anything is.  Trying to do this with the wrong tools compounds it.  I have seen some people give up and walk away from trying to improve applications because they struggle with the Groovy Calculations and the complexity it introduces to go beyond some of the basics, just because they are using a hammer when they need a screwdriver.  For example, it is simple to use a documented example and loop through the cells on a form, but to utilize the Groovy/Java objects and methods is the difference between using the default logic and taking Planning to a whole new level.  For those of us who are learning, testing simple functions can be very painful inside a Groovy Calculation.

I will by preface saying I am not a Groovy developer.  I am learning as I need functionality and I am trying to build a foundation to be as productive as possible.  Although Groovy in PBCS doesn’t give developers full access to all the Java libraries, much of the logic that is needed to develop new functionality can be tested outside of PBCS.  I have found that as I learn more and require more non PBCS related functionality, it is easier to test in the Groovy Console rather than in a PBCS calculation.  Some examples are

  • string functions like replace, regex, concatenate
  • mathematical functions
  • other manipulation that require the use of collections and hash tables

These can be used in looping through grid cells or building evaluation rules on data entered.  Hopefully, this is helpful to those learning Groovy.

How To Get Started

Download Java SDK

Before Groovy can be used, Java has to be installed.  Most systems already have it.  If not, the Java Development Kit can be downloaded and installed.  There is information about which version of Groovy and Java are compatible at groovy-lang.org.  The Java SDK can be downloaded from Oracle.

Download An Editor

Groovy can be edited in many free and paid programs.  Some of them are more robust than others and provide things like automatic code completion, color coding, and more advanced features that aren’t likely required at this novice level.  They also increase the complexity for those that are completely new to writing JAVA or Groovy.  If you are interested in this or need a longer term solution, check out these editors.

For those who want to just get started with a simple and supported editor to test some basic code, try the Apache Groovy Console.  The Windows Installer, the documentation, and the SDK are available to download and install.  Once installed, you are ready to go!  If you go to your Start menu in Windows, you will see a folder for the version of Groovy installed.  In that folder click on Start GroovyConsole to open the editor.

Using The Groovy Console

Much of what is done in the Groovy calculations can’t be accessed here.  We don’t have grids, cells, or any of the PBCS  methods that we interact with in a Groovy Calculation.  Groovy can also access the REST API (outside of Groovy Calculations), which opens up the ability to manage PBCS like EPM Automate.  I recently looped through the product catalogue at BestBuy.com and built a hierarchy!  This is a whole other beast, but it is worth mentioning.

Before we jump into testing a script, here are a few things that will be helpful using the Groovy Console.

  • The editor has two panes.  The top pane is where the script is developed and edited.  The bottom pane is where the results of the script are displayed when it is executed.
  • The toolbar has some common functions.  You can open and save your scripts, redo/undo, and execute from icons in this area.

Examples

I find it very helpful as I am learning, to test the logic and the results in this console.  Once validated, it will be moved to the PBCS calculation and used appropriately.  Here are some examples where it might be useful, and hopefully the separation of where to test what is highlighted.

Regex Example

There was a requirement on a form at a recent client where they wanted to accept input.  They used this to setup properties in the HR system.  The HR system could not accept some characters, so the ask was to only allow alphanumeric characters, a space, an underscore, and a dash.  We had to add validation to the run time prompt, as well as when the data was updated in a form.  Not being an expert with regex, I didn’t want to test this in a calculation (update calc, run calc, open job console, expand status, toggle between windows, etc).

So, I opened the Groovy Console and tested there.  The end result is below, but it was much easier to tweak the regex syntax directly in the console, running it, and seeing the result immediately, in one step.  This was easy to see and verify the output was void of any characters that were not allowed.  The length could be compared, pre and post character removal, and was used to stop the save of the data.

String text = "This - text ! has \\ /allot # of % special % characters"
println text
println text.length()
println text.replaceAll("[^a-zA-Z0-9 _-]", "")
println text.replaceAll("[^a-zA-Z0-9 _-]", "").toString().length()
println text.length() == text.replaceAll("[^a-zA-Z0-9 _-]", "").toString().length()

At this point, I proved out the regex functionality.  I can now go back to the Groovy Calculation and use this logic on the variable returned from the PBCS function (whether it be an RTP or a cell value) and remove the invalid characters or test to see if there are any, and act accordingly.  This is what it would look like

String enteredValue = rtps.RTP_NewEmployee.getEnteredValue();
if(enteredValueAdj.length() == enteredValue.length())
{ 
def mbUs = messageBundle(["validation.InvalidChars":"You have entered invalid characters.  Only alphanumeric characters, spaces, dashes, and underscores are accepted."])
def mbl = messageBundleLoader(["en" : mbUs])
throwVetoException(mbl, "validation.InvalidChars", rtps.RTP_NewEmployee)
}
Converting Nested Collections

I was building a Data Map override from a POV, and it wasn’t validating because some of the variables were collections that included a nested collection.  This whole concept was completely new to me, and again, I didn’t want to have to go through 3-5 steps to see if the result was returning a delimited list of members that the Data Map would accept.  Since I had no initial idea how to accomplish this, I searched and found examples that might accomplish what I wanted to achieve.  It took 5 to 10 iterations of examples to get to what I wanted and understand how this worked.  Updating a script in the Groovy Console, running it, and seeing the results in the same window proved much quicker to find a solution.

In the solution below, I created a variable that replicated the variable that PBCS that was returning (a list).  I was able to build out a few lines to eliminate the nested collections and ported this over to my Groovy Calculation.

This proved out that the simple loop below would give me a list I could pass to the Data Map, and was much quicker to solve than trying to do this in PBCS.

def orig_list = [10, 20, [1, 2, [25, 50]], ['Groovy']]
def usable_list = []

orig_list.collectNested([]) { 
 usable_list << it
 } 
println usable_list 
println '"' + usable_list.join('","') + '"'

The result of the executed script created two lines.  At this point, I could use this function in the Groovy Calculation by replacing the orig_list with the object returned from the PBCS function.  I used the usable_list in the Data Map.

[10, 20, 1, 2, 25, 50, Groovy]
“10”,”20″,”1″,”2″,”25″,”50″,”Groovy”

Wrapping Up

These examples are great examples of how we can use a pair of tools to create business logic efficiently.  If you are a seasoned java developer, much of this might seem ridiculous to you and question why one would ever use something outside of PBCS.  I get it.  Now that I know how these two function work, I likely will not use the Groovy Console to write and test this.  But, as I continue to learn more and more, being able to do this in something outside of PBCS has proven invaluable, increased my productivity, and significantly reduced my frustration.

If you are learning, or are an experienced Groovy developer, please share your insights with the community and post a comment!