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.

EPBCS and OAC Metadata Issue

It has been a while since my last post...a kind of writers block.

This week I have been working on demo content for upcoming webcast on working with EPM Cloud content in OAC DV and BI Dashboards.  And I ran into issues where members in my Account Dimension where not showing up in Analysis or Filters so I thought I would share what I found.

First I had imported my EPBCS app with the pre-built modules into my Physical Layer.

I moved the whole app to the BMM Layer

When I looked at the Business Diagram I see the 9 Cubes

kind of ugly mess...but there are 9 logical stars

To create the Presentation Layer I righted clicked and selected Create Subject Area for Logical Stars and Snowflakes

Voila, I have 9 subject areas.

The work I want to do is with Employ demographics so I will be working with the EPBCS - OEP_REP subject area.  I started working in DV and setting a filter on Account.  I quickly noticed I was not finding the Account I was interested in so I switched to Classic Home and created an analysis with the Account Hierarchy Column.

I am not seeing the Account structure that is what is in REP cube.

So back to the RPD.

This time I brought each cube into the BMM layer individually.

And when I went back to Analysis of the OEP_REP and checked the Account Hierarchy

And it matches the cube.

So something is happening in the RPD when working with multiple cubes in a BMM Layer object.

My recommendation...Import the App and create individual BMM Layer objects per cube.

OAC systemsettings

As of the writing of this post many on Oracle Analytics Cloud will have been patch to the latest version.  My current Cloud Analytics Cloud BI Instance is:

Oracle Business Intelligence Product Version 12.2.5.2.0 (Build BIPS-20190315164310 64-bit)

With this update there are a host of new properties available in the Service Administration.


 If you have worked with Narrative View you know that you need to set a property to Allow HTML Content or you will get the following error when you attempt to save the view:

 
One thing I have found with each patch/upgrade is that settings within bipodadmin / Service Administration do not hold or carry forward. 

I had previously set the property to true:

Yet, now after the update I cannot add new Narrative Views. 

So back to Service Administration.

You can get there via the DV / Console or via URLs

https://YourCloud.analytics.ocp.oraclecloud.com/bipodadmin/
 
https://YourCloud.analytics.ocp.oraclecloud.com/analytics/systemsettings

Scrolling down I see the property set to true.

Double Click on true and change to false

 

Follow Restart instructions

 

 

Then repeat process changing false back to true

I will leave reviewing the additional properties available in the systemsettings for another post.

 

OAC - BI: Custom Numeric Formatting

At a Client we had a use case to show values in pivots and tables scaled to Thousands and Millions.

Instead of dividing by 1,000 or 1,000,000 my client looked into format masking.

Here is what he came up with and I thought I would share

Oracle Managed OAC BI and MDX

Earlier this year I posted  OAC - BI with MDX.  This was for Customer Managed OAC.  

So what are your options if you have Oracle Managed (a.k.a. Autonomous)?  We know that you do not have access to the server.

if you go to the following URL for your Oracle Managed OAC 

https://oac_instance.analytics.ocp.oraclecloud.com/bipodadmin/ 

Double click on the 0 after the entry obis.EVALUATE_SUPPORT_LEVEL and change to a 2.

Just like you would have done in the NQSConfig.INI

Since this is Oracle Managed you will need to submit an SR for Oracle to restart your server for this change to take effect.

How Can I Help?

I recently ran for the ODTUG Board of Directors.  There were 4 open positions and 9 candidates on the ballot.

This morning I received the call that I did not make the cut.  While this was disappointing to hear, I told Mike Riley, the ODTUG Secretary, that I will continue to look for other opportunities to volunteer and add value to the organization.

This evening while I have been catching up on a TV series that I have found interest in I heard the main character ask “How can I help?”

For those of you who watch network TV you will recognize this quote from the main character Max in “New Amsterdam”.

So, for my readers who belong to a professional organization or for that matter any organization; ask yourself…”How Can I Help?”

Most professional organizations run on volunteers.  If they are anything like the ones I belong to, IOUG and ODTUG, they are always asking for people to help.

So ask yourself…”How Can I Help?”

Do you have a couple hours a month to volunteer to be on a committee?   

Do you have an experience on recent project or implementation that you could share?  Write an article for the newsletter or conduct an educational webcast.  

 Better yet submit an abstract to speak at a conference...believe me once you have done it, public speaking is not that scary.

So, for all you professionals out there who want to grow in your profession ask yourself…”How Can I Help?”

Please consider volunteering within the professional organization you are a member of.

OAC - BI with MDX

At KScope15 I attended a session presented by Mike Nader titled "Finding a Needle in a Haystack - Aggregate to Transaction-Level Financial Reporting Using Essbase and OBIEE"

In this session he talked about using a Driver query as a source for a filter.  The driver query made use of a MDX function and the EVALUATE clause within the Criteria section of an Analysis.  The slide from his session that this post is based on is shown here.

 
This presentation and concept has lingered in the dark recesses of my brain and recently when trying to create a filter for a range of members in my Time Dimension it bubbled back up.

So a bit of set up is needed.  First I have an OAC BI sourcing data from OAC Essbase.  My cube has a unified Time Dimension...That means my Year and Period are one dimension instead of 2 separate so it looks like this:

While for many Essbase developers this is not a typical design; it is how OBIEE likes to look at Time and makes for Time Series reporting a lot easier.


If this was a relational database I could easily get the a list of past 6 quarters via SQL.  But how can i get a range of months from an Essbase Cube?

EVALUATE('{[FY17_Q3].lag(5):[FY17_Q3]}/*%1*/',"Time"."Gen3,Time")

Formula syntax is invalid.
[nQSError: 10058] A general error has occurred. (HY000)
[nQSError: 43113] Message returned from OBIS. EVALUATE_SUPPORT_LEVEL inside NQSConfig.INI is not set to support EVALUATE. (HY000)
SQL Issued: SELECT EVALUATE('{[FY17_Q3].lag(5):[FY17_Q3]}/*%1*/',"Time"."Gen3,Time") FROM "ESSB_MapTest"
OK (Ignore Error)

 

Make a back up of the NQSConfig.INI and download a copy

Edit the entry EVALUATE_SUPPORT_LEVEL. 

Set the value equal to 2 so that all users can use the results of the function call.  Upload the revised NQSConfig.INI and restart the BI Services via the scripts provided in your OAC instance.

So now when I re-run the query I get my current Quarter and past 5.

This will be useful when combined with a Substitution Variable for Current Quarter.