Which Tool Should Be Used for Reporting?

My clients often wonder which Oracle tool they should use for reporting, building graphs, and generating dashboards. It’s a great question because, as anyone in this realm knows, there are many different options. This blog will touch on the three reporting tools I’m familiar with: Financial Reporting Studio (FRS), Web Analysis (WA), and Oracle Business Intelligence Answers (OBI).

One caveat here is that the bulk of my experience comes from the Oracle Hyperion side, so I have much more familiarity with FRS and WA, and definitely less so with OBI. So, this blog will be written from that perspective.

Below, I’ll give a synopsis of what each tool does, based on my experience, followed by my opinion of when each should be used.

Financial Reporting Studio
FRS is a great tool for producing regular reports, like a monthly reporting package. It allows you to create production level reports with a multitude of formatting options. FRS reports can be viewed in PDF or HTML format, from a client component or over the web. Reports can be gathered together in Books and batch scheduled to run at the frequency of your choosing, and then saved to a particular location or emailed out to a list of users.

FRS is not a tool for producing dashboards, is limited in its chart and graph functionality, and would not generally be used for ad-hoc reporting.

Web Analysis
WA comes from the Hyperion suite of products as the tool for building dashboards. In my experience, it’s most often used to create quick snapshots of data for management or executive level users. WA allows you to create multi-view looks at your critical business metrics, either in graphical or grid format. For example, a dashboard might include a line graph of sales by region in one quadrant, a bar chart of sales by VP in another quadrant, a pie chart of expenses by category, and a grid showing spending by department. WA includes traffic lighting as a feature, allowing you to highlight or color significant variances of data.

WA would not be used for production reporting.

Oracle BI Answers
OBI Answers is a tool for building both reports and dashboards from a variety of sources including relational and multi-dimensional databases. You can build similar dashboards to what WA offers and publish reports similar to what FRS offers. The entire OBI suite has pre-built modules by industry that allow for easier implementation depending on the particular business case.

While OBI Answers can use Oracle Essbase as a data source, there are some issues in doing so that prohibit the use when a particular hierarchy exists. This should be addressed in an upcoming release, but at this point, the OBI link to the Oracle Hyperion suite of products is very limited.

Which Tool?
So, which reporting tool should be used when? With the current releases and functionality available, here is how I would use them:

FRS – Use for regular production reporting such as income statements, operating expenses, headcount, and any other meaningful financial metrics from an Oracle Hyperion data source such as Essbase, Planning, or Financial Management. I generally don’t create charts and graphs using FRS because the options and functionality are fairly limited. But, if you have fairly straightforward and simple chart requirements, then FRS should work fine for you.

WA – I would use Web Analysis for producing grid and chart dashboards from an Oracle Hyperion data source. WA does a good job of incorporating “bells and whistles” that make a dashboard “pop”, providing important metrics quickly.

OBI Answers – OBI is a great tool to use to quickly build reports and dashboards from a data warehouse. In my opinion, this is the easiest tool to learn and use of the three. As I mentioned above, I don’t think it’s currently the right tool to use with Oracle Hyperion data sources, but that very well could change in the near future.

Going Forward
In future releases, I think that OBI will become the tool of choice for reporting and dashboarding, even for Oracle Hyperion data sources. Oracle is very good at creating synergies between their product lines, and while each individual application generally has their own set of tools, eventually, they converge to similar toolset technologies. I think as OBI gets more integrated with the Oracle Hyperion suite of products, it will become the tool of choice for reporting. In my opinion, it is easier to both learn and use compared to both Financial Reporting Studio and Web Analysis.

Tips and hints relating to the development of Financial Reporting Studio reports – Tip 1

In this installment of my blog contributions I would like to focus on some techniques that I have leveraged to get through some of the more challenging aspects of reports development.

While the function builder for member selection often allows you to get the detail selection that is needed I have found instances where I have had to become creative to achieve the exact detail that was necessary within the report being developed.

Four approaches that I leverage quite frequently are:

1. Selecting Multiple Members Based on Criteria
2. Defining Member Lists
3. Referencing separate data sources within the same grid
4. Using multiple grids and hiding all but one in the final product
   

Selecting Multiple Members Based on Criteria

You can select members dynamically based on user-defined criteria. You define criteria by creating expressions of members, Boolean operations, and commands. Boolean operators enable you to specify precise member combinations for the report, which is useful for dealing with large volumes of data. Use the AND, OR, UNION, and NOT Boolean operators, combined with expression commands, to refine your member selections.

For demonstration sake, lets assume you need to include all level 0 children of "Software/Other" but none of these members parents within the report. It is also necessary that when a new level 0 member is added that the report dynamically picks this member up.

To select multiple members based on criteria for this need I perform the following:

1. Double-click a dimension cell for which you want to assign members.
   
   
2. In the Select Members dialog box, right-click inside the selected area, then click Advanced.
   
   
3. Build the expression to satisfy the above need by 1^st selecting the descendants of "Software/Other"
   
   
4. Next toggle the Select Member pane to "Lists" and select "Level 0, Product "
   
   
5. Now update the operator to "AND"
   
   
6. Now render the report in Print Preview, the objective has been obtained
   
   
7. Explore other functions:

