Transcript Generic Information Builders' Presentation Template

THINKing in WEBFOCUS
Walter F. Blood
Director of Product Management
Information Builders
Front office
Mobile
Applications
Visualization
& Mapping
App/Dev, Portals
& Mash-ups
Enterprise
Search
Performance
Management
Reporting
Predictive
Analytics
Query &
Analysis
MS Office &
e-Publishing
Dashboards
Information
Delivery
Data Warehouse
& ETL
Business to
Business
Data Quality
Back office
Master Data
Management
Business Activity
Monitoring
Thinking in WebFocus
Analysis
Retrieve
Report
Generator
DEFINE
Screening
FOCUS or External Sort
Matrix
Aggregation
COUNT/MIN/MAX
TOTAL Screens
Output
Generator
COMPUTE
Format
Extracts
Thinking in WebFOCUS
Analysis

Read and parse the request

Open and parse Master File Descriptions
 Verify DBA access to file(s)

Determine I/O access modules required

Process DEFINE field expressions for pertinent files

Check DBA and set up access restrictions at SEGMENT
and FIELD levels
Thinking in WebFOCUS
Analysis




Activate segments: referenced subtree(s)
 Smallest subtree which contains “root” and every
segment referenced directly or indirectly (HEADING
PARENT AND GRAND CHILD)
Activate fields (real and DEFINEd)
Read USE list and/or check Access Files and
FILEDEF/ALLOCATES
Open and Verify files
Thinking in WebFOCUS
Retrieval

Retrieval is “Top Down, Left Right” within the referenced
sub-tree
 Unique segments are promoted into path of parent and




are never missing
IF/WHERE tests on database fields are evaluated upon
retrieval
DEFINE fields are evaluated if needed
IF/WHERE tests are evaluated on DEFINE fields on
segment by segment basis, AFTER all other selection
tests are passed
Each Path is processed independently, and fields are
merged on common sort fields
Thinking in WebFOCUS
Retrieval - SQL

SQL interface retrieval optimized
 WebFOCUS converted to SQL
 Levels of optimization
 Join
 Selection
 Aggregation
 DEFINEd fields
 Unoptimized – done in WebFOCUS
 Returns data at the Internal Matrix level or before
Thinking in WebFOCUS
Structures
JOIN EMP_ID IN EMPLOYEE TO
EMP_ID IN SPICE AS AJ
JOIN EMP_ID IN EMPLOYEE TO ALL EMP_ID IN KIDS AS BJ
EMP_ID
FIRST_NAME
LAST_NAME
KU
EMP_ID
SPOUSE
K
SH2
COVER_DT
KM
EMP_ID
TYPE_COVER
CHILD_DOB
FAMILY
CHILD_NAME
K
Thinking in WebFOCUS
Structures
SEG1
01
S1
JOIN EMP_ID IN EMPLOYEE TO
**************
*EMP_ID
** JOIN EMP_ID IN EMPLOYEE TO ALL
*FIRST_NAME **
*LAST_NAME
**
*
**
*
**
***************
**************
I
+-----------------+-----------------+
+-----------------+
I
I
I
SEGK
I INSSEG
I SEGS
I INSSEG
KM
04
I SH2
02
I KU
03
I SH2
..............
**************
..............
**************
:EMP_ID
::K *COVER_DT
**
:EMP_ID
:K
*COVER_DT
**
:SPOUSE
:
:CHILD_DOB
::
*TYPE_COVER **
*TYPE_COVER
**
:CHILD_NAME **
::
*FAMILY
**
:
:
*FAMILY
:
::
*
**
:
:
*
**
:
::
*
**
:
:
*
**
:............::
***************
:............:
***************
JOINED SPICE
.............:
**************
**************
JOINED KIDS
EMP_ID IN SPICE AS J1
EMP_ID IN KIDS AS J2
Limit 1024 segments in structure
Limit 1023 Joins
Thinking in WebFOCUS
DEFINE
Each DEFINE field is associated with a specific segment
 Segment is determined by
 WITH field
 Fields used in expression
 Constant expressions are evaluated when file is opened
