Joe Celko's Analytics and OLAP in SQL
Regular price
19.750 KD
inc. VAT
Couldn't load pickup availability
Table of contents
- Contentsxi
- Introductionxvii
- Beyond Queriesxvii
- Some of the Differences between OLAP and OLTPxviii
- Corrections and Additionsxxx
- Chapter 1. Basic Reports and History1
- 1.1 Cases2
- 1.2 Control-Break Reports3
- 1.3 Cross-Tabulation Reports3
- 1.4 Presentation Graphics4
- 1.5 Local Databases4
- Chapter 2. Cross-Tabulations7
- 2.1 Crosstabs by Cross-Join11
- 2.2 Crosstabs by Outer Joins12
- 2.3 Crosstabs by Subquery13
- 2.4 Crosstabs by CASE Expression14
- 2.5 Crosstabs with Row and Column Summaries14
- Chapter 3. Dimension Tables17
- 3.1 Star and Snowflake Schemas17
- 3.2 Kinds of Dimensions22
- 3.3 Calendars and Temporal Data26
- 3.4 Helper Tables29
- 3.5 Surrogate Keys30
- 3.6 Degenerate Dimensions32
- Chapter 4. Data Migration and Scrubbing33
- 4.1 Pumping Data34
- 4.2 Verification and Validation35
- 4.3 Extract, Transform, and Load (ETL)36
- 4.4 Databases Also Evolved36
- 4.5 Data Warehouses37
- 4.6 Extract, Load, and then Transform (E-L-T)38
- 4.7 Scrubbing Data with Non-First-Normal-Form (1NF) Tables40
- Chapter 5. MERGE Statement51
- 5.1 Simple MERGE Statement52
- 5.2 Merging without the MERGE Statement53
- 5.3 TRIGGERs and MERGE54
- 5.4 Self-Referencing MERGE55
- Chapter 6. OLAP Basics57
- 6.1 Cubes57
- 6.2 Dr. Codd’s OLAP Rules58
- 6.3 MOLAP62
- 6.4 ROLAP63
- 6.5 HOLAP63
- 6.6 OLAP Query Languages63
- Chapter 7. GROUPING Operators65
- 7.1 GROUP BY GROUPING SET65
- 7.2 ROLLUP66
- 7.3 CUBES68
- 7.4 Notes about Usage69
- Chapter 8. OLAP Operators in SQL71
- 8.1 OLAP Functionality72
- 8.2 NTILE()78
- 8.3 Nesting OLAP functions80
- 8.4 Sample Queries80
- Chapter 9. Sparseness in Cubes83
- 9.1 Hypercube84
- 9.2 Dimensional Hierarchies84
- 9.3 Drilling and Slicing86
- Chapter 10. Data Quality89
- 10.1 Checking Columns for Value Counts90
- 10.2 Finding Rules in a Schema91
- 10.3 Feedback for Data Quality97
- 10.4 Further Reading97
- Chapter 11. Correlation99
- 11.1 Causes and Correlation101
- 11.2 Linear Correlation102
- 11.3 Nesting Functions105
- 11.4 Further Reading106
- Chapter 12. Data Distributions107
- 12.1 Flat Distribution107
- 12.2 Zipfian Distribution108
- 12.3 Gaussian, Normal, or Bell Curve109
- 12.4 Poisson Distribution111
- 12.5 Logistic or SŽ Distribution112
- 12.6 Pareto Distribution112
- 12.7 Distribution Discovery113
- 12.8 References115
- Chapter 13. Market-Basket Analysis117
- 13.1 Simple Example of a Market Basket117
- 13.2 Relational Division119
- 13.3 Romney’s Division124
- 13.4 How to Use Relational Divisions127
- Chapter 14. Decision, Classification, and Regression Trees129
- 14.1 Casual Caldistics130
- 14.2 Decision and Correlation Trees132
- 14.3 Entropy134
- 14.4 Other Algorithms and Software135
- Chapter 15. Computer-Intensive Analysis139
- 15.1 Bootstraps140
- 15.2 Subgroups140
- 15.3 Bayesian Analysis141
- 15.4 Clustering143
- Chapter 16. Relationship Analytics147
- 16.1 Adjacency List Model for General Graphs148
- 16.2 Covering Paths Model for General Graphs152
- 16.3 Conclusion and Solution155
- 16.4 Further Reading158
- Chapter 17. Database Architectures159
- 17.1 Parallelism160
- 17.2 Hashing162
- 17.3 Bit Vector Indexes163
- 17.4 Streaming Databases163
- 17.5 Further Reading163
- Chapter 18. MDX from a SQL Viewpoint165
- 18.1 MDX SELECT Statement166
- 18.2 Hierarchical Navigation168
- 18.3 Set Operations169
- 18.4 GENERATE Function169
- 18.5 Time Series Functions169
- 18.6 Filtering170
- 18.7 ORDER ()171
- 18.8 TOP Functions171
- 18.9 Numeric Functions171
- 18.10 Conclusions172
- Index173
- About the Author183
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.
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.