Data Visualization in an EPM World - Part 1 - Connecting to EPM App

It should not come as a surprise when I say I like Data Visualization and Data Storytelling.

I have worked in Data Analytics for over 20 years and I always strive to find better ways of creating information from data.  Data Visualization and Data Storytelling is powerful way to convey data in way that allows users to gain insight and helps drive action.

Oracle extended connectivity from Oracle Analytics Cloud to Oracle EPM via the OAC Repository (RPD) in the ___ release.  In March of this year I wrote about some modeling considerations when working with EPM in the RPD (see EPBCS and OAC Metadata Issue ).

In the ___ release of Oracle Analytics Cloud Data Visualization (OAC-DV) direct connection to Oracle EPM was made available.  This connection supports EPBCS and FCC.

While connecting to EPM is easy querying from and making visualizations can be a bit of a challenge at first.

It should be noted that a subject area based on EPM within the RPD can be used in OAC-DV, this post will discuss the direct connection from OAC-DV.

In Sept. 2020, Oracle EPM introduced "Dashboards 2.0".  It greatly improved the visual display of EPM Data.

There are numerous reasons and benefits as to why one might want to use OAC-DV on Oracle EPM data.  That discussion is for another day.

I want to talk about the mechanics to connect and display EPM data in OAC-DV.

In the Connection Type interface there is a connection to Oracle EPM Cloud.

The 3 main parameters in the Create Connection window are the URL to EPM Cloud and the credentials.  For now this post will focus on using the shared connection credentials for Authentication, this could be a Service Account.  If there is need for data security the other two options should be explored.

The Connection needs to be saved before you can extend access to other users.

Once saved Click the connection and select Inspect.  On the Access page you can add Users or Roles.  The Password will need to be re-entered before the changes can be saved.

To create a new Data Set select Data Set from Create menu.

Select the EPBCS connection

This will open a list of the Applications/Databases within the EPM Cloud you connected to.  In this case i am connecting to EPBCS with the pre-built modules.  Select the Cube you want to work with and Click Add

 

 

Note: For EPM based data sets the data prep stage does not allow for preview or data actions. 

Click Create Project.

Now the fun begins...

Project screen is like all others. The Data Section looks like most other datasets.  As with Essbase the Measure to report on is a single data point and the dimensions make up the intersection to report on.

The Top Data Element Plan1 is where the amount field is found and some of the extra metadata from EPBCS that I will explore in detail in future posts.

When the App metadata is brought into the dataset you will see all the language aliases, you will also have "column" each generation.  Currently there is no way to pick the alias(es) you want in the dataset and there does not appear to be a way to remove them from EPM App.  If you import the EPBCS App into the RPD you could remove all the aliases that you do not need.

 

 

 

The columns to use in a project would typically be the <DIM> Name or <DIM> Name Gen #.

In cases where the hierarchy is ragged (Level 0 is at a different Generation those generation values will be empty.

Some Columns that will make analysis easier are:

<DIM> Name - Has all the members from all levels/generations

<DIM> Parent Key - Helpful for doing working with visualizations that show in Parent/Child

<DIM> Gen Number - Helpful in filtering

<DIM> Leaf Indicator - Helpful in filtering when you may want to show Level 0 members on a ragged hierarchy

Well that is a good start.  One thing to remember is that if you want others to view your projects you need to grant access to the dataset as well.

In my next post I will explore creating a project and the special considerations that need to be taken into account.