Thursday, 28 May 2015

OBIEE Data Viewing Default for Fixed Headers Scrolling or Content Paging

OBIEE Freeze Columns isn't really a new functionality of Oracle BI, but we had to perform some investigations after a client installed OBIEE patch 11.1.1.7.150120. Somehow the default setting for data viewing of Table and Pivot Table views changed ot Content paging and was not longer Fixed headers with scrolling content:

OBIEE Table Properties

We didn't care about it in the past, because default value was always scrolling with fixed headers. We couldn't find the following instanceconfig.xml setting in the OBIEE documentation. It's a very helpful option that enables scrolling as default when value is set to true - or defaults to content paging if set to false:

<Views>
...
  <GridViews>
    <DefaultScrollingEnabled>true</DefaultScrollingEnabled>
  </GridViews>

</Views>

Thursday, 14 May 2015

OBIEE 11.1.1.9 released

Did you already wait for OBIEE 11.1.1.9 to be released? So, here we go. The new Oracle BI version OBIEE 11.1.1.9.0 is now available for download at Oracle OTN.

If you need upgrade support then just ask for our OBIEE services. New features as provided in several Oracle BI documentation books and some undocumented functionalities are:
Oracle BI Users
  • Oracle Scorecard and Strategy Management: specify the number of decimal places to include in scores
  • Dashboards: create custom export layouts for exporting dashboard content to Excel
  • Manage accounts: replace account entries in the Access Control List (ACL) and to manage unresolved accounts
  • Selection steps: use variables to override values in selection steps
  • Export functionality: several new and changed components, export options Excel 2003 and Powerpoint 2003 no longer included in menu
  • Analyses: search within the subject areas, global variables and save columns to the Oracle BI Presentation Catalog for reuse in other analyses
  • New Treemap chart for visualisation
  • New Skyros style (similar to Oracle BI Cloud Service)
  • Horizontal layout for buttons and check boxes in Dashboard Prompts
  • Upload of custom images
  • HTML5 images instead of Flash
Oracle BI Metadata Repository Builders
  • Whole rpd checkout option added to multiuser menu
  • Several aggregate persistence improvements
  • Initialization block written in JSON syntax
  • Translation keys have been added to all presentation objects
  • Cloudera Impala supported
  • Access to Hyperion Planning data sources
  • Support for Oracle 12c Database In-Memory, Oracle 12c Database on Exadata and Oracle 12c Database In-Memory on Exadata
Oracle BI System Administrators
  • New properties for full-text search
  • Some new or enhanced configuration elements for export, graphs, table, pivot table and trellis views
  • New NQSconfig.INI settings
  • Debugging Agents using Fusion Middleware Control
  • Session tracking within Usage Tracking component
Oracle BI Security
  • New privileges: add EVALUATE_PREDICATE function, save column
Oracle BI Installation
  • When you perform a new 11.1.1.9 BI installation, Oracle BI Mobile App Designer is automatically installed with the Oracle BI EE components.
Oracle BI Integrators
  • Oracle Business Intelligence includes a SchedulerService Service.
Oracle BI Publisher Administrators
  • Oracle Endeca no longer supported as a data source
  • Support for Oracle WebCenter content server as a delivery destination
  • Configure memory guard on the properties page
And last but not least some excerpts from the Release Notes (please have a look to the documentation if there are any solutions provided):
  • From Oracle BI EE Release 11.1.1.9.0 onwards, Microsoft Windows 32-bit is no longer supported.
  • After upgrading to Oracle BI EE 11.1.1.9.0, OBIEE Mobile App Designer is not updated.
  • Authentication fails against third party LDAP when virtualize is set to true (applicable for Microsoft Active Directory as the identity store). Customers should check the availability of a patch for this issue on their installation platform before continuing to upgrade to 11.1.1.9.0 (Update: patch is available with additional information at Oracle Support).
  • SSL configuration changes required due to new JDK version (when using Oracle BI 11.1.1.9.0 with Weblogic 10.3.6 and JDK 7, download and apply Weblogic patch 13964737).
  • Axis label truncation in trellis views.
  • Export to Excel 2007+ and export to PDF do not support custom CSS styles.
  • Truncated Microsoft Office Excel 2007+ download for table, pivot table and trellis views.
  • Export options do not behave as documented.
  • Use of Custom Images in Analyses and Dashboards is a new feature, but is not documented yet.
  • Excel Analyzer and Online Analyzer deprecated in release 11.1.1.7 and removed from 11.1.1.9.

Oracle BI Cloud Service (BICS)

Oracle BI Cloud ServiceOracle BI Cloud Service aka BICS provides Platform as a Service (PaaS) functionality to clients. As you maybe know new features are added regularly by Oracle without any need for administration, patching or upgrade.

