Creative solutions for
your business challenges

News and Updates From M2 Dynamics

Hyperion Reports - Using Row and Column Templates

Friday, October 25, 2013

When I worked in Finance, I maintained many reports in Hyperion Financial Reporting that had the same set of members in the rows and/or columns. Several reports would have the same group of entities in the rows and when the business changed its entity structure, all the reports would have to be updated. To help with maintenance, a row template was developed that was linked to multiple reports. This saved time, as only one template had to be modified to update all linked reports.

Steps to Create a Row or Column Template:
(This example is for a row template but same steps can be used for column template)

  1. In Hyperion Financial Reporting Studio, create a new grid in a report or open an existing report that contains a grid with rows and columns.

  2. Select the rows or columns that you want in the template. Make sure the entire row is highlighted and not just the cells within the rows. You must include at least one data row in the template and you can include text or formula rows.


  3. Right click and select "Save as Row and Column Template".
       


  4. Save the object in the repository by entering in a name and click on "Save". Type will automatically be Row and Column Template.

       

       
    Note: Make sure that you have at least one data row in the grid that is not in the template or you will get an error message upon trying to save as a linked object. However, you can uncheck "Link to Source Object" and then save a template that includes all the rows in the grid.

  5. Choose Save or Inherit for Suppression and Row Height and Save or Discard for Page Breaks. Click on OK. Template is now complete and can be used in multiple grids, as needed.

Inserting a Row or Column Template
  1. Open the report and go to the grid that you want to insert your template in.

  2. Select the row that you want to be below your template rows. (For columns templates, it will place the template to the left of the column you selected). Make sure the row contains the same dimension as the row template you are inserting. Right click and select "Insert Row and Column Template".


  3. Select the template from the repository and click on "Insert". Make sure that "Link to Source Object" is selected.


  4. Notice that template rows are highlighted in yellow and on the right hand side of the screen you will see the path of the template below the "Link to Source Object". Save the Report.



    Note: You must have at least one data row in the grid that is not in the template. The template rows that are highlighted yellow cannot be modified except through format inheritance. In the Row and Column Template Properties, you can select the row or column that you want to use for inheritance. You can insert a hidden row/column that contains the formatting you need.
        


    Limitations: When you use a template, the Cell Formulas, Conditional Formatting and Text from text cells or non-heading cells are discarded.

Modifying a Row or Column Template
  1.  Open a report that contains a link to the template and click on one of the rows or columns that are in the template you want to modify.

  2. Uncheck the "Link to Source Object" in the Row and Column Template Properties.


  3. You will be asked if you are sure you want to unlink this object. Click Yes.


  4. The rows or columns will no longer be highlighted yellow and you can change as needed.


  5. After changes are complete, follow steps 2-5 above from "Steps to Create a Row or Column Template" to re-save your template using the same template name and folder. You will be asked if you want to overwrite the existing Template. Select Yes. Any reports that are linked to the template will be updated.


Author of this article: Kim Forest, Staff Consultant for M2 Dynamics.

Three Common Delays in Hyperion Planning Projects

Tuesday, September 17, 2013

Over the course of many years and over 20 Hyperion Planning implementations I have learned there are several areas of Hyperion Planning projects that commonly create delays. Being aware of these areas and factoring them into the estimate and project plan in advance can help to avoid these issues and set proper expectations with the client.
  1. Data is frequently underestimated when scoping an EPM project. The process of creating extracts from source systems, developing rules and mapping for loading, and data reconciliation is frequently an iterative process and can be very time consuming. In my experience it always takes longer than expected. And all of these tasks require participation from the client whose time is usually at a premium. While the exact length of time needed depends on many variables, I would recommend adding 25-50% to the original estimate in the project plan.

    Another common misconception is that Hyperion will miraculously solve your source system data issues. While Hyperion Essbase and Hyperion SmartView are great tools for users to have ad hoc access to large amounts of data, they are only as good as the data loaded. Any concerns with level of detail or accuracy of Actuals and metrics should always be addressed at the source system.

  2. Since development of Hyperion Reports usually occurs at the end of a project they are often overlooked during the requirements and design phase. The design phase can be hectic trying to gather a lot of information and translate it into a design during a short period of time. Hyperion Reports are an easy element to deal with later since they aren’t needed immediately. But this can create delays at the end of the project dealing with new application build if all of the necessary reporting requirements aren’t identified in advance. Key items to review during the requirements phase to avoid potential delays include:

    • Ensure you have all the dimensionality necessary to build the identified reports. There could be member formulas or alternate hierarchies that may not be needed during the planning process itself but are necessary for reporting.

    • Ensure you have all the necessary actual and metric data required.

    • Determine users, tool and method of distribution of reports.

  3. Once the design phase is complete and development begins it is important to have regular review meetings with the key stakeholders over the course of the Hyperion Planning build. While requirements may seem crystal clear during the design phase, once users see what is in their head presented in a new format they may realize it isn’t quite what they expected or they missed a key factor. This can result in modifications to the design and rework so it is critical to catch these items early to minimize the impact to the timeline. I always include several user check-ins as well as time to prepare for them during the build phase in all my project plans. This will help to mitigate any delays due to changes in requirements.

