Ohio Valley Oracle Application User Group

I had the honor of presenting at the September 2009 user group in the Hyperion track for those who attended the Ohio Valley Oracle Application User Group in Louisville.  The presentation focused on Maxl best practices, and how to integrate the results of maxl into other technologies.  The presentation was driven from a project completed late last year.  A recent client spent a tremendous amount of time verifying the results of daily and monthly processes.

Adding some consistency in the Maxl scripting, I integrated the results of the scripts, including the error and process logs, with .NET to produce a website that summarized the state of nearly 50 processes.  Administrators were able to view a web page that showed real time status of all their applications, including links to error logs.  The increased productivity of the administrative staff created a positive ROI in the first month of use.

 
/0 Comments/by

Compliment the Excel Add-In or SmartView with Excel Custom Lists

Many people use Custom Lists in Excel – sometimes without even knowing.  If you have ever typed January into a cell and used autofill (click the dark plus sign, and drag across other cells) to create February through December, you have used Custom Lists.

Excel has a few Custom Lists setup for users when it is installed. Select the Tools / Options menu, and display the Custom Lists tab to view them.  Users can create their own Custom Lists in this dialog box by entering a list separated by commas or importing a range of cells that already includes a list.

For Essbase users who use the Hyperion Spreadsheet Add-In or SmartView, this can become a valuable tool.  Many times Essbase users will want to display a specific list of accounts, measures, products, etc.  Rather than selecting these from the member selection, or typing them, Custom Lists can be created and used to reduce the effort.

Let’s assume a user is responsible for a subset of the existing products and those products are only sold in a few of the markets.  The user may spend a lot of time creating the market list every time they create a new retrieve.  If the user creates a Custom List, they can automate this selection process.  A Custom List might include the following members.

Columbus,Cincinnati,Los Angeles,Tempe,Dallas,Austin,Seattle,Denver,Nashville

All the user has to do now is type Columbus in the first cell and use the autofill to list the rest of the markets.  This function can save those who frequently create add hoc reports a lot of time.

Custom Lists can be created for just about anything, are easy and quick to create, and are useful in a variety of situations.  www.In2Hyperion.com is not just for those in a technical capacity.  User related ideas, such as using Custom Lists, will become more prevalent on this site.  Sign up for our newsletter and receive notifications when more Excel tips for Essbase users become available.

 
/0 Comments/by

Upgrade to Essbase 11 and enjoy a host of new features

There are a host of new features in version 11.  As with most product releases, there are the typical improvements related to memory, scripting, and stability.  But, there are some other, very notable, functional additions that might peak your interest.

Lifecycle Management

Shared Services now provides a consistent way to manage environments.  This console gives administrators the ability to compare applications, search for artifacts, and perform artifact migrations. It comes with a command line tool to automate tasks, as well as a full API for those who want to customize the process even further.

Typed Measures

Essbase now stores text!  Well, somewhat.  Text measures give administrators a way of storing a value other than a number in a data intersection.  Technically, it still stores numbers, but it represents a string.  A member in the measures dimension can have a text attribute.  This member is associated with an enumerated list.  Each member in that list has an index number, which is what is in the database.  When reporting is done, that number is converted to the associated text value in the enumerated list.  Members can also be tagged as Date, which changes the formatting to; you guessed it, a date.

Varying Attributes

Attributes have been around for a while now in Essbase.  Some people hate them and some love them.  They definitely have their place in the design of a database.  One limitation has been the inability to walk forward attributes over time.  For example, assume we have an attribute that identifies our customers into tiers based on their credit score.  If a customer’s score changes such that they move to a higher or lower tier, the history is lost because their attribute is the same for all time periods.  Not anymore.  Varying attributes adds the capability of Essbase to store, and calculate measures for attributes that vary over multiple dimensions.

Backup and Recovery

