migrate bi to apex 5

TH Technology

Karen Cannell

[email protected]

Migrate BI to APEX5:

Are We There Yet?

What Works, What Doesn’t & Lessons Learned

TH Technology

Migrate BI to APEX 5 ?

About Me …

Karen Cannell ~ Consultant, TH Technology

Mechanical/SW Engineer - Analyzed, designed,

developed, converted, upgraded, enhanced legacy &

database applications for 25+ years

Building APEX applications for government, medical,

engineering industries since HTMLDB

Leveraging the Oracle 10g,11g, 12c suite of tools

Oracle Ace Associate

Editor Emeritus , Technical Journal

Support Your User Group!

TH Technology


APEX Books …

Expert Oracle Application Express, APress

2015 Report Printing

Beginning Application Express

4.2, APress, 2013

Agile Oracle Application Express

APress, 2012

TH Technology


About You …

APEX Version?

Experience?

Migrating From?

Why?

Important BI Features?

“Printing” ?

TH Technology


BI to APEX ~ Are We Nuts?

APEX 4: Are We Nuts?

TH Technology


APEX is

NOT a

BI Tool.

TH Technology


BI to APEX 5 ~ Better?

TH Technology


APEX is

NOT a

BI Tool.

TH Technology


BUT… APEX

Can Be used to

Build BI-Like

Features.

TH Technology


BI to APEX 5

Leverage APEX Interactive Reports

to Mimic BI-Tool Features:

Flexibility – Column, Sort, Filter, Highlight

Grouping

Pivots

Downloads

Save, Share

TH Technology


Wait for APEX 5.1 Interactive Grid

Leverage APEX 5.1 Interactive Grid

to Mimic BI-Tool Features:

Greater Flexibility – Column, Sort, Filter,

Highlight

Drag and Drop

Greater “Excel-Like” Behavior

We’ll See!

TH Technology


BI to APEX 5

Universal Theme

Dynamic Actions

Interactive Reports

Re-Engineered

Multiples on One Page

Group By Enhancements

PIVOT!

TH Technology


Is That

Enough?

TH Technology


Key Considerations:

Data

BI-Like Features

Performance

$$ Price

TH Technology


$$ Price

”Free”

TH Technology


$$ Price

Really?

TH Technology


$$Price

Resources for Database

Yours or Hosted

Resources to Build

Design ~ Planning

Supporting Structures

App Itself

Features: Drills, Dyn Actions, Parameters

“Printing”

TH Technology


$$Price

Weigh Cost to Build APEX Solution

vs.

Cost BI License

Support

Annual or One-Time?

TH Technology


Data

Volume

Volume Per Page View

Terabytes? No.

Small-Medium Yes.

Interactive Reports

Rows Per Page

Pagination

How Much To Display At Once

TH Technology


Data

What Do Users Do With Their Data?

What They Tell You

What They Really Do

Watch Them…

Volumes?

Formats?

Post-Report Activity?

TH Technology


Data

“This is the data I need … “

“I import that into Excel and do … “

TH Technology


Data

How Much to “Display” on One Page?

Does one Need “All the Data” On One Screen?

Do They Run the Report, then Download

Everything?

Break That Habit

Filter Filter Filter

1000 Rows is optimal

TH Technology


Features

Decide What Users Need

Then Decide What Tool Fits

TH Technology


BI Tool Common Features

Select Columns

Sorts

Filtering

Tabular and

Crosstab (Pivot)

Aggregates

Grouping

Drills

Highlighting (Stop-

lighting)

Multi Data Source

Downloads

Printing

Data Volumes

TH Technology


APEX Interactive Reports Features

Select Columns

Sorts

Filtering

Tabular and

Crosstab (Pivot)

Aggregates

Grouping

Drills #

Highlighting

Single Data Source*

Downloads

“Printing”

Not Really

Small-Moderate Data

Volumes

# - Have to build

* - Sort Of

TH Technology


IR Features - Options

