Oracle Data Visualization Desktop Connecting to Essbase

With the December update to Oracle Data Visualization Desktop came the beta connection to Essbase.
 
This is a nice first step to OLAP connections but definitely has a way to go.
 
To create the connection to Essbase create a new Connection

 

 
 
Select Essbase

 
Provide Connection Information.  I just put the name of the server running Essbase. (IP address works too.)

 
Select your Cube

Create new Project

And view the Project Details

 

For Essbase users there are a few things to note:

• Measures Dimension is flattened
• Gen1 of the hierarchy is dropped
• UDAs come in as separate columns on Dimension

 
Inspecting the Data Source provides very little information and there are no options to pick a dimension to use as measure.

 
This new connection is a great first step but harkens back to the days of OBIEE 10 connecting to Essbase.

I am looking forward to kicking the tires on this connection in some DVD Projects.
 
 

FDMEE Custom Reports...A.K.A. BI Publisher

For the past several months I have been working with FDMEE.  In the course of the project it has become necessary to document our functional design of the FDMEE setup and integrations.

I am a firm believer in automating that which can be automated.  Fortunately for me the design of an FDMEE implementation is stored in the supporting tables within the FDMEE Schema that is created with during the FDMEE install and is updated as integrations are built. 

When I reviewed the reports that come Out of the Box in FDMEE I found that they met some of my needs but were also lacking in layout and data elements.  So I spent some time looking into how the reports in FDMEE are generated.

The report engine in FDMEE is an OEM version of Oracle BI Publisher.  The nice thing about BI Publisher is that it separates the query design from the report layout design from the translation files.  Then at runtime these are brought together to produce a report output. 

The enterprise version of BI Publish is very robust allowing for a large number of sources with several different layout design tools and layout template types producing many different report outputs to a variety of destinations.

BI Publisher within FDMEE is limited to SQL Query against the FDMEE Schema using MSWord RTF Templates to produce output in either PDF, HTML, Excel or XLSX format to the local file system FDMEE\outbox\reports.

The next series of posts I will show how to create a custom report within FDMEE using the following steps:

• Creating a Query Definition
• Designing a Report Layout
• Creating a Report Definition

BICS - Deliveries

Recently I signed back into BICS to work on an internal project and decided to poke around to see what is new.  I am bad at reading the "What's New" and tend to just jump in and see what changed with the latest maintenance update for August 2016.
 
One thing I noticed is the new feature within the Console interface.
 

This feature harkens back to OBIEE 10.x feature labeled "Delivers" which was renamed to "Agents" in 11.x.
 
Funny how old names are recycled...
 
In this case the two are not equal.  Deliveries, like most new features in BICS versus on-prem OBIEE, is a scaled down version of the on-prem counterpart.  Like its name "Deliveries" suggests, it is a way to send the output of a report/analysis to a recipient where as Agents has many more features and options available.

Deliveries is a great first step into scheduled email delivery of content to users.

Within the BICS Catalog view of Analyses there is a new "right click" menu item - Email.

On-prem OBIEE we have "Schedule"

This is where I have the biggest frustration...Why difference in names over similar features...

On-Prem there are Analyses and Dashboards...in BICS we have Analyses and Dashboards.

So why "Schedule" for on-prem and "Email" for BICS?

Yes, I understand that OBIEE on-prem there is not full parity of features with BICS for Analyses and Dashboards yet the names are the same.  So then why the difference for sending of OBIEE/BICS content to someone via email?

OK, I will get off my soapbox...


The Deliveries option within BICS is a very simplified version of OBIEE Agents/Scheduler.

First the interface; much more simplified and limited options in BICS.

versus the Agent options within on-prem OBIEE which allows for conditional logic and delivery options.

 

This new feature is very much welcome.  It allows BICS content to be shared with others who may not have BICS license.  Though, a feature that I wish was added is the ability to add content into BICS users Home Page or Header like within OBIEE Agent Destinations tab.

From an Administrative perspective I do like the interface to see the status of "Deliveries".

While BICS Deliveries has a long way to go to match OBIEE Agents; it is a welcome first step.

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!

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. 

 

 

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

 

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.