Monthly Archives: May 2015

Oracle Hyperion Financial Management (HFM): The Value Dimension Explained!

May 27, 2015

Oracle’s consolidation tool, Hyperion Financial Management (HFM), offers a number of system-generated dimensions.  Perhaps the most important of these dimensions is the “Value” dimension.  You’ll really do yourself a favor if you can understand how the Value dimension works in HFM, as this dimension drives the consolidations performed by the system. The Value dimension can seem complex, especially when trying to validate and reconcile data.  Simply put, the Value dimension is comprised of the different types of stored data in an HFM application.  Input data, translated data, adjustment data, and consolidation data can all be viewed separately.  In this way, the Value dimension provides an audit trail of data types.

Below is a screenshot of the Value dimension.  We will focus on the members in the red box for this blog.  In the Value dimension, members are grouped in triplets.  The triplets we will look at are Entity Currency->Entity Curr Adjs->Entity Curr Total and Parent Currency->Parent Curr Adjs->Parent Curr Total.

Joe 1

Every member of the Entity dimension is assigned a currency in the metadata.  Entity Currency will store the values for an entity in its assigned currency (sometimes referred to as “default currency” or “local currency”).  An entity’s Parent Currency is actually what it sounds like – it is the default currency of an entity’s parent.  Parent Currency will store the values for an entity translated to the currency of its parent.  This stored value will be generated when a consolidation is run in the system.  This consolidation process performs currency translations based on exchange rates that have been entered in the application.   Shown below is a ‘Canada Company’ entity which is the lone descendant of ‘US Parent’ in the organizational structure.  For ‘Canada Company’ in the Cash account, Entity Currency (which is CAD) is 2,500.00 and Parent Currency (which is USD) is 2,463.46.  Where ‘Canada Company’ is the lone descendant in the hierarchy of ‘US Parent’, 2,463.36 is the amount in Entity Currency (USD) for ‘US Parent’ as well.

Joe 2

Now to add a wrinkle: let’s look at the idea of adjustments being put into the application via journal entries.  The HFM journal process is a topic for a future blog, but understand that journal data is stored in a specific Value dimension member called “Entity Currency Adjust.”  Here we see that journal entries have their own home because the Value dimension acts like an audit trail!

The simple mathematical formulas to keep in mind are as follows:

Entity Currency + Entity Currency Adjust = Entity Currency Total

Entity Currency Total x foreign currency rate (which in this example is 0.9758) = Parent Currency

Below we see journal entry data in the amount of 100.00 in Entity Curr Adjs (the system abbreviation of Entity Currency Adjust).  This 100.00 gets added to the 2,500.00 in Entity Currency to give us 2,600.00 in Entity Curr Total.  The 2,600.00 is then translated to 2,537.08 at Parent Currency by the system.

Joe 3

Clients often want to know why a journal posted to Entity Currency Adjust does not show up as a translated value in Parent Currency Adjust.  The Value dimension does not work that way.  Entity Currency and Entity Currency Adjust are first added together in the Entity Currency Total triplet.  The Entity Currency Total value is then translated to produce a value in Parent Currency.  Parent Currency Adjust is reserved for journals that are posted in the currency of an entity’s parent.  Parent Currency combined with Parent Currency Adjust equals Parent Currency Total in this triplet.

The mathematical formula to would be:

Parent Currency + Parent Currency Adjust = Parent Currency Total

Building on our previous example, below we have added a Parent Curr Adjs (the system abbreviation for Parent Currency Adjust) in the amount of 200.00.  Combined with the translated value of 2,537.08 in Parent Currency, the amount in Parent Currency Total is calculated at 2,736.08.

Joe 4

There will be more to come on the other members of the Value Dimension, but if you can grasp what I have covered so far you are well on your way to understanding the intricacies of HFM dimensionality.

Author: Joseph Francis, Performance Architects


© Performance Architects, Inc. and Performance Architects Blog, 2006 - present. Unauthorized use and/or duplication of this material without express and written permission from this blog's author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Performance Architects, Inc. and Performance Architects Blog with appropriate and specific direction to the original content.

Oracle Data Integrator (ODI) Load Parameters for Oracle Business Intelligence Applications (OBIA)

May 22, 2015

Oracle Business Intelligence Applications (OBIA) use substitution variables which control the way data is loaded.  These variables must always be configured during an implementation of BI Applications.  The most important of these are the extract dates – which control the timeframe of data to be loaded into the system.

