Book description
This IBM Redbooks publication deals with exploiting DB2 UDB’s materialized views (also known as ASTs/MQTs), statistics, analytic, and OLAP functions in e-business applications to achieve superior performance and scalability. This book is aimed at a target audience of DB2 UDB application developers, database administrators (DBAs), and independent software vendors (ISVs).
We provide an overview of DB2 UDB’s materialized views implementation, as well as guidelines for creating and tuning them for optimal performance.
We introduce key statistics, analytic, and OLAP functions, and describe their corresponding implementation in DB2 UDB with usage examples.
Finally, we describe typical business level queries that can be answered using DB2 UDB’s statistics, analytic, and OLAP functions. These business queries are categorized by industry, and describe the steps involved in resolving the query, with sample SQL and visualization of results.
Table of contents
- Figures
- Tables
- Examples
- Notices
- Preface
- Chapter 1: Business Intelligence overview
-
Chapter 2: DB2 UDB’s materialized views
- Materialized view overview
- Materialized view CREATE considerations
- Materialized view maintenance considerations
- Loading base tables (LOAD utility)
- Materialized view ALTER considerations
- Materialized view DROP considerations
- Materialized view matching considerations
-
Materialized view design considerations
- Step 1: Collect queries & prioritize
- Step 2: Generalize local predicates to GROUP BY
- Step 3: Create the materialized view
- Step 4: Estimate materialized view size
- Step 5: Verify query routes to “empty” the materialized view
- Step 6: Consolidate materialized views
- Step 7: Introduce cost issues into materialized view routing
- Step 8: Estimate performance gains
- Step 9: Load the materialized views with production data
- Generalizing local predicates application example
- Materialized view tuning considerations
- Refresh optimization
- Materialized view limitations
- Replicated tables in nodegroups
-
Chapter 3: DB2 UDB’s statistics, analytic, and OLAP functions
- DB2 UDB’s statistics, analytic, and OLAP functions
- Statistics and analytic functions
-
OLAP functions
- Ranking, numbering and aggregation functions (1/2)
- Ranking, numbering and aggregation functions (2/2)
- GROUPING capabilities ROLLUP & CUBE
- Ranking, numbering, aggregation examples (1/3)
- Ranking, numbering, aggregation examples (2/3)
- Ranking, numbering, aggregation examples (3/3)
- GROUPING, GROUP BY, ROLLUP and CUBE examples (1/3)
- GROUPING, GROUP BY, ROLLUP and CUBE examples (2/3)
- GROUPING, GROUP BY, ROLLUP and CUBE examples (3/3)
-
Chapter 4: Statistics, analytic, OLAP functions in business scenarios
- Introduction
-
Retail
- Present annual sales by region and city
- Provide total quarterly and cumulative sales revenues by year
- List the top 5 sales persons by region this year
- Compare and rank the sales results by state and country
- Determine relationships between product purchases
- Determine the most profitable items and where they are sold
- Identify store sales revenues noticeably different from average
-
Finance
- Identify the most profitable customers
- Identify the profile of transactions concluded recently
- Identify target groups for a campaign
- Evaluate effectiveness of a marketing campaign (1/2)
- Evaluate effectiveness of a marketing campaign (2/2)
- Identify potential fraud situations for investigation
- Plot monthly stock prices movement with percentage change
- Plot the average weekly stock price in September
- Project growth rates of Web hits for capacity planning purposes
- Relate sales revenues to advertising budget expenditures
- Sports
- Appendix A: Introduction to statistics and analytic concepts
- Appendix B: Tables used in the examples
- Appendix C: Materialized view syntax elements
- Related publications
- Index (1/2)
- Index (2/2)
- Back cover
Product information
- Title: DB2 UDB's High-Function Business Intelligence in e-business
- Author(s):
- Release date: September 2002
- Publisher(s): IBM Redbooks
- ISBN: None
You might also like
book
Testen von Data-Warehouse- und Business-Intelligence-Systemen
Business-Intelligence- und Data-Warehouse-Projekte sind anders. Entsprechend andersartig sind auch die Testverfahren und Methoden, wobei vieles aus …
book
Security für Data-Warehouse- und Business-Intelligence-Systeme
Mit der steigenden Bedeutung der systematischen Datenanalyse – Stichwörter sind hier Big Data, Cloud-basierte Analysen, Mobile …
book
BIRT: A Field Guide to Reporting
The world-wide developer community has downloaded over three million copies of BIRT (Business Intelligence and Reporting …
book
Contrast Data Mining
This work collects recent results from this specialized area of data mining that have previously been …