Contents

Expand All · Collapse All

Title and Copyright Information

Preface

• Audience
• Documentation Accessibility
• Related Documents
• Conventions

What's New in Oracle Database?

• Oracle Database 11g Release 2 (11.2) New Features in Data Warehousing
• Oracle Database 11g Release 1 (11.1) New Features in Data Warehousing

Part I Concepts

1 Data Warehousing Concepts

• What is a Data Warehouse?
  • Subject Oriented
  • Integrated
  • Nonvolatile
  • Time Variant
  • Contrasting OLTP and Data Warehousing Environments
• Data Warehouse Architectures
  • Data Warehouse Architecture: Basic
  • Data Warehouse Architecture: with a Staging Area
  • Data Warehouse Architecture: with a Staging Area and Data Marts
• Extracting Information from a Data Warehouse
  • OLAP
    • Full Integration of Multidimensional Technology
    • Ease of Application Development
    • Ease of Administration
    • Security
    • Unmatched Performance and Scalability
    • Reduced Costs
    • Querying Dimensional Objects
    • Efficient Storage and Uniform Availability of Summary Data
    • Tools for Creating and Managing Dimensional Objects
  • Data Mining
    • Oracle Data Mining Functionality
    • Oracle Data Mining Interfaces

Part II Logical Design

2 Logical Design in Data Warehouses

• Logical Versus Physical Design in Data Warehouses
• Creating a Logical Design
• Data Warehousing Schemas
  • Star Schemas
  • Other Data Warehousing Schemas
• Data Warehousing Objects
  • Data Warehousing Objects: Fact Tables
    • Requirements of Fact Tables
  • Data Warehousing Objects: Dimension Tables
    • Hierarchies
    • Typical Dimension Hierarchy
  • Data Warehousing Objects: Unique Identifiers
  • Data Warehousing Objects: Relationships
  • Example of Data Warehousing Objects and Their Relationships

Part III Physical Design

3 Physical Design in Data Warehouses

• Moving from Logical to Physical Design
• Physical Design
  • Physical Design Structures
  • Tablespaces
  • Tables and Partitioned Tables
    • Table Compression
  • Views
  • Integrity Constraints
  • Indexes and Partitioned Indexes
  • Materialized Views
  • Dimensions

4 Hardware and I/O Considerations in Data Warehouses

• Overview of Hardware and I/O Considerations in Data Warehouses
  • Configure I/O for Bandwidth not Capacity
  • Stripe Far and Wide
  • Use Redundancy
  • Test the I/O System Before Building the Database
  • Plan for Growth
• Storage Management

5 Partitioning in Data Warehouses

• Overview of Partitioning in Data Warehouses

6 Parallel Execution in Data Warehouses

• What is Parallel Execution?
• 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

7 Indexes

• Using Bitmap Indexes in Data Warehouses
  • Benefits for Data Warehousing Applications
  • Cardinality
    • How to Determine Candidates for Using a Bitmap Index
  • Bitmap Indexes and Nulls
  • Bitmap Indexes on Partitioned Tables
  • Using Bitmap Join Indexes in Data Warehouses
    • Four Join Models for Bitmap Join Indexes
    • Bitmap Join Index Restrictions and Requirements
• Using B-Tree Indexes in Data Warehouses
• Using Index Compression
• Choosing Between Local Indexes and Global Indexes

8 Integrity Constraints

• Why Integrity Constraints are Useful 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

9 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
    • Materialized View Schema Design Guidelines
  • 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
  • Naming Materialized Views
  • 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
  • Materialized View Logs
    • Using the FORCE Option with Materialized View Logs
    • Materialized View Log Purging
  • Using Oracle Enterprise Manager
  • Using Materialized Views with NLS Parameters
  • Adding Comments to Materialized Views
• 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

10 Advanced Materialized Views

• Partitioning and Materialized Views
  • 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
  • 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

11 Dimensions

• What are Dimensions?
• Creating Dimensions
  • Dropping and Creating Attributes with Columns
  • Multiple Hierarchies
  • Using Normalized Dimension Tables
• Viewing Dimensions
  • Using Oracle Enterprise Manager
  • Using the DESCRIBE_DIMENSION Procedure
• Using Dimensions with Constraints
• Validating Dimensions
• Altering Dimensions
• Deleting Dimensions

Part IV Managing the Data Warehouse Environment

12 Overview of Extraction, Transformation, and Loading

• 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

13 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 Data Capture
    • 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

14 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

15 Loading and Transformation

• Overview of Loading and Transformation in 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

16 Maintaining the Data Warehouse