During the initial phases of a BI Apps project you would never do a full load of data into the warehouse, instead 1-3 months of data is usually acceptable.  Each BI Apps module uses different variables to control the timeframe of data being loaded.  For instance, there are a total of nine variables that control the extraction of the HR Analytics module!

INITIAL_EXTRACT_DATE (Note:  Common to all BI Apps, controls the overall timeframe if no specific variable is defined.)
HR_ABSENCE_EXTRACT_DATE
HR_ACCRUAL_EXTRACT_DATE
HR_WRKFC_EXTRACT_DATE
HR_PAYROLL_EXTRACT_DATE
HR_RCRTMNT_EXTRACT_DATE
HR_TIMELABOR_EXTRACT_DATE
HR_WRKFC_SNAPSHOT_HIST_EXTRACT_DATE
HR_WRKFC_SNAPSHOT_DT 

Often times, during the debugging of an executed load plan, it is important to lookup the value of these parameters in order to test the SQL.   For instance, let’s say your load plan has failed on a specific step and you want to investigate the SQL behind that step.

  1. Open the executed Load Plan by navigating to the ODI Operator > Load Plan Executions > All Executions and then double click on the load plan in question.  This will load the Load Plan on the right-hand side of the ODI user interface.
    JM 1
  2. Once the load plan is open, then click on the “Steps” tab – which will display all the Load Plan steps.
    JM 2
  3. Then expand to the step you are interested in and expand
    JM 3
  4. Then  scroll to the right and click on the Session ID
    JM 4
  5. Once the session opens navigate to Steps
    JM 5
  6. In this case navigate to the Load Data step and double click
    JM 6
  7. This will open the Session Task for loadingIn this case I’m interested in the Source Code
    JM 7
  8. You will then copy the SQL from this window and past it into your favorite SQL tool, in my case I’m using Oracle SQL Developer
    JM 8
  9. Finally, you will need to run find over the SQL code for anything that begins with “#BIAPPS”

In my case I have the following:

