Thursday, March 14, 2019

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

Tuesday, December 11, 2018

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 

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.

Thursday, October 25, 2018

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.

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.