Migrate BI to APEX 5?

Posted on by

Just back from presenting Migrating BI to APEX 5 at RMOUG Training Days 2016.  Great conference!  Great experts on all Oracle technology. If you have to pick one short sweet conference a year, this is it.

Ever thought of replacing your under-utilized, expensive or outdated BI tool with a suite of APEX Interactive Reports?  In *SOME* cases this makes sense:

  • Your Data Display requirements are reasonable – i.e. users are happy with seeing and working with a few thousand rows or less at a time.
  • Your BI feature requirements include APEX IR Actions (select, sort, filter, chart, pivot, group by aggregate, save, download), and
  • Your BI feature requirements do NOT include BI analysis, unlimited drill-downs, automatic or integrated analysis (such as percents, rank, etc), drag and drop report creation/analysis, and/or MS-Excel-like features. IF you want these, you have to build them.
  • You have APEX resources to plan, design, build and maintain the APEX app
  • You have Database Architects/Developers to plan, design, build and manage the data structures in the database required to consolidate and serve your data ( materialized views, indexes, or other rollups as needed as licensed for).
  • You are prepared to design, build and tune all for performance (as you always should anyways!)
  • You do NOT require out-of-the-box PDF Printing for the as-displayed data set.  Those who own and extensively use BI Publisher may not want to give up this printing luxury IF moving to APEX means doing so. There are many many printing uses cases for which there are many solutions – ensure you have a solution that fits (and is reasonable to implement) before you jump.

Given all this, it just might work for you  It has for many customers, successfully.  The caveats are:

  • If you need a feature that APEX does not natively supply, you must build it.
  • Dynamic Actions are great -but need to be implemented wisely. They may not always perform well with volumes of data.
    • ex: Dynamic parameter selection => Good.
    • Dynamic parameter selection AND dynamic refresh of the IR with the new parameters (a new query against the source data structure) may/will not perform so nicely, depending on the volume of data.
  • Some features are just not reasonable to build in APEX:
    • ex: True Excel-Like behavior
    •        Dynamic Aggregate adjustments

So – want to replace that aging Discoverer installation?  Consider APEX 5.

In planning pages, menus and features, consider these tips:

  • Plan and meticulously tune all structures – materialized views, CUBEs or ROLLUPS, or whatever works for your data. Anything slow here is magnified slow in APEX.
  • Give users mandatory parameters.  This forces up-front filtering, to reduce the result set to a reasonable size AND gives you a ready-made drill path.  We want reasonable size for reasonable performance of all interactive features.
  • Try 3 to 5 Parameters – More gets tedious for end users if they have to select every time.
    • A Temporal (Year. Quarter, Month), and Spatial ( Country, State, City ) and one or few others specific to your data.
    • Consider using Page Zero or building  a Plugin for displaying the same parameter set on all or several pages. (Yes, Plug-Ins can be used locally!)
  • Use a flag to control when data displays. This allows users to filter (using your parmeters Plugin) first, then wait for data to display.  Plan so users do not incur a big wait right up front.
  • Consider multiple IRs on one page, each containing different aggregates of you data depending on the parameters.  The parameters chosen control which ONE of thes IRs displays at any given time.
  • Consider multiple IRs on one page, each containing different Action Item features.  Authorizations for authenticated users control which ONE of thes IRs displays at any given time.
  • Plan and build drill paths wisely.  Intelligently build useful drill paths.  A drill on every column is not necessary. Drills on key columns are nice to have.

Know Your Users, and know what they really do with the data.  That helps you to design and build truly useful data sets and features.

Still not convinced? On the Fence ?

Consider waiting for APEX 5.1 Interactive Grid.  Previews of this new region type show some promising features that BI folks may find interesting:

  • Drag and drop headings
  • Lazy loading – an option to display the frame then the rest of the data
  • Loading data in pages, as opposed to one big result set.

Doesn’t seem like much, but these are big useability improvements , especially when we are considering paging through volumes of data.  Were I to be starting a BI migration project now,  I would investigate the APEX 5.1 Interactive Grid previews and plan my project accordingly.

In the meantime, see my Migrating tBI to APEX 5 slides here, and  if you have specific questions, reach out:

Leave a Reply