——————-
AND ( PAY_PAYROLL_ACTIONS.CREATION_DATE >= TO_DATE(SUBSTR(‘#BIAPPS.HR_PAYROLL_EXTRACT_DATE‘,0,19),’YYYY-MM-DD HH24:MI:SS’) )
——————-

You will need to look-up the parameter in order to know what substitution variable to put in place:

If you setup OBIA properly you would have set these variables in the BIACM.

Which is here: http://servername:9704/biacm/

1- Goto Data Load Parameters Administration
JM 9

2- Search on the parameter “Code”

3- Click the parameter code and look at the child record underneath

4- In this case 3/1/15

In this case I will then backspace over #BIAPPS.HR_PAYROLL_EXTRACT_DATE and replace it with 2015-03-01.

Now you can run the SQL and investigate further.

We hope you found this helpful!

Author: John McGale, Performance Architects


© Performance Architects, Inc. and Performance Architects Blog, 2006 - present. Unauthorized use and/or duplication of this material without express and written permission from this blog's author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Performance Architects, Inc. and Performance Architects Blog with appropriate and specific direction to the original content.

Four Ways to Better Incorporate Functional Requirements in a Planning Solution Implementation

May 20, 2015

During the requirements and design phases (pre-implementation) in an Oracle Hyperion Planning implementation project, the idea of improving the actual planning process often gets lost, recreating process issues in the new solution.  The steps below demonstrate some important functional items to consider during requirements and design for a planning solution.

  1. Consolidate Data Entry and Calculations

Most of the user experience boil downs to how data is entered and calculated. Many users maintain scenarios outside the planning solution. Unless these “shadow systems” are truly investigated, the new planning solution will end up with a disconnect between what users need and what the planning system actually does.

The idea is simple, but the execution is difficult. Some users will have to compromise unless the entire planning audience happens to use the exact same planning methods.

A nice beginning may be to look for what the team uses when inputting data. Do they refer to Actual data? Budget? Forecast? Do they enter by Account or by Entity? In addition, do they use any type of source data in combination with a driver to get their new numbers? Spend a little time talking with key stakeholders, and a great time-saving opportunity may come out of it.

  1. Use Realistic Timeframes for Reports Availability

A common misconception is that all numbers need to be available for reporting at all times. This may be true for some organizations, but most do not have a true need to see an updated total across the entire organization each time a single data entry is made. This thought process is important because there will always be a give and take with performance versus availability. As data timeliness becomes more important, either more resources must be dedicated or a performance impact must be acknowledged.

This section will be more important to some than others. There’s a vast difference between a solution that uses two chart-field strings versus one that uses five or six. For the former, data availability might be a very simple issue to address, while the latter will see many more challenges along the way. Luckily, Hyperion Planning offers multiple options. Ask following questions when looking for the right solution:

  • What data needs to be available immediately on a data change?
  • What data can be updated periodically throughout the day (e.g., twice a day)?
  • What data only needs to be updated once a day?
  • What data needs to be available based on the beginning/end of a cycle (e.g., budget open/close)?

The difficult part of this process is determining what is actually required versus what people are simply most comfortable saying. Most of the focus will be on “all data at all times,” but there is often a large disconnect between the “want” and “need” mentality. Once team attitudes shift to what is actually needed, data can be updated very efficiently.

  1. Create an Automation Path

The question often comes up, “Can <insert request> be automated?” The answer is….maybe. The truth is that every piece of automation requires the following: technical capabilities, time, and universal requirements. The most important of the three often comes down to the final item. As a requirement becomes more universal throughout the organization, it becomes more difficult for the technical solution to address and thus takes more time to implement. As the time increases for a single piece of automation, the time available for other potential automation ideas diminishes.

When considering different automation ideas, try to prioritize the most important ones. For example, loading actual data from an enterprise resource planning (ERP) system is the most common, since this will generally benefit 100% of the organization. A more specific example might be to implement a quick way to apply a cost of living adjustment (COLA) for employee budgeting. Instead of a user having to modify each salary with the same percentage increase, a calculation can be created to automatically apply that increase on the user’s demand. The question would now be, “Can the same calculation be used for all users across the organization, or do some groups calculate this increase differently?” As more scenarios are uncovered, the time to create the calculation increases. As this time increases, more opportunities are sacrificed.

  1. Provide a Way to Change

Those with an existing process often end up using a “lift and shift” methodology during the implementation of a new solution, meaning the new application is simply created to do exactly what the existing application may have previously done. Sometimes this is intentional as part of a “crawl, walk, run” approach where the first phase is used to get the feel of new technology with ensuing phases used to apply enhancements. However, more often than not, it’s the result of people not wanting or not knowing how to change.

A new implementation offers the opportunity to make a process change that may have been difficult to make to the existing application. If a “lift and shift” technique is going to be used, a well-thought-out roadmap should complement the solution to avoid similar pitfalls. Without this, a technical solution can only be as effective as the functional process that it automates.

While the points above are geared more towards new implementations, these can also be incorporated into existing solutions as well, especially during an upgrade. Remember, there has rarely been a time when too much time was spent during the requirements stage!

Author: Tyler Feddersen, Performance Architects


© Performance Architects, Inc. and Performance Architects Blog, 2006 - present. Unauthorized use and/or duplication of this material without express and written permission from this blog's author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Performance Architects, Inc. and Performance Architects Blog with appropriate and specific direction to the original content.

Oracle Planning and Budgeting Cloud Service (PBCS): Mapping Smart Lists to Dimensions for Attribute Reporting (BSO to ASO), Part Two

May 7, 2015

In Part One of this blog entry, I explained attributes, Smart Lists and how a planner can pick and choose various options from a drop-down list of Smart List values.  In this blog entry, I explain this entire process in more detail.

When a customer has specific attribute reporting requirements, using Smart Lists can help solve this by allowing the planner and end user to pick their choice of members from a drop-down list. The basic building blocks of this solution require one BSO plan type and one ASO plan type. At a high level, all your planning will be done in the BSO plan type (let’s call this “MAIN”), and the final reporting is done in the ASO cube (let’s call this “RPT”):

MC 3

How to Implement

First you create six Smart Lists that mirror the six attributes. As described in Part One of this blog entry, our customer maintained six attributes:

  • Fund
  • Function
  • ESEOR
  • FASB
  • Scholarship Types
  • Officer Codes

So naturally our Smart Lists should include (as shown in screen shot below after the list):

  • Fund List
  • Function List
  • ESEOR List
  • FASB List
  • Scholarship Type
  • Officer Codes

MC 4

Then, link these Smart Lists to six accounts of “Type: Smart List.”

MC 5

Create a Data Form that has all the cost centers listed along with these six accounts in the columns, then assign the relevant Smart List values to the cost centers. The really cool feature here is that using Oracle Planning and Budgeting Cloud Service (PBCS) data load functionality, these assignments can be loaded using a file:

MC 6

Create a simple business rule that circles through all the cost centers and ensures that every cost center has at least one attribute assigned to it. If there is no assignment, then set a default assignment:

MC 7

Create another business rule that takes these assignments from their placeholder location in “BegBalance” and moves it to all periods, scenarios, versions and years.

MC 8

Create another plan type, but this one should be ASO that contains Smart Lists as dimensions.  Finally, after the rest of your regular planning process, push these Smart Lists to the ASO reporting cube using “Map Reporting” functionality.

MC 9

And finally, when you log into the ASO plan type, you will now have the ability to report on these attributes.

MC 10

Advantages of Using Smart Lists

While this solution seems simple enough, there are certainly some maintenance-related tasks that a system administrator will need to perform each period, or when a new cost center is added.

However these are minor items compared to the significant advantages provided by using Smart Lists. In my opinion, some of these benefits include:

  • Allows a user to change an attribute assignment easily using either a data form or Oracle Smart View for Office
  • Loads using a file in the PBCS built-in data import feature
  • Takes up no space in the application and allows you to keep your Planning cube simple and robust
  • Extremely fast for reporting ASO plan types, which permits significantly faster data retrieval times

Another major advantage of using Smart Lists is the ability to mirror “varying attributes.” While I’m not going to discuss varying attributes in detail at this time, varying attributes are another Essbase feature that allows you to track multiple characteristics of the base dimension that could change over time. Since varying attributes are not available in Planning or PBCS in general, Smart Lists can be used to mirror this feature.

One disadvantage to be considered is requirements on real-time reporting. Since we’re using map reporting to push these values from a BSO to an ASO cube, there will be a delay between planning and reporting. For this case study, this was not an issue but this could be significant to others.

The Final Word

We implemented this solution at two of our customers with great success. And even with the data transfer delay caused with the Smart Lists being moved to a reporting cube, the customer experienced very little change to their planning process.

In addition, we’re using pre-built functionality within Planning and PBCS, which makes it easier for the end user to maintain the application and also get proper customer service should he or she need it.

I hope this solution we provided is exactly what some of you might have been searching for. If so, please do leave feedback telling us about your experiences or email us at sales@performancearchitects.com if you want help in this area.

Author: Mohan Chanila, Performance Architects


© Performance Architects, Inc. and Performance Architects Blog, 2006 - present. Unauthorized use and/or duplication of this material without express and written permission from this blog's author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Performance Architects, Inc. and Performance Architects Blog with appropriate and specific direction to the original content.

Oracle Planning and Budgeting Cloud Service (PBCS): Mapping Smart Lists to Dimensions for Attribute Reporting (BSO to ASO), Part One

May 7, 2015

This blog entry focuses on configuring Oracle’s Planning and Budgeting Cloud Service (PBCS)  in a unique way to report on various member attributes. At this point, you might be thinking, “Hang on, can’t we report on attributes in Oracle Hyperion Planning already, by simply assigning attributes to a base dimension?”  Traditionally, the answer is yes.

Why to Use Smart Lists for Attribute Reporting in PBCS

So why would I want to use Smart Lists to accomplish this? There are three reasons:

  1. Attributes Unavailable with Planning Connection in Current Version of PBCS

Typically, attribute reporting is accomplished through reporting tools via an Essbase database connection. However, in PBCS, there is no Essbase connection; only a Planning connection. While Oracle is scheduled to provide attribute dimensions through the Planning connection in the near future, this functionality is not currently offered.

  1. Flexible and Variable Attribute Assignments

Using Smart Lists to represent attributes allows users to assign the attribute on the fly, without having an administrator go in to change the metadata. In addition, using Smart Lists permits the user to change the assignment based on other dimensions (years, periods, etc.). This allows for an entity to be assigned a certain attribute for FY15, and then a different attribute for FY16, while leaving the assignment for FY15 with the historical reference. 

  1. Reporting Performance

Reporting on an attribute does not typically yield performance issues. However, in situations where there are several attributes, and reports are required in which multiple attributes are provided in the report rows, the traditional block storage outline (BSO) attribute model may have performance limitations.

What are Attributes?

In Essbase, attributes describe features or characteristics of a dimension. This is done by tagging a base dimension with an attribute dimension.

This allows for more detailed reporting on your application. What this means is that after you’re done with your planning, when you choose to extract and report on data, you can pull out the various characteristics or attributes of particular data sets.

The way this is achieved is by creating attribute dimensions and using these to tag a base dimension. For example, you could have a cost center such as “New York” and you want to describe more features about your New York sales region such as number of sales people or locations in New York.  To do this, you would create two attributes called “Sales Managers” and “Location.” You would then tag “Cost Center New York” with all the sales managers for that region along with all products sold in that region.

It would look like this:

MC 1

In your Oracle Hyperion Planning application, you would only be planning or budgeting by New York, in this example. But at the reporting end, you could now request further attributes of your cost center.

You can see why attributes are such a unique and important feature of Hyperion Planning and Essbase.

Select advantages of using attributes are:

  • These do not take up blocks or “space” in your database
  • They can be customized to be numbers, text, alphanumeric, etc.
  • They can be used in calculations by using comparisons such as “>” than, “=” to etc.
  • They can used to drill down to details of the base dimension in Oracle Smart View for Office for reporting

What are Smart Lists?

The Oracle Hyperion Planning Administrator Guide for Version 11.1.2.4 explains, “Smart Lists are custom drop-down lists that users access from data form cells in Oracle Hyperion Planning applications. When clicking into data form cells, users select items from drop-down lists instead of entering data. Users cannot type in cells that contain Smart Lists. Smart Lists display in cells as down arrows that expand when users click into the cells.”

Basically Smart Lists are similar to Excel lists and can be customized to provide a drop-down feature in a data entry web form that allows users to pick a value.

It would look something like this:

MC 2

The screen capture above is from a real client instance and as you can see, there are six Smart Lists or drop-downs available for a user to pick values.

Generally in the past, we’ve used Smart Lists to assign items like a budget method (where in, based on a plan method, a particular calculation is executed) or simply to assign a characteristic of a base dimension much like an attribute.

A very good real world example is in salary expense budgeting where an employee can be assigned one of four different benefit rates. These rates can be in a Smart List drop down that the user picks for each employee. We would then have a simple business rule that looks at each benefit rate Smart List value and performs a calculation based on the value picked.

Moving on, let’s now discuss real world uses of attributes and Smart Lists.

Customer Example

In one of my most recent PBCS implementations, my customer’s requirements were simple. They had a small planning application that allowed them to create a four year long range plan (LRP) and a one year forecast.

They have a “Cost Center” dimension that offers six additional reporting characteristics which are not required for them to perform their long range plan, but rather needed for additional reporting. These additional characteristics include:

  • Fund
  • Function
  • ESEOR
  • FASB
  • Scholarship Types
  • Officer Codes

Now, having performed multiple Essbase and Planning implementations, this would sound to me like a classic example where “attributes” should be applied.

Normally, the way this would be designed is:

  • Keep the application small and robust with “Cost Center” as the base dimension
  • Create six attribute dimensions for additional reporting
  • Tag various Level Zero Cost Centers with the relevant attributes
  • Report on these attributes using Smart View and Oracle Hyperion Financial Reporting

What’s Easier for The Planner? 

What we learned above: Attributes can be used for reporting when connected to an Essbase database.

However, only admins can change or modify the list of attributes assigned to a base dimension. User cannot do this.  As a result of this, there is no instant or fast way for a planner to see how changing an attribute will impact the business. 

How Smart Lists Give Power to the Planner 

As I mentioned above, our customer had specific requirements around attributes, and needed to report by six additional attributes of their cost center. 

Waiting for an admin to constantly make these attribute changes and assignments meant there would be significant delays and the overall budget process would be impacted.

In order to allow the planner to be able to pick from his or her choice of members from a drop-down allowed the planners to look at multiple variations to their budgets. It also allowed planners to try out various combinations without resorting to admin assistance.

In order to accomplish this, we created multiple Smart Lists that were all tagged to accounts and displayed in a data entry form.

Summary

To summarize in three steps, we:

  • Used Planning Smart Lists to capture each attribute for all of the cost centers (this was prepopulated via a load)
  • Smart List values were ported to RPT (ASO environment) as actual dimensions; allowing for full reporting
  • This is the primary reason for existence of a “RPT” plan type (database)

In Part Two of this blog we will delve further into this solution…stay tuned!

Author: Mohan Chanila, Performance Architects


© Performance Architects, Inc. and Performance Architects Blog, 2006 - present. Unauthorized use and/or duplication of this material without express and written permission from this blog's author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Performance Architects, Inc. and Performance Architects Blog with appropriate and specific direction to the original content.