Set Features On/Off

By Report

By Column

Authorizations for Levels of Privileges (Roles)

Who Sees What

Conditions on Report Region based on

Selections

What They See When

Condition on Report Region

Features

TH Technology


Either-Or Features

Report

Group By

Chart

Pivot

Only See One of These at a Time

TH Technology


Demonstration

Select

Sort

Filter

Aggregates

Grouping

Pivot

Save

Download

TH Technology


Lose

Build Own Report (from Scratch)

Auto Drill, Auto Aggregates

Out-of-the-Box ROLAP, MOLAP (Must Build)

True Dynamic Interactivity

True Analysis Features

Formatted Printing

Need to Build These

(or Do Without)

TH Technology


Parameters

Pre-Filter Eliminates Slow First Load

Reduce Volume of Result Set

Cascading LOV’s

Whatever is Needed for Your Parameter

Determination

Features

TH Technology


IR Aggregate Behavior

Original Report Display

Region: West

************

State County City Population

CA Orange County Irvine 100

CA Orange County Orange 200

CA Los Angeles Hollywood 300

CA Los Angeles Universal City 400

Sum 1,000

Actual APEX IR Result

Region: West

************

State County Population

CA Orange County 100


CA Los Angeles 300

CA Los Angeles 400

Sum 1,000

Intended Result Region: West

************

State County Population


CA Los Angeles 700

Sum 1,000

Know Your Users Inform Your Users

X

Features

TH Technology


Charts

W/In IR - User-Driven

Simple Charts – NOT Complex Charting

Display Chart OR Report

Chart on Base Data – NOT on GROUP BY Display

Out of IR – Full APEX Charting

Supplement the IR

in Another Region

Features

TH Technology


Charting

Feature

Chart

OR

Report

OR

Group

By

OR

Pivot

TH Technology


Pivots – Pivot Columns

Known Columns and Column Names

ex: Length by Month

vs.

Unknown Columns and Column Names

ex: Month by Length

Length by Year

Length by Area

Pre-APEX 5 This Is Hard …

TH Technology


… Skip 17 Slides…

TH Technology


APEX 5 IR Pivot

TH Technology


Saved Reports

Adds Flexibility to Report Strategy

Multiple Views of Same Data

Convenience

Alternate Default Reports

Saved Public Reports

Reports Select List to Access

Features

TH Technology


Drill Down Report

Features

TH Technology


Features ~ Drills

Build Parent Report

Build Child Report

Build Link in Parent to Child

Passing Parameters

Must Build Each One

Features

TH Technology


Drills

TH Technology


Drills

Use IR_COLUMN_NAME for the Link

[STATIC_ID]COLUMN_NAME for > 1 / Page

Create RESET Button or

Automatic Way to Clear Hidden Items

Same or Different Page

Plan – Plan - Plan

Feature

TH Technology


Download / Print Options

HTML

Excel

PDF

Must Configure PDF Output

(FOP or BI Publisher)

Generic

XLS (BI Publisher)

RTF (BI Publisher)

Email – Allows Sharing Results

TH Technology


…IF you Have BI Publisher

Why Are You Here?

TH Technology


Download Options – User View

Feature

TH Technology


Dashboard

Multi IRs on One Page

Ex: Table, Group, Chart, and Pivot

Driven by Same Parameters

OR Drill Between Report Views

Caveats:

Use the Best Tool for the Job

Multi APEX IR May Not Be the Wisest

Choice

Feature

TH Technology


Deployment

Normal APEX Deployment

Feature

TH Technology


Training and Documentation

Same as Required for APEX Application

Minimal Compared to Full BI Tool

Feature

TH Technology


Performance

Tune SQL Query First.

Tune the Page ~ Monitor Activity

Tune Each Element on the Page

All the Usual Tuning Methods Apply

Employ Materialized Views

Employ CUBEs, ROLLUP

As Needed to Support Desired Display

and Performance

Performance

TH Technology


