Wednesday, June 1, 2016

Countdown to KScope16

In just over 3 weeks from the date of this post KScope16 will take place in Chicago.  Once again I was privileged to serve as the Content Lead the Business Intelligence and Data Warehousing track.  I was very fortunate to once again work with a great group of people to review and select the sessions for this years BI & DW track. 

Michelle Kolbe (@MeKolbe)
Michael Rainey (@mRainey)
Nicholas Hurt (@Nicholas_Hurt)
Christian Berg (@Nephentur)
Christian Screen (@ArtOfBI)
Kevin McGinley (@kevin_mcginley)


This team took time out of their already busy lives to review over 90 sessions for 28 slots.  They gave up weekend time to get on a conference call to slug it out for their favorite sessions and finally coming together to pick what we felt would be sessions that helped showcase the BI & DW Track.

While you can see the Session Line up here I thought I would share some interesting facts about the BI & DW Sessions for KScope16.  For starters it was decided to spin off a separate track for Big Data and Advanced Analytics.  This allowed for the two tracks to focus on content core to those themes.

This year between BI/DW and BD/AA tracks KScope will have 7 hours of Hands On Labs with 5 of the labs being a 1 hour session.

15 Different Companies are represented as Primary Speakers.

13 of the 24 Primary Presenters have some form of Oracle Ace Designation covering 18 sessions.

Aside from the track sessions we had pick a topic and presenters for the Thursday Deep Dive.

The final bit of business I had to complete for KScope16 was to work with Vasu Murthy (@vmurthy), our Oracle Liaison, to set up the Sunday Symposium.  This was my second year working with Vasu, he was great in helping line up Oracle Product Management Team to put together a full day of sessions.  The line up can be found here.

I am really excited to see all my ODTUG Friends in Chicago for KScope16.  KScope has been my professional development home since 2010 in Washington D.C. and I am proud to have been a part of BI Track content selection for the past 5 years.

I hope to see you in Chicago!

Thursday, May 12, 2016

Fun with Jython and FDMEE – Getting ResultSet Column Names


When working with the fdmAPI call the documentation indicates that return is a ResultSet.

But what is in the ResultSet?
Sometimes the documentation indicates the table that ResultSet is based on.

Well you can open your favorite SQL Tool and query the FDMEE repository tables if you know what to look for.
In the case of the getLocationDetails call; it queries from the TPOVPARTITION table.
 
But can you get that information without a SQL Tool?  How can you do it within Jython? 
After a bit of searching the Python/Jython coding sites I found a method on the ResultSet that provided the information I needed.  The method ResultSet.getMetaData() provides the structure to get information needed.  The .getMetaData() coupled with metadataCollection.getColumnCount() and metadataCollection.getColumnName(##).

So, after a bit of trial and error, I came up with the following.

This produces a list.
 
So having these column names and if you look back on my first post on Fun with FDMEE and Jython where I used the method ResultSet.getString("ColumnName"), I can get the value of any field of the ResultSet without using SQL Tool and I can get creative with my Custom Scripts.

Until Next time...Happy Coding!


Update 6/3/2016:
------------------------
I just got to reading the 11.1.2.4.200 FDMEE Admin Guide published April 2016 and noticed the nice little block of code at the end of the description for getLocationDetails(BigDecimal pPartitionKey) within the Using the JAVA API section. 

 

 

Saturday, April 30, 2016

Oracle Data Visualization Desktop (DVD)


Oracle Data Visualization Desktop (DVD) has been released and now all the BI Bloggers including myself who did not get a beta copy are scrambling to review and post their thoughts.
The functionality of Data Visualization is not new the Business Analytic tools offered by Oracle.  OBIEE 12c and BI Cloud Services (BICS) have Visual Analyzer Projects and then there is Data Visualization Cloud Service (DVCS).  But Data Visualization Desktop (DVD) is Oracle's truly first direct to any data source desktop tool since the days of Interactive Reporting.
Now full disclosure, I am a true fan of Interactive Reporting.  I cut my business intelligence teeth on Brio 6.0 back almost 18 years ago.  I really like Interactive Reporting.  It provided direct access to almost any database via ODBC and could import local data files.  You had graphical data modeling, query and analysis and dashboards along with paginated reporting.  All in All it was and I argue still is a great tool. 

But in the age of acquisitions Oracle made a decision to take its acquisition of Siebel Analytics and make it the new face of Oracle Business Intelligence Enterprise Edition.  Unfortunately, Interactive Reporting which came along a few months later with the acquisition of Hyperion became the "Redheaded Stepchild" and was relegated to "Not a Strategic Product" along with SQR and Web Analysis.
Alright…enough of nostalgia lane!

The Desktop install comes with two sample datasets and sample Visual Analyzer Project to showcase some of the features and capabilities.

The part the interested me the most was direct to data access.

The Data Sources panel provides access to existing Data Sources, capabilities to create new data sources and manage connections.

Clicking Create New Data Source brings up the following dialog.

From a File is self explanatory and nice way to include non-curated data in an analysis. 

From Oracle Applications is interesting, if you read the print below the option you see

 "Select an Analysis containing the data you want to visualize."
 
 Looking at this Option I see that I can connect to an existing OBIEE instance.
 
After setting connection information and Signing in I see the OBIEE catalog I have access to
 
 
Navigating to an analysis
 
Reviewing and retrieving the data from the analysis
 
 My analysis is now a data source that can be used
 
My Data Source in a Visual Analyzer Project
 
The option to create data source from Database provides access to a large number of databases
 
Connecting to the database I can see the tables
 

Selected table is displayed and the columns can be inspected.
 
The data source is imported into DVD and is available for building a VA Project.
 
To add more data sources you can add in the Data Sources interface or you can add within the Visual Analyzer Project.
 

Adding requires you to go through the steps of creating a data source, picking a data source type and if the database already has connection then selecting the table. 
 
When the data source is added from within VA Project a wizard drives how that table will be used - Extend a Fact or Extend a Dimension.  If Extend a Dimension then the matching column is established.

 
Looking at the Source Diagram the relationship link is displayed.
 
 
Dimensions can be extended as well.
 
And when additional supporting dimensions are added and a complete model is built for the project the relationships of the data sources is more evident.
 
Now the diagram may be a bit confusing with some thinking that there are joins between all the tables but if you look closely you see the darker lines with numeric value showing actual joins.
 
 
So, my first impressions are that it is an easy tool to navigate and get into data analysis rather quickly.  I like that I can create multiple data sources from many different sources and add them into a VA Project.
 
I would like to see the ability to add more than one data source at a time. 
 
There will be many more blog posts by others in the weeks to follow and I look forward to their impressions.
 
 
For some great videos on DV capabilities check out

ORACLE BI TECHDEMO

https://www.youtube.com/user/OracleBITechDemos
 

Tuesday, March 15, 2016

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.

Saturday, March 12, 2016

Oracle BI Cloud Service March 2016 Update

If you are BICS subscriber you might have gotten an email early Saturday, March 12th. 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.

Wednesday, February 10, 2016

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.

Saturday, January 30, 2016

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.