I have seen many methods to making sure Essbase applications are secured.  In version 11, there are some new options for BSO databases.  First, an option in EAS exists to backup the entire database, including its data and all of its objects, to one file.  When changing things rapidly through the day, this is a nice feature to ensure you don’t lose valuable work.  The entire database can easily be restored.  This is much quicker than manually archiving all the objects (calc scripts, load rules, outlines, and reports) and keeping data exports.

Secondly, Essbase now includes the option to log transactions and replay them.  With this option turned on, Essbase applications can be restored with the option to replay all transactions that occurred after the backup occurred.  Now, a database can be restored to a specific point in time.

ASO Data Management

ASO now includes Maxl scripting to enable administrators to clear data from regions of a database in two ways.  The first and most obvious is to remove the values from the database.  The second is the ability to copy the data into another member as the inverse, resulting in a total of zero.

The use of Environment Variables

If your process management uses variables to decrease maintenance tasks from, this might be something that will intrigue you.  Version 11 has access to not only Essbase variables, but operating system environment variables as well.

Monitoring Environment Reponses

Many environments take advantage of partitioning.  Now, there is a way to evaluate the cost of using partitions.  Using the ENABLE_DIAG_TRANSPARENT_PARTITION configuration setting in the essbase.cfg file, administrators can log transaction response times.

Common Log Locations

Version 11 organizes all log files in one location.  This is a very nice improvement.  Rather than searching through each products’ directory tree for the area logs are stored, they are now located in one common folder, with a folder for each of the Hyperion products.

Override Implied Shares

Essbase now includes an option in the outline management section to ignore the default setting for implied shares.  This can be very helpful when using partitions, as well as a host of other situations.

Notable Calculations Additions

Now that members can carry a text or date value, there are a host of functions that open up a whole new realm of possibilities.  DATEROLL will increase a value based on a specific time interval.  DATEDIFF will take the difference between two dates at the interval designated.  DATEPART will pull the time period (week, month, day, etc) from any date.  These operations were difficult at best, in previous releases of Essbase.

 
/0 Comments/by

Users Can Improve Essbase Reporting Performance

Users of Essbase have some control over the performance of a database and how responsive it is when retrieving data.  With a basic understanding of how Essbase stores data, users can optimize performance by changing the order of the dimensions and members in a report.

It might be helpful to read our article on sparse and dense dimensions.  Here is a brief overview:

An Essbase database is comprised of thousands, if not millions or billions, of data blocks.  Each block of data, and its size, is defined by the dense dimensions in the Essbase outline.  The volume of blocks is dictated by the unique combinations of sparse dimension members.  If Time and Accounts are dense, each block created would hold all the months for every account.  If Organization and Product are sparse dimensions, there would be a block for each unique combination of Organization and Product.  A block would exist for Center 10 / Product A, as well as Total Organization / Total Product.  If the outline has 20 members in Organization and 15 members in Products, the database could have up to 300 independent blocks.

If a report is written to show an entire income statement for all 12 months for Total Product and Total Organization, how many blocks would have to be queried?  Remember, there is a block for each unique member combination of Organization and Product.  The answer is one, because there is a block for Total Organization/Total Product that includes every account and every member in the time dimension.

How many blocks would be accessed if a report pulled Total Sales (a member in the Accounts dimension) in January for every product?  Since the Product dimension is sparse and there are 15 products, 15 blocks would have to be opened to return the results.

Here is where your understanding of what sparse and dense represents will help you improve your reports.  Opening a data block, reading the contents, and closing it, is similar to opening, reading, and closing a spreadsheet.  It is much faster to open one spreadsheet, or block, than 15 spreadsheets.  So, if the database retrieves are written in such a way to minimize the number of blocks that need to be accessed, or the order in which they are accessed, performance can improve.

I will agree that if data for all 15 products is needed for the report, all 15 blocks have to be opened.  There is no way around that.  That said, often times users will build one worksheet for income statement and one worksheet for balance sheet.  This means that the report is making two passes on the same blocks.  In theory, it takes twice as long to open/read/close a data block 2 times than it does once.  It is faster to have the income statement and the balance sheet accounts in one worksheet, which only makes one pass on the required blocks.  One worksheet for Income Statement and one for Balance Sheet can be created with cell references to the worksheet that has the retrieved data, if 2 separate reports are required.

