Advanced Financial Master Data Management (MDM) and Oracle Hyperion Data Relationship Management (DRM)

Data Governance and Master Data Management are critical as companies struggle with
Enterprise Reporting and Performance Management challenges that include varying
business results, differences in business rules and a multitude of definitions
measured by different groups.
Presented at the Southwest Regional Oracle Applications User Group, Daniel Poon
showcased how to implement a world-class Data Governance and Financial Master
Data Management program using Oracle Hyperion Data Relationship Management. The
end result: a key interest to organization’s finance users, IT, SOX and CFO.
Daniel also shared how Fortune 500 companies have recently implemented a successful
Finance-driven Data Governance program, presented an effective strategy to deal
with Enterprise Master Data Management challenges; and demonstrated how Oracle
Hyperion Data Relationship Management can be implemented successfully.

May 15, 2012
Time: 2pmET, 1pmCT, 11amPT
Location: Convenience of your office

Posted in General | Leave a comment

Hyperion Enterprise & Reporting Now On Controlled Availability

Hyperion Enterprise is a prepackaged application for midsize organizations that helps financial managers improve financial consolidation and reporting efficiency while enabling line managers to quickly identify key trends and variances in order to make better, faster decisions. However, in January 2012, Oracle issued a Lifetime Support Policy that provided a change in availability and licensing of the application, including Hyperion Enterprise Reporting, and its related products and components.  Those that are current users of Hyperion Enterprise should note that the application is now placed on controlled
availability and additional sales of these modules are now restricted. Support for these products will follow the published Oracle Lifetime Support Policy guidelines where Premier Support for release 6.5.1.1 will end in April 2013.

However, Sustaining Support will be available for as long as a company licenses its
Oracle products.  This includes technical supports, as well as access to Oracle’s online support tool and knowledge bases. Key Performance Idea’s, a long-time Hyperion implementation consulting firm, can help you assess any impact this may have on your current implementation, as well as help you plan future decisions related to the use of Oracle products for financial and regulatory reporting.

For additional information on the Oracle Lifetime Support Policy and the services offered
under the Premier and Sustaining Support stages, please click here.

Posted in General, Hyperion Enterprise & Reporting | Leave a comment

Leverage Your iPhone/iPad/iTouch for Oracle EPM & Hyperion System Monitoring

Apple added notification center to iOS 5 in late 2011. It does an excellent job of allowing applications to send you notifications while preserving battery life. The out-of-the-box notifications include applications such as Weather, Stock Alerts, iMessages, Emails and Reminders.

The Case for iDevice for EPM System Notifications

What could be really cool though is that iDevices with iOS 5 opens up a whole new world of receiving up-to-the-minute alerts, including those from your Oracle EPM and Hyperion systems! A good example is to look at how Hyperion Essbase is being monitored in a lot of companies. Usually there are scripts written that would email the system administrators of process notifications or errors with backups, performance or online status. However, the problem with this is that if the administrator isn’t actively reading his/her emails, any issues that are being monitored may not be alerting the administrator on a timely basis.

This is where iPhone/iPad could be invaluable if we could marry these notifications (be it legacy or not) with iOS 5. In this post, we will show you how easy you could be sending your Oracle EPM system notifications to your iPhone/iPad/iTouch in real time. That way, you will be alerted on any system related issues and take actions, without needing to watch your inbox 24 by 7. What’s more, you won’t have to change a line of code or any of your existing notification infrastructure.

OK before we start, we need an application that has the ability to send us real time notification to our iPhone/iPad.  Simply go to the App Store and download Boxcar directly to your iPhone, iPad or iPod Touch or download it through iTunes. Boxcar is free if you don’t mind the ads or you can pay $4.99 if you wish to disable that.

Next you will need to register an account within the app and then log into their website. With Boxcar, it can send you a notification when you forward an email to it. Boxcar will assign your account an unique email address that you can forward the notification to. You can obtain this by clicking on Add a New Service and then choose Email Account. From here you can click on Get Your Boxcar Forwarding Address.

After that, what we need to do is to forward our system notifications/alerts from our primary email account e.g. Microsoft Outlook. This can be done by creating inbox rules that will forward selective emails to our Boxcar email address (that we obtained above).