(segment 0)
 DEFINEs are evaluated ONLY if required by request
 DEFINEs are evaluated at detail level only
Thinking in WebFOCUS
Order of Retrieval – FOCUS Files
TABLE/GRAPH/MATCH
 Root segment instances are obtained in physical
order
 Lower level instances obtained in SEGTYPE order
within parent segment
 TABLEF
 Root and child segment instances are obtained in
SEGTYPE order
 No sorting

Thinking in WebFOCUS
Order of Retrieval – FOCUS Files
TABLE FILE filename.fieldname
Fieldname is not indexed


Segment containing “fieldname” becomes ROOT of
the view, and is retrieved physically
Fieldname is indexed
Segment containing “fieldname” is accessed via the
equality test on INDEX
Other segments become children of the new root
segment
Thinking in WebFOCUS
Order of Retrieval – FOCUS Files
SET AUTOPATH = ON
Alternate Physical View is created through the
referenced segment highest in the hierarchy
SET AUTOINDEX=ON
Alternate Indexed View is created if there is an Equality
test specified for an indexed field on the referenced
segment highest in the hierarchy
SET AUTOSTRATEGY=ON
Equality IF/WHERE test on primary key field in logically
retrieved segment will terminate “chain chasing” as
soon as possible
Thinking in WebFOCUS
Autostrategy – FOCUS Files
STATE
WHERE DATE EQ ‘9912’
1
2
3
4
5
STATE=IL,
STATE=IL,
STATE=IL,
STATE=IL,
STATE=MI,
DATE=0002
DATE=0001
DATE=9912
DATE=9911
DATE=9911
IL
0002
Continue Search
Continue Search
DATE
Continue Search
Next Parent
Next Parent
MI
0001
9912
9911
9910
9911
9910
9909
Thinking in WebFOCUS
FIXRETRIEVE – SUFFIX=FIX
SET FIXRETRIEVE=ON
 Equality IF/WHERE test on primary specified sort field in single
segment FIX file will terminate retrieval as soon as possible
FILE=TESTFI,SUFFIX=FIX
SEGNAME=TESTSEG,SEGTYPE=S1
FIELD=COUNTRY,E01,A10,A10,$
FIELD=CAR
,E02,A16,A16,$
WHERE COUNTRY EQ ‘FRANCE’
Retrieval stops if:
Set FIXRETRIEVAL = ON
ENGLAND
ENGLAND
ENGLAND
FRANCE
ITALY
JAGUAR
JENSEN
TRIUMPH
PEUGEOT
AUDI
Thinking in WebFOCUS
Multiple Paths
 Retrieval is performed for each path separately
 Unique segments are always in the path of their parent
 Unique segments are SEGTYPE = U, KU, DKU, KLU
 JOIN TO creates DKU segments
 JOIN TO ALL creates DKM segments
 TABLE/MATCH/GRAPH
 Instances from each path will be merged in the sort process by
the common parent(s)
 TABLEF does not merge data from multiple paths
 Alternate file views can be used to create single paths
 SET MULTIPATH controls multiple path retrieval
Thinking in WebFOCUS
Multiple Paths
Airport
ARRIVE_TIME
DEPART_TIME
ARR_FLIGHT
DEP_FLIGHT
ARR_FROM
DEPART_TO
Thinking in WebFOCUS
Multiple Paths
ARRIVE_TIME
ARR_FLIGHT
ARR_FROM
DEFINE FILE AIRPORTS.ARRIVE_TIME
DELAY=DEPART_TIME – ARRIVE_TIME;
END
TABLE FILE AIRPORTS.ARRIVE_TIME
Airport
PRINT DEP_FLIGHT DEPART_TIME
WHERE AIRPORT EQ ‘O’’HARE’
WHERE DELAY GT 60
WHERE DEPART_TO EQ ‘LAX’
DEPART_TIME
DEP_FLIGHT
DEPART_TO
WHERE ARR_FROM EQ ‘TOR’
END
Thinking in WebFOCUS
Multiple Paths
FAMILY
FAMILY
FAMILY
COLLEGE
INVESTMENT
Thinking in WebFOCUS
Multiple Paths Instances
1
2
A
I
B
B
M
C
3
M
C
I
D
FAMILY
FAMILY
COLLEGE
FAMILY
INVESTMENT
Thinking in WebFOCUS
Multiple Paths
TABLE FILE SAMPLE
PRINT COLLEGE INVEST
BY FAMILY
WHERE COLLEGE EQ ‘B’
WHERE INVEST EQ ‘I’
END
SET MULTIPATH=SIMPLE
SET MULTIPATH=COMPOUND
FAMILY
COLLEGE
INVEST
FAMILY
COLLEGE
INVEST
------
-------
------
------
-------
------
1
B
I
1
B
I
2
B
.
3
.
I
Thinking in WebFOCUS
Retrieval - Short Paths