• Select NOT to add the Boolean operator NOT to the expression. NOT is the inverse of the selected condition.
• Type a left parenthesis, (, to add an opening character to the expression.
• Type a right parenthesis, ), to add a closing character to the expression.
• Click in the OPERATOR column, then select AND, OR, or UNION.
• When all conditions must be met, use the AND operator.
• When one condition of several must be met, use the OR operator.
• To combine the data, use the UNION operator.
  

In the next installment we will cover "Defining Member Lists."

Annotation Service in Hyperion Workspace – Providing collaborative commentary

A significant enhancement that was delivered with the release of Hyperion Reporting and Analysis Release 11.1.1 was annotations. Annotations provides a comprehensive set of annotation functionality that enables users to freely collaborate, save facts and findings in their reports, and access tools to readily manage annotations across their systems. This commentary is available interactively through the Hyperion Workspace, is equally accessible through printed output, and provides a unified read-only mechanism for the Hyperion Financial Management and Hyperion Planning commentary (for example, Cell Text, Cell File Attachments, and so on).

The Annotation Service, used with Financial Reporting reports, provides a collaborative tool that captures collective information that can be shared on particular documents and data. It provides a range of flexibility from simple notations to full-fledged threaded discussions, laying the foundation for collaboration, compliance reporting, and business process analysis. Authorized users have the ability to create, remove, and respond to annotations. Annotations can be attached to a report object (grid object, text object, chart object, and image object), and can reference a full or partial POV. Annotations included in reports can be viewed when selected for books and snapshot books.

The screen shots below demonstrate the application of an annotation to a grid object for a specific full POV.

After rendering the report through WorkSpace, right click on the cell that is specific to the annotation

Select new Annotation, the following dialog box is displayed:

The annotation tab captures the title, description and a predefined category. Predefined categories exist describing the nature of the annotation. Categories are Comment, Assessment, FYI, Misc (Miscellaneous), Other, Performance, or Target.

The Context tab captures the Object and Dimension information. Object information can be a datasource or any object on the report. For example: for a data source, the annotation is attached to any Financial Reporting report using the specified data source. For a Grid:data source, the annotation is attached to the specified grid and data source. For Text, Charts, or Grids, the annotation is attached the specified object with no dimensions on the object.

Attachments tab — Link attachments to an annotation. Attachments can be a file on your local drive, any object in the repository, or a URL.

When OK has been selected and the annotation has been applied a visual indicator, a paper-clipped document appears within the upper left-hand corner of the cell

By hovering over this icon, the description of the annotation appears, included within this dialog box are links to showing details, a reply capability, open attachment and closing the dialog box

When show details has been selected, the following is displayed:

You can close the details dialog box by selecting the down arrow between the grid and details window

You can also use the View menu to enable or showing or hiding annotations. To view annotations select View > Show Annotation. To hide annotations, select View > Hide Annotations.

Users with the Modify or Full Control permissions can respond to annotations through the annotations bubble or the Annotation List Panel in a Financial Reporting report. While replying, one can change the category and also add attachments like EPM Workspace documents, local documents on your computer, or URLs.

To respond to annotations:

In a report that contains annotations, do one of the following:

1. Click an annotation icon, then click the Reply icon and respond to the annotation.
   




2. Locate the Annotation List Panel at the bottom of the screen, hover over the Show panel and click it. Then click the Reply icon and respond to the annotation.
   

You can render this report in the PDF format by selecting Open in PDF as shown belown:

The report is rendered in the Adobe PDF format. The annotation is evident with a footnote number 1.

When printed, the annotations and reply threads will be displayed on a separate footnotes page

Automating Your Reporting Package in Hyperion

Putting together your monthly, quarterly, and annual reporting packages is menial enough, but if that process is done manually, it makes it all the more tedious. Especially if, in a worst case scenario, you’re having to consolidate, reconcile, and update individual spreadsheets and print those off one-by-one….

Hyperion Workspace provides a great solution to remedy this problem through its inherent batching and scheduling functionality. This feature set allows you to collect reports into a book, batch reports and books, and schedule them for a variety of output. Following is a brief rundown of the scheduling process.

First off, you will need to decide whether your reports should be gathered into a book. Books generally consist of logical sets of reports and/or report snapshots. A report snapshot is a picture of a report on a specific date and time and does not dynamically link back to the source database. You might see a book gathered by category (monthly, quarterly, annually), by scenario (actual, budget, forecast), or by functional area (sales, engineering, administrative). It all depends on business need and logical groupings.

Books have their own point of view (POV) that allow you to override user or grid POVs. For those who aren’t familiar with Hyperion POVs, in a report, a POV is a single dimension member that applies to the entire report. From a report perspective, you have dimension members in rows, columns and the page. Any dimensions not included in the row, column, or page fall to the POV and any dimension in the POV can only have a single member selected. A grid POV applies to a grid in a specific report, whereas a user POV applies for a specific user across multiple reports. So, as an example, if you wanted to run all reports for the budget scenario for 2010, you could override report selections by using the book POV. Books can also be set up with prompts, allowing member selections to occur when the book is run.

