Adventures in Groovy – Part 24: Don’t Be Stingy With Reusable Code

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.  When you start doing more in Groovy calculations, the more you can replicate the better.  Making some of the code business rule agnostic will make you more productive and will create a consistent approach to calculation strategy.  An example of this would be variables for the dimensions in the POV.  Or, maybe a common map used to push data from WFP to the P&L.  Regardless of the use, as soon as you see things that will be duplicated, put them in a script.  Scripts can be embedded in Groovy calculations just like regular Business Rules.

Header Script

This is an example of something that I find useful for every Groovy calculation I am writing.  It accomplished a couple things.

  1. It stores the forecast months so data maps, smart pushes, Essbase calculations, and grid builder functions, can be dynamically execute on all months for a budget and the out months for the forecast based on the scenario selected.
  2. It gets the numeric value for the current month to be used in other localized calculations.
  3. It creates a calendar object to get the current time for logging performance.
  4. The parameters are logged to the job console.
/*RTPS: {RTP_Month}*/

def Month = rtps.RTP_Month.toString().replaceAll("\"", "")
def MonthFix = rtps.RTP_Month.toString()
def months = ['Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec']
def actualMonths = []
def forecastMonths = []

months.eachWithIndex {item, index ->
  if(index > months.findIndexOf{it == Month})
    forecastMonths <<  item 
  else
    actualMonths <<  item 
}

def actualMonthsFix = """\"${actualMonths.join('", "')}\""""
def forecastMonthsFix = """\"${forecastMonths.join('", "')}\""""
def useMonths = []

if(operation.grid.pov.find{it.dimName =='Scenario'}.essbaseMbrName.toString().toLowerCase().contains('forecast'))
  useMonths = forecastMonths
else
  useMonths = months

//Get time for logging perforance
Calendar calendar = Calendar.getInstance()
calendar.setTimeInMillis(currentTimeMillis())

// Get the numeric value for the month (Jan=0)
int monthNumber = Calendar.instance.with {
  time = new Date().parse( "MMM", Month )
  it[ MONTH ]
}

println "The current month number is $monthNumber"
println "The current month is $Month"
println "The current month (Fix) is $MonthFix"
println "The actual months (list) are ${actualMonths}"
println "The actual months (string) are ${actualMonthsFix}"
println "The forecast months (list) are ${forecastMonths}"
println "The forecast months (string) are ${forecastMonthsFix}"

Conversion Table

If the application moves data from more detailed plan types to a consolidated P&L, it likely has conversions.  This is an example of an account map from a Gross Profit plan type to a P&L plan type.  How it is used will be explained in a later submission, but it is used in every calculation that synchronizes data from the GP to the P&L cube.  Therefore, it is a great candidate to have in a shared library (a script) so it can be maintained in one place.

def acctMap = ['Cases':'Units',
               'Sales':'42001',
               'COGS':'50001',
               'Tax':'50015',
               'Chargeback':'56010',
               'Investment Gains':'50010',
               'Writeoffs':'56055',
               'Growth Attributed to New Products':'56300',
               'Samples':'56092'
                ]

Sharing Scripts

Just as scripts can be embedded in regular business rules, the exact same syntax is used in Groovy calculations.  Assume the header script above is called Common Groovy and sits in an application named FinPlan in the GP plan type.  The inclusion of the script into any  Groovy calculation would be done by inserting the following text.

%Script(name:=" Common Groovy ",application:="FinPlan",plantype:="GP")

Conclusion

These are just examples to get you thinking about how you can reduce the headaches down the road when your Groovy calculations need maintained.  It can easily be expanded to include common POV members and other common code.  Be mindful of how global this is as not all POV members are in all forms.  You might find it useful to have a script that is shared everywhere and a few others shared for like calculations or forms.  You aren’t limited to only including one shared script.

If you have a snippet you are using in all your Groovy calculations, share it with the community.  We always like to get feedback and learn from each other.




Building A Planning Application Using EPMA (11.1.2.3)

This post covers the basics of building a Planning app. Building a Planning application can be a straightforward process; however, there are some pitfalls to watch out for. One or two mistakes can lead to some major headaches in trying to decipher what went wrong. Whether a beginner or a seasoned vet looking for a quick refresher, this guide will outline the steps necessary to successfully create/deploy a Planning application.

