Hyperion Planning

The Challenges of Hyperion Support - Part 2 Smart View and User Support

The Bean Consulting Group

Introduction

As we covered in Part #1 of our series – Metadata Management – we felt that the unique challenges of supporting a Hyperion application environment within the IT department, rather than from within finance or accounting, could be addressed by providing some insight that might help bridge the gap between the IT department and application DBAs who typically support enterprise applications and the finance and accounting groups that typically utilize Hyperion applications.

In this, the second article in our series, we will tackle the topic of Smart View and User Support. Smart View and user support for Hyperion application typically breaks down into two primary areas – client or desktop support and data support.  Client support with Smart View is fairly straight forward provided users have the administrative permissions to install the Smart View add-in on their desktop, the ability to download the installer from the Hyperion Workspace, and an understanding of the connections they are using.  We will address these items in a bit more detail later.

Data support with Hyperion applications, on the other hand, requires an understanding of the Point of View, and the ETL process used to load data to the Hyperion applications.

It’s The Point of View (POV)

So, what is the point of view?  Commonly referred to as an intersection, the Point of View (POV) can be thought of as GPS coordinates for your data.

In traditional General Ledger (GL) systems transaction records are tracked by Chart of Account (CoA) segments and related properties or attributes.  As seen in the example below, the “piece” of data in the transaction record is located at, what is commonly referred to as, the intersection defined by the following “coordinates”:

Transaction Record:  50115-0100-2510-0010-2000-0000-9999-001-2500.75

  • Account - 50115

  • Department - 0100

  • Location - 2510

  • Cost Code - 0010

  • Product - 2000

  • Project – 0000

  • Customer - 9999

  • Currency - 001

  • Data Element – 2500.75

Typically these Chart of Account segments and related properties or attributes (or coordinates if the geographic analogy works better for you) are logically mapped into what are referred to as dimensions; and these dimensions in combination create a logical model for the business.  Again, using the “geographic coordinates” analogy, these dimension might be similar to latitude, longitude, elevation, etc. which provide a way of describing the data’s “location”.

In turn, these data dimensions contain hierarchies that aggregate or “roll data up” at various levels and perform calculations on the data, all of which can then be used to support business analytic activities and performance management processes.