Finally, books have a table of contents feature, enabling you to show and/or print out a listing of the reports included in the book.

The second step would be to create a batch. A batch can include items such as individual reports, report snapshots, and books. The batch is required in order to schedule reports for output. Also, if reports or books included in the batch contain prompts, members can be selected for those prompts as part of the batch set up. Like a book, a batch has its own POV that can override the POVs of its included objects.

The last step in automating your reporting package is to schedule the batch. The batch scheduler allows you to generate output immediately or on a specified date, time, and recurring frequency. Oftentimes, what I’ll see clients do is run their scheduled reports afterhours, so the processing time occurs while end users are out of the system. From a performance standpoint, this is optimal since the batch generation won’t compete with end users for system resources.

A newer feature of batch scheduling is bursting. Bursting allows you to enable reports to be run for multiple POV members. As I mentioned above, the POV is a single dimension member applying to a particular object. Bursting lets you choose multiple members and then runs the batch output for those selections. This would be useful if, for example, you wanted to run both budget and actual reports or reports for the western, eastern, northern, and southern regions.

The next batch setting is the output. You have the following options:
• Generating a report snapshot saved in the reports repository. A nice feature here is that you can set permissions to the output as part of the batch scheduler.
• Printing output.
• Exporting the output in PDF format to a shared directory and/or as an email attachment. This can be especially useful for users who do not have an ID set up in the application.
• Exporting the output in HTML format to a shared directory.

Finally, you can set up the batch scheduler to send an email if the batch generation is either successful or unsuccessful.

In terms of the mechanics for setting up books and batches and scheduling them, it’s fairly straightforward. All of these items are created in Hyperion Workspace. For books and batches, that’s done through the File -> New -> Document menu option. To schedule batches, click the Navigate icon and go to Schedule -> Batch Scheduler.

Once all your reports, books, and batches have been set up and scheduled, you really should have a self-sustaining system with dramatically decreased cycle times.

Financial Reporting Studio Report Design & Development Considerations

As a standard part on any EPM implementation the output from the culmination of the all project work (requirements, design, development, testing, etc.) is reporting. Typically, prior to an EPM implementation for Planning or Essbase, client reporting is typically performed in Excel spreadsheets. Thru the use of Excel spreadsheets reports are developed and consolidated into a reporting package. Utilizing Excel spreadsheets for financial reporting is fraught with many pain points. Making changes to multiple worksheets within a single workbook is often a tedious and time consuming task as well as the potential for human error in terms of incorrect cell references and worksheet links. I’ve worked with clients who have had to change dozens and dozens of worksheets in preparation for the next budget cycle. This process took weeks and weeks to complete and the owner of this process was never sure that all the spreadsheets were absolutely correct. The lack of flexibility with an Excel-based reporting is significant.

Oracle’s Hyperion Financial Reporting Studio (FRS) relieves companies of this problem. We’ll be discussing high-level reporting strategies to consider when creating reports using Oracle’s FRS.

Report Design Considerations

1) Determine the set of reports to be developed in FRS – this is a critical aspect of report development and should be determined during the requirements gathering process of a project but should be revisited as you move closer to the report development cycle to ensure the report set is still in alignment with the project requirements. In working with clients, the most successful report development phase of projects has been where client have been able to provide a full set of static reports not only for the current cycle but reports for a full year. This will provide you with the ability to determine if there any anomalies or differences between the reporting cycles.

2) Dynamic Reports – the objective should always be to develop dynamic reports because of their inherent ability to reduce maintenance. Dynamic reports should include:

a. Use of Essbase substitution variables. The benefits of utilizing substitution variables include reduced go-forward report maintenance and provides client with the ability to reuse a set of reports for different reporting cycles.

b. Use of relationship members rather than hard-coding dimension members. This will reduce report maintenance when new accounts, dept members, etc. are added to the Essbase outline. Instead of going into every report to update the particular row or column, the new member will automatically incorporate appear in reports. For clients that have a significant number of FRS reports the potential on-going time-savings is considerable.

c. Use of FRS functions. FRS provides a series a pre-built functions that enable report developers to develop reports that are dynamic in nature.

3) Report Consistency – reports should be developed with a consistent “look and feel”. Rows and columns should have a consistent height and width, spacing rows and columns widths should be consistent as well. Fonts should be consistent across reports. Headers and footers should be consistent across all reports.

By following these guidelines during the report developed process, clients will have the ability utilize FRS to its full effectiveness and have in place dynamic flexible reporting that will create more time for analysis of the data rather than the ticking and tying process. In addition, the ability to reusability of dynamic reporting and reduced maintenance and preparation for the future reporting cycles will enable will provide organizations the insight to their business that may not have had previously as well as provide consistent reporting themes across the organization.