In order to forward selective emails from Outlook: As an example below, we configure this within Outlook Web by choosing Options | Organize Emails | Inbox Rules. In this rule, we are filtering any emails with an email subject of ‘[Hyperion Alert]‘ be forwarded to our Boxcar email address.

And that’s all you have to do. Any time a Hyperion alert email is sent to your regular inbox, it will be forwarded to Boxcar, and within 5-30 seconds, your iPhone/iPad/iTouch will receive the alert even when your iDevice is in locked state. Is that a Key Performance Idea or what?

What’s More?

Boxcar has a nice feature which allows you to silence its notifications for a period of time. For me, I have mine set up to be silent from 10 pm to 7 am so that my iPhone doesn’t continue to send audible alerts during those hours. This is a very nice touch from the developers because I am sure my wife appreciates it.

If you like to read of these post, please subscribe to our RSS feed and follow us on Twitters.

 

Posted in Oracle Hyperion EPM | Tagged , , , , , , , | Leave a comment

Oracle Data Integrator: FP&A Scenario for the Real World

Oracle’s recent release of Oracle Data Integrator (ODI) Enterprise Edition 11g delivers high-performance data movement and transformation among enterprise platforms.  It delivers an open architecture and built-in connectivity that provides for straightforward metadata and data loading into Oracle Enterprise Performance Management (EPM)
applications including the Oracle Essbase, Hyperion Planning, and Hyperion Financial Management (HFM)suite of products.  Not only is ODI product easy-to-use, it helps
reduce development costs, improve productivity and it is bundled with Hyperion at no extra cost.  Furthermore, ODI is fully integrated with Oracle Fusion Middleware, Oracle Database, and Exadata and, due to its open, standard-based architecture works with 3rd party applications.

The new 11g release provides several new capabilities including faster and more efficient loading and transformation of real-time data into a data warehouse, better productivity with new mapping wizards, quick-editor, generated code simulation and enhanced error table management, and improved support for online analytical processing (OLAP), SAP and new APIs that can be embedded directly within new custom applications.  Although the release provides users a new tool to learn, my recent experience with clients to implement ODI has proven that the tool can be leveraged easily and provide an effective,
high-performance architecture.

For instance, recently I worked with a client to utilize ODI for the data load of their Financial Planning and Analytics EPM application.  Client requirements were typical of any organization and included the need for metadata from their data warehouse (DW) to build the Planning application, direct data load from the General Ledger (GL) into an Aggregate Store Option (ASO) cube and from the DW into a Block Storage Option (BSO) cube.  Furthermore, data from both their Human Resource (HR) and GL needed to be brought together to load data into their Hyperion Workforce Planning application.  We were able to leverage ODI and create interfaces to load Planning Metadata sourced from the DW and Essbase data sourced from the GL, HR and DW.  The company now loads packaged metadata and interfaces via a scheduled, nightly scenario. Furthermore, after each nightly load, Essbase calculations and partition rebuild is executed with MXL directly from ODI.  ODI proved to be a powerful application that combines data integration, maintenance, and scheduling into a single tool.

Finally, the development tools of the new 11g version, including Topology Manager, Designer and Operator, provided strategic assistance to efficiently and effectively integrate data.  The Topology Manager, used to manage connections of logical and physical data sources and targets, organized and configured all drivers, created the physical architecture including servers, logins and drivers, and also created the logical architecture with the Logical Identifier that defined the physical architecture and import function to keep the import standard from Development to Test to Production environments.  Designer, used to create source to target connections, provided key items such as the ability to create models to define data structures and reverse data structures to directly read metadata fields, as well as the ability to create and import knowledge modules which were key to loading metadata and data into Essbase, Planning, and HFM.  The Operator tool, used to execute ODI data load packages, provided session lists to track completion of interfaces, packages and scenarios, packaged code or scenarios for execution, and the ability to schedule tasks through an ODI agent.

My experience with ODI has made me a believer in the new release.  This is a great tool that can be scaled with existing source and target hardware and is bundled with Hyperion at no extra cost.  My clients are cutting hardware costs and lowering total cost of ownership.  For more information on ODI, as well as additional examples of different ODI architectures, including a SOX compliant example, view my YouTube video: Oracle Data Integrator for Scalable Higher Performance Architecture.