I frequently see another example of a report requiring multiple passes to the same data block.  Using our example dimensions above, assume product information is required in a report for multiple accounts.

    Jan Feb Mar
Income Product A      
Income Product B      
Income Product C      
Income Product D      
Expense Product A      
Expense Product B      
Expense Product C      
Expense Product D      

The Essbase retrieve above would start from the top of the spreadsheet and move down the rows to retrieve the data from Essbase.  This cycle would open the Product A block, then B, C, and D, and retrieve the associated income for each.  It would then have to reopen the same 4 blocks to access expenses.

The following example, again going from top to bottom, would access both income and expense while the block is open.  The way this retrieve is setup, it eliminates the need to access the same block multiple times, yet still pulls the required information.

    Jan Feb Mar
Income Product A      
Expense Product A      
Income Product B      
Expense Product B      
Income Product C      
Expense Product C      
Income Product D      
Expense Product D      

These examples are very small.  In a real world example, a report of this size would not produce significant variances in the time it takes to retrieve them.  Users often have spreadsheets that are hundreds of rows long and take minutes to retrieve.  In these situations, eliminating the need to access the same block multiple times can produce notable improvements in the time it takes to retrieve data from Essbase.

With a basic understanding of how your database is setup, users of Essbase can help themselves with some simple changes to the format of the retrieve worksheet.  If access to the dimension properties in your database is unavailable, ask your system administrator to supply them for you.

 

 
/0 Comments/by

Better Business Decisions by Automating Redundant Tasks

I started my career as an accountant and never had any aspirations of doing the same thing all day, every day.  While I struggled through what I considered monotonous job functions, I developed a knack for finding ways to automate my job.  As a result, I didn’t have to do repetitive tasks and I had more time to learn the business. Don’t get me wrong, accountants possess a unique set of skills and talent that I respect trumendously. It is a critical function of any business.  So, kudos to you accountants!

When I get involved with building new applications with Hyperion, or updating existing models, it pains me to see accounting, finance, and the staff who support Hyperion continue to perform repetitive tasks that dominate their time.  It can drive talented people to look for employment elsewhere.  It inflates salaries and jeopardizes credibility with an increase in human error. It also deteriorates the quality of business analysis, introducing a greater risk of poor decisions.  Inflated expenses and poor management decisions can be catastrophic to any business.

Automation in accounting and finance areas is critical to productivity.  Being able to support the constant push from management to become better and faster with less resources is always challenging.  If your Hyperion environment is supported outside of finance, IT areas are under just as much scrutiny.  How much of your time, or staff, is spent generating reports?  How much more time could be spent helping analyze the business and adding value to management decisions?  From an IT prospective, how much of your time is spent supporting the environment and responding to requests where answers could be automatically generated?  If 20% of your reparative tasks were eliminated, how much more effective you would you be?  How much more experience would you gain?  How much more marketable would you be both internally and externally?

Many of the possibilities for automation are never discussed.  Most people don’t even realize how much time they spend performing repetitive tasks that could be automated. Some think it would be impossible to automate and others think it would be too expensive.  The examples below were both accomplished in a matter of weeks.  The investment had a positive return within months.  The non-monitory gain was felt immediately.

Don’t think of why it can’t be done.  Think of a solution without constraints and ask, “How can we get there?”  With the proper guidance and background, massive improvements can be accomplished with minimal effort.

To spark some thought, think about these situations.

Monitoring Essbase jobs and keeping users informed of system status

Are you responsible for managing all the jobs that run on Essbase server(s) and are constantly asked if something has completed, or when something will complete, by your users?  Some organizations have a person dedicated to managing this information flow.

