The long-awaited OBIEE 188.8.131.52.2 BP1 (Bundle Patch 1) patchset came out late on Friday afternoon and contains a number of must-see improvements and new features for Oracle Business Intelligence 11g. This patchset follows the 184.108.40.206 release that came out back in March 2012, which provided a bunch of new features such as source-control integration in the BI Administration tool, and new analysis and dashboard features such as right-click selection steps for analysis views, the ability to selectively remove Apply and Reset buttons from dashboard prompts. It's probably fair to say though that the 220.127.116.11 release was primarily there to enable the initial release of Oracle Exalytics In-Memory Machine plus the latest version of the Oracle Fusion Applications, and this new 18.104.22.168.2 patchset provides the remainder of the 22.214.171.124.x UI features first introduced at 2011's Oracle Openworld such as high-density visualisations, support for a reworked Oracle BI Mobile IOS client, and the usual set of bug-fixes and documentation updates (though not the View Suggestion Engine that was also included in the preview, but seems to have slipped to 126.96.36.199 now).
Applying the patchset is a bit of a fiddly task but nowhere as involved as performing the (dreaded) in-place upgrade. You'll need to be on either OBIEE 188.8.131.52, 184.108.40.206.1 or 220.127.116.11.2 and the patchset is "cumulative", meaning that it includes all the fixes and new features included in earlier 18.104.22.168.x patches (though Oracle Support may have given you other, one-off patches that fix particular issues for your system - if this includes you, best check in with Oracle Support to see if 22.214.171.124.2 BP1 includes your patch, requires you to remove it, or whatever). The 126.96.36.199.2 BP1 patchset is actually made up of seven separate patches available for download on http://support.oracle.com that you should really apply together, in their entirety, to your system:
- 14223977 Patch 188.8.131.52.2 BP1 (1 of 7) Oracle Business Intelligence Installer*
- 14226980 Patch 184.108.40.206.2 BP1 (2 of 7) Oracle Real Time Decisions
- 13960955 Patch 220.127.116.11.2 BP1 (3 of 7) Oracle Business Intelligence Publisher
- 14226993 Patch 18.104.22.168.2 BP1 (4 of 7) Oracle Business Intelligence ADF Components
- 14228505 Patch 22.214.171.124.2 BP1 (5 of 7) Enterprise Performance Management Components Installed from BI Installer 126.96.36.199.x
- 13867143 Patch 188.8.131.52.2 BP1 (6 of 7) Oracle Business Intelligence
- 14142868 Patch 184.108.40.206.2 BP1 (7 of 7) Oracle Business Intelligence Platform Client Installers and MapViewer
There's also a mandatory JDeveloper patch that updates some of the common Fusion Middleware files, which you also need to apply:
- 13952743 Interim Patch for Bug: 13952743
Although it's tempting to skip some of the patches, such as the RTD one, or the JDeveloper one that's listed in the patchset readme file, advice from the OBIEE development team is to make sure you apply all of the patches, including the JDeveloper one, or you might introduce an issue that won't be immediately apparent but might affect you later on. I downloaded them all and applied them in just over 20 minutes, so it's not a big undertaking, though you'll need to apply the patches to every individual BI installation if you've scaled-out your BI Instance over multiple servers.
If you're on an earlier release of OBIEE 11g, for example 220.127.116.11, you'll need to upgrade to 18.104.22.168 before applying this patchset. If you're in this position, you've got two upgrade choices:
- Do an out-of-place upgrade, basically installing OBIEE 22.214.171.124 in a new middleware home, copying across the metadata files and running upgrade scripts as required, then deleting the old installation once you've verified that the new one works. This approach can be less technically complex than an in-place upgrade, but requires more disk space and doesn't bring across any UI customisations or security settings (policy store and WLS LDAP server), so may not be practical for a productionized OBIEE 11g system. Consider this an "intermediate-level" admin task that can get much more complex if you need to manually migrate UI and/or security settings.
- Do an in-place upgrade, updating in-place your OBIEE and (potentially) WebLogic binaries and configuration files, often a technically more complex task and in practice quite tricky to get 100% correct, but with the benefit of preserving your UI customisations, security arrangements, and minimising the need for additional disk space. Consider this an expert-level task that may require you to check through a few blogs or raise an SR if not everything makes it through the upgrade in one piece.
If you're still on OBIEE 10g, either because you're waiting until a near-terminal release of OBIEE 11g before making the move or because you're using a version of the BI Applications prior to the 126.96.36.199 release, you'll need to upgrade to OBIEE 11g first (you can go straight to 188.8.131.52 if you do this now), then apply the 184.108.40.206.2 BP1 patchset on top. Finally, if (like us) you're an Exalytics customer, you should wait until Oracle release the combined Exalytics patchset which will include this (or maybe a later or customised) OBIEE patchset plus potentially patches for WebLogic Server, TimesTen, Oracle Linux and Essbase in what Oracle refer to as a "version stripe".
Another question I've been asked recently is whether OBIEE 220.127.116.11.2 is "stable" and suitable for upgrading to from the 18.104.22.168 or 22.214.171.124 releases. To be honest, only Oracle Support and Oracle BI product development really know how stable this release is compared to earlier ones, but if you bear in mind Oracle's BI "tick-tock" release schedule where major patch releases (OBIEE 126.96.36.199, OBIEE 188.8.131.52 etc) are then followed later in the year by bugfix and stability releases (184.108.40.206.2 for example, the current most-stable .5.x release), this should hopefully be the bugfix/"tock" release and suitable for upgrading to. All the talk I've heard from Oracle about future releases is around 220.127.116.11.0, though to be honest as so many features got held back to 18.104.22.168.2 BP1 you might even considering this the "tick" release, so it's tricky to say. If it were my decision, I'd upgrade to this release anyway as it's likely to have the most bugfixes and Oracle Support will typically ask you to apply this patch if you hit an issue, so I'd plan for applying it soon but be prepared for some of the new features to have some "version 1" glitches.
So, finally, in all cases, factor in time for regression testing and try out any upgrades or patches on your test environment before moving onto your production server, and make sure you upgrade any client tool installations on developer workstations to the same release. Drop us a line if you need any help with OBIEE upgrades or applying patchsets such as this one.
So once you've got yourself to the 22.214.171.124.2 BP1 version of OBIEE, what new features can you expect to see? Let's go through them now, starting with new Presentation Server features.
Trellis Views, Microcharts and Sparklines
First off, the headline new feature in terms of visualisations is Trellis Charts; these are "grids of charts" that display matrices of measures with each cell in the matrix containing a micro chart, showing for example sales in each territory and product category graphed over time. Trellis Charts are available for all installations of OBIEE 126.96.36.199.2 BP1, not just Exalytics-enabled ones, but as we'll see in a moment they're best suited to Exalytics environments. Simple Trellis Charts, as shown in the screenshot below, allow you to select a particular graph type for each trellis chart cell, with in the example below each cell in the trellis showing sales broken-down by product category.
To create a trellis chart, you create your analysis as normal, selecting at least one measure and, typically, two or more attribute or hierarchy columns. Switch to the Results tab and select New View > Trellis > Simple, and then arrange your columns using the layout panel as shown above. In this example, each trellis cell contains a bar chart microchart showing sale amount by product category, "dimensioned" by region and month across the rows and columns.
Microcharts have their legends moved out of each individual trellis cell and into the trellis view in-general, and in the case of Simple Trellis views are either vertical or horizontal bar charts, pie, line or other regular chart types. So far so good, but what are Advanced Trellis views then?
Advanced Trellis views give you more flexibility in determining the chart types for multiple measures, and also introduce the concept of sparkline charts, a type of microchart used for displaying measure trends over (typically) a time dimension to accompany a measure value at a particular point in time. Let's take another example where we want to show the current values for revenue and amount sold for all product categories across the store hierarchy, but we want to show the trend for revenue alongside today's number. The criteria for this type of analysis might look like the screenshot below:
To create the advanced trellis view I switch to the Results tab and then select New View > Trellis > Advanced, which initially lays out my columns as shown in the screenshot below:
With this initial layout, the trellis cells are arranged by the store hierarchy and product categories as the rows, measures as the columns and each cell breaking down each measure value by month. What we'd like though is rows to be just the store hierarchy, columns to be product categories, and measures to be displayed as:
- Revenue : revenue over all time
- Revenue (trend) : revenue displayed as a sparkline chart showing its trend over time
- Sale Amount : sale amount over all time
Doing this gives us a view of what the numbers are now, plus what the trend that led to this number looks like. Do do this, we can use the View As and Bars settings within the VIsualization area to set the display type for each measure, with the display type selected from one of four settings:
- Numbers, with just the measure providing the number required
- Spark line, which takes both a measure and also a time-based hierarchy or attribute to show the measure over a given time period
- Spark bar or area, which requires a measure and also an attribute or hierarchy to break the measure down by
In this case we set the Revenue and Sale Amount visualisations as Numbers, and the Revenue (trend) as Spark Line, move the product category column into the Columns area and then get the visualisation shown below.
Now the analysis user can not only see the current value for the revenue measure, but also the trend that led up to this measure, giving it a bit more context.
So how does the Presentation Server produce this type of microchart-based view? Well looking at the query log file for the first (simple trellis view) example, you can see that the SQL that's being sent to the database is actually pretty simple:
select sum(T117.FCAST_SAL_AMT) as c1, T107.PROD_CAT_DESC as c2, T880.REGION_NAME as c3, T127.MONTH_YYYYMM as c4 from GCBC_SALES.STORES_RAGGED T880 /* Dim_STORES_RAGGED */ , GCBC_SALES.TIMES T127 /* Dim_TIMES */ , GCBC_SALES.PRODUCTS T107 /* Dim_PRODUCTS */ , GCBC_SALES.SALES T117 /* Fact_SALES */ where ( T107.PROD_ID = T117.PROD_ID and T117.MONTH_YYYYMM = T127.MONTH_YYYYMM and T117.STORE_ID = T880.STORE_PK ) group by T107.PROD_CAT_DESC, T127.MONTH_YYYYMM, T880.REGION_NAME
and it's a similar SQL query that's produced for the advanced trellis view as well. My take on this is that it's actually the Presentation Server that's doing the hard work here, instead of having the BI Server create separate sub-queries for each trellis view it's instead using the Presentation Server to break the numbers down and graph each trellis cell separately, something that's presumably fairly CPU and memory-intensive on the server and why it was generally put together to take advantage of the Exalytics In-Memory Machine. You can, however, use trellis views on any OBIEE 188.8.131.52.2 BP1 system, but just be aware that they could be resource intensive at the BI tier and are really aimed at customers using Exalytics as their platform.
Other Presentation Server new features provided with the 184.108.40.206.2 BP1 patch include:
- The ability to enter target values manually into KPIs for eventual write-back into an Essbase cube
- A new type of layer called "lines" for Mapviewer views that can be used, for example, to show routes between customers or to a central point of interest
- Proper access to Smart KPI Watchlists, a version of KPI Watchlists that were possible through a bit of hacking in previous releases that filter the list of KPIs by object relationships, by object types, by perspective associations, by performance, or by business owner assignments (those of you who attended Kevin McGinley' masterclasses at the Brighton and Atlanta BI Forums will remember these from his Scorecarding session)
So that's it for Presentation Server new features. What about the BI Server and the BI Administration tool?
Repository Model Checker, and Presentation Layer Display Customization Features
One of the challenges in working with the Exalytics-only Summary Advisor, but also the Aggregate Persistence Wizard in-general, is that it's picky about what repository settings it considers valid. For example, the Summary Advisor doesn't yet work with skip-level or ragged hierarchies, and aggregate persistence throws a fit if you logical keys aren't unique (which is fair enough, really). Spotting these issues before you run the Aggregate Persistence Wizard or the Summary Advisor can be tricky, so the 220.127.116.11.2 BP1 patch introduces something called the Model Checker, which you can access from File > Check Models > Complete or Filtered by Statistics when your repository is open online.
The Model Checker differs from the Consistency Checker in that it's looking for object settings that will affect aggregates created by either the Aggregate Persistence Wizard or the Summary Advisor (which uses the Aggregate Persistence feature in the background), and in my example uncovered settings that could either prevent aggregates being generated, or cause them to use values that might be incorrect (though it didn't pick up the fact that I had a ragged/skip-level dimension, which in the current Exalytics release causes a Summary Advisor error). Another welcome change for aggregate persistence provided by this patch is that aggregate persistence scripts that generate multiple sets of aggregates won't now fail overall if just one aggregate creation fails, something useful for Exalytics customers who might be creating dozens of aggregates in one script execution.
Other repository and BI Administration tool new features include the ability to selectively display presentation columns based on an expression returning "0" (to display) or non-zero (to not display) values, like this:
and a more intuitive way of nesting presentation tables within each other, via a new Child Presentation Tables setting for presentation tables that lets you select which other tables are nested within it.
So now we've looked at BI Server and Presentation Server new features in 18.104.22.168.2 BP1, let's take a look at the final major new feature in this patchset - support for the new Oracle BI Mobile HD iPad client.
Enabling Oracle BI Mobile HD for the Apple iPad
Oracle BI Mobile came out with the 22.214.171.124 release of OBIEE 11g, and I wrote a short introduction to the iPhone and iPad versions in my Oracle Magazine column a few months ago. The first Oracle BI Mobile client did have a few shortcomings though, mainly around interaction with graphs and other views and the lack of any offline access to reports or data. Interestingly, rather than extend and enhance the existing Oracle BI Mobile client, Oracle have "re-imagined" the iPad version of Oracle BI Mobile and released an entirely new, more IOS-native app called Oracle BI Mobile HD, downloadable from the iTunes Store and with the 126.96.36.199.2 BP1 patchset on the server as a prerequisite. So what does this new mobile client provide?
The configuration and login process for the new BI Mobile HD client is much the same as the previous iPad and iPhone clients, but once you're in you're presented with a much more "IOS-native" homepage with a coverflow-style view of available reports and dashboards, and buttons along the bottom for quick access to common functions.
Once you're in and looking at a dashboard, for example, the prompt controls are now IOS-native, making them more "touch-friendly" and removing the issue with long prompt value lists that we had with the previous client version.
You can now drill on charts and other objects, and access actions to drill-across to other analyses or invoke a business function or procedure.
Alerts appear on the homepage when content is delivered to you, and you can also save content locally so that you can review a particular dashboard or analysis even when offline. When you do this, the charts and drilling are disabled, as are the prompts, making it effectively a static view of the report which can then be refreshed on-demand.
There's still to my mind a couple of key features missing from Oracle BI Mobile though, even with this updated release. Firstly, many of our banking and financial services clients can't deploy Oracle BI Mobile in its standard form, instead requiring apps such as these to be hardened and wrapped using technologies such as Good Technologies' Good Dynamics. This requires the co-operation of the application vendor, or ideally an SDK for the IOS app so that companies like Good can do this themselves, but until then quite a few clients won't be able to make use of the tool.
The other thing I'd like is some way to create ad-hoc queries using an iPad app - this could be a simplified UI like BI Composer or something more ambitious, but I think any alternative client to the standard desktop web browser needs to have some way to create new analyses as well as consume existing ones. Still, it's a nice improvement on the previous version and getting closer to some of the "state-of-the-art" IOS BI client tools such as RoamBI - but Oracle can't rest on their laurels here as vendors such as Qliktech are designing their future BI tools using mobile as the primary interface, so expect lots of innovation and catching-up from Oracle in this space.
Just to round-up then, here's the list as far as I know of what's new in the 188.8.131.52.2 BP1 patch release, the most interesting of which I think I've covered in this posting:
- Improved Method to Achieve Nested Folders
- Ability to Control Presentation Layer Object Visibility
- Ability to Check for Modeling Problems that Affect Aggregate Persistence
- Improvements to the Aggregate Creation and Deletion Process
- Using XML Files to Automate Physical to Logical Mapping for ADF Data Sources
- Trellis Charts, Microcharts, Sparklines and Line formats for MapViewer views
- Scorecard improvements (reordering of objects in the initiatives pane and strategy panel)
- Ability to create smart watch lists
- Support for write-back of manually entered targets for KPIs into Essbase
- Installation of BI Composer using a script, rather than manual configuration of JEE libraries etc
- New homepage with coverflow-style display of dashboards, as well as recent and favourite objects
- IOS-native controls
- Catalog navigation
- Support for IOS multi-tasking, with the app retaining the state when you switch out of it
- Ability to launch the app via a URL pointing to a particular catalog object
- Graph interactivity, support for the new trellis view, search and interactive scorecards
- Print analyses and other catalog objects via wireless printing
- Offline storage of content with the ability to refresh on-demand
(Remember, whilst Oracle BI Mobile HD is free on the iTunes Store, your organisation will either need the Oracle BI Mobile license (named-user only) to use it, or Oracle BI Foundation which also includes Oracle Scorecard & Strategy Management and Oracle Essbase Server - contact us if you've got any questions around licensing)
There's also a bunch of EPM/Essbase fixes contained with the 14228505 patchset, a new ODBC/JDBC interface for Oracle BI Server diagnostics, a new Security Diagnostics Helper (thanks, Adam!) updates for Real-Time Decisions, and new versions of the BI Publisher and BI Administration client tools to accompany this release. Unfortunately, 184.108.40.206.2 BP1 came out too late to get into the book I'm writing which is now in the final edit stage, but I'll be adding a few tips and notes during my last set of edits to mention the new features that this patchset provides.
That's it for now - keep an eye on the blog for an upcoming ODI 11g series based on some tech lead work I've being doing on a large-scale project in the UK, where I'll be talking about instrumentation, performance, resilience and recovery, and how to ensure design integrity when working as part of an Agile project.