Step 2: Adding ‘Advanced Suppression’ to each of the 12 Trigger columns.

The Conditional Suppression set on each of these columns (see Step 1) will suppress the column that correlates to the Period selected. If the end-user selects Jan, then the column representing Jan will be suppressed. This is used later in step 4 of the report development.

Keys:

  • The Advanced (Conditional) Suppression for each column relates to the 12 Periods added in Step 1.
  • The logic for Jan is as follows:
    • Suppress Column If:
      • "Member Name" "Period" "equals" "Jan".
      • "Jan” is the actual member name.
  • The same logic in place for Jan will be required for the Feb-Dec columns, Thus…
    • Suppress Column If:
      • "Member Name" "Period" "equals" "Feb".
      • "Feb" is the actual member name.
      • Etc…
  • Once steps 1 & 2 are complete, development of the trigger section has been finished.

 

If you have worked with Hyperion Financial Reporting, odds are good that you’ve come across report requirements for Rolling Years and Periods, that is to say a report that spans periods from multiple years. This is an easy build if your data source includes a single “rolling” dimension for ‘Year’ and ‘Period’ – you utilize the “RelativeMember” function to move up or down the hierarchy. The difficulty arises when programming this same logic into a report with separate ‘Year’ and ‘Period’ dimensions. A solution utilizing "Trigger" columns is shown below.

The use of what I call trigger columns allows for Hyperion Financial Reporting to display rolling Years and Periods, whether your requirement is for a 2 or 12+ month rolling report. The Trigger section of the report requires both the ‘Year’ and ‘Period’ dimension to be columns on the report, while any dimension; such as Account or Product, can be included on the rows. The steps below detail a rolling 5-month solution.

Step 1:  Add data columns for all 12 periods (Jan-Dec).

These columns will function as the Trigger section, essentially telling the other columns what the end-user selected for Period. This is important because later sections of the report require knowledge of the selected Period to ultimately decide what is displayed.

Keys:

  • These 12 columns MUST be Data columns.
  • These 12 columns MUST be hidden.
  • The Period member selection MUST be set to “Current Point of View for Period”.
  • The Year member can be any member – not relevant to the trigger.

  • Optional: Overwrite the Period POV cell with the 12 periods (Jan-Dec) as shown below.

Oracle EPM 11.1.2 Talleyrand Release

Apr 8 2010 In: General

 

Yesterday, Oracle announced the official launch of the Enterprise Performance Management 11.1.2 Release.  This release introduces some major enhancements to the existing Hyperion applications, along with three new products – Disclosure Management, Financial Close Management and Public Sector Planning and Budgeting. 

I’ve had the opportunity to see the release live during a partner launch event and have been very impressed thus far.  Over the next few weeks, In2Hyperion will begin to look at this release and share how to take advantage of new functionality. 

View the press release below.

http://www.oracle.com/us/corporate/press/066183

More information is also available on the Oracle website, which has been updated to include information for the 11.1.2 Release.

http://www.oracle.com/us/corporate/press/mediakits/epm-system-release-media-kit-066160.html.

Generate Random Sample Data

Feb 9 2010 In: Microsoft Excel · General

Whether in a finance organization or a technical role, most of us have had the need to create sample data to use to test Hyperion systems.  In2Hyperion is sharing a tool to make this process more efficient.  By defining a numeric range, the number of columns, and the number of rows, an excel spreadsheet will be generated with the appropriate random data.

This tool can be access at http://www.in2hyperion.com/Tools/RandomNumbers.aspx.

EPM Reformation

Nov 4 2009 In: General

The EPM Reformation

Enterprise Performance Management (EPM) is undergoing the same transformation that Enterprise Resource Management (ERP) systems brought about in the early 90s.  As complimentary solutions such as Asset Management, Payroll, and General Ledger converged into one consolidated, modular system, so too are the solutions that comprise EPM (Financial Consolidation, Budgeting/Forecasting, Strategic Planning, and Reporting).   Along with the obvious benefits and economies of scale that accompany this transition, we must be aware of the pitfalls associated with the design, implementation, deployment, and support of these mission critical applications.