SET ALL=OFF -- JOIN INNER
 High level segments with missing referenced
descendents rejected (short path)
 SET ALL=ON -- JOIN LEFT_OUTER
 High level segments with missing referenced
descendents accepted (missing fails IF/WHERE tests)
 SET ALL=PASS
 High level segments with qualified or missing
referenced descendents accepted (missing passes
IF/WHERE tests)
Thinking in WebFOCUS
Retrieval - Short Paths
 Unique segments (U, KU, DKU, and KLU) are never
considered missing
 Referenced, not present, Unique Segments are defaulted to
blanks for alpha fields, zeros for numerics
 Unique segments do not create short paths. Therefore, ALL
settings are irrelevant for Unique segments.
 Note: Missing referenced descendents of missing unique
segments revert to ALL=OFF logic
Thinking in WebFOCUS
Internal Matrix Generation
The output of the SORT/MERGE phase is
conceptually a matrix
 One row for each distinct combination of sort keys
 One column for each verb object
 Computed columns, row and column totals, subtotals,

and summaries are not yet done
All data is in internal form
Thinking in WebFOCUS
Internal Matrix - Contents
Verb Objects (both SUM and COUNT are calculated)
 Fields following a verb
 Fields used in COMPUTEs not previously
mentioned
 Fields used in HEADINGs or FOOTINGs
 Fields used in SUBHEADs or SUBFOOTs, not
previously mentioned
 MISSING fields are not counted
Sort Fields
 BY fields
 ACROSS fields (used as low order BY at sort
phase)
 FOCLIST (verb is PRINT or LIST)
Thinking in WebFOCUS
Multiple Verb Sets - limits



Up to 16 verbs with associated BY fields can be
specified
Up to 128 sort fields may be specified
Up to 1024 verb objects may be specified
Thinking in WebFOCUS
Multiple Verb Sets
SUM SALES BY DIVISION
SUM SALES BY DIVISION BY YEAR
LIST SALES BY DIVISION BY YEAR
DIV
SALES YEAR SALES FOCLIST SALES
EAST
210
99
100
1
40
NORTH
SOUTH
WEST
150
30
10
2
60
00
110
3
110
99
60
1
60
00
90
2
90
99
30
1
10
2
20
1
10
99
10
Thinking in WebFOCUS
Internal Matrix - Sorting
 Aggregation is performed as record is sorted, with



FOCUS sort
Aggregation is performed by external sort if SET
EXTAGGR = ON
Numeric Fields are added. Alpha fields being
SUMmed, return the LAST value within the BY field,
the FST value with external sorts
 (SET SUMPREFIX=LST if required)