First, open up the dimension library, and then click File, New, and Application. This will take you to the first screen in the Application setup: Application Type

 

 

A. Application Type

There are 3 sections that make up the first screen

  • Application Information
  • Planning
  • Calendar

We will tackle them one at a time…

Application Information:

  1. In this first section, give a name for your application, as well as select Planning from the type dropdown. Giving the application a description is optional
  2. Since we are creating an application using dimensions from the EPMA library, leave “Create Blank Application” & “Auto Create Local Dimensions” unchecked

Planning:

  1. Select the application type (typically this will be General), and select the default currency for the application. You can select “Use Multiple Currencies” but this is not the most effective way of going about it. For more detailed insight on the utilization of multiple currencies in a Planning app, check out the following article:
  2. Select and name the Plan Types that will be in the application. From the screenshot below, the application will have 3 plan types (Plan1, Plan2, Plan3)

 

Calendar:

  1. Monthly, Quarterly, and Yearly application setup.
    • Base Time Period: This can be 12 months, Quarters, or Custom (if you need a lower level of detail such as weeks)
    • Fiscal Year First Month/Fiscal Year Start Date: Set the first month of the fiscal year as well as if it is the same calendar year or the previous calendar year
    • Weekly Distribution: There are 4 options from this dropdown (Even, 445, 454, 544)
  2. CAUTION: For the following section, both the Period and Year dimensions should be renamed to “Periods” & “Years” if Dynamic Time Series is going be turned on for the application (M-T-D, Q-T-D, Y-T-D, etc). If the dimensions are named Year and Period, Planning will not be able to differentiate between the dimensions and the dynamic time series members, causing errors.
  3. Name the dimension “Years” and select the first fiscal year/number of fiscal years. The number of years ranges from 1-100.

After completing all of the above settings, click next to move on to the next screen, Dimension Selection:

 

B. Dimension Selection

This screen will have sections for the required, custom, as well as other dimensions for the application. First, the screen will auto populate whatever dimensions are already in the EPMA library. In this example, Entity, Account, and Alias are already filled in. The remaining dimensions (Version & Scenario) need to be populated via the dropdown menu:

These dimensions will be added as local dimensions to the application. Give the dimensions a name and make sure that the type matches the dimension that is being updated:

After setting the required dimensions, the custom dimensions can be selected in the next section. By clicking on the “[Select]” dropdown, the rest of the dimensions from EPMA will be available for selection in the application (note: your dropdown menu will not display the same selection as in this screenshot, as these dimensions had to be created in the Shared Library first):

If there are any Attributes, Smart Lists, or UDA’s that need to be added, the “Other Dimensions” section is the place to do that. Notice that there are 3 different Attributes to choose from in our Test application (I created these in the Shared Library beforehand as an example).

Reviewing the dimensions, there are 4 new local dimensions (Version, Scenario, Years, & Periods), 3 custom dimensions (Customer, Product, & BusinessUnit), & 3 other dimensions (Attribute, SmartList, & UDA). Click Next to go to the Application Settings screen.

 

C. Application Settings

  1. The entire dimensionality of the new application is available to view/edit. Exclude/add members to the hierarchy as necessary. Make sure that all the dimensions are accounted for.
  2. Properties of the application: This is a review of the application settings from the first screen. Double check that base time period, fiscal start year, and fiscal year first month are set. Also, make sure that the default alias table is set.

After making those changes, click Validate. A list of errors and warnings will populate below. These errors and warnings need to be taken care of before clicking Finish. There are a wide variety of errors that may be encountered such as duplicate members, duplicate aliases, invalid members, invalid plan types, etc. depending on your application. After fixing an error, click validate again and the error will disappear from the list.

Once there are no more errors, the application is ready to be deployed. Click Finish and go to the Application Library and right click, then deploy the application.




BUG REPORT – Shared Members Security in EPMA

Oracle has confirmed a bug related to the deployment of security with a planning application maintained in EPMA in version 11.1.2.x.  When the Shared Members checkbox is selected in an EPMA deployment of a Planning application, it ignores this option.  Even if the Shared Members box is checked, the user still only gets access to Ohio Region, and not the children, in the example below.   Oracle is currently working on a patch.