EPM as a Compliment to ERP

Just as Enterprise Resource Planning (ERP) solutions are an essential component of the Back Office operations of every Fortune 500 Company, Enterprise Performance Management systems are complimentary in nature and provide insight into the operational and financial effectiveness of the organization.  Metaphorically speaking - If the organization was an automobile, ERP would be the engine and EPM would be the gauges.  Carrying the analogy forward, nothing prevents us from operating a car without a speedometer, gas gauge, or heat indicator.  Furthermore, when the car is running well (at least in so far as we perceive) we have little interest in these instruments.  But, what about when we hear the first ping in the engine, or the car doesn’t respond when we hit the accelerator.  Worse yet - A seize up (aka Recession).  In the absence of information conjecture prevails and we are forced to speculate as to the cause of the problem. Without EPM, organizations are essentially operating their business in a similar fashion; reactive at best, "from the gut" at worst.

The Problem

From a business (functional) perspective, EPM solutions are categorized by the convergence of Analytic Application such as Financial Consolidation, Budgeting/Forecasting, and Strategic Planning with traditional Business Intelligence Solutions hallmarked by Query & Reporting, Key Performance Indicator (KPI) Dashboards, and Enterprise Scorecards.  As EPM has evolved from it’s siloed upbringing as a departmental solution to the Enterprise-class solutions of today, the underlying technology required to support these applications has become more broad and in recent years increasingly more complex.  This evolution is both natural and expected.   Given the expansive use of EPM based solutions, technical constructs such as multidimensional databases, data marts, enterprise data warehouses, workflow engines, web services, SOA, Calculation Scripts, ETL packages, and Master Data (Hierarchies) have all become vital components to the architecture. 

In so far as organizations appreciate the criticality of EPM solutions to their organization, there is a gross under estimate of the effort associated with deploying and supporting these mission critical applications.  This similar lack of effort appreciation is shared by the ERP implementation of the 90s.  How often did we hear about the $2 million dollar ERP solution that came in at $20 million or more?

Gaining an Appreciation

Few would argue that the ERP solutions of today have not brought about a degree of integration and consistency throughout the business.  The ability to integrate key operational back-office systems up and down the organization with the capacity to exchange data between functional modules without fear of inconsistency is certainly a hallmark of the ERP promise.  But, this integration did not come without a price.  The same can be said for EPM.

ERP and EPM are both the harbingers of consistency, transparency, and audit ability.  As such, they force the institution of standards and controls where they have not historically existed.   Furthermore, there is an illusion that these disciplines run contradictory to loosely coupled legacy processes that are thought to be more flexible, nimble, and sufficient for supporting the business.  Whereas this may appear to be true when viewing each process as a stand-alone, siloed operation (forecasting separate from budgeting, separate from financial consolidation, separate from operational reporting).  It is important to have the right perspective here.  As with traditional ERP solutions, to gain an understanding of the EPM value proposition, you must first rise above the individual business solutions that encompass performance management (i.e. Financial Consolation, Budgeting/Forecasting, Reporting, etc).  Only by viewing these applications from a holistic, integrated business perspective can you appreciate the business and technology economies of scale that accompany Enterprise-class EPM solutions.

The Point   

EPM solutions if approached correctly must be seen as the acronym implies, "Enterprise" in scope.  Similar to their ERP counterpart, EPM solutions can and in many cases should be implemented modularly, but under the auspice of an overall Solution Deployment Strategy.  Notice the term “Solution” not “Application”.  Applications are but one component of the EPM strategy.  Others include: Technical Infrastructure, Data Management/Governance, Process Integration, Communication/Change Management, and Administration & Support.  When you view EPM solutions from this perspective it is hard not to appreciate the level of involvement required from Executive Leadership, The Business, and Information Technology.  Organizations must think of their EPM solutions as "ERP Projects"; enterprise enabling solutions that require the establishment of well documented and endorsed strategy that align with the corporate directive.   In this vain, EPM requires a realistic investment of resources, time and capital to be successful.   Then again, you could pull away from the car lot with a 1971 Pinto and hope no one hits you from behind…

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.

Get notified when a new post is published.