Generic Information Builders' Presentation Template
Download
Report
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