Oracle Data Visualizer Series - Direct Connections to Essbase: Enhancing Analytics with Named Hierarchies

Published October 14 2020 by John Patterson
Back to insights

Oracle Data Visualizer is a powerful tool for telling stories and getting to the bottom of things quickly. It has the capability to connect to scores of data sources from flat-files, spreadsheets, relational databases, Oracle Autonomous Data Warehouse, Essbase, and now EPM Applications. It can also transform data in some of those sources and join it together with other sources.

This article focuses on Direct Connections to Oracle Essbase and a subtle Application improvement that will enhance end-user experience and organizational analytical capabilities.


Essbase Direct Connection – Hierarchy Navigation

Below is a visualization of Package Type Information from an enhanced Sample.Basic cube. Our visualization is highlighting “The Beverage Corporation’s” level of environmental responsibility by displaying sales by recyclable package type in green in the pie chart.

The first step in creating this visualization is dragging and dropping dimensions from the left side of the Data windowpane to the values and color palette section of the chart.   Filters for Sales and Regions were applied with simple right clicks on the relevant dimensions.   Recycle Category and Material Category were both moved to the color section of the workspace on the right, which created the hues of green and yellow based on hierarchical sub-categories.  

And there you have it, a responsive pie chart in seconds that tells a visual story. The results can be duplicated, filtered, and drilled on.   The chart type can be changed instantaneously to one of a few dozen options depending on the data set.   Underlying numbers may be displayed in a table.   Additional charts can be added to this screen to display summaries or subsets of data.   The results can be published or exported with ease and shared with others that have access rights to the data source. It’s all only a few button clicks.

Incredibly easy!

There is a small catch with Direct Connections.   In Oracle Data Visualizer, named hierarchies are displayed to ease navigation for analysts.   In the absence of named hierarchies, Data Visualizer displays hierarchies as numbered generations as shown in the example of PkgType below.

The mapping from the former chart illustration with named hierarchies to the latter Data Visualizer selection illustration with unnamed hierarchies is as follows:

  • Gen2 of Pkg Type is Recycle Category
  • Gen3 is Material Category
  • Gen4 is Pkg Material

The news isn’t so bad. Data Visualizer Users may investigate each generation to make choices for visualization selections.   For instance, right-clicking on Gen2, Gen3, or Gen4 will allow a user to filter on the hierarchy and select specific dimension members. This will help any user identify what level of the outline to select as a filter for a visualization. Gen2 through 4 can also be dragged onto Visualizations.

It is easier to intuit the named Market hierarchies of Region and State for Visualization purposes than unnamed Generations under Pkg Type.  

Naming Hierarchies – Essbase 19c Cloud Interface

Confession time. As a long time Essbase Developer, I am guilty of not naming hierarchies. I suspect I am not alone. There hasn’t been an overwhelming need to date. Data Visualizer is changing this scenario. It’s a good thing.

Naming hierarchies requires mindful planning and dialogue around hierarchy choices and nomenclature.  When hierarchies are named, it may make sense to avoid ragged hierarchies in some cases. It also may make sense to normalize hierarchies. It requires some extra management and attention post-go-live.

The payoff is worth the effort. Users will be able to better leverage Oracle’s latest Analytics interfaces against Essbase and many other sources with more intuition and less training.  

Here’s a depiction of our Pkg Type dimension from Essbase 19c:

“Metal” or “Waxed Cardboard” are examples of an implied share that we may normally consider as a candidate for a ragged hierarchy. However, in terms of producing a richer Visualization and interface experience, it makes sense to balance these members with subcategories above them. Even in prior versions, it would have produced a preferable reporting experience.

In Essbase 19c and OAC, hierarchies are named by Inspecting a Cube and navigating to the Dimensions tab. For larger applications, it may take a second or two for generations to render on this screen.

Pkg Type without named Hierarchies will look like this:

This seems simple, but it is less than obvious. From this screen, it does not appear possible to double-click on Gen1, Gen2, Gen3, or Gen4 and type in a new name.   It’s really that easy.

Here’s the view of our Pkg Type once the change is implemented:

By changing the Essbase assignment, a quick refresh in Data Visualizer will produce the following selections for hierarchies. In the following example, a user is filtering on Recycle Category and may now pick the Non-Recyclable or Recyclable members for a Visualization.

Here’s another example of a use case on the Market Dimension, where Region and State are named hierarchies and used simultaneously to create a filtered and detailed Visualization. In this example, the East Region is filtered from the named Region hierarchy by a right-click selection.

Next, the State hierarchy is dragged onto the color palette at which point our pie chart automatically drills to a depiction of East States:


Naming Hierarchies – Smart View Cube Designer Interface

For existing Essbase Cubes, it may be easier to apply and deploy named hierarchies en masse using Smart View’s Cube Designer Interface. Cube Designer is easiest to use for this purpose. It does not require mentioning hierarchies that will not be named.  

The fastest way to get do a mass update with Cube Designer is to export an existing cube from 19c via Essbase Cloud to Excel. Once the exported cube is downloaded, if it already has any named hierarchies, the third Worksheet in the exported Workbook will be “Cube.Generations”.

If a “Cube.Generations” Worksheet does not exist, create it and add named generations in the following format:

Finally, run the Build Cube step in Smart View Cube Designer and deploy the results.

Happy Visualizing with named hierarchies!

For comments, questions, or suggestions for future topics, please reach out to us at  Visit our blog regularly for new posts about Cloud updates and other Oracle Cloud Services such as Planning and Budgeting, Financial Consolidation, Account Reconciliation, and Enterprise Data Management.  Follow Alithya on social media for the latest information about EPM, ERP, and Analytics solutions to meet your business needs.

Contact us