Monthly Archives: August 2015

Limiting Oracle Essbase Data Loads to Level 0 (Leaf) Members

August 14, 2015

Author: Andy Tauro, Performance Architects

Recently somebody asked me, “Is there a way to limit data loads to Oracle Essbase databases to ensure that only Level 0 or leaf members are loaded?” Essbase itself does not have a constraint that will disallow a user from loading data to a non-leaf member, assuming that the said user has the necessary privileges to load data to the database. In the case of the Essbase BSO (Block Storage Option), the data gets loaded just fine, and then will get replaced when the database is aggregated. In the case of the Essbase ASO (Aggregate Storage Option), one gets presented with a (very) polite warning saying that data loads to “summary” level members will be ignored.

So then why the need to limit this? The simplest reason may be to reduce the amount of data records being sent to Essbase; in other words, reduce waste to increase efficiency. Or, the less records loaded, the faster the data load. There also may be a bit more concerning reason: the data generation process is not creating records at the right level of detail. In this case, if the data is loaded to a non-Level 0 intersection, and no data is loaded to the Level 0 member, the data in the parent intersection can get wiped out when the child is aggregated up, resulting in loss of that data point.

Usually, when we implement an Essbase application, it is more than a simple database. There is a process built around Essbase to gather, process and load the data, and, of course, to ensure that the right level of security is assigned to the data. In other words, what we call an ETL (Extract, Transform, Load) process (or ELT, depending on who you’re talking with in the data integration arena!). Typically, this kind of process is built around using a user account that has enough privileges to perform both the data load as well as the application of the correct security filters. Such a user account would require at least “Application Manager” (although more often “Administrator”) status and would also have access to write data to any part of the database (leaf as well as non-leaf). However, when such processes are built, there is usually an appropriate mechanism put in to perform validations on the data, including to ensure that only leaf-level records are being generated.

When the ETL process is not that elaborate (or even when it is elaborate and there still is a need to avoid such erroneous data loads), there is a simpler way to trap such “bad” records: create a user account used only for data loads and give this account “write” privileges to just leaf members, not anything higher than that. This way, if the data load attempts to load a “bad” record, Essbase will throw a security violation error for the record, which can be caught by the ETL process to trigger appropriate action.

Such a filter only needs to restrict one dimension, so can be as simple as a ‘Write’ on ‘@LEVMBRS (Product,0)’ and ‘Read’ on ‘@IDESCENDANTS (Product)’. The error messages will be trapped in the error file, something to the effect of:

\\ Incorrect Access To Store Record

“100” “New York” “Jan” “Actual” 678 271 94 51 0 2101 644 2067

The drawback of this approach is that it does not indicate the exact dimension/member that is the cause of the failure, but at least indicates the record that is bad. If the filter is simple enough to lock down only one dimension, then we just need to look at the member for that dimension in the “bad” record (in the above case “100”). The other drawback is the need for a dedicated user account for data loads, in addition to the account created for the rest of the ETL process like dimension and security refresh.

All said and done, there may be many creative ways to get to the same goal, making life better with Essbase. Which one you pick depends on what works for your implementation. Having a knowledgeable implementation partner helps. Need some help with your implementation or with coming up with a solution, or do you just want to comment on this idea? Send us a note at communications@performancearchitects.com. We would love to hear from you.

Cheers!

 

 

 

 

 

 

 


© 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.

OAUG Best of COLLABORATE15 eLearning Webinar Preview: Implementing an Integrated Planning and Consolidation Process Solution

August 7, 2015

Author: Ron Woodlock, Performance Architects

I presented “Implementing an Integrated Planning and Consolidation Process Solution” at the Oracle Application Users Group (OAUG) COLLABORATE 2015 conference in Las Vegas this April. I am honored that this session was voted one of the highest rated sessions of the conference by the conference attendees. As a result of this ranking, the OAUG’s team asked me to present this session again during their “Webinar eLearning Series: Best of COLLABORATE15.” I wanted to provide a preview of some of the topics I’ll be covering during this webinar. Here is a link to the registration page for the webinar so that you can attend the session.

Overview

In this session, I will explore the benefits of implementing Hyperion Financial Management (HFM) in conjunction with Oracle Hyperion Planning and demonstrate how automated data synchronization during the financial close can help provide a single version of the truth and shorter closing cycle times. Before and after comparisons provide clear evidence of the value proposition of this type of implementation.

Historical Context

The practice of managing an organization using data or metrics has been evolving for 40 years. Universal principles have solidified and include:

  • Business strategy needs to be defined in financial terms or “outcomes;”
  • The drivers of successful “outcomes” need to identified and managed; and
  • Transparency of outcomes and drivers is provided to everyone that needs the information

Ron1

Oracle’s Enterprise Performance Management (EPM) or Hyperion suite of applications enables organizations to efficiently apply these principles. Oracle EPM provides functionality that enables the configuration and automation of integrating data between applications. In this case study we’ll explore techniques to share actual data between the financial close process and planning application.

Solution

Our overall objective is to create a repeatable process that integrates data between two business processes: the financial close and budgeting and forecasting. This objective can be further broken down as:

  • Share data / dimensions between applications
  • Automate standard data interfaces
  • Define business rules and automate synchronization between applications
  • Wrap all of this together in a repeatable close process

Ron2

This solution combines technology and process redesign to significantly enhance the financial close and planning processes.

Looking forward to having you join the webinar!