As of April 2015 some the following new features were added:
  • Integrate with Oracle Database Cloud Service (Database as a Service)
  • Upload data models from a file
  • Add your own data to analyses
  • Explore data using Visual Analyzer
Please follow Oracle Support document Oracle Business Intelligence Cloud Service (BICS) - New Features and Enhancements for further updates and history.

Oracle BI Mobile Apps - iOS and Android

Oracle released new version 11.1.1.7.0 (11.6.39) of Oracle BI Mobile HD app for Apple products (see App Store). And that's new:
  • Support for Visual Analyzer content
  • Bug and stability fixes
Oracle BI Mobile HD app requires iOS 7.1 or later.

Android App is currently in version 11.1.1.7.0.375 and was updated at last in March 2015 (see Google Play). New functionalities:
  • Updated UI for material design
  • Optimized for Android 5.x
  • Support for sharing connections via URL
  • Oracle BI Mobile Security Tool Kit available on Oracle Technology Network
  • Simplified server configuration
  • Simplified connection switching
  • Translations
  • Bug fixes
Please find former Oracle BI Mobile release information also in this blog.

Saturday, 9 May 2015

OBIEE Non-Conforming Dimensions

BI Forum 2015Yesterday BI Forum 2015 in Brighton ended with the Gerd's session Driving OBIEE Join Semantics on Multi Star Queries as User (slides available at SlideShare). It was a great pleasure to speak in Brighton and to get quite good information in other OBIEE, data discovery and data visualisation sessions. This article adds some details to presentation just mentioned.

OBIEE multi star queries are a powerful but complex Oracle BI functionality. They allow simple retrieval of new business insights and deliver added value due to cross-functional analyses. Presentation introduces a major change within Oracle BI query logic since release of OBIEE 11.1.1.7.1 (see also Oracle Support document OBIEE: Setting Fact to Total Level Of Hierarchy No Longer Forces Outer Join). Example uses an OBIEE logical model with two non-conforming dimensions Invoice Item and Return:

OBIEE Star Schema Non Conforming Dimensions

The figure Invoice Qty has Return total level set and Return Qty total level of Invoice Item. If conformed content is added to the query then OBIEE performs inner, left or full outer joins on conformed columns depending on filter types applied:
  • Non-conforming inner join when global filters are applied to both non-conforming dimensions.
  • Non-conforming left or right outer join when non-conforming dimensions are filtered mutually exclusive by a global and a local filter.
  • Non-conforming full outer join when only local filters are used for non-conforming dimensions.
Global filters are commonly used filters within a report. They apply to the full query. Local filters just filter measure field content and look like:

FILTER(<measure> USING <expression>)

Presentation slides provide query details and OBIEE generated SQL for all combinations mentioned above. This behaviour isn't feasible for all query cases. For deactivation insert the following statement into the Prefix field of Advanced tab of Oracle BI Answers (same procedure as shown in OBIEE Variable LOGLEVEL and Report Based Logging):

SET VARIABLE FOJ_GRAND_TOTAL_LBM=1;

After applying OBIEE server is forced to only perform full outer joins as prior to OBIEE 11.1.1.7.1.

Saturday, 2 May 2015

OBIEE Graph Conditional Formatting on Other Column

Image below illustrates an example for conditional formatting of OBIEE table views. KPI Value field conditional format is calculated based on Threshold column value. OBIEE system provides Target and Threshold values for improvement of application usability. KPI visualisation is often based on equivalent graph and chart layout but on different targets and thresholds. OBIEE internal threshold calculation providing fixed values like green and red simplifies view development and layout.

OBIEE Table Conditional Formatting

That works quite good for table views, but conditional formatting based on another column isn't possible for graph views like the vertical bar chart shown. Graph conditinal formatting only accepts conditions derived from the same column and comparison with a static value or a presentation variable:

OBIEE Graph Conditional Formatting

Even adaptation of the Analysis XML doesn't help. To solve such a request add two additional KPI Value fields for green and red. Apply calculation as follows to get the result as shown in the image afterwards:

KPI Value Green
CASE
  WHEN "Fact - KPI"."Threshold" = 'green'
  THEN "Fact - KPI"."KPI Value"
END

KPI Value Red
CASE
  WHEN "Fact - KPI"."Threshold" = 'red'
  THEN "Fact - KPI"."KPI Value"
END

OBIEE Graph Conditional Formatting Calculation

Change the vertical bar chart to be a stacked vertical bar chart and apply standard style formatting to show desired colours. Of course, remove legend and fields not used to get an acceptable visualisation.

OBIEE Graph Conditional Formatting Other Column

In combination with OBIEE Change Field Content and OBIEE Change Analysis Subject Area replication of layout gets very easy.