Adding a Spreadsheet from Excel Using the Smart View Plug-In in Oracle Hyperion ESSBase

Excel is an Electronic Spreadsheet Program; an electronic spreadsheet is a computer software program that is used for storing, organizing and manipulating data. Spreadsheets are often used to store financial data. There are more options to enhance its performance and capabilities.
In this Oracle Essbase End-user Tools blog, we’ll explore how Essbase and its specific add-in for Excel can bring the performance of your staff to another level when a support tool for data-driven decision making or data analysis is required.

1. Main Motivation

Microsoft Excel is the worlds most used and versatile business analysis, reporting and strategy software. So, that business user can carry on working in a comfortable environment. Excel offers certain business intelligence capabilities & makes it easier than ever to explore and analyze data.

Instead of trying to fight the end-users’ natural instincts, setting Excel against the BI system, a more suitable approach would perhaps be the following: Benefits of BI system using Excel as its final consumption tool

• Time savings one of the key advantages of Business Intelligence is that most business processes are automated which generates important savings both in time and costs.

• Easier and quicker access to information. It is clear that, over the last few years, the amount of business data has propelled.

2. Essbase Technical Overview

Before starting a more detailed description of how Essbase could enhance your BI stack, let’s review what Essbase actually is from a technical perspective.

Oracle Essbase is the market leading online analytical processing (OLAP) server for enterprise performance management (EPM) applications .Designed specifically for business users, Oracle Essbase supports forecasting, variance analysis, root cause identification, scenario planning and what-if modeling for both custom and packaged applications.

In contrast with On-Line Transaction Processing (OLTP), OLAP is focused and optimized for processing queries instead of transactions.

3. Benefits and enhancements of empowering Excel with Essbase

So far, we have introduced Essbase as a tool that could empower Excel in order to provide an Enhanced experience to Business users working with data in spread sheets, and we have also outlined the technology behind it. But what exactly are the specific features and capabilities that this OLAP system can really offer Excel?

Let’s have a look at some of what Essbase can do for Excel users:

Build your own ad hoc analysis in a user-friendly way.

And remember you will still be using your favorite spreadsheet program!

Navigate the data.

Once a cube is accessed from Excel, exploring the data through all its dimensions is both fast (we are talking about sub-second query times) and intuitive. After a quick training session on the new features offered by this tool, users will find themselves slicing and dicing the cube according to the specific information they are looking for.

Drill-down through the hierarchies of your dimensions.

Cube dimensions are designed in a hierarchical fashion in Essbase, and aggregation rules are always defined for its measures. This provides built-in drill-down functionality, allowing the end-user to retrieve data with the best-fitting detail for each analysis.

No exporting and importing data.

There is no need to export the requested data from any BI presentation tool for subsequent import to Excel; the data is already in Excel, and you only need to access the cube, look for the required data and build your own custom analysis. Bear in mind that once the data is in your spreadsheet, no one is going to prevent you from applying formulas to get new calculations to support your analysis.

4. Hands On Smart View add-in for Excel

Now everything’s ready for show-time! Let’s proceed to a brief demonstration of how to access to your Essbase applications (cubes) with the Smart View plug-in for Excel.
Once the add-in (kindly provided by Oracle) is installed in the user’s local machine, the SMART VIEW tab will be available right after the default ones.

The very first time, it will be necessary to set up the connection to Essbase. Once this is done, connecting to the server is going to be as easy as:
➜ Open the Smart  View table
➜ Click the Panel button
➜ Access Shared Connections
➜ Fill in your user name and password

Note that all the security settings for data governance are managed through user accounts in the Oracle’s Web Logic domain.

Clicking the cube (in this example we are selecting TBC_PO) will make different options to access and manage it available.


In this case, we select Ad hoc analysis, thus loading the cube and making the ESSBase tab available.

Analysis 1
We want to look at the HSP_Rates per Version across the year 2016. We will need both Version and Year dimensions:

Analysis 2

Now let’s say that we are interested in the HSP_Rates by Entity and Scenario, across the year 2016, but I don’t need to go for Account dimension by leaving it alone.
We will need both Entity and Scenario dimensions. Then, as we did in the other analysis, we will drill-down until we get the desired detail of data.

Let’s have a look at the final dataset:

Conclusion:

Historically speaking, many Excel-based add-ins, were available to access EPM Suite of products. Smart View provides a convergence of these technologies into a single client with which Office users can leverage EPM data.

Smart View makes EPM and BI data (Business Intelligence Enterprise Edition) available via Microsoft® Office in a useful manner. Providing information within the productivity applications where users spend most of the day allows everyone in an enterprise to do their jobs better.

Smart View allows for application access (HFM, Planning for example), ad hoc analysis queries (Business Intelligence Enterprise Edition, Essbase, Planning, etc.) and existing Reporting & Analysis content import.

Comments are closed.