Joe Celko's Analytics and OLAP in SQL (eBook)

Joe Celko (Autor)

eBook Download: PDF

2010 | 1. Auflage
208 Seiten
Elsevier Science (Verlag)
978-0-08-049593-4 (ISBN)

Infos

Before SQL programmers could begin working with OLTP (On-Line Transaction Processing) systems, they had to unlearn procedural, record-oriented programming before moving on to SQL's declarative, set-oriented programming. This book covers the next step in your growth. OLAP (On-Line Analytical Processing), Data Warehousing and Analytics involve seeing data in the aggregate and over time, not as single transactions. Once more it is time to unlearn what you were previously taught.

This book is not an in-depth look at particular subjects, but an overview of many subjects that will give the working RDBMS programmers a map of the terra incognita they will face-if they want to grow.

* Expert advice from a noted SQL authority and award-winning columnist, who has given ten years of service to the ANSI SQL standards committee and many more years of dependable help to readers of online forums.

* First book that teaches what SQL programmers need in order to successfully make the transition from transactional systems (OLTP) into the world of data warehouse data and OLAP.

* Offers real-world insights and lots of practical examples.

* Covers the OLAP extensions in SQL-99, ETL tools, OLAP features supported in DBMSs, other query tools, simple reports, and statistical software.

---

Joe Celko's Analytics and OLAP in SQL is the first book that teaches what SQL programmers need in order to successfully make the transition from On-Line Transaction Processing (OLTP) systems into the world of On-Line Analytical Processing (OLAP). This book is not an in-depth look at particular subjects, but an overview of many subjects that will give the working RDBMS programmers a map of the terra incognita they will face - if they want to grow. It contains expert advice from a noted SQL authority and award-winning columnist, who has given ten years of service to the ANSI SQL standards committee and many more years of dependable help to readers of online forums. It offers real-world insights and lots of practical examples. It covers the OLAP extensions in SQL-99; ETL tools, OLAP features supported in DBMSs, other query tools, simple reports, and statistical software. This book is ideal for experienced SQL programmers who have worked with OLTP systems who need to learn techniques and even some tricks that they can use in an OLAP situation. - Expert advice from a noted SQL authority and award-winning columnist, who has given ten years of service to the ANSI SQL standards committee and many more years of dependable help to readers of online forums- First book that teaches what SQL programmers need in order to successfully make the transition from transactional systems (OLTP) into the world of data warehouse data and OLAP- Offers real-world insights and lots of practical examples- Covers the OLAP extensions in SQL-99; ETL tools, OLAP features supported in DBMSs, other query tools, simple reports, and statistical software

Inhalt