General Tuning Tips

Set Application Processes to execute Per

Session

Set Page Process to exec Per Page vs Per

Session

Tune Page Zero Components

Use Declarative Conditions

ITEM = Value vs. PL/SQL Expression

TH Technology


IR Settings for Performance

Max Rows

1000 is optimal

Larger #s, Longer Wait

Return All Rows = Longer Wait

Use Filters to Limit Data Set

(User Training ~ Change Habits?)

Balance w/ User Requirements

TH Technology



Rows Display w or w/o Pagination

No Pagination – Better

Rows X to Y - Pagination

Rows X to Y of Z – Must Get All Rows and

Paginate

Balance w/ User Requirements

TH Technology



1000

optimal

No “of Z”

TH Technology


IR Settings for Performance, cont’d

Row Sorting

Sort adds an ORDER BY on the column

Index Sorted Columns

Limit Sorts to Essentials

Balance w/User Requirements

TH Technology


Dynamic Actions

Dynamic Actions Get Confused (Slow)

With Large Data Volumes

Use Wisely

Use Sparingly

TH Technology


Parameter Settings for Performance

Default Parameters ~ Ease of Use

BUT

Executes Report Once/Set on Navigation to

Page… Not OK for Long Queries.

Solution:

Clear (At Least One) Parameter on Page Load

Some Action Req’d To Display

TH Technology


APEX is

NOT a

BI Tool…

TH Technology


Key Considerations

Data Same

Features Fewer, Ok or Not?

Performance Have to Plan, BuildS

and Tune for it.

$$ Price Down, UNLESS You Build

Too Much

TH Technology


What Works

Interactive Reports Flexibility

Simple Menus

Simple Pages

Strategic Parameter Selection

Generic Printing

No License, Support Cost

Ease of Use

TH Technology


What Doesn’t …

NOT for Unlimited Drills

Must Plan And Build

NOT for Advanced BI Functionality

None. UNLESS You Build It

NOT for Fancy Layouts

BI Publisher or XSL-FO for Formatted Reports

NOT Data Mining

NOT For Everyone!

TH Technology


Lessons Learned

Know What Your Users Need

Plan Queries – Plan Layouts – Plan Features

Plan Supporting Structures for Queries

Know Your Data, How to Present It

Plan Download/Printing Approach

TRAIN USERS

Know What Users Really Do With Data

BUILD SMART

TH Technology


Test Test Test Test Test

Test for Performance

Test w Full Data Volumes

Test w Full User Volume

TH Technology


Closing ….

What Works – Is it Enough?

What Doesn’t – Does It Matter?

Know Your Users

Know What Users Really Do

With Data

TH Technology


One the Fence?

Wait!

APEX 5.1 ~ Interactive Grid

Drag and Drop

Set Read-Only for “Report”

Improved Interactivity

TH Technology


Your Call …

Data

Features

Performance

$$ Price

Good Luck!

TH Technology


Resources

Sizing for Performance (2007!) Mike Hichwa

http://www.oracle.com/technetwork/issue-archive/2007/07-

jan/o17browser-083149.html

Best Source of APEX Help

Apexworld.com

http://www.apexworld.com

Enhanced Aggregation, Cube, Grouping and Rollup (OLAP

Reporting Embedded in SQL) Shouvik Basu http://www.orafaq.com/node/56

http://www.oracle.com/technetwork/issue-archive/2007/07-jan/o17browser-083149.html









http://www.apexworld.com/

http://www.orafaq.com/node/56

TH Technology


Share Your Knowledge!

Call for Articles/Reviewers

Editor, ODTUG Technical Journal

[email protected]

TH Technology

Questions ?

Migrate BI to

APEX 5 WWWD and LL

[email protected]

mailto:[email protected]

TH Technology

Thank You

Migrate BI to

APEX 5 WWWD and LL

[email protected]

mailto:[email protected]

TH Technology


Really?…

migrate bi to apex 5

Software