BICS External Data - Reloadable

This past weekend I wrote a post about the March 2016 BICS upgrade.

I just stumbled on this nice new feature in the latest upgrade to BICS.

The part about external data that I found frustrating was once it was loaded in as a data set I could not refresh the data.

Old BICS

 New BICS

 

 

This makes External Subject Areas (XSA) much more dynamic and easy to work with.

Oracle BI Cloud Service March 2016 Update

If you are BICS subscriber you might have gotten an email early Saturday, March 12^th. If not you will soon.
 
 

 

 
 
Yes, the long awaited upgrade to BICS...The last upgrade release was Nov. 2015, it seems so long ago...
 
That release gave us the Heat Matrix, Geospatial integration (Maps), enhancements to the Data Modeler and a fresh new look.
 
Well this new updated to the BI Cloud Service is packed with some much anticipated new features and functionality.
 
Some of these new features include:

• Multiple Database Connections
• Multiple Subject Areas
• Webcat Lift and Shift
• Remote Data Connector
• Save Column As
• BI Catalog Search'
• Safe Domains
• API to purge cache by table
• HTML/JavaScript Support

These new features have begun to soften the line between OBIEE 12c on premises and OBI Cloud in terms of functionality.
 

Multiple Database Connections:

Now no longer either DBSaaS or DBaaS.  Connections to other Oracle Cloud Databases are now supported.

 

 

Multiple Subject Areas:

Even without multiple database connections this is huge and starts to move BICS from a Departmental solution to an Enterprise solution.
 
 

Webcat Lift and Shift:

RPD Lift and Shift has been available since the last release now Webcat Lift and Shift is available.  The Web Catalog Subject Area Tasks added two new options.  Archive and Unarchive.  This not only support on-prem to cloud movement of web catalog contents but also cloud to cloud movement.
 
 

Remote Data Connector:

This enables querying data residing in on-premises relational sources without moving data to cloud.  And this topic warrants a separate blog post. 
  

Save Column As:

This feature has been available in OBIEE on premises.  And between Webcat Lift and Shift or Multiple Subject Areas could be my favorite enhancement in this release of BICS.
 

 

 

 
 

BI Catalog Search:

This is the same as on-premises OBIEE 12c.  The only difference right now is that VA and OBI Ask are not integrated into Search.  I expect that to be in the next update.

 

Safe Domains:

This allows administrators to Whitelist domains to allow BICS Content to be embedded in external websites as well as sites that are safe for images to be included

 

 

HTML/JavaScript Support:

Now true customization can take place.  This feature extends the use of HTML in Columns and Analysis.  D3 people will love this!
 

 

 

In conclusion

This is just quick overview of some of the new features added to BICS in this latest release. Again my top 3 - Multiple Subject Areas, Webcat Lift and Shift and Save Column As.
 
For more details on what's new check the documentation or check out the ORACLE BI TECHDEMO

BTW... for you VA people there are some new features there as well and I will be writing about them soon.

Fun with Jython and FDMEE

The past few months I have had the opportunity to assist in FDM to FDMEE migrations and during that time I have been able to explore Jython scripting.
 
I have loved programming since I was in high school and wrote my first program on an Apple 2.  Yeah, a very long time ago.  No matter where my career path has taken me I seem to find a way back into some form of programming.
 
As part of the migration efforts I needed to convert the FDM VBScript to Jython.  While some of the Event Scripts could have been kept in VBScript it was cleaner to convert to Jython for 2 reasons...VBScript is not strategic for Oracle with FDMEE and I like a puzzle.
 
One of the migrations I had was from FDM Classic with the Essbase Source Adapter to FDMEE 11.1.2.4.  This particular client had stored email server and email addresses in the integration settings to be used to send an email upon failure during any part of the data loading done by FDMEE.
 

 
The one thing I did not want to do was to embed this information in my each one of my Event Scripts - AftImport, AftValidate, AftExportToDat, AftLoad, AftCheck...
 

 

 
So where could I store this information and yet make it accessible to easily modify once.  Why not the Integration Options for a Location?
  

 
If I look at the TPOVPARTION table which stores location information is the 4 Integration Option values.

A simple SQL statement

So how do I get these easily without having to write SQL in my Jython?

Enter the FDMEE API and particularly the 

 

     getLocationDetails(BigDecimal pPartitionKey)

 

 

So what does this look like in Jython in my Eclipse editor?

 

And the Output...

 

So putting it into my FDMEE Event Scripts...

Now the neat thing about this approach is the if there are different people responsible for different locations they could get individual emails when there is an issue with their location by switching out the hardcoded fdmLocID to the fdmContext["LOCKEY"].

Well that was a fun diversion from BI and since I am having fun with Jython and FDMEE I may find some other neat tidbits to share in the future.

OBIEE & Essbase Value Hierarchy Bug & Workaround

To begin with this article is based on a bug I submitted.

Bug 22302005 - LOGICAL LEVEL KEYS GET MESSED UP WHEN DIMENSION IS SET TO VALUE BASED HIERARCHY

When OBIEE 11.1.9 was released it took a while for me to actually start ‘Net New’ with my RPD development.  I just upgraded and kept working.  Well the day came that I needed to bring a new cube into my OBIEE 11.1.9 environment.  When I attempted to query a hierarchy dimension that I had set to Value Based / Parent-Child I got the following error.

So I looked at the Log to see the Logical SQL.

I know I had used this cube on my 11.1.1.7 instance and I had no issues so I went and looked at the RPD in my 11.1.1.9 instance.

Physical layer there is the Measures Member Key…

So I went to my 11.1.1.7 instance and re-ran the same analysis

Then I looked at my Business Model

And the Physical Layer. Hmmm… no Measures Member Key

 

A Side by Side view often helps to see the difference.

 

Using my OBIEE 11.1.1.7 instance as my reference 

I edited the Detail Level properties of the Measures Hierarchy.

Then added the Gen3,Measures as a new Logical Key

 

Next I edited the properties of the Measures Dimension to set the Primary Key - DetailKey to the Gen3,Measures.

Finally I deleted the the offending column.

 

The last thing to do was to save and check in my changes then test the hierarchy in an OBIEE Analysis and now no errors.

As a point of reference this bug is in current release of OBIEE 12c as well.

So until Oracle fixes Bug 22302005 this is a simple work around.

OBIEE Repository Variable as Column Header

We had a client with a business requirement to produce a columnar dataset with the column headers of a table for the measure to display the value of an Essbase Substitution Variable which was being stored as a dynamic repository variable.  In a previous post I showed how I manage Essbase Substitution Variable in the repository.
 

I know I can use Presentation variables to change column headers, but my Essbase Substitution Variables are Repository Variables and that just doesn’t seem to work. 

  

Now the first thing one might ask is why not use a pivot with periods as columns.  Well like most clients there is a particular view they wanted and there were other columns needed that are not shown.

So after some noodling and playing around I came up with an option.  Use a Dashboard Prompt to set a Presentation Variable and use the Repository Variable to set the default value of the prompt.
 

 

 

I used the Presentation Variable to set the Custom Heading for the column. 

 

 

This little analysis was then embedded in a dashboard and working with Dashboard Properties add the dashboard prompt is hidden. 

 

 

 
 
The dashboard looks like this.  Notice that the 3^rd column has the proper month name showing.
 

 
Thanks and until next time.