Should I Implement Oracle's Hyperion Workforce Planning Module

One of the questions that have been coming up more frequently with prospects and clients these days is whether they should implement the Workforce Planning module or develop a custom-built workforce planning solution. Before we discuss that question further, let’s briefly review some of the features and functionality of the Workforce Planning module.

Workforce Planning is a purpose-built module employee planning solution that is seamlessly integrated into Hyperion Planning. Workforce Planning contains pre-built functionality of the most commonly used planning practices. Upon initialization of the Workforce Planning module a series of pre-defined Account, Entity, Employee, Scenario and Version members, web forms, Smart Lists, member formulas, business rules and menus are created. These artifacts assist in the development of the employee expense plan.

Workforce Planning also includes functionality that calculates a variety of employee related expenses such as Merit Increase, Bonus, Overtime, Health Insurance, Employee Taxes (i.e. Social Security, FUTA, SUI, etc.). Additional Workforce Planning features include New Hire functionality, Transfer In/Transfer Out functionality, the ability to identify each employee with a specific Employee Status’ (i.e. Active, Leave of Absence, Maternity, etc.) as well as FTE and Headcount planning.

Benefits of implementing Workforce Planning

The obvious benefit of implementing Workforce Planning include having a purpose-built application for employee planning that incorporates the vast majority of employee related expenses. A by-product of implementing Workforce Planning is that it provokes thought around questions that may not be considered when developing a custom-built workforce planning solution. Workforce Planning requires specific detailed information and data at a lower level of detail than most custom-built solutions and so the application design needs to take that into consideration.

A residual benefit from a design perspective of implementing Workforce Planning is that it creates a fourth Essbase database for your Hyperion Planning application. A standard Planning application contains three Essbase databases. Often times during an implementation having a third database available is very useful in that it provides greater application design flexibility.

For many organizations, employee related costs are the primary cost driver, Workforce Planning allows companies to plan employee-related expenses at the employee level, whether a New Hire or an Existing employee. Workforce Planning also provides flexibility to plan at a higher level, by department for example. Home-grown spreadsheet models typically do not provide that level of flexibility.

Workforce Planning’s seamless integration with Hyperion Planning it enables you to combine your employee data and information with your non-employee data for a complete Plan or Forecast enabling to you to analyze the impact of your workforce plan in real time.

Video: Oracle Procurement and Spend Analytics at Los Alamos National Laboratory

Ware Hartwell, BI Project Manager at "the lab", spoke at a recent conference about their approach to implementing the Oracle BI Applications - specifically the Procure & Spend module. The video outlines some of the challenges that the implementation team faced and their recommendations for others facing the same challenges.

Los Alamos National Laboratory (LANL) is one of the largest science and technology institutions in the world that does multidisciplinary research in fields such as national security, outer space, renewable energy, medicine, nanotechnology, and supercomputing. Through a unique mentor-to-mentee training program, LANL is shifting from standard operational reporting to generating business intelligence insight with the use of Oracle Business Intelligence Enterprise Edition and Oracle Business Intelligence Applications.

Part 2 of 3

Part 3 of 3

DAC, ETL and Database Performance Tuning

DAC Setup:

DAC High Performance Execution:

· Automate Full and Refresh mode optimization rules (Analyze tables)

· Queue execution tasks for performance

· Set the level of Informatica session concurrency

· Load balancing across multiple Informatica Servers

· Restart the execution plan from point of failure

· Automate updating the database optimizer

· Analyze Data Warehouse tables when refreshing the Load.

Load Balancing across multiple Informatica Servers:

· On can set the maximum number of workflows that can be executed on a per server basis.

· This option provides tremendous flexibility to quickly fine tune for the maximum throughput

· Tasks are automatically load balanced across multiple servers

Database Tuning:

· Database Server Throughput

1. Processes commands from the Informatica Server to read, transform and write data

2. Data Storage is a critical performance variable

3. Spread data across multiple disks

4. RAID configuration can offer significant benefits

· Database configuration and schema management dramatically affect the performance of ETL and queries

1. Create relevant indexes

2. Partition large fact tables

3. Ensure that database server parameters are optimal

· Ensure that cost-based optimization is enabled

· Ensure that statistics isolate Data Warehouse Data into its own database

· Create Foreign Keys in the Database but configure not to enforce the foreign key relationship

· Analyze for occurrences of highly skewed data that is indexes.

Review Indexing Strategy for Data Warehouse Tables

· Index all New Primary Keys and Foreign Keys

· Frequently used in drill-down paths in a Dimensional Hierarchy

· Columns used in frequent reports within Filters

· Use different indexes for different types of data:

· Bitmap:

A query constrains multiple columns which have few distinct values in their domains (large numbers of duplicate values).

A large number of rows satisfy the constraints on these columns

The referenced table contains a large number of rows.