I implemented a solution at a large financial institution to conquer this problem.  The result was a solution that required zero effort to maintain and provided a summary of over 50 processes in one web page.  It gave the status of the process, when it last executed, if there were any errors, and a link to the log and error files if they were required.  Access was granted to all the Essbase administrators.  Another page was available for all users that displayed the status of the application, when it was last loaded, when it was last calculated, and several other useful sources of information.

The days of searching through folders on multiple servers are now long gone for system administrators.  Users are more informed and support tickets diminished substantially.  The estimated time savings was 4-6 hours per day.

This solution was built using existing technologies, limited to Maxl, Windows scripting, ASP.NET, and access to an IIS Server to host the website.  It was 100% maintenance free and built dynamically enough so that new applications could be added and applications could be renamed or deleted.  All this is possible without changing any code or processes.

Distribution of reports

A large international organization distributed over 150 reporting templates to an equal amount of people in the US and abroad.  These templates were distributed daily through the monthly close of business.  The daily adjustment cycle finished updating the reporting Essbase application around 2 AM.  When a finance staff member arrived around 8 AM, the work began.  The template was refreshed and saved for each of the 150 business entities.  Emails were then sent to each of the 150 people with their respective report.  This process took about 6 hours every day it was performed.

Using existing technology, a process was created to traverse through a spreadsheet that had 2 columns, which was maintained by finance.  The first was the business unit, followed by the email the report was to be sent to.  Using the Essbase toolkit and Excel, a process was initiated as soon as the database was updated that opened a spreadsheet that included the template, changed the business unit, refreshed the template, saved it, and emailed to the intended recipient.  This process took less than 1 hour and all the reports were distributed before 4 AM.  Customers received their reports earlier (those in Asia a day early), no human errors were made, and the finance staff now had an additional 6 hours to add value.

 
/0 Comments/by

Hyperion Release 11 Architecture and Installation, Part 2 of 5

“Pre-Installation Requirements”

In installment #1 of this guide, we reviewed the architecture considerations and defined a simplistic architecture to use as a reference moving forward.  I recommend you read the previous post before you pick up this one.  I also recommend reading

“Oracle Hyperion Enterprise Performance Management System Installation Start Here Release 11.1.1.2.pdf (128 pages)” from the Oracle Documentation Library.

To reiterate our general approach, the Hyperion architecture establishment and installation activities in our organization cover the following five areas.

  1. Defining an Architecture – Work with the client to define the hardware, software, and the distribution of Hyperion components
  2. Provide Pre-Installation Requirements – Provide the client with a detailed list of activities prior to the installation
  3. Installation – Running the installation and configuration utilities
  4. Validation – Perform all functional activities necessary to validate the environment readiness
  5. Documentation – Provide the client with all the details of the environment as it is configured.

In this post, I will go through step 2 that the Hyperion architect, should deliver.  Steps 3-5 will be available in the coming weeks.  For the sake of simplicity I will be using the example of a common installation, primarily Hyperion Planning, Hyperion Financial Management (HFM), and the core BI applications.

As part of any installation, some items need to take place before the Fusion Installer is started.  I like to create a checklist of things that need to be done.  Often times these things are out of my control and I will rely on Database Administrator, Network Administrators, and other System Administrators.  This checklist contains the following elements.

  • Web Application Server Specifications
  • Relational Repository Information
  • General System Administration
  • Network Information
  • Additional Components
  • DCOM Configuration
  • IIS and .NET Configuration

I’ll start with the Web Application Server Specification.  Once the web application server platform is chosen from the table below, the installation and configuration often falls on System Administrators.  Items such as clustering, system account management, and JVM setting are managed outside of the Hyperion installation.  Other times, I’ll get admin access and manage it myself.  The first item to do is to validate the application server is certified.  This is directly from Oracle Enterprise Performance Management System – Supported Platforms Matrices “Oracle Enterprise Performance Management System, Fusion Edition Release 11.1.1.2)” in the Oracle document library.  I recommend reviewing this document.  It can change from release to release.

 