• 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
• Refreshing Materialized Views
  • Complete Refresh
  • Fast Refresh
  • Partition Change Tracking (PCT) 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 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
  • Fast Refresh with CONSIDER FRESH

17 Change Data Capture

• Overview of Change Data Capture
  • Capturing Change Data Without Change Data Capture
  • Capturing Change Data with Change Data Capture
  • Publish and Subscribe Model
    • Publisher
    • Subscribers
• Change Sources and Modes of Change Data Capture
  • Synchronous Change Data Capture
  • Asynchronous Change Data Capture
    • Asynchronous HotLog Mode
    • Asynchronous Distributed HotLog Mode
    • Asynchronous AutoLog Mode
• Change Sets
  • Valid Combinations of Change Sources and Change Sets
• Change Tables
• Getting Information About the Change Data Capture Environment
• Preparing to Publish Change Data
  • Creating a User to Serve As a Publisher
    • Granting Privileges and Roles to the Publisher
    • Creating a Default Tablespace for the Publisher
    • Password Files and Setting the REMOTE_LOGIN_PASSWORDFILE Parameter
  • Determining the Mode in Which to Capture Data
  • Setting Initialization Parameters for Change Data Capture Publishing
    • Initialization Parameters for Synchronous Publishing
    • Initialization Parameters for Asynchronous HotLog Publishing
    • Initialization Parameters for Asynchronous Distributed HotLog Publishing
    • Initialization Parameters for Asynchronous AutoLog Publishing
  • Adjusting Initialization Parameter Values When Oracle Streams Values Change
  • Tracking Changes to the CDC Environment
• Publishing Change Data
  • Performing Synchronous Publishing
  • Performing Asynchronous HotLog Publishing
  • Performing Asynchronous Distributed HotLog Publishing
  • Performing Asynchronous AutoLog Publishing
• Subscribing to Change Data
• Managing Published Data
  • Managing Asynchronous Change Sources
    • Enabling And Disabling Asynchronous Distributed HotLog Change Sources
  • Managing Asynchronous Change Sets
    • Creating Asynchronous Change Sets with Starting and Ending Dates
    • Enabling and Disabling Asynchronous Change Sets
    • Stopping Capture on DDL for Asynchronous Change Sets
    • Recovering from Errors Returned on Asynchronous Change Sets
  • Managing Synchronous Change Sets
    • Enabling and Disabling Synchronous Change Sets
  • Managing Change Tables
    • Creating Change Tables
    • Understanding Change Table Control Columns
    • Understanding TARGET_COLMAP$ and SOURCE_COLMAP$ Values
    • Using Change Markers
    • Controlling Subscriber Access to Change Tables
    • Purging Change Tables of Unneeded Data
    • Dropping Change Tables
  • Exporting and Importing Change Data Capture Objects Using Oracle Data Pump
    • Restrictions on Using Oracle Data Pump with Change Data Capture
    • Examples of Oracle Data Pump Export and Import Commands
    • Publisher Considerations for Exporting and Importing Change Tables
    • Re-Creating AutoLog Change Data Capture Objects After an Import Operation
  • Impact on Subscriptions When the Publisher Makes Changes
• Considerations for Synchronous Change Data Capture
  • Restriction on Direct-Path INSERT
  • Datatypes and Table Structures Supported for Synchronous Change Data Capture
  • Limitation on Restoring Source Tables from the Recycle Bin
• Considerations for Asynchronous Change Data Capture
  • Asynchronous Change Data Capture and Redo Log Files
  • Asynchronous Change Data Capture and Supplemental Logging
  • Asynchronous Change Data Capture and Oracle Streams Components
  • Datatypes and Table Structures Supported for Asynchronous Change Data Capture
  • Restrictions for NOLOGGING and UNRECOVERABLE Operations
• Implementation and System Configuration
  • Database Configuration Assistant Considerations
  • Summary of Supported Distributed HotLog Configurations and Restrictions
    • Oracle Database Releases for Source and Staging Databases
    • Upgrading a Distributed HotLog Change Source to Oracle Release 11 (11.1 or 11.2)
    • Hardware Platforms and Operating Systems
    • Requirements for Multiple Publishers on the Staging Database
    • Requirements for Database Links

Part V Data Warehouse Performance

18 Basic Query Rewrite

• 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

19 Advanced Query Rewrite

• 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

20 Schema Modeling Techniques

• Schemas in Data Warehouses
• Third Normal Form
  • Optimizing Third Normal Form Queries
• Star Schemas
  • Snowflake 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

21 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
• 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

22 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
  • 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

23 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

24 Advanced Business Intelligence Queries

• Examples of Business Intelligence Queries

Glossary

Index