· B-Tree:

Occur frequently in WHERE clauses

Often used to join tables (include aggregate tables)

Occur in ORDER BY clauses (the index can facilitate ordering)

Occur in a foreign key reference constraint

Used to enforce PRIMARY KEY and UNIQUENESS constraints

Composite Key Indexes.

ETL Setup/Tuning:

The first step is ETL performance tuning is to identify performance bottlenecks. This can happen in the Source or Target database, Mapping, Transformation and System. The strategy is to identify a performance bottleneck, eliminate it and then identify the next performance bottleneck until it is satisfied with the performance.

· Run Test Sessions

· Study performance details and thread statistics

· Monitor system Performance

· Eliminate Source and Target Database Issues

· Eliminate Mapping Issues

· Eliminate Transformation Issues

Study performance details and thread statistics:

You can use thread statistics to identify source, target, and transformation issues. The Integration service uses one reader thread, one transformation thread and one writer thread to process a session. The session log provides the following thread statistics:

· Run Time

· Idle Time

· Busy

If a transformation thread is 100% busy, consider adding a partition point in the segment. When you add partition points to the mapping, the integration service increases the number of transformation threads. If system is running near full capacity, then do not add more threads.

Mapping Issues:

Mapping level optimization may take time to implement but it can significantly boost session performance. Generally you reduce the number of transformations in the mapping and delete unnecessary links between transformations to optimize the mapping. Configure the mapping with the least number of transformations and expressions to do the most amount of work possible. Here are some simple steps:

· Optimize the flat file sources

· Configure Single Pass reading

· Optimize Simple Pass through Mappings

· Optimize filters

· Optimize data type conversions

· Optimize expressions

Transformation Issues:

You can optimize following types of Transformation in Mapping:

· Aggregator Transformation

· Joiner Transformation

· Lookup Transformation

· Sequence Generator Transformation

· Source Qualifier Transformation

Aggregator Transformation:

Here are some guidelines to optimize the performance of an aggregator transformation:

· Group by simple columns

· Use sorted Inputs

· Use Incremental aggregation

· Filter data before you aggregate it

· Limit port connections.

Joiner Transformation:

Joiner transformations can slow performance because they need additional space at run time.

· Designate the master source as the source with fewer duplicate key values

· Designate the master source as the source with the fewer rows

· Perform joins in a database when possible.

Lookup Transformation:

Lookup table is on the same database as the source table in your mapping and caching is not feasible, join the tables in the source database rather than using a lookup transformation.

· Use the optimal database driver

· Cache lookup tables

· Optimize the lookup condition

· Index the lookup table

· Optimize multiple lookups

Sequence Generator Transformation:

The Number of cached values property determines the number of values the Integration Service caches at one time. Make sure that the number of cached value is not too small. You may consider configuring the number of cached values to a value greater than 1000.

SQL Qualifier Transformation:

Use the select distinct option for the source qualifier transformation if you want the Integration Service to select unique values from a source. Use Select Distinct option to filter unnecessary data earlier in the data flow. This can improve performance.

Data Warehouse (Target Database):

When ETL sessions writing into Data Warehouse tables, you can perform following tasks to increase performance:

· Increase Checkpoint Intervals

· Use Bulk Loading

· Minimize Deadlocks

· Increase database network packet size

· Optimize Target Database

Increase Checkpoint Intervals:

The Integration service performance slows each time it waits for the database to perform a checkpoint. To increase performance, consider increasing the database checkpoint interval. When you increase the database checkpoint interval, you increase the likelihood that the database performs checkpoints as necessary, when the size of the database log file reaches its limit.

Use Bulk Load:

When bulk loading enabled, the Integration service bypasses the database log, which speeds performance. Without writing to the database log, however, the target database cannot perform rollback. As a result, you may not able to perform recovery. But when you use bulk loading, weigh the importance of improved session performance against the ability to recover an incomplete session.

Minimize Deadlocks:

To improve session performance, you can increase the number of target connection groups the Integration Services uses to write to the targets in a sessions. To use a different target connection group of each target in a session, use the different database connection name for each target instance. You can specify the same connection information for each connection name.

Increase Database Network Packet Size

Increase the network packet size to allow larger packets of data to cross the network at one time. Increase the network packet size based on database you use:

· Oracle – You can increase the database server network packet size in listener.ora and tnsnames.ora file. Check with your DBA for additional information about increasing the packet size.

Optimize Target Database:

If the target database is Oracle, you can optimize the target database by checking the storage clause, space allocation and rollback or undo segments.

When you write to an Oracle Database, check the storage clause for the database objects. The database should also store table and index data in separate tablespaces, preferably on different disks.

When you write to Oracle Database, it uses rollback or undo segments during loads. Make sure you have appropriate tablespaces. The rollback or undo segments should also have appropriate storage clauses.