Posted in Oracle Data Integrator | Tagged , , , , , , | Leave a comment

How to Shutdown Essbase The Right Way

This blog post is geared for Essbase administrators who definitely has the need to shutdown Essbase for their environment periodically or occasionally. For regular maintenance, the administrator most probably has the need to update system configurations or perform regular database backup. Occasionally, the administrator may face a hung process that he/she will need to deal with. While there are many ways of doing it, I want to discuss the right way, the safest way, the way that needs to be done, the best practice. Why? Because if you don’t, you may face the bigger problem of security file corruption or worse yet, database corruption.

Essbase Shutdown Periodically

In most environments, the Essbase administrator would invoke MaxL scripts to shutdown Essbase prior to performing the necessary maintenance routine tasks. Alarmingly, many chooses to simply issue the shutdown command i.e. ‘alter system shutdown’. While this seems to work 90% of time, for the remaining 10%, Essbase will likely be hung by a user process or by locked objects. Therefore, I want to show you the better way of shutting down Essbase, by taking other factors into consideration.

First let’s take a look at the proper MaxL script that should be applied:

spool on to <path>/myshutdown.log
login <adminuser> <adminpassword> on <host>;
alter system logout session all force;
alter database <application>.<database> unlock all objects;
alter system kill request all;
alter system unload application all;
alter system shutdown;
logout;
spool off;
exit;

What we need to pay attention to, are the commands that were issued prior to the final shutdown command. For instance, the command ‘alter system logout session all force‘ will log out all users, including those that maybe retrieving or calculating data. This command takes care of the common problem that are often seen by administrators: ‘Cannot unload database [Simple.Basic] while user [Daniel Poon] is performing database operation.  Wait for the user to complete the operation, or ask the user to abort it.  Log out all users and then unload the database.’

Next, the command ‘alter database <application>.<database> unlock all objects‘ will unlock all the objects that had been locked by users. For example, it’s quite common that the outline gets locked which could affect a number of maintenance operations.

And then, the command ‘alter system kill request all‘ will terminate all the existing processes that are running.

After that we will issue the command ‘alter system unload application all‘ to shutdown each individual application and database(s).

The above three steps illustrates a proper shutdown sequence, taking into account the very likely scenario that someone may still be engaged with the database when the administrator wants to perform maintenance. After these three steps are completed should you really be issuing the final ‘alter system shutdown’ to shutdown Essbase.

Essbase Shutdown Occasionally

In the unlikely event that the Essbase server is hung, and the administrator was not able to shutdown the server with the above MaxL commands, there maybe little choice except to terminate the Essbase agent.

In Windows, you can simply end the process within the task manager.

In Linux, you will need to first locate the process ID of the Essbase agent by issuing the command: ‘ps -ef | grep ESS‘. After noting down the process ID e.g. 1366, you can then try to shutdown the server using: ‘kill -15 1366‘. The ‘-15′ is a switch that allows the program to do any clean up prior to closing off the process. However, if that fails, you may have no choice but to close it with the command: ‘kill -9 1366‘.

Please note that we do not recommend shutting down Essbase by terminating the Essbase agent because there’s significant risks associated with abnormal shutdown. Therefore our disclaimer to you is that ‘use at your own risk !‘ Now in my experience, Essbase normally comes back online without issues after an abnormal shutdown 98% of time. But when the 2% strikes, a number of scenarios may occur:

  • Essbase database security file is corrupted. You will need to restore the security file from the essbase.bak file.
  • Essbase database is corrupted and cannot be started and exception files (.xcp) will be created. You will need to restore the entire database from backup.
  • Invalid block header maybe introduced into the cube, this is the silent killer which may pose problems later on.

Prevention Better Than Cure

In order to minimize the problems associated with an abnormal shutdown, we recommend the following:

Put in QRYGOVEXECTIME in the essbase.cfg to prevent the types of queries that will cause Essbase to be overly preoccupied with processing and refuses to shutdown:

  • A long-running query against a database that accesses attributes at a high level, forcing many dynamic calculations to occur.
  • A query that uses the “Drill to bottom” option in a large dimension.
  • A query that uses the “Drill to all levels” option in a a large dimension.

Change the Essbase timeout frequency by issuing the following commands in MaxL:

alter system set session_idle_limit INTEGER 60 minutes;
alter system set session_idle_poll INTEGER 30 minutes;

The idea here is we will reduce the user timeout down from the standard 60 minutes to 30 minutes. Firstly, this reduces the number of idle users in the system, and as a result, it also reduces the chance of hanging or problems caused by running out of system resources. Secondly, the above commands will increase the backup frequency of the Essbase security file, in order to reduce the chance of database corruption even if Essbase would be shutdown abnormally.

Last but not least, we recommend you to perform regular backup of your Essbase database e.g. exports and file system backup. This will be invaluable when there is a need to restore the database should disaster strikes.

Posted in Oracle Essbase | Tagged , , , | Leave a comment

Oracle Smart View for Office: 10 Tips & Tricks You Must Know

Oracle Hyperion Smart View for Office (Smart View) is a Microsoft Office Add-in that provides a common interface designed specifically for analysts to interact with the data and business intelligence content that they have stored within their Oracle’s Enterprise Performance Management (EPM) system.

One of the most common way of using Smart View is users access Excel to interactively investigate the data contained in the different sources such as Oracle Essbase, Hyperion Planning, or Hyperion Financial Management (HFM) etc.

In this post, we will discuss some of the tips and tricks that we think you’ll find very useful.

1. Downloading the right Smart View Client?

Smart View has gone through many changes over the last few releases. Users are recommended to upgrade their Smart View client that had been installed on their PC. One major confusion for users though is not knowing where and which version they should be downloading. This is because users don’t necessarily upgrade their client when they are informed of a change, it could take several months for various reasons. When they have a genuine need to do so later, more changes may have been introduced.

Fortunately Oracle has made this a simpler process now. If you want to obtain the most compatible version of Smart View client, simply log into Oracle Workspace and click on Tools | Install | Smart View. Oracle Workspace will prompt you to download the relevant SmartView client.

2. Dealing with ‘#NoData’ or ‘#Missing’

In data intersections where there is no data i.e. blank, Smart View will display “#NoData” or “#Missing” by default depending on the version of your Smart View. This can cause issues for your Excel report because both “#NoData” and “#MIssing” are text cells that cannot be computed. One way to overcome this is to configured Smart View to display zero in its place. To do this,  click on the Options button, under the Data Options group, you will see the “#NoData/Missing Label” setting. Simple type ‘0‘ into the field and then press OK.

3. How to print Point of View (POV) Headers

One of the key user benefits Often, analysts would generate a widely popular report such as “Revenue by Quarter” in Smart View. On screen, the Point of View manager is shown to allow the user to understand the data inference (e.g. geography, scenario, product etc). However, if you print the Excel worksheet, you cannot see the Point of View.

Well here’s a trick. In Excel click on the Insert tab and then click the Insert Header & Footer button. Now enter “POV:{}” on the header or footer field. When you click outside of the field to commit the entry, you will not see any changes.

In order for the Point of View to appear on your worksheet, you will need to click the File | Print | Print button.

4. Fix Smart View Time Out Issue

Large data retrieval often requires more time to complete. As a result, users may be notified of the error: “The request timed out. Contact administrator to increase NetRetryCount and NetRetryInterval.”

To overcome this on the client’s PC (where Smart View is installed), go to the Windows Registry Editor by clicking Start | Run | regedit. Next navigate to the following entry:

[HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Internet Settings]\

One important thing to take note is make sure you choose “CurrentVersion” and not “Current Version” with a space, because both folders exist under the Windows folder, and can be quite confusing.

Now enter the following registry key and value:

  • “ReceiveTimeout”, use dword and enter hexidecimal ’00dbba00′;
  • “KeepAliveTimeout, use dword and enter ’002BF20′;
  • “ServerInfoTimeout”, use dword and enter ’002BF20′.

The above values are shown in hexadecimal, not decimal and correspond to 18000 milliseconds (3 minutes) and 240 minutes for the receive timeout.

Now, restart the client PC and you should be able to retrieve large data sets without issues.

5. How to Retain Excel Formatting

For most analysts, building financial reports requires rigorous Excel formatting e.g. cell color, borders, data precisions etc. In Smart View version 11.1.2.1, the user has the option whether to let Smart View or Excel control the formatting. For highly formatted report, users should use “Excel Formatting” over Smart View. To do this, click on the Options button. Choose the Formatting tab and check the “Use Excel Formatting” option. Click OK to confirm.