Server Notes
Oracle Application Server 10g (10.1.3.3.x) a If Oracle Application Server is used as the Web application server, Oracle HTTP Server is also required.  Profitability and Cost Management supports only Oracle Application Server 10.1.3.x.
Oracle WebLogic Server 9.2 (MP1 minimum) / 9.2.xb Shared Services requires WebLogic Server patch CR283953” for all platforms. You can obtain this patch at the BEA web site.
IBM WebSphere 6.1.0.17 / 6.1.x C  

 
Embedded Java container d  

 
a Supports these editions: Java, Standard One, Standard & Enterprise. Includes support for Oracle Application Server Single Sign-On.

b WebLogic Express is supported for each supported version of WebLogic Server; non-base versions are supported only via manual deployment.

c WebSphere Express, ND, and XD Editions are supported for each supported version of WebSphere; ND and XD are supported only via manual deployment.

d For this release, Apache Tomcat 5.5.17 is the embedded Java container that is installed automatically on all platforms. Apache Tomcat is supported only in this capacity. If future EPM System releases embed different Java application servers, Apache Tomcat will no longer be supported. For deployments that require high availability or failover, Oracle recommends using a commercially supported Web application server that supports high availability and failover.

I request the URL and authentication information since this will be needed during the deployment.  If I am doing a manual deployment, I will request contact information from the web application server administrator and work in collaboration on the deployment.

The next item on my checklist is to get the Relational Repositories Information set up.  This is mostly straightforward.  In general, I like to create a tablespace/database for each component ((Hyperion Foundation, Essbase Admin Services / Business Rules, EPMA, Planning, Financial Management, etc).  A distinct tablespace/database for each component makes it easier to manage in my opinion.  Although it may not be strictly necessary, the documentation does not seem to be clear on the matter.  I say ‘better safe than sorry’.  For the installation and deployment, I’ll need credentials for each table.  Based upon some Q&A, I’ll make initial size recommendations.

The target installation servers have a General System Administration checklist containing the information that I’ll need to execute the installation.  This is made of the following items.

  • Operating Systems version/build
  • Account on each server to run the Hyperion services and account requirements
  • External Authentication information (MSAD, LDAP, or OID if applicable)
  • Drive/Volume information identified for installation of the Hyperion software.
  • DCOM and .NET account information if HFM or FDM are to be installed

Next, I identify the Network Information necessities for appropriate communication between servers.  This includes IP addresses, DNS information, validation of name resolution, trace between servers, subnet configuration, etc.  This is vital so the components can communicate via Fully Qualified Domain Name, Short Name, and IP address.  Some components use different variations of name resolution probably because the components were developed separately and have not been fully standardized.

In addition to the Hyperion Software, Web Application Servers, and Relational Repositories there are a few Additional Components that need to be installed.  A PDF writer is needed for Reports Server to render .pdf reports in Workspace.  This can be GhostScript or Acrobat distiller.  I suggest referring to the “Start Here” documentation to see what is currently supported but we often go with GhostScript due to its cost.

For the Windows Administration, we provide the DCOM Configuration information needed to support FDM, EPMA, and FDM.  This includes the DCOM account information, permissions, and authentication information.  Although this is spelled out in detail in the “Start Here” manual, I like to provide step-by-step information with screen shots since DCOM is often confusing…well it is to me at least.

The last thing we review is the IIS and .NET Configuration.  IIS is often not installed as part of a standard OS build.  We make sure this requirement is specified, ensuring .NET is installed, and validate it is the appropriate version.

As with any installation, I recommend the Installation Architect read, and re-read, the Hyperion Manuals on there own rather than relying on this information or intuition.  It can always change and your installation may have some caveats that I have not covered.  For our purposes, with all the above activities completed and validated, we should be ready to start laying out the binaries and start the Hyperion Installation.  We will review the Fusion Installer and Hyperion Configuration Utility in our next installment.

 
/0 Comments/by