© Copyright IBM Corp. 2002 247
Index
Symbols
‘W’ statistic 186
A
Adjusted R2 110
aggregation 151
ALLOW READ ACCESS 39
ALTER TABLE 33, 41
alternative hypothesis 225
appended data 38
AST 21
atomic 36
Automatic Summary Tables 21
AVG 101, 171, 192, 195
B
Bernoulli sample 105, 151–152
Business Intelligence 5
advantages of functionality in database engine
12
enhancements in DB2 UDB 11
importance 7
strategy 9
Business Intelligence overview 1
business scenarios 149
C
CASE expression 55, 177
CHECK PENDING CASCADE DEFERRED 39
CHECK PENDING NO ACCESS 22–23, 37–38,
40, 44, 66
CHECK PENDING READ ACCESS 38, 40
Chi-squared 182, 226
chi-squared 229
collocated joins 96
compensating predicates 49
compensation 44
concepts 217
Chi-squared 229
conditional probability 231
Correlation 222
COVARIANCE 220
equi-height histograms 233
equi-width histograms 233
Extrapolation 231
HAT diagonal 226
Histograms 232
Hypothesis testing 224
Interpolation 231
Probability 231
Regression 223
Sampling 232
Standard deviation 219
Transposition 232
VARIANCE 218
Wilcoxon rank sum test 229
Conditional probability 231
CONST_CHECKED 26
CORRELATION 101, 112, 165, 185
correlation 222
correlation coefficient 116, 222
COUNT 102
COUNT_BIG 102
COVARIANCE 103, 111, 220
CUBE 47, 94, 127, 138, 144, 181, 185, 206
CURRENT MAINTAINED TABLE TYPES FOR OP-
TIMIZATION 44
CURRENT REFRESH AGE 44
D
data warehouses 20
deferred refresh 18, 27, 33, 60
DEFINITION ONLY 41
DELETE ONLY state 33
DENSE_RANK 130–131, 174, 185, 206, 213
DFT_QUERYOPT 44
DISABLE QUERY OPTIMIZATION 26
dynamic SQL 34, 43, 59, 63
E
e-business 2, 5, 8
drivers 2
impact 5, 8
ENABLE QUERY OPTIMIZATION 44
equi-height histogram 180, 233
248 High-Function Business Intelligence in e-business
equi-width histogram 177, 233
Event Monitor 64
exception tables 23
EXPLAIN 43
Extrapolation 231
F
filtering predicates 65
finance business scenario
Identify potential fraud situations for investiga-
tion 192
Identify target groups for a campaign 181
Identify the most profitable customers 173
Identify the profile of transactions concluded re-
cently 176
Plot monthly stock prices movement with per-
centage change 193
Plot the average weekly stock price in Septem-
ber 195
Project growth rates of web hits for capacity
planning purposes 198
Relate sales revenues to advertising budget ex-
penditures 201
full refresh 29, 34, 41
G
Generalizing local predicates 69
generalizing local predicates 65
GROUP BY 138, 154
group-between 123
Group-bounds one and two 124
group-end 123
GROUPING 138, 160, 181
GROUPING function 50
GROUPING SETS 94
grouping sets 47
group-start 123
H
HAT diagonal 202, 204, 226
histogram 232
Hypothesis testing 224
I
immediate refresh 18, 34, 60
IMPORT 26
INCREMENTAL 22, 27, 29
Incremental refresh 29, 34
incremental update 35, 40–41
Index Advisor 67
information business 7
Interpolation 231
ISOLATION 59
J
join predicates 52
L
latency 18, 20, 33
Least Squares Fit 223
left-tailed hypothesis test 226
linear regression 110, 114, 226
LOAD 26
ALLOW READ ACCESS | NO ACCESS 38
CHECK PENDING CASCADE DEFERRED | IM-
MEDIATE 38
LOADing 37
Locking contention 60
LOG(n) 181
logging 33, 41, 60, 90
M
MAINTAINED BY SYSTEM 28
populate 23
MAINTAINED BY USER
populate 25
Matching criteria 44
matching criteria 44, 66
matching inhibited
Friendly Arithmetic 59
Isolation Mismatch 59
Materialized view contains more restrictive pred-
icates than in the query 57
Materialized view missing columns that are in
the query 57
Query includes the following constructs 56
Query with an expression not derivable from ma-
terialized view 58
matching permitted
Aggregation functions and grouping columns
46
Case expressions in the query 55
Extra tables in the materialized view 53
Extra tables in the query 52
Index 249
Superset predicates and perfect match 45
Materialized view
cache 20
concept 17
create 21
customer scenario 18
design 60
DROP 42
functionality 18
limitations 92
matching considerations 42
motivation 16
optimization 34
refresh approaches 26
tuning 87
materialized view 13, 21
aggregate tables 20
apply delta 35
considerations 19
creating 19
delta aggregation 35
delta joins 35
LOADing 37
MAINTAINED BY SYSTEM 22
MAINTAINED BY USER 22
nicknames 20
non-aggregate 21, 31
parameters 19
replicated 31
thin 61
wide 60
materialized view design
Step 1
Collect queries & prioritize 63
Step 2
Generalize local predicates to GROUP BY
64
Step 3
Create the materialized view 65
Step 4
Estimate materialized view size 65
Step 5
Verify query routes to “empty” materialized
view 66
Step 6
Consolidate materialized views 66
Step 7
Introduce cost issues into materialized view
routing 67
Step 8
Estimate performance gains 67
Step 9
Load the materialized views with production
data 69
Materialized view syntax 241
MAX 103
median 128
computing it 129
MIN 104
MQT 21
multi-dimensional cluster 38
multi-query optimization 90
N
NO DATA MOVEMENT 38, 40
NON INCREMENTAL 22
non-linear equation 199
non-linear regression 117
NOT INCREMENTAL 27, 29
null hypothesis 224–225
Nullable 51
nullable 50
O
OLAP functions 117
DENSE_RANK 122
RANK 122
OLTP 20
one-tailed tests 226
ORDER BY 122, 135–136, 156, 159–160, 167,
174, 177, 185, 193, 195, 206
OVER 156, 159–160, 167, 177, 193, 195, 206
OVER clause 133
P
package cache 63
packages 41
PARTITION BY 122, 132, 156, 159, 167, 206
pipelining 36
population 103, 225
population standard deviation 105, 220
population variance 106, 219
precision issues 58
probability 231
PROPAGATE IMMEDIATE 31
p-value 226
250 High-Function Business Intelligence in e-business
Q
QUERY OPTIMIZATION 44
query rewrite 42, 48, 54, 56, 59
R
R Squared 224
R2 224
RAND 104, 151
RANGE 123, 136
RANGE BETWEEN 195
RANK 129, 131–132, 159–160, 167, 174, 181, 185,
206, 213
Redbooks Web site 246
Contact us xxii
referential integrity 39, 53–54
informational 60
Informational constraints 54
NOT ENFORCED 54
System-maintained 54
REFRESH DEFERRED 22, 27, 30, 44, 60, 92, 95
REFRESH IMMEDIATE 22, 30, 34, 37, 44, 60, 92
Refresh optimization 90
REFRESH TABLE 23, 27, 37, 90, 243
INCREMENTAL 243
NOT INCREMENTAL 243
REGR_AVGX 202
REGR_COUNT 199, 204
REGR_ICPT 199, 204
REGR_SLOPE 199, 204
REGR_SXX 202, 204
REGR_SXY 204
REGR_SYY 204
regression 223
R2 224
regression functions 107
REGR_AVGX 107
REGR_AVGY 107
REGR_COUNT 107
REGR_INTERCEPT 107
REGR_R2 107
REGR_SLOPE 107
REGR_SXX 107
REGR_SXY 107
REGR_SYY 107
regression standard deviation 205
Regression sum of squares 110
Replicated tables 95
replication
Inter-database 97
Intra-database 97
Residual sum of squares 110
retail business scenario
Compare and rank the sales results by state and
country 160
Determine relationships between product pur-
chases 164
Determine the most profitable items and where
they are sold 167
Identify stores’ sales revenues significantly dif-
ferent from average 171
List the top 5 sales persons by region this year
159
Present annual sales by region and city 154
Provide total quarterly and cumulative sales rev-
enues by year 156
right-tailed hypothesis test 226
ROLLUP 47, 51, 94, 138, 140, 154, 160, 206
ROW_NUMBER 131, 174, 185
ROWNUMBER 122, 177
ROWS 123, 135–136
ROWS BETWEEN 193
ROWS BETWEEN, 195
S
sample data 150
sample standard deviation 105, 220
sample variance 106, 219
sampling 151, 232
sampling rate 152
secondary log 33
SET INTEGRITY 23, 37, 40
FULL ACCESS 38
SET SUMMARY 33
significance level 226
simple linear regression 110, 223
Simple Random Sample 232
Snapshot Monitor 43, 63
sports business scenario
For a given sporting event 206
Seed the players at Wimbledon 213
SQL Compiler 42
staging table 22–23, 27, 29, 31, 60
standard deviation 113, 204, 219
Standard error 110
State 44
statement cache 43
Index 251
static SQL 34
Statistics and analytic functions 100
Statistics, analytic and OLAP functions 14
STDDEV 105, 113, 171, 192
SUM 106, 177, 181, 185
SUMMARY 19
summary table 31
synchronization 36
System maintained 27
T
t statistic 110
Tables 235
tables
AD_CAMP 236
BIG_CHARGES 236
CAL_AD_CAMP 236
CUST 236
CUST_DATA 236
CUSTTRANS 236
DDL 236
EMPLOYEE 236
EVENT 237
FACT_TABLE 237
FEB_SALES 237
LC_PURCHASES 237
LOC 238
LOOKUP_MARKET 238
PRICING 238
PROD 238
PROD_OWNED 238
SALES 239
SALES_DTL 239
SEEDINGS 239
STOCKTAB 239
SURVEY 239
SURVEY_MORTG 239
T 240
TRAFFIC_DATA 240
TRANS 240
TRANSACTIONS 240
TRANSITEM 240
temporary tables 36
Total sum of squares 110
Transposition 232
truncation issues 58
two-tailed hypothesis test 226
U
User maintained 27
V
VARIANCE 106, 113, 218
W
Wilcoxon Rank Sum 183, 186, 226, 229
Window aggregation group clause 123
Z
z-lock 29, 90
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.