6. Using Provider Services Properties

In Smart View version 11.1.2.1, users will appreciate the ability to control the Smart View Client. For example, the user could set the “Force Client Upgrade” option and the Smart View client could be automatically prompted for upgrade whenever Oracle EPM is upgraded by IT. This could help to reduce future manual maintenance effort. The Provider Services preference can be found towards the bottom right of the Smart View window.

7. Display Member Name and Alias On The Same Row

One of my pet peeve on SmartView vs. the legacy Essbase Add-in is that SmartView does not have the extremely useful option to display the member name and alias together. Well that’s all changed in from Smart View version 11.1.2.102. You can turn on the option to display both member names and alias in Smart View by clicking on the Options button, choose the Member Options tab and select”Member Name and Alias” for Member Name Display.

8. Using Multiple Grids

One extremely cool feature in the new version of Smart View is the ability to retrieve multiple grids into the same worksheet. Previously this is prohibited. In addition, you can use a different data source e.g. different Essbase cubes for each of the data grid created. To do this, all you have to is highlight a region of your worksheet and select “Ad hoc analysis” for a particular data source.

You will be asked if you want to enable the multiple grids feature in this worksheet. Choose Yes.

Data for the first grid will be pulled into the worksheet. After this, you can repeat the same action to build more data grids on the same worksheet.

9. Use Smart Slices To Create Data Views

Smart Slices can be described as a view of a data source such as Oracle Essbase or Hyperion Planning, made up of a selection of members, filters or combination designed to limit the data set. It is especially useful for distribution of information to users who has authorization to use the data, and is likely to be using it repeatedly. For example, you could create a Smart Slice for the General Ledger accounts for the current month, which will be useful to the accounting department for the financial close.

10. Smart View “Power Pack” Extensions and Utilities

Although Smart View is already a very powerful tool, it’s functionality can be further extended by installing the Smart View Power Packs. You can download and install the Smart View Power Packs by clicking on the link here.

The list of Smart View extensions are quite impressive. They include:

  • Adjust Data Extension – Simple data adjustment panel for use with Smart View.
  • Options Extension – Quickly change the most common options for Smart View.
  • Table of Contents Extension – Display open workbooks and worksheets originized by connection type.
  • MDX Script Library Extension – Run MDX scripts and use them as a starting point for new queries.
  • Substitution Variables Extension – List and use Essbase substitution variables in ad-hoc operations.
  • Grid Themes Extension – Format ad-hoc grids with pre-defined themes.
  • Member Search Extension – Search for Essbase memebers in the Smart View panel without the need to open a dialog.
  • Smart Chart Extension – Link native Excel chart to ad-hoc grids. The chart will update as you zoom or pivot.
  • Extend Smart View Add-In – Once installed the add-in will add a new ribbon to Excel called ‘Extend SV’. This ribbon allows you to refresh all worksheets and workbooks, submit data for all worksheets and workbooks, and store key options with the workbook so when another user opens that workbook they can use the options the creator was using.
  • Smart View VBA Conversion Tool – Tool that will assist in the conversion of Legacy (Classic) Add-In VBA code to equivalnt Smart View VBA Code.

This post is getting long, and so we will cover the Smart View extensions in another post in the future. Thank you for visiting our blog.

 

Posted in Oracle Hyperion Smartview for Office | Tagged , , | Leave a comment

Welcome to the KPI Blog!

Welcome to the Key Performance Ideas Blog! We have dedicated this blog to sharing our expertise around EPM (EnterprisePerformance Management) strategic and goal-oriented solutions and the Hyperion software and related technologies.  Our goal is to provide meaningful information regarding the technology and industry to help you improve and optimize your EPM solutions.  As an Oracle Gold Partner, Key Performance Ideas is a full-service consulting company that offers services designed to enable companies of all sizes to
derive the most value from their Oracle Hyperion and Business Intelligence software solutions.  Follow us and gain access to the latest trends and tips and tricks, as well as reviews on recent version releases and upgrades, to leverage your technology
investment.  You can also follow us on YouTube and Twitter.

Posted in General | Tagged , , , , , | Leave a comment