Use your remaining 2009 budget for 2010 training

BICG currently has a promotion going where you can take advantage of BICG University expertise at 30% off for a limited time. This is a great opportunity for those with a fiscal year that ends in December and few dollars left in their 2009 budget... you can use it for training that can be taken in 2010.

BICGU offers professional training for Oracle Business Intelligence and Enterprise Performance Management. Courses are offered via computer-based training (CBT), self-paced, online classroom, or on-site (domestic or international).

Oracle BI & EPM Training
30% off All Courses
Enroll before Jan 31
http://www.bicguniversity.com

Experiences with Hyperion Budgeting Applications

Boy, do I miss the annual budgeting process. Who can argue with long hours, strict deadlines, and countless iterations?! Ok, I may be stretching it a bit here, but I certainly have had plenty of experience with the corporate planning process in many shapes, forms, and flavors. Following is a recount of my experiences with the growth and evolution of the Hyperion planning tools.

As a corporate financial analyst in the early ‘90s, the company I worked for performed the annual budget, quarterly forecasts, and monthly reporting using Excel spreadsheets – a practice that was commonplace back then, and still is today, to a large degree. As we all know, Excel is a fantastic tool that serves many purposes. But, as part of the budgeting process, it definitely presents many challenges and leaves a lot to be desired.

Some difficulties I’ve experienced when using Excel for planning include:
• Having to consolidate a multitude of linked spreadsheets on a regular basis.
• Many sources of data.
• Time spent having to repair or update faulty links.
• A confusing maze of cell formulas.
• Trying to determine which file is the most current version.

In the mid-90s, we purchased a tool called Hyperion Pillar to facilitate the budgeting cycle. Pillar was a very good planning tool that was at the top of its class in its heyday. As a side note, the one distinct thing I remember about Pillar when I was first introduced to it was that administrator users were called Motherships and planner users were called Satellites. I think this set the product apart all by itself.

For all the things that were faulty about the naming conventions, the tool itself was very strong. You had a centralized place for standardized calculations (globals). Data was neatly categorized into different modules (expense, revenue, asset, etc.). Financial reports were built by default. There was a single, master file that consolidated in each planner’s file (yea, no more spreadsheet links!). And, it had built-in security down to the dimension level (depts, accts, etc.).

Pillar was a great tool (and still is), which aptly served its purpose for many organizations. But, like other financial applications, it also had its limitations and weaknesses. For example, the reporting aspect did not allow any real formatting or customization. The distribution and consolidation process (sending files to and collecting files from end users) was cumbersome and often resulted in mismatched data. There were limitations in dimensions, line items, and other application objects that made it unusable or inefficient for larger organizations.

I spent many years supporting Pillar while I worked in Hyperion Technical Support and one of the most frequent requests we heard went as follows:

Client: “Hi, can you please have the developers implement Print Preview”.
Me: “Um, yeah, we’ll get right on that….”

If you ever used Pillar, this was one of the biggest complaints about the product. “Every application has a Print Preview!!” For various reasons, Hyperion was not able to implement this functionality into Pillar until the latest releases. And, at that point, Pillar was in its twilight. But, again, for the few faults it had, I would still be the first to say, “I Loved Hyperion Pillar!”

Around the year 2000, Hyperion developed a new budgeting tool that was supposed to be the next generation version of Pillar. I was lucky enough to support the beta and 1.0 versions of Hyperion Planning. Now you want to talk about fun – this was fun! Well, not really…. Challenging might be a more appropriate word. As you can imagine, supporting any 1.0 version of a product is difficult. After supporting a stable, well-liked product like Pillar for many years, trying to support this new brain-child called Planning was downright impossible. We went from a propriety, self-contained program to an application that incorporated relational databases, web application servers, an OLAP engine (what the?!), separate reporting tools, calculation scripting, batching, scheduling, etc. All I could think was, “I want my mothership back!!”

But, as with most successful programs, things always get better. Having been a part of that initial release, I’ve been able to experience Planning’s tremendous growth and stability. You have a web-enabled tool that allows end users to enter their budgets over the web. No more spreadsheets, no more files - the only footprint an end user needs is a web browser. The tool incorporates built-in process management, through both the budgeting and approval process. Hyperion Planning is well integrated with its OLAP engine Essbase and we finally see the ability to perform most of the administrative functions in the web interface. And, for those who still love their spreadsheets (and I know there are many who do!) Planning and Essbase are fully incorporated with the Microsoft Office suite, using a tool called Smart View. This tool allows you to keep a dynamic link into the Hyperion database while working from the familiar Office environment.

