Oracle Hyperion SmartView - New Features in 11.1.2 (Fusion Edition)

April 29, 2009 by Chris Khoury

New Features in the Fusion Edition Include:

1. Data Source Manager: Replaces Connection Manager. A dock-able pane which allows us to switch between the three interfaces: the Common Provider Connections, the Independent Provider Connections, and the Simulation & Forecasting Workbooks Connections.

2. Common Provider Connections (PROVIDED CONNECTIONS PREVIOUSLY ONLY BETWEEN SMART VIEW AND ESSBASE): Where we can manage connections our connections to Essbase, Planning, and OBIEE. The connections go through Hyperion Provider Services. Smart Slices of these data sources are created, stored, and accessed through this interface of the new Data Source Manager.

3. Independent Provider Connections: Is comparable to the previous Connection Manager. In it, we can manage connections to Hyperion Reporting and Analysis and HFM. We can use the Independent Provider Connections to connect to Planning, but we will be able to only use the functionality available in release 9.3.1.

4. Simulating & Forecasting Workbooks: If the user is licensed to use Crystal Ball, they can access and work with Oracle Crystal Ball through this interface.

5. Smart Slices: Is a reusable perspective (extract) of an Essbase, Planning, or OBIEE data source. It is composed of a single member, a combination if single members, filters, or combination of single members and filters. These components limit the users to the data they can work with (boundaries). There is no limit on how many Smart Slices can be created. For example; Smart Slices can be created for different sales geographical regions, different product lines. Smart Slices are created by Administrators and are stored in the Provider Services layer. Access to Smart Slices is assigned to the users by the Admins.

6. Report Designer: Reports writers can use this interface to manager reports based on Smart Slices. A report can be displayed as a Grid, Table, or a Chart. Report Designer works with Essbase, Planning, and OBIEE.

7. Essbase Enhanced Drill-Through Capabilities: using Essbase Studio.

8. Planning Ad Hoc Functionality: In previous Smart View versions, we were able to use the Ad Hoc Functionality only in Essbase connections. In this release, we can use Ad Hoc in Planning as well. We are able to Zoom In/Out, and Pivot. Ad Hoc Functionality in Planning is a part of the Web Forms Design (defined in Web Form Design).

9. Planning à Web Launch: Planning Web Forms can be launched directly into a Smart View Excel Sheet.

10. HFM Adding Members to Data Forms: If this feature is enabled by Administrators, user can insert additional members and enter and save data.

11. HFM Linked Forms: When Administrators create Data Forms, they can define links from one form to another to enable drill-through to a more specific data entry point. For example; a form that includes Account Summary Data, can be linked to another form which provides more details.

12. HFM --> Changing Database Passwords: We can change the Data Source Passwords for HFM directly from Independent Provider Connections.

13. Accessibility: Supporting Documentation accessible to the Disabled Community.

14. Installation and Configuration: Smart View can be installed by running the Executable File for Essbase. It is also available as a Standalone Executable file.

Oracle Hyperion SmartView Overview

April 29, 2009 by Chris Khoury

The Oracle Hyperion Smart View for Office (Smart View) is a component of Hyperion Foundation Services. Smart View provides a common Microsoft Office interface designed specifically for Oracle's Enterprise Performance Management (EPM) and Business Intelligence (BI). Using Smart View, you can view, import, manipulate, distribute and share data in Microsoft Excel, Word and PowerPoint interfaces. It is a comprehensive tool for accessing and integrating EPM and BI content from Microsoft Office products.

Ad Hoc -or- Free Form Analysis

This is for Users requiring an environment to have a conversation with the data. Typically they use the Excel environment to interactively investigate the data contained in the source(s). They may start with templates that begin the process (such as East->Cola->Sales) or a blank sheet where they begin shaping and altering the grids of data as they use the exposed functionality. Typically users go after the data from Oracle EPM sources such as Essbase, Planning or Financial Management using clicks & drag drop for the most part. Other data sources supported for ad hoc analysis includes Oracle Business Intelligence Enterprise Edition.

Predefined Form Interaction

EPM application users that execute predefined input/reporting forms find Smart View a convenient way of completing tasks within the Office environment. These would be planners, consolidators and the like that have a desire to work in the excel environment either for consistent experience compared to the web application or to tie other spreadsheet-based models into their process. For example, Pepsi Bottling uses Smart View for Planning in order to incorporate data still housed in spreadsheet and workbook-based models.

Report Design

