There is a new, and often requested, option added to Smart View. If you use drill through to Data Management (PBCS) or FDMEE (On Prem), download the most recent release of Smart View. We, as users, now have the option to change where the result of our drill through queries is returned. Users can either be asked where the result should be displayed, have it displayed in your browser, or (drum roll) have another tab created that holds the results in Excel. Read more
Tag Archive for: PBCS
I was fortunate enough to speak for ODTUG a few weeks ago and really excited that my discussions around Groovy are getting some of the most attended and most interactive ODTUG webinars. If you have put any of these presentations together, you know how much time it takes to do the research, consolidate the information, make it presentable, and spend the time to hopefully make it fluid. So, when you provide feedback, I really appreciate it.
The Webinar
There were some questions I wasn’t able to answer, so here goes.
Can the dataCellIterator take functions like @IDESC, @CHLIDREN, etc?
The iterator iterates through the grid, so it doesn’t have the ability to do this directly. I am not sure of the question, but you could iterate through the grid and for each cell use these functions in other classes/methods to do things like see if it has children, or check to see if it is a child of something.
Is there a way to have a groovy business rule to call a non-groovy business rule – for example if cells were edited then run BR1 else return?
Yes and no. There is no way to execute another rule. But, you can embed it into the script like you can in any other rule or script. You can neither write the string or drag and drop the rule into the Groovy rule. It doesn’t always put it where the cursor is, but you can cut and paste it to wherever you need it. It basically is like an include and just embeds the script text, so it would need to be in a string builder.
Can We improve the Aggregations with Groovy?
Yes and no. If you dynamically create an aggregation script that is the exact same as a normal rule, the same time would result. Where you do get a benefit is that you can only consolidate the impacted members and dimensions based on what has been edited. You can also move the data from the BSO to the ASO cube and eliminate the need to aggregate, which would obviously improve the perception of speed.
Does groovy interact with workflow, valid intersection, copying attached documents / supporting detail?
Workflow is in the roadmap. I confirmed last week with development, so it is coming. Attachments and supporting detail can be copied by executing smart pushes.
Can grids be generated on the fly using groovy?
They sure can, but they aren’t visible to the user. There are two grid builders for retrieving and submitting data.
Is there any documentation available to give performance comparisons between business rule/calc and groovy?
Not that I know of, but as previously stated, Groovy doesn’t make Essbase faster. The perception to users will be that it does, but it is only because we have the ability to isolate what we calculate more than we did before. That said, if you use the grid builders to do the calculations and submit the results rather than use BSO calculations, you might see different results. There are some things (allocations) that I think is faster in BSO. I think using the grid builders on ASO – I do see improvements in performance using Groovy over procedural calculations. But, I want to emphasize that the majority of the time the speed is improved because of the ability to calculate only what we need to.
Groovy collections are used all throughout the ePBCS API. If you are not familiar with collections, you may want to take a look at Adventures in Groovy – Part 27: Understanding Collections before you continue. Maps, which are a type of collection, are very useful when moving data between different applications that have different member names representing the same data. In a the example below, data is moving from a product revenue cube to a financial cube. In the detailed cube, the member names are more descriptive, like Net Sales. In the financial application, the same data is a true account number from the GL, and names 42001. Mapping data between these two can easily be done with Groovy maps.
Introduction
There are two components to understanding the use of these maps. First, the map must be defined for use. The construction of the map is a delimited list of items. Each of the items is made up of an key and a value. These are separated by a colon.
//set account map def acctMap = ['Units':'Units', '42001-Product Sales':'Net Sales', '50001-Cost of Sales':'COGS', '50015-Write-offs':'Write-offs', '56010-Chargebacks':'Customer Satisfaction Discount', '50010-Sales and Discounts':'Sales and Discounts', '56055-Overstock Discount':'Overstock Discount', '56300-Customer Satisfaction Discount':'Customer Satisfaction Discount', '56092-Multi-Purchase Discount':'Multi-Purchase Discount', '56230-Open Box Discount':'Open Box Discount', '56200-Damage Container Discount':'Damage Container Discount', '56205-Damaged Box Discount':'Damaged Box Discount', '56090-Group Purchase Discount':'Group Purchase Discount']
The second piece is retrieving the mapped value. The value on the left of the colon is referenced and the value on the right will be returned. The following would return 56230.
[acctMap.get("56230-Open Box Discount")]
A fully vetted example follows of moving data from one database to several others. The function’s use is embedded in a loop, so rather than a hard coded value, the member of the account dimension is used as the accounts (rows in the form) are being iterated. It looks like this.
[acctMap.get(it.getMemberName('Account'))]
Working Use Case
The map above is used in several places for several reasons. First, the map is created. Second, the map is iterated and the key is used to create a data grid for all the values that will be copied, or synchronized, to the destination cube. Third, the map is used to lookup the converted value to create the grid connected to the destination. this is a complete working example. The items in red are specific to the map and its use.
//Dimension employeeDim = operation.application.getDimension("Account")
//****************************************************************************
// Data Movement between Apps
//****************************************************************************
// Get POV
String sCompany = operation.grid.getCellWithMembers().getMemberName("Company")
def sMaterialGroup = operation.grid.getCellWithMembers().getMemberName("Material_Group")
String sChannel = operation.grid.getCellWithMembers().getMemberName("Channel")
def lstProducts = []
operation.grid.dataCellIterator({DataCell cell -> cell.edited}).each{
lstProducts.add(it.getMemberName("Product"))
}
String strProducts = """\"${lstProducts.unique().join('","')}\""""
println "data push running for " + strProducts
if(operation.grid.hasSmartPush("Prod_SmartPush") && lstProducts)
operation.grid.getSmartPush("Prod_SmartPush").execute(["Product":strProducts,"Currency":'"USD","Local"'])
//set account map
def acctMap = ['Units':'Units',
'42001-Product Sales':'Net Sales',
'50001-Cost of Sales':'COGS',
'50015-Write-offs':'Write-offs',
'56010-Chargebacks':'Customer Satisfaction Discount',
'50010-Sales and Discounts':'Sales and Discounts',
'56055-Overstock Discount':'Overstock Discount',
'56300-Customer Satisfaction Discount':'Customer Satisfaction Discount',
'56092-Multi-Purchase Discount':'Multi-Purchase Discount',
'56230-Open Box Discount':'Open Box Discount',
'56200-Damage Container Discount':'Damage Container Discount',
'56205-Damaged Box Discount':'Damaged Box Discount',
'56090-Group Purchase Discount':'Group Purchase Discount']
Cube lookupCube = operation.application.getCube("rProd")
DataGridDefinitionBuilder builder = lookupCube.dataGridDefinitionBuilder()
builder.addPov(['Years', 'Scenario', 'Currency', 'Version', 'Company','Store_Type','Department','Source','Product','View'], [['&v_PlanYear'], ['OEP_Plan'], ['Local'], ['OEP_Working'], [sCompany],['Store_Type'],['Total_Department'],['Tot_Source'],['Tot_Product'],['MTD']])
builder.addColumn(['Period'], [ ['ILvl0Descendants("YearTotal")'] ])
for ( e in acctMap ) {
builder.addRow(['Account'], [ [e.key] ])
}
DataGridDefinition gridDefinition = builder.build()
// Load the data grid from the lookup cube
DataGrid dataGrid = lookupCube.loadGrid(gridDefinition, false)
def povmbrs = dataGrid.pov
def rowmbrs = dataGrid.rows
def colmbrs = dataGrid.columns
def tmpColMbrs = []
//Fin Grid Setup
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")
finGrid.addPov('&v_PlanYear','OEP_Plan','Local','OEP_Working',sCompany,'Prod_Model')
rfinGrid.addPov('&v_PlanYear','OEP_Plan','Local','OEP_Working',sCompany,'Prod_Model','MTD')
def colnames = colmbrs[0]*.essbaseMbrName
String scolmbrs = "'" + colnames.join("', '") + "'"
finGrid.addColumn(colmbrs[0]*.essbaseMbrName as String[])
rfinGrid.addColumn(colmbrs[0]*.essbaseMbrName as String[])
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)
}
DataGridBuilder.Status status = new DataGridBuilder.Status()
DataGridBuilder.Status rstatus = new DataGridBuilder.Status()
DataGrid grid = finGrid.build(status)
DataGrid rgrid = rfinGrid.build(rstatus)
finCube.saveGrid(grid)
rfinCube.saveGrid(rgrid)
Finishing Up
This is a relatively simple concept and not terribly difficult to implement. It is also something most will benefit from when synchronizing data with the dataGridBuilder. Have something to add? Post a comment and I will get back to you promptly.
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. Read more
If you have used Smart Pushes, you have undoubtedly run into security issues. The PBCS development team is working on a way to bypass this, but there is no release date. If you haven’t run into this, you probably have and didn’t know it. Here is the issue. Read more
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. Read more
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. Read more
One of the more surprisingly useful things that can be done in a Groovy calculation is querying metadata. This was discussed in Part 11: Accessing Metadata Properties, but I recently had a situation where users could load data at parent levels and have it allocated down to the bottom of the hierarchies. Read more
Building on the previous post and in the spirit of reusing code, this will expand on the concept by walking through the creation and use of functions in Groovy calculations. Functions have huge value in reducing your Groovy calculations and streamlining development. Read more
Now that you are knee deep in Groovy, help yourself out and reuse common code. The more you learn, the more efficient you will be and will laugh at some of your initial attempts at your Groovy calculations. If you are like me, you get excited about all the possibilities and learn as you go. When you find better ways to do something, or even preferable ways, you end up with an application with inconsistent representations of the same logic. You are too busy to go back and update all the snippets you have improved, so it stays in a somewhat messy state.
Do yourself a favor and start reusing some of the things you need in every script. Read more