Author of this article: Suzanne Veres Regional Practice Director for M2 Dynamics.

Hyperion Financial Reports – Adding Comments and Notes

Monday, August 12, 2013

We just implemented Oracle Hyperion Planning for one of our Higher Education clients and moved all their reporting from an Excel based model to Hyperion Financial Reporting. Our client used to manually add copious text, notes, and variance explanations to each Summary Department Roll-up as well as each individual Department. We were challenged with providing them with an easy way to insert and access notes into their new Hyperion Financial Reports tool.

We solved the problem by creating a separate data entry form in Hyperion Planning to capture all commentary. The Hyperion Planning form would focus only on one specific intersection per department. This allowed the user to select the specific month, department or department roll-up and place a note in that intersection using the Comments option in forms. Unlike summary level data that gets rolled-up to the parent and wipes out any data input at the parent level in Essbase, you are able to enter a comment at the parent level in Hyperion Planning and save it.

On the Hyperion Financial Report, we added the intersection containing the note to the bottom of the report and then hid that cell so that the data (number in dollars in this case) did not show up on the report. In a text row that we created, we used the “CellText” function to call the cell containing the note.

As long as the Hyperion Planning user(s) entered a comment in a department or department rollup in the Planning form, the note appears perfectly in the report. Since we are utilizing commentary and relational content to read a text field, we need to ensure the Hyperion Financial report utilizes Hyperion Planning Database Connection instead of Essbase Connection.

This Hyperion Financial Reports tip is a big win for our client as they now have all their financial reports online, including very detailed variance analyses and notes, without having to complete off-line reporting or manual activities in Excel.

 

Author of this article: Steve O'Neil, Senior Staff Consultant for M2 Dynamics.

Oracle On Demand for Hyperion Services

Thursday, February 02, 2012

 

Oracle has taken the first steps into the growing field of cloud computing. Oracle advertises pre-built and hosted environments for almost all of their products and boasts up to date environments, backups, and scalability for future growth. This service is mainly targeted for clients that do not want or do not have the resources and dedicated IT team for maintaining a local Oracle environment.

 My current and first experience with Oracle’s On Demand Service is for a Hyperion Essbase environment. When Oracle initially configures the environment to the client’s specifications, the Oracle On Demand team contacts the client’s internal IT department. In this step they create a VPN from the client’s internal network to the Oracle hosted environment. Unless the client specifies, the environment would not be open to the internet, and is only open to the client’s internal network. Oracle maintains an internal Firewall on the VPN connection. Below is an illustration of the cloud computing concept:

All instances of the Oracle applications, such as Hyperion Planning, Hyperion Financial Reporting, Hyperion Essbase Server, and any other Oracle Hyperion application, are hosted with in a Virtual Machine. The client can choose the details of the VM machine, such as the operating system, processing power, and system memory, just to name a few.   The client can also leave it to the Oracle technical team to decide the configuration.  Also, what seemed somewhat interesting to me was that the Hyperion Essbase server itself was hosted with in a virtual machine instance, even though Oracle makes a point of suggesting that Hyperion Essbase server should be hosted on a physical environment. Depending on the requirements of the client, Oracle provides test, QA, and Production environments, each with application server, Essbase server, and accompanied with OHS web servers.

