Game on!  A record number of abstracts were submitted for Kscpoe12, and here are 3 presentations you will want to check out. 

Session 1: Financial Reporting

Josh Forrest is the best FR person I have ever worked with.  He is presenting “Simplify report development utilizing best-practice methodologies with Hyperion Financial Reporting.”  He is going to cover the benefits and drawbacks of using shared objects, among other things. 

Title:  Simplify report development utilizing best-practice methodologies with Hyperion Financial Reporting
Date:  June 27th
Time:  3:00 PM-4:00 PM

Session 2: Infrastructure

Rob Donahue is one of the most talented architecture resources you will ever find.  The Kscope team thinks so highly of him that they have asked him to be involved, have given him a time slot, and are going to determine the topic at a future date.  Whatever the topic, if you want to learn something about the infrastructure side, make sure you attend.

Date: June 25th
Time:  8:30 AM-9:30 AM

Session 3: Hyperion Planning

I will be presenting a Hyperion Planning solution that provides a great way to give users the ability to spread data at a finite level on any spread method defined.  If you are looking to create, or enhance, your budgeting process, you won’t want to miss how we solved this problem. 

Title:  Create Unlimited Custom Spreads for Driver Based Planning
Date:  June 25th
Time:  11:30 AM-12:30 PM

There are times when planning and forecasting databases grow for apparently no reason at all. The static data (YTD actuals) that is loaded hasn’t changed and the users say they aren’t doing anything different.

If you load budgets or forecasts to Essbase, you probably do what I’m about to tell you. If you are a systems administrator and have never seen how finance does a budget or forecast, this might be an education.

The culprit? More...

There are several ways to export data from Essbase on a large scale. Pulling it via Excel (Smart View or the Essbase Add-In) is not the best way to get large amounts of data when the goal is to move the data somewhere else, so this option will not be covered.

Database Export

The easiest method is to export all the data from a database by exporting the database.  This can be done in EAS.  This method is easy to automate with Maxl, but has little flexibility with formatting and the only option is to export all the data.  It can be exported in column format so the data can easily be loaded into another data repository.  If the data needs to be queried, or manipulated, this is a good option.  More...

Goodbye to the days of JavaScript in order to enforce data input policies and rules to Planning web forms.  With Planning version 11.1.2 and newer, Oracle has introduced a powerful set of tools for data validation within the Planning Data Form Designer itself.  Let’s walk through a scenario of how this works.

Say that we have a product mix form that will be used to input percentages as drivers for a revenue allocation.  Here’s what the form looks like:

We should expect that the sum of these percentages to be 100% at the “Electronics” parent member.  If this is not the case, the revenue allocation will incorrectly allocate data across products.  So how do we enforce this rule?  Simple… let’s take a look at the data form design.

As a row definition we’ve included two member selections; 1) Descendants(Seg01) or Descendants(Electronics) and 2) Seg01 or Electronics.  We are going to add a validation rule to row 2 of the data form.  To do this, highlight row 2 and click the + sign to add a new validation rule.  Notice that in the validation rules section, it now says ‘Validation Rules: Row 2’. 

The Data Validation Rule Builder will then be launched. Let’s fill in the rule.  We should ensure the Location is set to ‘Row 2’.  We’ve filled in a name and quick description, then ensured that the ‘Enable validation rule’ check box is checked.

For the rule we’ve defined some simple if logic:

IF [Current Cell Value] != [Value = 1] THEN [Process Cell] ;

To define what occurs if this condition is met we choose the ‘Process Cell’ action defined by the small gear with a letter A next to it.  Here we will highlight the cell red and notify the user with a validation message.

We click through to save the Process Cell definition and the Validation Rule itself and should now see the rule in the data form definition.

So let’s take a look at how the end user will interact with this form.  Percentages are entered by product for each month.  Upon save, notice that all months for Electronics that equal 100% appear normal.  December only sums to 90% and is highlighted in red as we specified in the data validation rule.  We cannot limit the user’s ability to save the form until the cell equals 100%; we can only notify them of the issue, and explain the cause and potential resolutions.