All verb objects are also COUNTed
Thinking in WebFOCUS
Merge – FOCUS Sort
BINs
FOCSORT
SORT
MERGE
BINs
FOCSORT
SORT
MERGE
BINs
FOCSORT
Thinking in WebFOCUS
EXTSORT = ON, AUTOTABLEF = OFF
BINs ~ First
5000
Records
FOCSORT
S001WK01
S001WK02
S001WK03
BINs
Subsequent
Records
S001WK04
EXTSORT
S001WK05
S001WK06
S001WK07
S001WK08
S001WK09
SORT
MERGE
FOCSORT
Thinking in WebFOCUS
EXTSORT = ON, AUTOTABLEF = ON
OFFLINE
BINs ~ First
5000
Records
S001WK01
S001WK02
S001WK03
Subsequent
Records
Or…
S001WK04
EXTSORT
S001WK05
S001WK06
Extract
S001WK07
S001WK08
S001WK09
Or…
Hotscreen
Thinking in WebFOCUS
Output Stage
SORT
Internal Matrix
COMPUTE’s
IF/WHERE TOTAL
Secondary Sort
BY TOTAL
Post Matrix Processing
Totals…
FOCSML
Report
FML/EMR
Processor
Formatting/Stylsheets
Extract
HOLD/PCHOLD/SAVE
Thinking in WebFOCUS
BY TOTAL
TABLE FILE EMPDATA
SUM SALARY
CNT.PIN
COMPUTE AVGSAL=SALARY/CNT.PIN; AS 'AVE,SALARY'
BY HIGHEST TOTAL AVGSAL NOPRINT
BY DEPT
END
PAGE
1
DEPT
---ACCOUNTING
SALES
MARKETING
CUSTOMER SUPPORT
PROGRAMMING & DVLPMT
PERSONNEL
CONSULTING
ADMIN SERVICES
SALARY
-----$283,300.00
$395,200.00
$570,700.00
$198,400.00
$182,300.00
$216,800.00
$126,300.00
$56,200.00
PIN
COUNT
----5
7
11
4
4
5
3
2
AVE
SALARY
-----$56,660.00
$56,457.14
$51,881.82
$49,600.00
$45,575.00
$43,360.00
$42,100.00
$28,100.00
Thinking in WebFOCUS
Output Stage
SORT
Internal Matrix
COMPUTE’s
IF/WHERE TOTAL
Secondary Sort
BY TOTAL
Post Matrix Processing
Totals…
FOCSML
Report
FML/EMR
Processor
Extract
Formatting/Stylesheets HOLD/PCHOLD/SAVE
Thinking in WebFOCUS
ON Sortfield Options
 Subtotaling – BY and ACROSS
 SUBTOTAL and SUB-TOTAL
 RECOMPUTE and SUMMARIZE
 MULTILINE suppresses operation if only single


detail line
RECAP (COMPUTE)
SUBFOOT/SUBHEAD
Thinking in WebFOCUS
Totaling
BY specified only
and TABLE *
BY specified plus
Higher Bys
TABLE *
ADD’s all numeric
columns
SUBTOTAL
COLUMN-TOTAL
SUB-TOTAL
Recalculates
COMPUTE’s
ADD’s up other
numeric columns
RECOMPUTE
SUMMARIZE
* NOTOTAL Suppresses Grand Totals
Thinking in WebFOCUS
ON Sortfield Options - Format
 PAGE-BREAK [REPAGE]
 NOSPLIT
 **FOLD-LINE
 **SKIP-LINE
 UNDERLINE
** May be specified on verb-objects
Thinking in WebFOCUS
ON TABLE Options
 Totaling – ON TABLE…
 COLUMN-TOTAL
 ROW-TOTAL
 SUMMARIZE
 NOTOTAL
 RECAP
 [PAGE-BREAK AND ] SUBFOOT/SUBHEAD
Thinking in WebFOCUS
HEADings and FOOTings
 References to fields in HEADING and FOOTING
become verb objects with the first verb
 References to fields in SUBHEAD and SUBFOOT
become verb objects only if not previously
mentioned
 Fields used in HEADINGs and SUBHEADs are
taken from the first line within the group (BY phrase
or page)
Thinking in WebFOCUS
ON TABLE Extract Options
 Extracts
 ON TABLE HOLD [FORMAT …]
 ON TABLE PCHOLD [FORMAT…]
 ON TABLE SAVE [FORMAT…]
 SET commands specific to format
 Interface SET
 WebFOCUS SET
Thinking in WebFOCUS