Tuesday, March 27, 2018

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?


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


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.


Friday, November 17, 2017

OAC - PBCS Connectivity

I am not one to re-post Oracle Documentation or Oracle Readme's and pretend to make it my own.  My intent for any of my blog posts is to extend the information provided or provide clarity.

The OAC 17.3.5 (August 2017) release extended connectivity to PBCS / EPBCS.

The configuration / setup instructions can be found here.  OAC PaaS Docs

If you need to see pictures of this process you can do internet search for other blog posts that another person produced.

I followed each of these and when I attempted to connect and import metadata I received the following:

the NQSAdminTool.log file was not too helpful

However, I got to thinking; for the connectivity to Essbase Cloud I needed to open up ports on my Cloud Compute.  I checked with my contact within my contact within Product Development and this was confirmed.

So after logging into myCloudService and switching to the Compute Console / Network

I created a new Security Application with connectivity on port 9506


Next, I created a Security Rule with the Destination ANALYTICS/instance/ora_bi_infraadmin

Then back to my OAC Client Admin tool.  I provided all the necessary connection information and Voila!  I am able to import my EPBCS cubes into my RPD.

 Until next time!

Monday, November 13, 2017

UPDATE: OAC - Import Essbase Cloud to OAC-BI Admin Tool

In my post OAC - Import Essbase Cloud to OAC-BI Admin Tool

I shared that the Essbase Database Type does not work for importing Essbase metadata and that you needed to use the Essbase 9 type the switch back to Essbase in order to pass the consistency check.

Well I opened an SR and was told that this fixed in the OAC 17.4.1 patch and related BI Admin Tool.  

So I have updated my Database Type in my Essbase Connection

And when I attempted to import metadata I still get the error.

Since i am in the test mode I figured I would see if the Essbase 11 database type worked.

 And... low and behold it does.

Now I no longer need to switch the Database Type to 9 then to each time I need to import metadata.  And I can leave the Database Type set to Essbase 11 and it will pass consistency check.

Until next time.

Friday, November 3, 2017

OAC - Service Console

I recently ran into an issue where in our OAC instance we move our DBaaS to a different cloud instance and thereby it was assigned a new IP address.  Unfortunately the RPD Connection Pool was not updated.  Our RPD has a number of Session Variables that when we attempted to sign on to our OAC instance all we saw was the following:

It took a while to figure out what was going on and it was only after I looked at the diagnostic logs for the BI Server that I found that my connection was blacklisted.


Now came the hard part.  How do I sign into OAC and access the Console interface where I can Replace the Data Model with a new RPD?

Fortunately there is an administration interface that can be accessed that does not link to BIServer.  This interface is the Business Intelligence Cloud Service - Service Console

This Service Console allowed me access to the Snapshots

Access to this Service Console is via the following URL:


With access to this screen I was able to replace the RPD with a corrected connection string and was able to successfully log in to my OAC instance.

Hopefully this URL will save many hours of frustration.

Friday, September 29, 2017

OAC Datasets and Connections in Synopsis

it is late in the evening of Sept. 29, 2017 or early in the morning of Sept. 30, 2017...Regardless...I cannot sleep!

I noticed that my Galaxy Note 8 updated my Oracle Synopsis App.  I see that it is a 2.0 release...I am not one to read manuals and updates right off so I started looking around on the app to see what was new.  The first thing that caught my eye was in the Settings.  Something new stood out...Smart Lock on OAC

Well that really got me wondering...Can my Synopsis connect to my OAC instance and if so what can I access.  So I went to the main screen and created a new project and there was an option to connect to OAC.

So I figured, in for a penny in for a pound.  I tapped (because we all know you do not click on a tablet) and I got a sign on screen.

I provided my credentials and I was presented with a list of data sources. I had just created a data file named Kool Kart Dataset from a Data Flow I was working on and I noticed it was in my list.

Checking my Data Sources in OAC i see most of the same list...

The only difference is that my Subject Areas (the purple icons at the top of the list) are not listed in my Synopsis menu...This seems right since Synopsis seems top operate from a data file based on columns of Dimensions and Facts.  So what happens if I try to load up one of the Essbase Connections...I tapped on Sample.Basic and...

Well that was educational.

So I tapped on my Kool Kart Dataset.  And it loaded up into Synopsis with some quick analyses based on the data in the file.

While it did a pretty good job of interpreting the data in the data file it treated the Year column as a measure as indicated by it being displayed on the X-axis with Revenue and Number of Orders.

Well that is something to investigate further.  

The ability to connect to OAC, display content in Synopsis is a huge leap forward in mobile analytics.  Oh and by the way, did you know Synopsis is FREE...Look for it on Apple iTunes and Google Play.

Friday, August 18, 2017

OAC - Import Essbase Cloud to OAC-BI Admin Tool

I finally have time to play with Oracle Analytics Cloud Enterprise Edition (OAC EE).  

I have been interested in setting up my OAC BI instance to work off an Essbase Cloud cube.

So; First Things First...Download the latest version of the OBIEE Admin Tool that supports BICloud


At the time of this post it was

Keep an eye out for updates...

I have an Oracle Analytics Cloud Enterprise Edition instance that allows me to work with an RPD for Data Modeling instead of the built in data modeler that we know from BICS.  The BICS Data Modeler does not let me connect to Essbase Cloud Service.  I also have an Oracle Analytics Cloud Essbase Cloud Service.  I need to make my Essbase Cloud Cubes available as subject areas in my OAC-BI instance.

The cube I will start with is the Sample.Basic from my Essbsae Cloud server.

So starting up the Admin Tool I need to create a new RPD.  I selected 'No' for the Import Metadata because I like to create my Physical Layer one piece at a time. 

In the Physical Layer I created a New Database...

Since I am connecting to an Essbase Cloud instance I selected the latest version of Essbase that is available in the list of Database Types,

Next I created the connection pool.  I put the Public IP address for my Essbase Cloud Instance

I import the Essbase metadata by right clicking on connection pool

I authenticated to the Essbase Cloud instance and navigated to my Cube to import and started the Import.  The process started and up popped a message.

So it was time to see what following the Import Metadata option from File menu after deleting everything in the Physical Layer.

I selected the available Connection Type - Essbase 9+ 

set my connection to the public IP

Once again navigated to my cube and imported it

This time it worked

And my cube shows up fine in the Physical Layer.

After renaming the Database, Connection Pool adjusting Dimension properties to suit my needs I followed same process as an on-prem cube in the RPD.

It is time to save all my work and like any good RPD Developer I click yes on the Global Consistency Checker.

And what do we have here...?  The warning indicates that my Database Type is not correct.

I checked the Physical Layer properties of the database, I see that i have a couple more options than I did when I selected Import Metadata.  I selected the most recent release version available in the list.

This time when I saved and allowed for Global Consistency Check I did not get any warnings or errors!

So it is time to submit an SR and  deploy to the cloud!  But that is topic for another post....