This service is a good alternative to traditional locally hosted environments, but it is a far cry from having a locally hosted environment. It does have short comings. Some Clients have IT policies that make having an offsite hosted environment difficult. Also, any changes however minor they might be, have to be approved by an Oracle On Demand representative. For example, I needed access to a Linux box that housed the Essbase server in order to back up the Essbase application data. The steps I had to take were as follows:

  1. I contacted the point person that was in charge of Oracle products, whom in turn requested that I associate “My Oracle Support” (MOS) account to the client’s support identifier.
  2. Once I was granted permission, I logged into my MOS account and accessed settings. Within the settings screen, I selected “On Demand Environments”. Now I can see all the servers that have been designated for the client’s use. If you don’t have permission to access any or some of the systems, contact your Oracle On Demand representative. Below is an image of the Oracle On Demand environments screen:

  3. Select the machine that you require access. From the “On Demand Environments” tab, click on the button “Request New Account”. You will receive access within 24 to 48 hours upon approval by an Oracle Representative.

Over all my experience with the Oracle On Demand service was neutral. The sore point for me is the overall performance. The system seems to be sluggish at times and slow to respond. Also, for almost all infrastructure maintenance needs, one has to rely on Oracle to handle the situation via Service Requests, which could take some time. Even though the burden for system maintenance and backup eventually falls on Oracle’s shoulders, I would only see this as an option for a client whom would like to dedicate as little IT resources to Hyperion as possible.

 Author of this article:  Arpa Joakim, Consultant for M2 Dynamics

Unique Solution to a Hyperion Financial Report Limitation

Monday, December 19, 2011

Background Information:
M2 Dynamics just finished a large Oracle Hyperion Planning design and implementation project where the client’s reporting requirements were very unforgiving. We needed to create 70 reports, of which up to 20 expanded on data by columns instead of rows. In addition, the column expanding reports were very large and complex. We were able to create a basic template in Hyperion Financial Reports, however, performance was horrible when querying more than five projects, and the ability to page break and sort by column was another element that proved to be unworkable for the required report specs. To make matters worse, these specific reports were considered key deliverables and failure to provide these reports would have doomed the project.


Report Specs:

To give you an idea of what we are up against, below are some overall details that more or less pertained to all 20 reports. The reports would detail net profit and loss by specific product:
  • Each unique product was set-up as a parent, and each parent had at least one to 23 children. In this example, the Parent referred to a product and the children were versions or iterations of the product.
  • Some Reports detailed the profit and loss at the parent level and some reports detailed the P&L at the parent and child level.
  • Products were also identified by seasons, genre, and markets.
Imagine a report with column headers similar to the one detailed below that expands for 1,000 plus products:



Identifying the problem:

We performed a test to determine if our performance issue was in Hyperion Financial Reports, or Essbase. We set up a simple ad hoc query in Excel and used Essbase to drill on the columns by product and then compared it to a drill by row. Expanding by columns in Essbase proved to be very slow and the query would often time out depending on how many products we were drilling on. However, drilling by rows was very fast, taking only seconds in most cases, and never experiencing a time out instance.

Thinking Outside the Box:

Now we knew that a typical HFR (Hyperion Financial Report) design was not a viable solution, and we started brainstorming. We needed a solution that was:

• Professional

• Elegant
• Fast
• Easy to maintain
• User friendly

What we developed was a unique solution that fully utilized the power of Hyperion Essbase and provided the users with a report and process that was superior to their current set of reports. We designed these column expanding reports in Excel using a combination of HsGetValue formulas and VBA coding.


The Report Design:

The first step in creating these reports was to set up a “Main Template” that contained all the rows and the primary columns needed for the report. In order for these reports to be dynamic, we next set up a “Master Sheet” that would allow the users to select their dimensions. Each dimension they selected on the Master Sheet linked into the Main Template via an excel formula. We next created the HsGetValue formulas in the Main Template Sheet. Below is a partial screen shot of a report’s Main Template and the Master Sheet:

For this particular report, we needed it set up for the following parameters:
  • Report by parent and children – up to 400 plus parents
  • Create an individual sheet for each parent and its children
  • Insure that print formatting is consistent for each sheet
  • Name and sort the sheets by project name
  • Create an index page to easily find projects with links to each sheet
Now that we had our Main Template and Master Sheets set up, and we knew how many possible projects we needed to be able to report on, we copied the single report we setup on the main template and pasted on the same Main Template 3,000 plus times. This will allow us to retrieve the project member name and alias from Hyperion Planning App and paste it into the proper columns. Then a Refresh in SmartView will populate all 400 plus projects with real time Hyperion Planning data in a matter of seconds. To do this is where our VBA code comes into play.

