1/35
Contents
List of Examples
List of Figures
List of Tables
Title and Copyright Information
Preface
Audience
Documentation Accessibility
Related Documents
Conventions
Changes in This Release for Oracle Database Data Warehousing Guide
Changes in Oracle Database 12
c
Release 1 (12.1.0.2)
Changes in Oracle Database 12
c
Release 1 (12.1.0.1)
Part I Data Warehouse - Fundamentals
1
Introduction to Data Warehousing Concepts
What Is a Data Warehouse?
Key Characteristics of a Data Warehouse
Contrasting OLTP and Data Warehousing Environments
Common Data Warehouse Tasks
Data Warehouse Architectures
Data Warehouse Architecture: Basic
Data Warehouse Architecture: with a Staging Area
Data Warehouse Architecture: with a Staging Area and Data Marts
2
Data Warehousing Logical Design
Logical Versus Physical Design in Data Warehouses
Creating a Logical Design
What is a Schema?
About Third Normal Form Schemas
About Normalization
Design Concepts for 3NF Schemas
Identifying Candidate Primary Keys
Foreign Key Relationships and Referential Integrity Constraints
Denormalization
About Star Schemas
Facts and Dimensions
Fact Tables
Dimension Tables
Design Concepts in Star Schemas
Data Grain
Working with Multiple Star Schemas
Conformed Dimensions
Conformed Facts
Surrogate Keys
Degenerate Dimensions
Junk Dimensions
Embedded Hierarchy
Factless Fact Tables
Slowly Changing Dimensions
About Snowflake Schemas
About the Oracle In-Memory Column Store
Benefits of Using the Oracle In-Memory Column Store
Faster Performance for Analytic Queries
Enhanced Join Performance Using Vector Joins
Enhanced Aggregation Using VECTOR GROUP BY Transformations
Using the Oracle In-Memory Column Store
Using Vector Joins to Enhance Join Performance
Automatic Big Table Caching to Improve the Performance of In-Memory Parallel Queries
About In-Memory Aggregation
VECTOR GROUP BY Aggregation and the Oracle In-Memory Column Store
When to Use VECTOR GROUP BY Aggregation
Situations Where VECTOR GROUP BY Aggregation Is Useful
Situations Where VECTOR GROUP BY Aggregation Is Not Advantageous
When Is VECTOR GROUP BY Aggregation Used to Process Analytic Queries?
3
Data Warehousing Physical Design
Moving from Logical to Physical Design
About Physical Design
Physical Design Structures
Tablespaces
About Partitioning
Index Partitioning
Partitioning for Manageability
Partitioning for Performance
Partitioning for Availability
Views
Integrity Constraints
Indexes and Partitioned Indexes
Materialized Views
Dimensions
Hierarchies
Typical Dimension Hierarchy
4
Data Warehousing Optimizations and Techniques
Using Indexes in Data Warehouses
Using Bitmap Indexes in Data Warehouses
Benefits for Data Warehousing Applications
Cardinality
Using Bitmap Join Indexes in Data Warehouses
Using B-Tree Indexes in Data Warehouses
Using Index Compression
Choosing Between Local Indexes and Global Indexes
Using Integrity Constraints in a Data Warehouse
Overview of Constraint States
Typical Data Warehouse Integrity Constraints
UNIQUE Constraints in a Data Warehouse
FOREIGN KEY Constraints in a Data Warehouse
RELY Constraints
NOT NULL Constraints
Integrity Constraints and Parallelism
Integrity Constraints and Partitioning
View Constraints
About Parallel Execution in Data Warehouses
Why Use Parallel Execution?
When to Implement Parallel Execution
When Not to Implement Parallel Execution
Automatic Degree of Parallelism and Statement Queuing
In-Memory Parallel Execution
Optimizing Storage Requirements
Using Data Compression to Improve Storage
Optimizing Star Queries and 3NF Schemas
Optimizing Star Queries
Tuning Star Queries
Using Star Transformation
Star Transformation with a Bitmap Index
Execution Plan for a Star Transformation with a Bitmap Index
Star Transformation with a Bitmap Join Index
Execution Plan for a Star Transformation with a Bitmap Join Index
How Oracle Chooses to Use Star Transformation
Star Transformation Restrictions
Optimizing Third Normal Form Schemas
3NF Schemas: Partitioning
3NF Schemas: Parallel Query Execution
Optimizing Star Queries Using VECTOR GROUP BY Aggregation
Part II Optimizing Data Warehouses
5
Basic Materialized Views
Overview of Data Warehousing with Materialized Views
Materialized Views for Data Warehouses
Materialized Views for Distributed Computing
Materialized Views for Mobile Computing
The Need for Materialized Views
Components of Summary Management
Data Warehousing Terminology
Materialized View Schema Design
Schemas and Dimension Tables
Guidelines for Materialized View Schema Design
Loading Data into Data Warehouses
Overview of Materialized View Management Tasks
Types of Materialized Views
Materialized Views with Aggregates
Requirements for Using Materialized Views with Aggregates
Materialized Views Containing Only Joins
Materialized Join Views FROM Clause Considerations
Nested Materialized Views
Why Use Nested Materialized Views?
Nesting Materialized Views with Joins and Aggregates
Nested Materialized View Usage Guidelines
Restrictions When Using Nested Materialized Views
Creating Materialized Views
Creating Materialized Views with Column Alias Lists
Materialized Views Names
Storage And Table Compression
Build Methods
Enabling Query Rewrite
Query Rewrite Restrictions
Materialized View Restrictions
General Query Rewrite Restrictions
Refresh Options
General Restrictions on Fast Refresh
Restrictions on Fast Refresh on Materialized Views with Joins Only
Restrictions on Fast Refresh on Materialized Views with Aggregates
Restrictions on Fast Refresh on Materialized Views with UNION ALL
Achieving Refresh Goals
Refreshing Nested Materialized Views
ORDER BY Clause
Using Oracle Enterprise Manager
Using Materialized Views with NLS Parameters
Adding Comments to Materialized Views
Creating Materialized View Logs
Using the FORCE Option With Materialized View Logs
Materialized View Log Purging
Registering Existing Materialized Views
Choosing Indexes for Materialized Views
Dropping Materialized Views
Analyzing Materialized View Capabilities
Using the DBMS_MVIEW.EXPLAIN_MVIEW Procedure
DBMS_MVIEW.EXPLAIN_MVIEW Declarations
Using MV_CAPABILITIES_TABLE
MV_CAPABILITIES_TABLE.CAPABILITY_NAME Details
MV_CAPABILITIES_TABLE Column Details
6
Advanced Materialized Views
Partitioning and Materialized Views
About Partition Change Tracking
Partition Key
Join Dependent Expression
Partition Marker
Partial Rewrite
Partitioning a Materialized View
Partitioning a Prebuilt Table
Benefits of Partitioning a Materialized View
Rolling Materialized Views
Materialized Views in Analytic Processing Environments
Materialized Views and Hierarchical Cubes
Benefits of Partitioning Materialized Views
Compressing Materialized Views
Materialized Views with Set Operators
Examples of Materialized Views Using UNION ALL
Materialized Views and Models
Invalidating Materialized Views
Security Issues with Materialized Views
Querying Materialized Views with Virtual Private Database (VPD)
Using Query Rewrite with Virtual Private Database
Restrictions with Materialized Views and Virtual Private Database
Altering Materialized Views
7
Refreshing Materialized Views
Refreshing Materialized Views
Complete Refresh
Fast Refresh
Partition Change Tracking (PCT) Refresh
The Out-of-Place Refresh Option
Types of Out-of-Place Refresh
Restrictions and Considerations with Out-of-Place Refresh
ON COMMIT Refresh
Manual Refresh Using the DBMS_MVIEW Package
Refresh Specific Materialized Views with REFRESH
Refresh All Materialized Views with REFRESH_ALL_MVIEWS
Refresh Dependent Materialized Views with REFRESH_DEPENDENT
Using Job Queues for Refresh
When Fast Refresh is Possible
Recommended Initialization Parameters for Parallelism
Monitoring a Refresh
Checking the Status of a Materialized View
Viewing Partition Freshness
Scheduling Refresh
Tips for Refreshing Materialized Views
Tips for Refreshing Materialized Views with Aggregates
Tips for Refreshing Materialized Views Without Aggregates
Tips for Refreshing Nested Materialized Views
Tips for Fast Refresh with UNION ALL
Tips for Fast Refresh with Commit SCN-Based Materialized View Logs
Tips After Refreshing Materialized Views
Using Materialized Views with Partitioned Tables
Fast Refresh with Partition Change Tracking
PCT Fast Refresh Scenario 1
PCT Fast Refresh Scenario 2
PCT Fast Refresh Scenario 3
Using Partitioning to Improve Data Warehouse Refresh
Refresh Scenarios
Scenarios for Using Partitioning for Refreshing Data Warehouses
Refresh Scenario 1
Refresh Scenario 2
Optimizing DML Operations During Refresh
Implementing an Efficient MERGE Operation
Maintaining Referential Integrity
Purging Data
8
Synchronous Refresh
About Synchronous Refresh
What Is Synchronous Refresh?
Why Use Synchronous Refresh?
Registering Tables and Materialized Views for Synchronous Refresh
Specifying Change Data for Refresh
Synchronous Refresh Preparation and Execution
Materialized View Eligibility Rules and Restrictions for Synchronous Refresh
Synchronous Refresh Restrictions: Partitioning
Synchronous Refresh Restrictions: Refresh Options
Synchronous Refresh Restrictions: Constraints
Synchronous Refresh Restrictions: Tables
Synchronous Refresh Restrictions: Materialized Views
Synchronous Refresh Restrictions: Materialized Views with Aggregates
Using Synchronous Refresh
The Registration Phase
The Synchronous Refresh Phase
The Unregistration Phase
Using Synchronous Refresh Groups
Examples of Common Actions with Synchronous Refresh Groups
Examples of Working with Multiple Synchronous Refresh Groups
Specifying and Preparing Change Data
Working with Partition Operations
Working with Staging Logs
Staging Log Key
Staging Log Rules
Columns Being Updated to NULL
Examples of Working with Staging Logs
Error Handling in Preparing Staging Logs
Troubleshooting Synchronous Refresh Operations
Overview of the Status of Refresh Operations
How PREPARE_REFRESH Sets the STATUS Fields
Examples of PREPARE_REFRESH
How EXECUTE_REFRESH Sets the Status Fields
Examples of EXECUTE_REFRESH
Example of EXECUTE_REFRESH with Constraint Violations
Performing Synchronous Refresh Eligibility Analysis
Using SYNCREF_TABLE
Using a VARRAY
Demo Scripts
Overview of Synchronous Refresh Security Considerations
9
Dimensions
What are Dimensions?
Creating Dimensions
Dropping and Creating Attributes with Columns
Multiple Hierarchies
Using Normalized Dimension Tables
Viewing Dimensions
Viewing Dimensions With Oracle Enterprise Manager
Viewing Dimensions With the DESCRIBE_DIMENSION Procedure
Using Dimensions with Constraints
Validating Dimensions
Altering Dimensions
Deleting Dimensions
10
Basic Query Rewrite for Materialized Views
Overview of Query Rewrite
When Does Oracle Rewrite a Query?
Ensuring that Query Rewrite Takes Effect
Initialization Parameters for Query Rewrite
Controlling Query Rewrite
Accuracy of Query Rewrite
Privileges for Enabling Query Rewrite
Sample Schema and Materialized Views
How to Verify Query Rewrite Occurred
Example of Query Rewrite
11
Advanced Query Rewrite for Materialized Views
How Oracle Rewrites Queries
Cost-Based Optimization
General Query Rewrite Methods
When are Constraints and Dimensions Needed?
Checks Made by Query Rewrite
Join Compatibility Check
Data Sufficiency Check
Grouping Compatibility Check
Aggregate Computability Check
Query Rewrite Using Dimensions
Benefits of Using Dimensions
How to Define Dimensions
Types of Query Rewrite
Text Match Rewrite
Join Back
Aggregate Computability
Aggregate Rollup
Rollup Using a Dimension
When Materialized Views Have Only a Subset of Data
Query Rewrite Definitions
Selection Categories
Examples of Query Rewrite Selection
Handling of the HAVING Clause in Query Rewrite
Query Rewrite When the Materialized View has an IN-List
Partition Change Tracking (PCT) Rewrite
PCT Rewrite Based on Range Partitioned Tables
PCT Rewrite Based on Range-List Partitioned Tables
PCT Rewrite Based on List Partitioned Tables
PCT Rewrite and PMARKER
PCT Rewrite Using Rowid as PMARKER
Multiple Materialized Views
Other Query Rewrite Considerations
Query Rewrite Using Nested Materialized Views
Query Rewrite in the Presence of Inline Views
Query Rewrite Using Remote Tables
Query Rewrite in the Presence of Duplicate Tables
Query Rewrite Using Date Folding
Query Rewrite Using View Constraints
View Constraints Restrictions
Query Rewrite Using Set Operator Materialized Views
UNION ALL Marker
Query Rewrite in the Presence of Grouping Sets
Query Rewrite When Using GROUP BY Extensions
Hint for Queries with Extended GROUP BY
Query Rewrite in the Presence of Window Functions
Query Rewrite and Expression Matching
Query Rewrite Using Partially Stale Materialized Views
Cursor Sharing and Bind Variables
Handling Expressions in Query Rewrite
Advanced Query Rewrite Using Equivalences
Creating Result Cache Materialized Views with Equivalences
Verifying that Query Rewrite has Occurred
Using EXPLAIN PLAN with Query Rewrite
Using the EXPLAIN_REWRITE Procedure with Query Rewrite
DBMS_MVIEW.EXPLAIN_REWRITE Syntax
Using REWRITE_TABLE
Using a Varray
EXPLAIN_REWRITE Benefit Statistics
Support for Query Text Larger than 32KB in EXPLAIN_REWRITE
EXPLAIN_REWRITE and Multiple Materialized Views
EXPLAIN_REWRITE Output
Design Considerations for Improving Query Rewrite Capabilities
Query Rewrite Considerations: Constraints
Query Rewrite Considerations: Dimensions
Query Rewrite Considerations: Outer Joins
Query Rewrite Considerations: Text Match
Query Rewrite Considerations: Aggregates
Query Rewrite Considerations: Grouping Conditions
Query Rewrite Considerations: Expression Matching
Query Rewrite Considerations: Date Folding
Query Rewrite Considerations: Statistics
Query Rewrite Considerations: Hints
REWRITE and NOREWRITE Hints
REWRITE_OR_ERROR Hint
Multiple Materialized View Rewrite Hints
EXPAND_GSET_TO_UNION Hint
12
Attribute Clustering
About Attribute Clustering
Types of Attribute Clustering
Attribute Clustering with Linear Ordering
Attribute Clustering with Interleaved Ordering
Example: Attribute Clustered Table
Guidelines for Using Attribute Clustering
Advantages of Attribute-Clustered Tables
About Defining Attribute Clustering for Tables
About Specifying When Attribute Clustering Must be Performed
Attribute Clustering Operations
Privileges for Attribute-Clustered Tables
Creating Attribute-Clustered Tables with Linear Ordering
Examples of Attribute Clustering with Linear Ordering
Creating Attribute-Clustered Tables with Interleaved Ordering
Examples of Attribute Clustering with Interleaved Ordering
Maintaining Attribute Clustering
Adding Attribute Clustering to an Existing Table
Modifying Attribute Clustering Definitions
Dropping Attribute Clustering for an Existing Table
Using Hints to Control Attribute Clustering for DML Operations
Overriding Table-level Settings for Attribute Clustering During DDL Operations
Clustering Table Data During Online Table Redefinition
Viewing Attribute Clustering Information
Determining if Attribute Clustering is Defined for Tables
Viewing Attribute-Clustering Information for Tables
Viewing Information About the Columns on Which Attribute Clustering is Performed
Viewing Information About Dimensions and Joins on Which Attribute Clustering is Performed
13
Using Zone Maps
About Zone Maps
Difference Between Zone Maps and Indexes
Zone Maps and Attribute Clustering
Types of Zone Maps
Benefits of Zone Maps
Scenarios Which Benefit from Zone Maps
About Maintaining Zone Maps
Operations that Require Zone Map Maintenance
Scenarios in Which Zone Maps are Automatically Refreshed
Zone Map Operations
Privileges Required for Zone Maps
Creating Zone Maps
Creating Zone Maps with Attribute Clustering
Creating Zone Maps Independent of Attribute Clustering
Modifying Zone Maps
Dropping Zone Maps
Compiling Zone Maps
Controlling the Use of Zone Maps
Controlling Zone Map Usage for Entire SQL Workloads
Controlling Zone Map Usage for Specific SQL Statements
Maintaining Zone Maps
Zone Map Maintenance Considerations
Refresh and Staleness of Zone Maps
About Staleness of Zone Maps
About Refreshing Zone Maps
Refreshing Zone Maps
Refreshing Zone Maps Using the ALTER MATERIALIZED ZONEMAP Command
Refreshing Zone Maps Using the DBMS_MVIEW Package
Performing Pruning Using Zone Maps
How Oracle Database Performs Pruning Using Zone Maps
Pruning Tables Using Zone Maps
Pruning Partitioned Tables Using Zone Maps and Attribute Clustering
Examples: Performing Pruning with Zone Maps and Attribute Clustering
Example: Partitions and Table Scan Pruning
Example: Zone Map Join Pruning
Viewing Zone Map Information
Viewing Details of Zone Maps in the Database
Viewing the Measures of a Zone Map
Part III Data Movement/ETL
14
Data Movement/ETL Overview
Overview of ETL in Data Warehouses
ETL Basics in Data Warehousing
Extraction of Data
Transportation of Data
ETL Tools for Data Warehouses
Daily Operations in Data Warehouses
Evolution of the Data Warehouse
15
Extraction in Data Warehouses
Overview of Extraction in Data Warehouses
Introduction to Extraction Methods in Data Warehouses
Logical Extraction Methods
Full Extraction
Incremental Extraction
Physical Extraction Methods
Online Extraction
Offline Extraction
Change Tracking Methods
Timestamps
Partitioning
Triggers
Data Warehousing Extraction Examples
Extraction Using Data Files
Extracting into Flat Files Using SQL*Plus
Extracting into Flat Files Using OCI or Pro*C Programs
Exporting into Export Files Using the Export Utility
Extracting into Export Files Using External Tables
Extraction Through Distributed Operations
16
Transportation in Data Warehouses
Overview of Transportation in Data Warehouses
Introduction to Transportation Mechanisms in Data Warehouses
Transportation Using Flat Files
Transportation Through Distributed Operations
Transportation Using Transportable Tablespaces
Transportable Tablespaces Example
Other Uses of Transportable Tablespaces
17
Loading and Transformation in Data Warehouses
Overview of Loading and Transformation in Data Warehouses
Data Warehouses: Transformation Flow
Multistage Data Transformation
Pipelined Data Transformation
Staging Area
Loading Mechanisms
Loading a Data Warehouse with SQL*Loader
Loading a Data Warehouse with External Tables
Loading a Data Warehouse with OCI and Direct-Path APIs
Loading a Data Warehouse with Export/Import
Transformation Mechanisms
Transforming Data Using SQL
CREATE TABLE ... AS SELECT And INSERT /*+APPEND*/ AS SELECT
Transforming Data Using UPDATE
Transforming Data Using MERGE
Transforming Data Using Multitable INSERT
Transforming Data Using PL/SQL
Transforming Data Using Table Functions
What is a Table Function?
Error Logging and Handling Mechanisms
Business Rule Violations
Data Rule Violations (Data Errors)
Handling Data Errors in PL/SQL
Handling Data Errors with an Error Logging Table
Loading and Transformation Scenarios
Key Lookup Scenario
Business Rule Violation Scenario
Data Error Scenarios
Pivoting Scenarios
Part IV Relational Analytics
18
SQL for Analysis and Reporting
Overview of SQL for Analysis and Reporting
Ranking, Windowing, and Reporting Functions
Ranking
RANK and DENSE_RANK Functions
Bottom N Ranking
CUME_DIST Function
PERCENT_RANK Function
NTILE Function
ROW_NUMBER Function
Windowing
Treatment of NULLs as Input to Window Functions
Windowing Functions with Logical Offset
Centered Aggregate Function
Windowing Aggregate Functions in the Presence of Duplicates
Varying Window Size for Each Row
Windowing Aggregate Functions with Physical Offsets
Reporting
RATIO_TO_REPORT Function
LAG/LEAD
LAG/LEAD Syntax
FIRST_VALUE, LAST_VALUE, and NTH_VALUE Functions
FIRST_VALUE and LAST_VALUE Functions
NTH_VALUE Function
Advanced Aggregates for Analysis
LISTAGG Function
LISTAGG as Aggregate
LISTAGG as Reporting Aggregate
FIRST/LAST Functions
FIRST/LAST As Regular Aggregates
FIRST/LAST As Reporting Aggregates
Inverse Percentile
Normal Aggregate Syntax
Inverse Percentile Example Basis
As Reporting Aggregates
Restrictions
Hypothetical Rank
Linear Regression
REGR_COUNT Function
REGR_AVGY and REGR_AVGX Functions
REGR_SLOPE and REGR_INTERCEPT Functions
REGR_R2 Function
REGR_SXX, REGR_SYY, and REGR_SXY Functions
Linear Regression Statistics Examples
Sample Linear Regression Calculation
Statistical Aggregates
Descriptive Statistics
Hypothesis Testing - Parametric Tests
Crosstab Statistics
Hypothesis Testing - Non-Parametric Tests
Non-Parametric Correlation
User-Defined Aggregates
Pivoting Operations
Example: Pivoting
Pivoting on Multiple Columns
Pivoting: Multiple Aggregates
Distinguishing PIVOT-Generated Nulls from Nulls in Source Data
Unpivoting Operations
Wildcard and Subquery Pivoting with XML Operations
Data Densification for Reporting
Partition Join Syntax
Sample of Sparse Data
Filling Gaps in Data
Filling Gaps in Two Dimensions
Filling Gaps in an Inventory Table
Computing Data Values to Fill Gaps
Time Series Calculations on Densified Data
Period-to-Period Comparison for One Time Level: Example
Period-to-Period Comparison for Multiple Time Levels: Example
Create the Hierarchical Cube View
Create the View edge_time, which is a Complete Set of Date Values
Create the Materialized View mv_prod_time to Support Faster Performance
Create the Comparison Query
Creating a Custom Member in a Dimension: Example
Miscellaneous Analysis and Reporting Capabilities
WIDTH_BUCKET Function
WIDTH_BUCKET Syntax
Linear Algebra
CASE Expressions
Creating Histograms
Frequent Itemsets
Limiting SQL Rows
SQL Row Limiting Restrictions and Considerations
19
SQL for Aggregation in Data Warehouses
Overview of SQL for Aggregation in Data Warehouses
Analyzing Across Multiple Dimensions
Optimized Performance
An Aggregate Scenario
Interpreting NULLs in Examples
ROLLUP Extension to GROUP BY
When to Use ROLLUP
ROLLUP Syntax
Partial Rollup
CUBE Extension to GROUP BY
When to Use CUBE
CUBE Syntax
Partial CUBE
Calculating Subtotals Without CUBE
GROUPING Functions
GROUPING Function
When to Use GROUPING
GROUPING_ID Function
GROUP_ID Function
GROUPING SETS Expression
GROUPING SETS Syntax
Composite Columns
Concatenated Groupings
Concatenated Groupings and Hierarchical Data Cubes
Considerations when Using Aggregation
Hierarchy Handling in ROLLUP and CUBE
Column Capacity in ROLLUP and CUBE
HAVING Clause Used with GROUP BY Extensions
ORDER BY Clause Used with GROUP BY Extensions
Using Other Aggregate Functions with ROLLUP and CUBE
In-Memory Aggregation
Computation Using the WITH Clause
Working with Hierarchical Cubes in SQL
Specifying Hierarchical Cubes in SQL
Querying Hierarchical Cubes in SQL
SQL for Creating Materialized Views to Store Hierarchical Cubes
Examples of Hierarchical Cube Materialized Views
20
SQL for Pattern Matching
Overview of Pattern Matching
Why Use Pattern Matching?
How Data is Processed in Pattern Matching
Pattern Matching Special Capabilities
Basic Topics in Pattern Matching
Basic Examples of Pattern Matching
Tasks and Keywords in Pattern Matching
PARTITION BY: Logically Dividing the Rows into Groups
ORDER BY: Logically Ordering the Rows in a Partition
[ONE ROW | ALL ROWS] PER MATCH: Choosing Summaries or Details for Each Match
MEASURES: Defining Calculations for Export from the Pattern Matching
PATTERN: Defining the Row Pattern That Will be Matched
DEFINE: Defining Primary Pattern Variables
AFTER MATCH SKIP: Restarting the Matching Process After a Match is Found
MATCH_NUMBER: Finding Which Rows are Members of Which Match
CLASSIFIER: Finding Which Pattern Variable Applies to Which Rows
Pattern Matching Syntax
Pattern Matching Details
PARTITION BY: Logically Dividing the Rows into Groups
ORDER BY: Logically Ordering the Rows in a Partition
[ONE ROW | ALL ROWS] PER MATCH: Choosing Summaries or Details for Each Match
MEASURES: Defining Calculations for Use in the Query
PATTERN: Defining the Row Pattern to Be Matched
Reluctant Versus Greedy Quantifier
Operator Precedence
SUBSET: Defining Union Row Pattern Variables
DEFINE: Defining Primary Pattern Variables
AFTER MATCH SKIP: Defining Where to Restart the Matching Process After a Match Is Found
Expressions in MEASURES and DEFINE
MATCH_NUMBER: Finding Which Rows Are in Which Match
CLASSIFIER: Finding Which Pattern Variable Applies to Which Rows
Row Pattern Column References
Aggregates
Row Pattern Navigation Operations
Running Versus Final Semantics and Keywords
Row Pattern Output
Correlation Name and Row Pattern Output
Advanced Topics in Pattern Matching
Nesting FIRST and LAST Within PREV and NEXT
Handling Empty Matches or Unmatched Rows
Handling Empty Matches
Handling Unmatched Rows
How to Exclude Portions of the Pattern from the Output
How to Express All Permutations
Rules and Restrictions in Pattern Matching
Input Table Requirements
Prohibited Nesting in the MATCH_RECOGNIZE Clause
Concatenated MATCH_RECOGNIZE Clause
Aggregate Restrictions
Examples of Pattern Matching
Pattern Matching Examples: Stock Market
Pattern Matching Examples: Security Log Analysis
Pattern Matching Examples: Sessionization
Pattern Matching Example: Financial Tracking
21
SQL for Modeling
Overview of SQL Modeling
How Data is Processed in a SQL Model
Why Use SQL Modeling?
SQL Modeling Capabilities
Basic Topics in SQL Modeling
Base Schema
MODEL Clause Syntax
Keywords in SQL Modeling
Assigning Values and Null Handling
Calculation Definition
Cell Referencing
Symbolic Dimension References
Positional Dimension References
Rules
Single Cell References
Multi-Cell References on the Right Side
Multi-Cell References on the Left Side
Use of the CV Function
Use of the ANY Wildcard
Nested Cell References
Order of Evaluation of Rules
Global and Local Keywords for Rules
UPDATE, UPSERT, and UPSERT ALL Behavior
UPDATE Behavior
UPSERT Behavior
UPSERT ALL Behavior
Treatment of NULLs and Missing Cells
Distinguishing Missing Cells from NULLs
Use Defaults for Missing Cells and NULLs
Using NULLs in a Cell Reference
Reference Models
Advanced Topics in SQL Modeling
FOR Loops
Evaluation of Formulas with FOR Loops
Iterative Models
Rule Dependency in AUTOMATIC ORDER Models
Ordered Rules
Analytic Functions
Unique Dimensions Versus Unique Single References
Rules and Restrictions when Using SQL for Modeling
Performance Considerations with SQL Modeling
Parallel Execution
Aggregate Computation
Using EXPLAIN PLAN to Understand Model Queries
Using ORDERED FAST: Example
Using ORDERED: Example
Using ACYCLIC FAST: Example
Using ACYCLIC: Example
Using CYCLIC: Example
Examples of SQL Modeling
SQL Modeling Example 1: Calculating Sales Differences
SQL Modeling Example 2: Calculating Percentage Change
SQL Modeling Example 3: Calculating Net Present Value
SQL Modeling Example 4: Calculating Using Simultaneous Equations
SQL Modeling Example 5: Calculating Using Regression
SQL Modeling Example 6: Calculating Mortgage Amortization
22
Advanced Analytical SQL
Examples of Business Intelligence Queries
Example 1: Percent Change in Market Share of Products in a Calculated Set
Example 2: Sales Projection that Fills in Missing Data
Example 3: Customer Analysis by Grouping Customers into Buckets
Example 4: Frequent Itemsets
Glossary
Index
Scripting on this page enhances content navigation, but does not change the content in any way.