© 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 Hyperion Planning 11.1.2.4 PSU 002: How to Address Member Formula Editor Issue with External Web Servers

August 6, 2015

Author: Andrew Tauro, Performance Architects

During a recent upgrade of Oracle EPM (Hyperion) from version 11.1.2.2.x to 11.1.2.4 we ran into a little trouble with the software. While the issue was not with the upgrade itself, we saw this issue when we updated the software to the latest patch-set level that was available at the time of the deployment. In this case, we went with PSU (Patch Set Update) 002 for Oracle Hyperion Planning and Calculation Manager. One of the defects in PSU 001 is that the Member Formula Editing functionality in Planning does not work when using Internet Explorer (IE) 11. It works fine in Firefox 31 as well as IE 10. This defect was fixed in PSU 002, as we saw elsewhere.

 

However, in this environment, things worked a little differently. Besides the Oracle EPM stack deployed on a highly-capable Oracle Exalytics machine, for security reasons there is an Apache Web Server-based External Web Server (EWS) solution additional (gateway) web server machine also deployed. This machine lives outside the DMZ and is the means through which all users access the environment and the solutions within it. And this set up worked great even with Hyperion EPM 11.1.2.4.001, other than the aforementioned defect when using Internet Explorer 11 (IE11).

 

With PSU 002, the member formula editor stopped working again, and this time all web browsers were affected. On investigation, we found that the functionality worked when we used the Oracle HTTP Server (OHS) web server that was installed on the Exalytics machine, but not when we used the EWS. The EWS is configured using the same configuration set up as this OHS instance.

Andrew 1

 

 

On a closer look, we found that the issue is caused by the application performing redirection look-ups outside the Web Server, which is not usually the case. Conventionally, when one application module needs to talk to another, the request is sent via the Web Server, which then routes it to the appropriate module. In this case, the application module was reading a copy of that configuration and performing its own redirection. This is usually not a problem, when that web server is the OHS, or a web server is configured within the software stack. But in this particular set up, the application does not have access to a copy of the configuration of the EWS, and since the configuration differs in some ways for security reasons, this look-up broke the connection.

 

Oracle Support is pursuing a fix for this issue, and in the meantime suggests the same workaround that was suggested to IE11 users with PSU001, to use the Planning Smart View Admin Extension:

  1. Log on to your Workspace and Planning applications.
  2. Go to Tools > Planning Admin Extensions.
  3. Install the Planning Extensions.
  4. Log on to Smart View.
  5. Expand Application and expand Dimension.
  6. Right click on Dimension > Edit Dimension.
  7. Go to Selected Member and enter the member formula.
  8. Click on Submit Data; the member formula is now updated in Planning and Essbase.
  9. If you are unable to see this in EAS console outline, log on to Planning Web > Access Application and refresh the database.

Since some of the user base uses Firefox, in this case the software was rolled back to PSU 001 for Planning and Calculation Manager. IE11 users will have to work with the Admin Extension, which in itself is a pretty good tool.

If you have any queries regarding this patch set update (PSU), or are wondering if it may affect you, or if you are looking for some help using the Planning Smart View Admin Extension, feel free to drop us a line at communications@performancearchitects.com.

Cheers!

 

 


© 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.

Closing Time! The Top Five Reasons to Implement Oracle Hyperion Financial Management (HFM)

August 5, 2015

By Joe Francis, Performance Architects

A core tool within Oracle’s EPM (Hyperion) product suite, Hyperion Financial Management (HFM) focuses on the key business practice of the financial close. HFM allows users to cost-effectively expedite the process of consolidating, closing, and reporting results. In addition, the product enables organizations to satisfy the ever-growing list of global regulatory requirements. But perhaps the tool’s greatest benefit is its auditability of data which helps deliver confidence in the numbers. There is a laundry-list of reasons to consider an HFM implementation, but here are the five we at Performance Architects think are most important:

  1. Manual consolidation is too risky. Excel workbooks with layers upon layers of macros and formulas are confusing, break easily, and only a few key people really understand them. Auditing, versioning, tracking and disaster recovery are also difficult – if not impossible – using spreadsheets. HFM assists organizations in automating and managing the close process. Built-in functionality combined with customized business rules will allow users to translate foreign currencies; perform top-down allocations; eliminate intercompany activities; and consolidate data all under the watch of a tightly controlled review and approval process called “Process Management.”

 

  1. HFM can grow as your business grows. HFM is essentially a well-designed relational database. Your organization’s complex and growing structure featuring legal entities, departments, products, and currencies can peacefully co-exist and can be integrated into an HFM application. In addition, the tool is web-based, providing for an easy roll out to new users.

 

  1. HFM meets external reporting needs. Globalization has opened new markets and led to ever-changing reporting standards. In addition, economic uncertainty and fraud have brought greater scrutiny of financial statements. HFM supports International Financial Reporting Standards (IFRS) and Generally Accepted Accounting Principles (GAAP) and – if needed – can “““““`do both at the same time.

 

  1. HFM is part of the Oracle Hyperion Financial Close Suite. HFM is fully integrated with ancillary tools to support governance, visibility, and transparency into the financial close process. There are several tools within this suite, but three highlights include:

 

  1. HFM is now available on Exalytics! Previously HFM was only available on traditional Windows platforms. Beginning with the 11.1.2.4 release, the tool can also be used using Oracle’s Exalytics (using Linux). This new architecture provides for greatly enhanced performance.

© 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.