Today, with the acquisition by Oracle, Hyperion Planning’s capabilities have expanded farther than ever before. Take reporting, for example. You have the use of the native tools, Financial Reporting Studio and Web Analysis. Or, you can take advantage of the Oracle Business Intelligence (OBIEE) suite of products. You also have more flexibility in data integration. Oracle has the tools that allow you to automate the loading of source data or metadata from your ERP, synchronize your headcount information from your HR system, or drill-down to detailed data in a data warehouse.

Going forward, I believe the Oracle acquisition will be good for Hyperion Planning as integration with other products and interfaces will become more seamless and user friendly. Who knows maybe the budgeting process can actually become pleasant? Ok, maybe the holiday punch I had was a little “strong”, but certainly the current release of Planning should be a tool that allows analysts to do more analyzing (what a concept!) and less number crunching.

Loyalty Analytics in 7.9.6 for an actionable Customer Insight

The latest version of Oracle BI Applications Marketing Analytics 7.9.6 has released Loyalty Analytics which is critical for any organization that effectively

uses Marketing Analytics as a toll to track and execute their Marketing track their Marketing Programs, Campaigns. Loyalty Analytics may be treated as an add-on to helping an aggressive Customer Relationship Management and retain more customers. This would surely help an organization to maintain competitive edge especially in the current business world.

It is a fully integrated solution for Siebel Loyalty Management allowing users to analyze the performance of Loyalty Programs.

B2B which is Account loyalty specific analytics like Account revenue, growth, status, geography, industries and B2C analysis which is Consumer loyalty specific analytics.

Of the 61 OOTB reports built in 4 Dashboards and 13 Dashboard Pages, let’s understand the specifics of the four Dashboards

Finance, Loyalty Program, Membership Analytics, Partnership

The finance dashboard tracks reports specific to Revenue for a Program or Member, liability of programs, Redemption Costs

Loyalty Program is very important dashboard that can be used to track reports specific to the loyalty programs launched in Siebel CRM application.

It facilitates reports to track Loyalty overview, transactions, promotions and points. It provides more helpful reports for executives to analyze top performing and underperforming programs, Redemption trends

Membership Analytics is another dashboard that helps to analyze membership trends, revenue and membership churns

Partnership dashboard helps analyze the performance of partners in terms of profitability and revenue trend.

There are quite a few important reports that may be developed beyond the existing OOTB canned reports to explore more analysis

1. For Partners -->

a. Partners performance by geography (Revenue)

b. Customers retention by partner

2. For Loyalty Program --> Points transferred

3. Enrollments --> Unused/Dead enrollments

4. Transactions --> Lease Redeemed products

5. Points --> Bonus Points

6. Cost and Revenue --> Unused Credits for cancellations

While the above spelled is not limit there can be many reports based of business requirements and customization in the base Loyalty Application

What's the right level of detail to include in Hyperion Planning

When facilitating design sessions with clients, the question that invariably comes up is what level of detail should be included within their Hyperion Planning application.

Often, a clients first instinct is to include the level of detail that exists within their ERP system for actual data. When confronted with this desire/requirement, I like to encourage spirited conversation between project sponsors and subject matter experts by having them address the following two items.
1) Does a desire exist across the majority of the user base, to plan/budget at the same level of detail for which actual data occurs?
2) Does this level of detail for formulating a plan/budget coincide with management’s performance management objectives?

I have found that the best practice for the level of detail contained within a planning application or applications is that it should reflect management’s performance management objectives. Detail for detail’s sake wastes resources, but arbitrarily limiting the level of detail is not a best practice if it limits analysis against important factors that must be controlled.

If the driving desire to include this level of detail in the Planning application is only to facilitate analysis against actual data and business owners have no desire to perform data entry to each and every travel expense account for example, then other alternatives exist that will not encumber the Planning application(s).

Successful Planning implementations provide the level of detail within the planning model that business owners are accustom to and desire to formulate their Plans/Budgets. Meaningful reporting and analysis often may require further details then what users desire to plan to. This disconnect can be addressed through properly architected reporting applications, partitioning, or drill through.

When addressing the level of detail that is to be included within a Planning application, answering the two fundamental questions above has lead me to successfully architect and implement in excess of 30 Planning applications. More often than not, most clients when really pushed to consider what is really necessary from a Plan/Budget perspective versus what is necessary from a Reporting/Analysis perspective will arrive at a Planning application that is more summary level.

While meeting management’s performance management objectives for Reporting and Analysis shouldn’t be ignored, encumbering users with unnecessary levels of detail within a Plan/Budget only introduces potential for issues with performance, maintenance and end user satisfaction.

The mantra that I as well as many other seasoned EPM professionals subscribe to when asked to architect a Planning application is that, “Provide the level of detail necessary to formulate the Plan. Planning should be used as a means to facilitate Plan/Budget vs. Actual analysis, but if the level of detail that this analysis occurs at is beneath the level of Plan formulation then this analysis should be done outside of Planning (i.e. Essbase, partitioning to a reporting app, drill through).”