Reporting is another dimension of Smart View usage which leverages the capabilities of EPM data retrieves. Once the data is available within Office we can create reports as needed based on a combination of data sources. For example Planning & Financial Management data could be used to compare actual to budget. Reports could be made more complex by providing the ability to compare multiple scenarios, for different periods etc. The power of office can be used to create Reports in the Office environment and can be refreshed periodically as needed.

Smart View Key Capabilities

- Ability to integrate EPM & BI data directly from the data source into Microsoft Word, Microsoft PowerPoint, and Microsoft Outlook

- Full ad-hoc and free form analysis capabilities for Hyperion Financial Management, Hyperion Essbase, Hyperion Planning and Oracle Business Intelligence Enterprise Edition

- Data access from Relational or Multi-Dimensional sources

- An alternative, Office-based interface for leveraging Hyperion Planning, Hyperion Financial Management, and Hyperion Workforce Planning data entry forms

- Ability to create data perspectives for end users called Smart Slices

- Ability to execute Hyperion reporting jobs from within Office applications

- Ability to dynamically refresh Hyperion data across Office documents with a single click

- Ability to perform selective, multi-page import of documents residing in a Workspace repository into Office documents, including reports created in Financial Reporting, Interactive Reporting, SQR and Web Analysis.

- Point of View (POV) search for efficient,context-based searching

- Support for highly formatted grids in Microsoft Excel

- Support for Hyperion Essbase Visual Explorer (included in the Smart View plug-in) delivering one-click visualizations, bi-directional communication, and linked data views

- Report Creation in Excel, Word and PowerPoint using EPM and BI data

- Comprehensive VBA toolkit for developing custom Office-based analytic interfaces

SSL Enable Your OBIEE / BI Publisher Installation

April 21, 2009 by Pablo Bryan

I recently had the need of exposing an OBIEE implementation out to the internet. As I finished the installation, I realized that the login prompt and other components are passed as plain text openly through the wire. So the need came up to secure this installation with SSL. SSL will encrypt with a private/public key all the communication between the server and the browser.

In order to SSL enable OBIEE there are several components that you need to modify, but first you need to generate a server certificate and have it signed by a trusted certificate authority. If you don’t want to pay the $200 or so you can self sign the certificate, but you will get the annoying warning messages in the browser. I got a free 90 day signed certificate from instantssl.com. Before we get started, I would recommend that you backup all the configuration files you are going to modify in case something goes wrong you can always go back to the saved versions.

The first step is to generate a keystore with keytool like so:

keytool -genkey -keyalg RSA -alias myserver_mycompany_com -keystore myserver_mycompany_com.key -storepass password -dname "CN=myserver.myhost.com, OU=MyCompanyDept, O=MyCompany, L=Irving, ST=Texas, C=US"

This will generate a file called myserver_mycompany_com.key that is the keystore where your key lives. I saved this file in a secure place with restricted access where only Administrators (or root for linux/unix systems) have access. In my case I saved it to /root/ssl/ The alias is important as you will use it to import the signed chain from the Trusted CA.

Make sure you specify the fully qualified name of your server as the CN (i.e. obiee.mycompany.com) especially if you are going to have the certificate signed by a trusted CA.

If you are going to self sign the certificate, run the following:

keytool -selfcert -alias myserver_mycompany_com -keystore myserver_mycompany_com.key –storepass password

If you are going to get a Trusted CA to sign your certificate, run the following:

keytool -certreq -alias myserver_mycompany_com –keystore myserver_mycompany_com.key -file myserver_mycompany_com.cert

