Table of contents for Joe Celko's analytics and OLAP in SQL / Joe Celko.


Bibliographic record and links to related information available from the Library of Congress catalog
Note: Electronic data is machine generated. May be incomplete or contain other coding.


Counter
Basic Reports and History                                               1
1 l     Cases                                                      2
1.2     Control-Break Reports                                      3
13      Cross-Tabulation Reports                                   3
1.4     Presentation Graphics                                      4
1.5     Local Databases                                            4
2    Cross-Tabulations                                                       7
2.1     Crosstabs by Cross-Join                                   11
2.2     Crosstabs by Outer joins                                  12
23      Crosstabs by Subquery                                     13
2.4     Crosstabs by CASE Expression                              14
2,5     Crosstabs with Row and Column Summaries                   14
3  Dimension Tables                                                       17
3 1     Star and Snowflake Schemas                                17
3.2     Kinds of Dimensions                                       22
3.2.1     Slowly Changing Dimensions                           23
3.2.2     Hierarchical Dimensions                              23
3.2 3     Categorical Dimensions                               26
.3      Calendars and Temporal Data                               26
3 3.1     Report Range Tables                                  28
3 4    Helper ables                                               29
3.5     Surrogate Keys                                            30
3.6     Degenerate Dimensions                                     32
4   Data Migration and Scrubbing                                       33
4.1    Pumping Data                                           34
"4.2    Verification and Validation                           35
4.3    Extract, Transform, and Load (ETL)                     36
44     Databases Also Evolved                                 36
4.5    Data Warehouses                                        37
4.6    Extract, Ioad, and then Transform (E-L-T)              38
.6,1     Does It Work?                                     39
4.7    Scrubbing Data with Non-First-Normal-Form (1NF) Tables  40
4_7.1    Setting up a Scrubbing Table                      40
4.7.2    Designing a Target Table                          46
4.7.3    Adding Constraints and Validations                48
5   MERGE Statement                                                    51
5.1    Simpple MERGE Statement                                52
5.2    Merging without the MERGE Statement                    53
5.3    TRIGGERs and MERGE                                     54
5.4    Self-Referencing MERGE                                 55
6   OLAP Basics                                                        57
6.1    Cubes                                                  57
6.2    Dr. Codd's OLAP Rules                                  58
6 .21    Basic Features                                    59
62.2     Special Features                                  61
6.2.3    Reporting Features                                61
6. 24    Dimension Control                                 62
6.3    MOLAP                                                  62
6.4    ROILAP                                                 63
6.5    HOLAP                                                  63
66     OLAP Query Languages                                   63
7   GROUPING Operators                                                 65
7.1    GROUP BY GROUPING SET                                  65
7.2    ROLLUP                                                 66
7.3    CBES                                                   68
7.4    Notes about Usage                                      69
8   OLAP Operators in SQL                                             71
8.1    OLAP Functionality                                     72
8.1.1    Row Numbering                                     72
8.1.2    RANK and DENSE RANK                               74
8 1.3     he Window Clause                                 75
8.2     NTILE0!                                               78
8.3    Nesting OLAP functions                                 80
84     Sample Queries                                         80
9   Sparseness in Cubes                                               83
9.1    Hypercube                                              84
9 2    Dimensional Hierarchies                                84
10  Data Quality                                                      89
10.1   Checking Columns for Value Counts                      90
10.2   Finding Rules in a Schema                              91
10 2.1   Mathematical Rules                                93
10.2.2   Logical Rules                                     94
10 .23   Pattern Rules                                     96
10.3   Feedback for Data Quality                              97
10.4   Further Reading                                        97
1 I Correlation                                                       99
11. 1  Causes and Correlation                                101
11.2   Linear Correlation                                    102
11.2.1   Pearson's r                                      1.02
11 2 2   The Rashomon Effect                              105
11.3   Nesting Functions                                     105
11.4   Further Reading                                        06
S2  Data Distributions                                                  107
.1 j   Flat Distributio                                         1 07
12 2   Zipfian Distribuonn                                      108
[2.3   Gaussian Normal, or Bell Curve                            09
12.4    Poisson Distribution                                    111
125     Logistic or " Distribution                              112
12.6    Pareto Distribution                                     112
12.7   Distribution Discovert                                   113
12.7.1   Missing Data Discovery                              114
12 8   References                                               115
33  Market-Basket Analysis                                              117
13.1   Simple Example of a Market Basket                        117
13 2   Relationai Division                                      119
13.2.    Division with a Remainder                           120
131.2.   Exact Division                                      121
13.2.3   Todd's Division                                     122
13.2.4   Division with Set Operators                         124
133    Romney's Division                                        124
13 4   How to Use Relational Divisions                          127
14  Decision, Classification, and Regression Trees                       129
14.1   Casual Caldistics                                        130
14.2   Decision and Correlaion Trees                            132
14.3   Entropy                                                  134
144    Other Algorithms and Software                            1.35
S5  Computer- ntens ve Analysis                                          139
15.1   Bootstraps                                               140
15,2   Subgroups                                                140
15 3   Bayesian Analysis                                        141
15.4   Cl hstering                                              143
15.4.1   Uses for Clustering                                 144
15.4.2   Problems with Clustering                            144
I   Relationship Analytics                                            147
16.    Adjacency List Model for General Graphs                148
16.2   Covering Paths Model for General Graphs                152
16.3   Conclusion and Solution                                155
16,4   Further Reading                                        158
17  Database Architectures                                            159
17] . Parallelism                                            160
S7.1. t  Parallelism Failures                              160
17.2   Hashing                                                162
17.3   Bit Vector Indexes                                     163
T1     Streaming Databases                                    163
1   .5 Further Reading                                        163
18  MDX from a SQL Viewpoint                                          165
18.    MDX SELECT Statemenir                                  166
182    Hierarchical Navigation                                168
18.3   Set Operations                                         169
1 8.4  GENERATE Function                                      169
18.5   Time Series Functions                                  169
8.6    Filtering                                              170
18.7   ORDER ()                                               171
18.8   TOP Functions                                          171
18.9   Numeric Functions                                      171
8. 10  Conclusions                                            172



Library of Congress subject headings for this publication: SQL (Computer program language)OLAP technology