Tuesday, April 18, 2017

Data Visualization - Smart Insights

Smart Insights have been available in Oracle Data Visualization Desktop for a few releases and now available in Oracle Analytics Cloud (OAC)

So what are Smart Insights?
• They provide an at-a-glance assessment of your data
• Allows analysts to quickly understand the information the data contains
• You can easily see how measures are distributed in various attributes/dimensions
• Provides starting point for further data analysis

In looking at tabular data it is difficult to see patterns and distribution of measures across dimensions

When we look at the same data via Smart Insights we can see how the data is distributed across attributes/dimensions.
So how does one access Smart Insights?

First add a data source to a project.  Then switch to Prepare Mode. Finally switch the view from Data to Visual.

The Visual Mode creates a series of simple data visualizations.  The initial views are the number of rows of data distributed across all the attributes/dimensions in the data set.

The view can easily be changed from row count to other measures/facts in the data set by changing the Summarize by.
Additional properties that help with analyzing the data is flagging whether show null rows in an attribute/dimension.  The include others option is useful when the data has many different values in an attribute since the number of values on an axis also referred to as binning is limited.

The Binning of values on the X and Y Axis follow these
• Number of Bars Depends on Data Distribution
• Normally 10 bars are shown and all other data is displayed in a bar called Other
• If 20% or more of the data falls into the Other the system will break that data into the number of bars needed

The style of the visualizations align to the type of data in the attribute/dimension.
• Non-numeric or Text - Horizontal bar chart
• Date and Time - Line chart
• Numeric - Vertical bar chart

From the samples shown throughout this post one can see how beneficial they are to easily understanding the data, finding initial patterns and providing a starting point for further data analysis.

Monday, December 12, 2016

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.

Wednesday, November 30, 2016

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

Tuesday, September 6, 2016

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.

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 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