Of course, this is a simple example of what can be done using Planning’s Data Validation Rules.  The possibilities are endless.  Oracle has more scenario walkthroughs in the Planning Administrator’s Guide.  View them here: http://download.oracle.com/docs/cd/E17236_01/epm.1112/hp_admin/ch08.html

The format of the data that is loaded to Essbase is often an after-thought.  But, should it be?  When requesting the data file from a source system, it is more important than you may think to have it sorted to mirror your outline.    

Assume an outline has the following dimensions.

• Period [DENSE]
• Account [DENSE]
• Region [SPARSE]
• Category [SPARSE]
• Product [SPARSE]
• Organization [SPARSE]

The most efficient way to receive a data file would be to have it sorted by Organization, Product, Category, Region, and then Account.  Data files load faster when the columns that hold the sparse members are sorted in reverse order of the sparse dimensions that exist in the outline.

The reason the data loads faster is because it opens a block of data only one time.  If the data was sorted by the dense members first, then every block would have to be opened multiple times.  If the same sparse member combinations have 3,000 dense members with data, the block would be opened up to 3,000 times.  

There are some more important benefits of doing this, however.  When the block is opened multiple times, the database becomes far more fragmented than it needs to be.   Fragmentation causes calculations to be slower and retrieving data can also be impacted, which can lead to frustrated customers.

By not sorting the data when loaded, every time a data load occurs, any performance issues that may exist are exacerbated.  So, anytime possible, sort the data load files by the last sparse dimension in the outline, the second to last sparse dimension in the outline, and so on.  You may be presently surprised at the benefits.

Everybody knows the quickest way from point A to point B is a straight line.  Everybody assumes that the path is traveled only one time – not back and forth, over and over again.  I see a lot of Essbase calculations and business rules, from experienced and novice developers, that go from point A to point B taking a straight line.  But, the calculation travels that line multiple times and is terribly inefficient. 

Here is a simple example of a calculation.  Assume the Account dimension is dense, and the following members are all members in the Account dimension.  We will also assume there is a reason to store these values rather than making them dynamic calc member formulas.  Most of these are embedded in a FIX statement so the calculation only executes on the appropriate blocks.  To minimize confusion, this will not be added to the example.

Average Balance = (Beginning Balance +Ending Balance)  / 2;
Average Headcount = (Beginning Headcount + Ending Headcount) / 2;
Salaries = Average Headcount * Average Salaries;
Taxes = Gross Income * Tax Rate;

One of the staples of writing an effective calculation is to minimize the number of times a single block is opened, updated, and closed.  Think of a block as a spreadsheet, with accounts in the rows, and the periods in the columns.  If 100 spreadsheets had to be updated, the most efficient way to update them would be to open one, update the four accounts above, then save and close the spreadsheet (rather than opening/editing/closing each spreadsheet 4 different times for each account).   

I will preface by stating the following can respond differently in different version.  The 11.1.x admin guide specifically states the following is not accurate.  Due to the inconcistencies I have experienced, I always play it safe and assume the following regardless of the version.

You might be surprised to know that the example above passes through every block four times.  First, it will pass through all the blocks and calculate Average Balance.  It will then go back and pass through the same blocks again, calculating Average Headcount.   This will occur two more times for Salaries and Taxes.  This is, theoretically, almost 4 times slower than passing through the blocks once.

The solution is very simple.  Simply place parenthesis around the calculations.

(
Average Balance = (Beginning Balance +Ending Balance)  / 2;
Average Headcount = (Beginning Headcount + Ending Headcount) / 2;
Salaries = Average Headcount * Average Salaries;
Taxes = Gross Income * Tax Rate;
)

This will force all four accounts to be calculated at the same time.  The block will be opened, all four accounts will be calculated and the block will be saved. 

If you are new to this concept, you probably have done this without even knowing you were doing it.  When an IF statement is written, what follows the anchor?  An open parenthesis.  And, the ENDIF is followed by a close parenthesis.  There is your block!

"East"
(IF(@ISMBR("East"))
    "East" = "East" * 1.1;
ENDIF) 

I have seen this very simple change drastically improve calculations.  Go back to a calculation that can use blocks and test it.  I bet you will be very pleased with the improvement.