VBA Code:

We used VBA code to create a one button macro that the users could click to create their report. We added a number of staging sheets to the file in order to pull our data and then manipulate as needed. Once the report is complete, the staging sheets are deleted. We created the VBA code in steps as order of operations is a critical factor. For this report, the steps are as follows:

  • We turned off some Excel functionality to make the code run faster, such as screen updating, disabled events and turned calculations to manual
  • Unhide all worksheets, such as our staging sheets that we hid from the users
  • Set Initial Hyperion SmarView Preferences - Since each user can have different setting on their SmartView addin, we had to set the Hyperion SmartView preferences in the code, as follows:
setalias = HypSetAliasTable("Series Level Mbr Name", "Default")
nodatatext_new = HypSetSheetOption(Empty, 13, "#NumericZero")
noaccesstext_new = HypSetSheetOption(Empty, 14, "#No Access")
  • Activate SmartView connection in first sheet and Format Column A for Text in all pull sheets – in some of our staging sheets, we setup SmartView adhoc's. We needed to active the SmartView adhoc's and format them for text. At this point, if the user is not logged into SmartView, they will get the login popup screen.
HypRetrieve ("Series Level Mbr Name")
Sheets(Array("Series Level Mbr Name", "Zero Level Mbr Name", "Zero Level Alias")).Select
Columns("A:A").Select
Selection.Clear
Selection.NumberFormat = "@"
  • Retrieving member Names and Aliases – Now we retrieve the member name and alias from Hyperion using the “HypGetChildren” VBA function.
  • The previous process will bring in all project members into our staging sheet. We copy these members into the sheet with our Adhoc query and use the HypRetrive VBA function to refresh the query, thus eliminating any zero or null value projects.
  • Copy the Project name and alias to a staging sheet where we use VBA coding to add four rows between each project – this is done to align the projects with the main template
  • We Copy the project name and alias, with the newly added blank rows and paste transpose them to the Main Template
  • Next we refresh the Main Template to update the HsGetValue formulas with Hyperion Data.
  • Cut and paste projects to a new sheet and name the new sheet to the same as the project name. This will repeat until all the projects have been saved as their own sheets.
  • Sort the sheets in alpha numeric order
  • Create a index with each sheet linked to its name
  • Worksheet clean-up – eliminate all staging worksheets and disable the run macro button on the Master Sheet
Final Result:
This VBA process, despite its complexities, is amazingly fast. If the user just selects one project, it only takes seconds. To create this report with 400 individual worksheets, only takes two to four minutes to run, depending on the speed of the end users PC. Initially on our first attempts at this report, it was taking 15 to 20 minutes to complete. We fine tuned it by eliminating the print formatting from the code by pre-formatting the copy and paste sheet. Also we minimized our data pull, reworked and eliminated some of our staging sheets.

This was a big win for the client, as their old system was not able to run for all 400 plus projects, and even if they ran it for 200 or less, it would take 30 to 45 minutes to run. An additional advantage, if they made an update in Hyperion for some of the data, they did not have to rerun the entire report, they can now just do a refresh in Excel SmartView add in that takes only seconds to complete.

Author of this article:  Steve O'Neil, Consultant for M2 Dynamics


 


Copyright © 2012 M² Dynamics, all rights reserved.
Privacy Statement | Terms of Use | Site Map

M² Dynamics is an Oracle Partner | Implementation and Solution Design Consultants | Oracle Hyperion Planning | Oracle Hyperion Planning 11.1.2.2 Release | Oracle Hyperion SmartView | OracleHyperion Essbase | Oracle Hyperion Essbase Integration Services | Oracle Hyperion Work Force Planning | Oracle Hyperion Capital Planning (CapEx) | Oracle Hyperion Financial Close |Oracle Hyperion Financial Reporting | Oracle Hyperion Financial Management (HFM) | Oracle Data Integrator (ODI) | Oracle Data Relationship Management (DRM / MDM) | Oracle Business Intelligence Enterprise Edition (OBIEE) | Oracle Hyperion Technical Architecture Design and Scoping | Oracle Enterprise Performance Management Architect (EPMA) | Oracle Hyperion Technical Architecture Design and Scoping

M² Dynamics
2967 Michelson Drive, Suite G417
Irvine, CA 92612

Phone: 347.460.M2M2 (6262) | Fax: 801.650.7979 | Email:  info@m2-dynamics.com