This will generate a file called myserver_mycompany_com.cert which you need to use when getting your certificate from a trusted CA. I used instantssl.com. Use the content of this file to request your signed certificate (it's free for 90 days).

If you are using a self signed certificate, all you need to do is export the certificate from your key and import it into the SA (OBIEE) and JAVA certificte store like so:

keytool -export -alias myserver_mycompany_com -keystore keystore.ssl -file myserver_mycompany_com.cert

And to import the file:

keytool -import -alias myserver_mycompany_com -file myserver_mycompany_com.cert -keystore JRE_HOME/lib/security/cacerts

for Java, and:

keytool -import -file chapulin.cert -keystore OracleBIData_HOME/web/config/certificates/saw.keystore

For the OBIEE keystore. The default password for the OBIEE certificate store is "password". Of course you need to replace the values JRE_HOME and OracleBIData_HOME with actual values from your installation. What this does is enable the signed certificate to be trusted by the OBIEE installation and any programs using JAVA in this server.

Once you have added your certificate files to the keystores, you need to update the OC4J configuration of your OBIEE server. The files to edit are as follows:

Edit the file OracleBIHOME/oc4j_bi/j2ee/home/config/default-web-site.xml

The first tag should be the web-site tag you need to add the secure="true" attribute like so:

<web-site ... port="443" display-name="OC4J 10g (10.1.3) Default Web Site" schema-major-version="10" secure="true" schema-minor-version="0" >

Also note that I changed the port from the default 9704 to 443. I did this since some firewalls block odd ports like 9704 and I needed to access this box from anywhere even behind firewalls and most firewalls open up the standard SSL port of 443.

Next, you need to add a tag with the following info:

<ssl-config keystore="PATH_TO_KEYSTORE/myserver_mycompany_com.key" keystore-password="password" />

Where PATH_TO_KEYSTORE points to the folder where you saved the keystore that we generated on the first step above, and the password matches the password you've been using in all the "keytool" steps.

We're almost done, but not quite there yet. In order to fully utilize SSL we need to update the BI Publisher config file to use SSL as well. We already added the certificate to the keystore. Now we need to update the pointers in the config files.

First we need to update OracleBIData_HOME/web/config/instanceconfig.xml file. Look for the following entries and update the protocol from "http" to "https" and update the port, in my case from "9704" to "443" like so:

<ServerURL>https://myserver.mycompany.com:443/xmlpserver/services/XMLPService</ServerURL>
<WebURL>https://myserver.mycompany.com:443/xmlpserver

And finally, we need to update the BI Publisher's xml config file. You can also do this via the web, but I found it much easier to do it all at the command line while I'm updating all the other configuration files.

OracleBI_HOME/xml/XML/Admin/Configuration/xmlp-server-config.xml

You need to look for the tags:

<property name="SAW_PROTOCOL" value="https"/>
<property name="SAW_PORT" value="443"/>

And update their values to the values you are using.

After all of these changes, restart all services including oc4j and you should be able to have OBIEE SSL enabled with BI Publisher.

OAUG BI SIG Events at Collaborate '09

April 20, 2009 by Jim Glazar

Collaborate '09 is quickly approaching and is scheduled for May 3rd - 7th in Orlando, Florida. Those of you who are going to be there on Sunday, May 3rd I highly encourage you to attend the BI and Hyperion SIG meetings.

1) OAUG BI SIG Meeting - 1pm - 3:15 pm
2) OAUG Hyperion SIG Meeting - 3:30 - 5:45 pm

As a Board Member on the OAUG BI SIG, I know that we have some great things planned for our BI SIG Meeting including a Build vs. Buy panel discussion with OBI EE professionals from both Humana and Gallup who have recently implemented OBI EE.

On Sunday night after the SIG Meetings the BI and Hyperion SIGs are going to co-sponsor a fun event at BB Kings from 6:30 - 8:30 pm. This will be a chance for people to network with other Hyperion and BI colleagues and to catch up with old friends.

For more details go to the OAUG BI SIG website: http://obisig.oaug.org/

Hope to see you at Collaborate '09 and feel free to come by and say hi to us at the Kerdock Consulting booth.

OBI EE Gurus Initial Post

April 17, 2009 by Jim Glazar

Welcome to the new OBI EE Gurus Blog. The main intent of this blog is to provide our readers with content covering all things to do with OBI EE (Oracle Business Intelligence Enterprise Edition).

Our goal is to provide timely and accurate information and include content such as:

• Tips & Tricks
• Integration Points (especially with the Hyperion applications)
• New Features
• Strategic & Roadmap Information

A little info about myself:

I am the Director of the BI Practice at Kerdock Consulting which is a Certified Advantage Partner with Oracle. I started working with Oracle EPM and BI applications back in 1996 so over 12 years ago - boy has time flown by. With the acquisitions of Hyperion and Siebel, Oracle really for its first time has a comprehensive and top tier BI & EPM offering. So for me, I find this to be an exciting time in this space and look forward to continuing to work with the Oracle suite of applications and witnessing their continued integration.

About the team of OBI EE Gurus:

Kerdock Consulting has a strong group of OBI EE technical resources and in fact many of them are certified Oracle Instructors. So we have a strong team of resources that will be posting their OBI EE knowledge on this blog. Furthermore, our firm also specializes in Hyperion so our resources also have extensive experience with the Hyperion applications so they are knowledgeable on OBI EE and Hyperion integration points.

In conclusion, I hope now that you have found our blog that you find it informative and useful and don't forget to tell your friends and colleagues.