In many cases, although not always, these transactional data records are loaded into a data warehouse using a star schema (or snow flake) model using some form of Extract-Transform-Load (ETL) process (see article #4 in our series Data Integration, Ownership and Security for more information on this process).  As part of the ETL process into the data warehouse, the transactional data is physically mapped into dimension and fact tables which align with the logical dimensional model that has been developed for the business.  And, if a data warehouse isn’t utilized, a similar ETL process is used to load data into the Hyperion application, mapping transactional data into the logical dimensional model developed for the business.  This is depicted below:

Once we think about these ETL processes that feed the data warehouse or the Hyperion application, it becomes understandable why metadata management and governance are so crucial to successful analytics and performance management, and why we covered this in our first article in the series (Metadata Management – Governance Is The Key).

If there isn’t well “scrubbed” data in the data warehouse, or being loaded through the ETL process into the Hyperion application, then the results gleaned from either may be suspect.  Consider the case where no governance is provided in the creation of the data warehouse fact and dimension tables – in this case, we may have “duplicate” dimension members that if not consolidated when used for analysis, could lead to erroneous conclusions.

Smart View vs. Hyperion Financial Reports

End users typically access Hyperion data either through Smart View or though Financial Reports which are developed using Hyperion Reports Studio (we will discuss Hyperion Financial Reports and Reports Studio in the last article in our series).

As the Oracle® Hyperion Smart View for Office User's Guide Release 11.1.2.3 states, “Oracle Hyperion Smart View for Office provides a common Microsoft Office interface for Oracle Essbase, Oracle Hyperion Financial Management, Oracle Hyperion Planning, Oracle Hyperion Enterprise Performance Management Workspace, Oracle Hyperion Reporting and Analysis, Oracle Hyperion Financial Close Management, and Oracle Hyperion Enterprise® data sources. Using Smart View, you can view, import, manipulate, distribute, and share data from these data sources in Microsoft Excel, Word, Outlook, and PowerPoint.

Historically, Smart View (or, in its prior life, the Excel Essbase Add-In) was most commonly used with MS Excel connecting to Essbase for the reporting and analysis of financial data; and, we will discuss that use case along with a few of the other ways that Smart View is used with the Hyperion and Microsoft suite of products.

Smart View Connections

Each of the Hyperion products – Essbase, Hyperion Planning, and Hyperion Financial Management - has a Smart View connection with tools and functionality specific to that product.  We will discuss each of these in more detail below.

Once Smart View is installed, data source connections are accessed from the Smart View Panel using a shared or private connection. Users are able to manage connections, open grids, forms, and task lists once a connection is made to the data source.

Smart View gives the user the option to connect to a data source through a Shared or Private connection. A shared connection allows the user to select from all avaliable data sources whereas a private connection allows the the user to create a list of “favorites” among all shared data sources. The advantages of creating a private connection is that it allows the user to bookmark and rename certain connections. This becomes useful when you have a long list of Essbase databases but only use a few of them.

The process of creating shared and private connections is outlined below.

Shared Connections

Smart View shared connections are stored in a central location and are available to multiple users. They are typically created and maintained by an administrator and users cannot edit or rename them unless saved as a private connection.

A shared connection is established by entering the shared connection URL (http://:19000/workspace/SmartViewProviders) within the Advanced tab of the Smart View Options as shown below.

Opening a Data Source through a Shared Connection

To open a data source from a shared connection, select the Smart View ribbon in Excel then click Panel.

Once the Panel is displayed, select Shared Connections which then will prompt you to enter your credentials to connect to the data source.

Navigate to and highlight (single-click) the desired application and database then select Ad-hoc Analysis in the Action Panel.

Once a connection has been made, it will appear as a Recently Used connection which can be accessed as a shortcut for future use. Click the pin icon to pin connections to the list.

 

Private Connections

Private connections are created by the end user by saving a shared connection or by manually entering in a provider URL. When a private connection is created it becomes the active connection.

Creating a Private Connection

When creating a private connection, either select Private Connections within the Smart View Panel or select the drop down box next to the home icon and choose Private Connections.

The list should be blank if no private connection has been created. Navigate to Shared Connections, select the Essbase server if necessary, and expand to the desired connection. Right-click and select Add to Private Connection.

Give the connection a name and click OK.

Once the private connection has been made, you can now activate the connection in the Connections icon in the Smart View ribbon by selecting Connections then Active Connections.

Now when you view the private connections within the Smart View panel you will see the Essbase server and the selected database instead of the full list of applications.

Create a Private Connection Using URL

To manually create a private connection, select Private Connections within the Smart View panel and simply enter the URL for the data source to which you want to connect in the text box and click the     icon.

The URL syntax for the various data sources is as follows:

Financial Management

http(s)://servername:port/hfmofficeprovider/hfmofficeprovider.aspx

Hyperion Enterprise

http://servername:port/heofficeprovider/heofficeprovider.aspx

Planning

http(s)://servername:port/HyperionPlanning/SmartView

Essbase

http(s)://servername:port/aps/SmartView

Reporting & Analysis

http(s)://servername:port/raframework/browse/listxml

Financial Close Management

http://servername:port/fcc/servlets/smartview/fcmsvservlet

Hyperion Strategic Finance

http://servername:port/StrategicPlanning/SmartView

 

Excel Smart View Connections

Smart View provides users with multiple ribbons which replaced the old Hyperion ribbon. The Smart View functionality in Excel is accessed through ribbon commands on each respective ribbon. The Smart View ribbon, which contains commands for common operations, will always be present. Once a connection is made the corresponding data source ribbon is displayed. Each ribbon displays only the commands supported for the connection; for instance, an Essbase connection will only have functionality that is relevant to Essbase.

Smart View Ribbon

Some of the most commonly used commands include:

  • Panel – opens the Smart View panel that manages connections

  • Connections – quick access folder to common or saved connections

  • Undo – allows a user to undo a query or change

  • Refresh – refreshes the connected sheet

  • Submit Data – allows data submission through Smart View

  • Options – connection URL, member, data, formatting, and configuration options

We will now take a deeper look at each of these connections and the functionality available in each.

Essbase

Oracle Essbase (Essbase) is a multidimensional database management system (MDBMS) that provides a platform to build analytic applications for comprehensive analyses. Essbase is built to house and calculate data; other tools, such as Smart View or HFR are used to display data.

Smart View is an ad hoc analysis and reporting tool that allows users to view and share data from Essbase across Microsoft Excel, Word and PowerPoint.  Smart View enables users to perform the following tasks:

  • Query data and create reports

  • Use functions to build common POVs

  • Preform ad hoc analysis on data

Essbase Ribbon – Essbase related commands

Some of the most commonly used commands used on the Essbase ribbon include:

  • Zoom In/Out – dills in and out of a hierarchy to display data intersections

  • Keep/Remove Only – keeps or removes the member that is selected

  • Member Selection – provides a list of member to add to a spreadsheet

  • POV – allows member selection for members not on the spreadsheet

  • Pivot – allows dimensions to be pivoted from rows to columns and vice versa

  • Cascade – allows users to create separate tabs in the Excel spreadsheet

  • POV – opens the Point of View panel for dimension member selection

  • Drill Through –access to “drill through” reports that provides direct access to detailed “transaction” level reports of the underlying relational data for a particular intersection of data in Essbase

  • Submit Data – allows data submission through Smart View

Planning

Hyperion Planning (Planning) is a Web-based budgeting and forecasting tool that integrates financial and operational planning processes with improved business predictability. Planning and Essbase work in “unison” to provide a budgeting and planning analytic system that allows data entry and calculations with “one source of truth.” After connecting to Planning, the “Planning Ad Hoc” ribbon will appear.

Planning Ad Hoc Ribbon – Planning related commands

Some of the most commonly used commands include:

  • Zoom In/Out – dills in and out of a hierarchy to display data intersections

  • Keep/Remove Only – keeps or removes the member that is selected

  • Member Selection – provides a list of member to add to a spreadsheet

  • POV – allows member selection for members not on the spreadsheet

  • Cell Actions – these are Essbase intersection level details that are stored in a the Planning relational database

    • Cell Text – allows users to enter comments (just like Cell Comments in Excel), however these comments can be accessed through the Financial Report Studio as well for reporting with the related numeric data

    • Supporting Detail – allows users to enter an additional level of detail in the Planning application model using simple calculations, such as qty * price; again, with the additional detail stored in the Planning relational database and accessible for reporting through Financial Report Studio.

Planning Panel

Accessing Planning application data, Forms, and Task Lists are done within the Smart View Panel once a connection is made. Simply navigate to the desired Form, Task List, or Planning database for ad hoc analysis as shown below.

 

View Part 2 of this Article

E-mail: info@thebean.co

Article Archive

Business Intelligence

There are no articles in this category

Essbase

What is Essbase?

Hyperion Planning

DRM Administrator: Day in the Life
The Challenges of Hyperion Support - Part 1: Metadata Management – Governance is the Key
The Challenges of Hyperion Support - Part 1: Metadata Management – Governance is the Key (Continued)
The Challenges of Hyperion Support - Part 2 Smart View and User Support
The Challenges of Hyperion Support Part 2: Smart View and User Support (Continued)
The Challenges of Hyperion Support - Part 3: Managing the Process and Workflow of the Finance Office
The Challenges of Hyperion Support - Part 3: Managing the Process and Workflow of the Finance Office (Continued)

There are no articles in this category