What Does Checking Shared Members Do?

By default, any member that is a shared member under a parent with security, it gets excluded.  For example, if the security for Ohio Region is set to @IDESCENDANTS with READ access, the three members below Ohio Region would have no access.
– Ohio Region
– Columbus (Shared)
– Cincinnati (Shared)
– Cleveland (Shared)

The filter that gets pushed to Essbase would look something like this.

@REMOVE(@IDESCENDANTS(“Ohio Region”),@SHARE(@IDESCENDANTS(“Ohio Region”)))

When the shared members are checked, it tells Hyperion that you want to include shared members in the security.  The same example above, with shared members selected, would give users access to all 3 members.  The filter that gets pushed to Essbase would then look like this.

@IDESCENDANTS(“Ohio Region”)

The Workaround

The workaround for this is to deploy the hierarchies from EPMA, and Refresh the database (security only) with Shared Members selected from Hyperion Planning.

When a patch is released, we will release the details.




Hyperion Release 11 Architecture and Installation, Part 4 of 5

“Validation”

In installment #3 of this series we installed and configured the 11.1.x software.  In this installment we will discuss what Infrastructure Architect will do before the environment is turned over to the development or migration teams.

It is quite frustrating to the developers if the environment is not fully functional when they start using the system.  Additionally, it is very frustrating for the installation architect to have users in the environment as debugging of issues is occurring.  Each installation and configuration project plan should include at least a day or two to review an environment, restart it a few times, check out the logs, and then test the functionality of all installed components.  The number of items to validate depends on the products used and licensed by the client but it should start with the following and adjust as necessary.

  • Shared Services
  • Essbase
  • EAS and Business Rules
  • Planning
  • Financial Reporting
  • Web Analysis
  • Interactive Reporting
  • SQR
  • Workspace
  • Smart View and Provider Services
  • Financial Management
  • Financial Data Quality Management
  • Oracle Data Integrator
  • Data Relationship Management
  • Strategic Finance

The Installation Architect will test the use functionality of each of the installed product to ensure there are no errors.  This activity takes a combination of functional and technical ability.  The installation architect must know how the application works from the interface as well as understanding what any potential errors mean and how they may be corrected.  I’m not suggesting the infrastructure engineer know how to create a P&L report or design a Planning application, but the ability to navigate the user interfaces and test functionality eliminates the problems of encountering them after development has begun.

Early in my exposure to these applications, I’d spend a lot of time with a developer or functional user of the applications to show me how to test the system.  I’d ask them to tell me the first thing they try to do when they get a new environment.  It is always useful to know more about how the applications are used.

Some of the common problems that occur include the following.
EPMA dimension server does not resolve in Workspace
Shared Services doesn’t find users in Active Directory
Cannot create Planning Application
Cannot create FDM Database
ODI repositories are not available
Common Essbase commands do not work

The solutions to some of these problems may range anywhere from Database Access Permissions, Windows Security Rights, DCOM Settings, or incorrect Active Directory Setup.  Over the past few years working on dozens of installation, I’ve seen all of these.  From encountering many of these, the pre-installation requirements covered in installment #2 have been improved.  Some of these problems don’t arise until functionality is tested.  It’s important to test each installation and environment.  I’ve had situations where the development environment will test out fine and the QA Environment will have issues.  Each installation is usually different from each prior installation because of server settings, security policies, database settings, firewalls, or some other nuance.

If there are problems with the functionality there are a number of resources available to assist in troubleshooting.  I find the Oracle Technology Network Forum to be very useful.  I recommend anyone looking to work in this space, get an ID, and get involved.  You may also find some real useful things on blogs like this or a number of other very experienced bloggers.  There is a wealth of information at Oracle Support in the knowledge base.  In addition, if you have a support agreement with Oracle, register here and you can get support from Oracle.

Assuming everything is functioning as expected, the environment is turned over to the appropriate support person, or maybe support falls on the same individual that did the installation.  Either way, there is a lot of information that needs to be collected.  In the next installment, we’ll look at the information that should be compiled to capture the state of the environment as it was at the end of the installation as well as information that is useful to those that will be using the system.