Front Cover 1
Joe Celko's Analytics and OLAP in SOL 8
Copyright Page 9
Contents 12
Introduction 18
Beyond Queries 18
Some of the Differences between OLAP and OLTP 19
Corrections and Additionsx 21
Chapter 1. Basic Reports and History 22
1.1 Cases 23
1.2 Control-Break Reports 24
1.3 Cross-Tabulation Reports 24
1.4 Presentation Graphics 25
1.5 Local Databases 25
Chapter 2. Cross-Tabulations 28
2.1 Crosstabs by Cross-Join 32
2.2 Crosstabs by Outer Joins 33
2.3 Crosstabs by Subquery 34
2.4 Crosstabs by CASE Expression 35
2.5 Crosstabs with Row and Column Summaries 35
Chapter 3. Dimension Tables 38
3.1 Star and Snowflake Schemas 38
3.2 Kinds of Dimensions 43
3.3 Calendars and Temporal Data 47
3.4 Helper Tables 50
3.5 Surrogate Keys 51
3.6 Degenerate Dimensions 53
Chapter 4. Data Migration and Scrubbing 54
4.1 Pumping Data 55
4.2 Verification and Validation 56
4.3 Extract, Transform, and Load (ETL) 57
4.4 Databases Also Evolved 57
4.5 Data Warehouses 58
4.6 Extract, Load, and then Transform (E-L-T) 59
4.7 Scrubbing Data with Non-First-Normal-Form (1NF) Tables 61
Chapter 5. MERGE Statement 72
5.1 Simple MERGE Statement 73
5.2 Merging without the MERGE Statement 74
5.3 TRIGGERs and MERGE 75
5.4 Self-Referencing MERGE 76
Chapter 6. OLAP Basics 78
6.1 Cubes 78
6.2 Dr. Codd’s OLAP Rules 79
6.3 MOLAP 83
6.4 ROLAP 84
6.5 HOLAP 84
6.6 OLAP Query Languages 84
Chapter 7. GROUPING Operators 86
7.1 GROUP BY GROUPING SET 86
7.2 ROLLUP 87
7.3 CUBES 89
7.4 Notes about Usage 90
Chapter 8. OLAP Operators in SQL 92
8.1 OLAP Functionality 93
8.2 NTILE() 99
8.3 Nesting OLAP functions 101
8.4 Sample Queries 101
Chapter 9. Sparseness in Cubes 104
9.1 Hypercube 105
9.2 Dimensional Hierarchies 105
9.3 Drilling and Slicing 107
Chapter 10. Data Quality 110
10.1 Checking Columns for Value Counts 111
10.2 Finding Rules in a Schema 112
10.3 Feedback for Data Quality 118
10.4 Further Reading 118
Chapter 11. Correlation 120
11.1 Causes and Correlation 122
11.2 Linear Correlation 123
11.3 Nesting Functions 126
11.4 Further Reading 127
Chapter 12. Data Distributions 128
12.1 Flat Distribution 128
12.2 Zipfian Distribution 129
12.3 Gaussian, Normal, or Bell Curve 130
12.4 Poisson Distribution 132
12.5 Logistic or “S” Distribution 133
12.6 Pareto Distribution 133
12.7 Distribution Discovery 134
12.8 References 136
Chapter 13. Market-Basket Analysis 138
13.1 Simple Example of a Market Basket 138
13.2 Relational Division 140
13.3 Romney’s Division 145
13.4 How to Use Relational Divisions 148
Chapter 14. Decision, Classification, and Regression Trees 150
14.1 Casual Caldistics 151
14.2 Decision and Correlation Trees 153
14.3 Entropy 155
14.4 Other Algorithms and Software 156
Chapter 15. Computer-Intensive Analysis 160
15.1 Bootstraps 161
15.2 Subgroups 161
15.3 Bayesian Analysis 162
15.4 Clustering 164
Chapter 16. Relationship Analytics 168
16.1 Adjacency List Model for General Graphs 169
16.2 Covering Paths Model for General Graphs 173
16.3 Conclusion and Solution 176
16.4 Further Reading 179
Chapter 17. Database Architectures 180
17.1 Parallelism 181
17.2 Hashing 183
17.3 Bit Vector Indexes 184
17.4 Streaming Databases 184
17.5 Further Reading 184
Chapter 18. MDX from a SQL Viewpoint 186
18.1 MDX SELECT Statement 187
18.2 Hierarchical Navigation 189
18.3 Set Operations 190
18.4 GENERATE Function 190
18.5 Time Series Functions 190
18.6 Filtering 191
18.7 ORDER () 192
18.8 TOP Functions 192
18.9 Numeric Functions 192
18.10 Conclusions 193
Index 194
About the Author 204

Produkt-Details

• Erscheint lt. Verlag: 26.7.2010
• Sprache: englisch
• Themenwelt: Sachbuch/Ratgeber
• Themenwelt: Informatik ► Datenbanken ► Data Warehouse / Data Mining
• Themenwelt: Mathematik / Informatik ► Informatik ► Programmiersprachen / -werkzeuge
• Themenwelt: Mathematik / Informatik ► Informatik ► Software Entwicklung
• ISBN-10: 0-08-049593-1 / 0080495931
• ISBN-13: 978-0-08-049593-4 / 9780080495934