Contents
- Audience
- Organization
- Related Documentation
- Conventions
- Documentation Accessibility
- Oracle9i Release 2 (9.2) New Features in Oracle OLAP
- Why OLAP?
- Analytical Processing Answers Business Questions
- Types of OLAP Applications
- Analytical Reporting
- Predictive Analysis
- The Oracle9i Integrated Relational-Multidimensional Database
- Components of Oracle OLAP
- Calculation Engine
- Analytic Workspace
- OLAP DML
- SQL Table Functions
- OLAP API
- OLAP Catalog
- Applications Access to Oracle OLAP
- What Is the OLAP DML?
- Extensive Analytic Capabilities
- Features of the Multidimensional Model
- Basic Categories of OLAP DML Commands
- Aggregation
- Allocation
- Data Selection
- Data Exchange
- File Reading and Writing
- Financial Operations
- Forecasts and Regressions
- Models
- Numeric Computations
- Statistical Operations
- Text Manipulation
- Time Series Manipulation
- Methods of Executing OLAP DML Commands
- OLAP Worksheet: The OLAP DML Development Tool
- Procedure: Open OLAP Worksheet
- Embedding OLAP DML Commands in Programs
- Building SQL-Based OLAP Applications
- Methods of Accessing Multidimensional Data From SQL
- Embedding OLAP DML Commands in SQL
- Building Analytical Java Applications
- About Java
- Deploying Java Applications
- The Java Solution for OLAP
- Oracle Java Development Environment
- Introducing the BI Beans
- Thick-Client Configuration
- Thin-Client Configuration
- Metadata
- Runtime Repository
- Navigation
- Formatting
- Graphs
- Crosstabs
- Tables
- OLAP BI Beans
- Wizards
- Understanding the OLAP API
- How the OLAP API Accesses Multidimensional Data
- Intelligent Caching
- Calculation Capabilities
- Overview
- Preparing a Database for the OLAP API
- Types of Data Stored in a Data Warehouse
- Historical Data
- Derived Data
- Metadata
- Data Structures in Relational and Multidimensional Data Stores
- Relational Table Storage
- Multidimensional Table Storage
- Temporary and Persistent Analytic Workspaces
- About Star, Snowflake, Parent-Child, and Multidimensional Schemas
- Choosing a Schema for Your Data
- OLAP Metadata Model
- Mapping Data Objects to Metadata Objects
- Measures
- Dimensions
- Time Dimensions
- Hierarchical Dimensions
- Attributes
- Level Attributes
- Dimension Attributes
- Cubes
- Measure Folders
- Overview of the OLAP Catalog
- Tools for Creating OLAP Metadata
- OLAP Catalog Components
- Logical Steps for Creating OLAP Metadata
- Accessing the OLAP Catalog
- Data Warehouse Requirements
- Basic Star or Snowflake Schema
- Dimension Tables with Complex Hierarchies
- Solved and Unsolved Fact Data
- Multidimensional Data
- Parent-Child Dimensions
- Creating Metadata Using Oracle Enterprise Manager
- Procedure: Accessing OLAP Management
- Defining Metadata for Dimension Tables
- Defining Metadata for Fact Tables
- Viewing a Cube's Data
- Procedure: Viewing a Cube's Data
- Creating Metadata Using PL/SQL
- Views of OLAP Catalog Metadata
- CWM2 Packages for Creating OLAP Dimensions
- CWM2 Packages for Creating Cubes
- CWM2 Package for Mapping Metadata
- CWM2 Package for Creating Analytic Workspaces
- CWM2 Package for Creating Level-Based Dimension Tables
- CWM2 Packages for Classification and Validation
- Administration Overview
- Initialization Parameters for Oracle OLAP
- OLAP_PAGE_POOL_SIZE
- Initialization Parameters for the OLAP API
- Creating Tablespaces for Analytic Workspaces
- Creating a Tablespace for Rollbacks
- Creating a Temporary Tablespace
- Creating Tablespaces for Analytic Workspaces
- Querying the Size of an Analytic Workspace
- Setting Up User Names
- Controlling Access to External Files
- Creating a Directory Alias
- Granting Access Rights to a Directory Alias
- Example: Creating and Using a Directory Alias
- Understanding Data Storage
- User-Owned Tables
- System Tables
- Monitoring Performance
- System Tables Referenced by OLAP Performance Views
- Summary of OLAP Performance Views
- V$AW_CALC
- V$AW_OLAP
- V$AW_SESSION_INFO
- Overview
- Summary of OLAP_API_SESSION_INIT Subprograms
- ADD_ALTER_SESSION Procedure
- Syntax
- Parameters
- Exceptions
- Examples
- DELETE_ALTER_SESSION Procedure
- Syntax
- Parameters
- Exceptions
- Examples
- CLEAN_ALTER_SESSION Procedure
- Syntax
- Examples
- ALL_OLAP_ALTER_SESSION View
- Choosing to Use an Analytic Workspace
- Relational and Multidimensional Data Models
- Advantages of OLAP
- Functional Summary
- Procedure: Create the OLAP Catalog Metadata
- Procedure: Create the Analytic Workspace Cube
- Procedure: Create SQL Access to the Analytic Workspace
- Column Structure of Dimension Views
- Sample Dimension View
- Grouping ID Column
- Column Structure of Fact Views
- Choosing a Summary Management Strategy
- Summary Management with Analytic Workspaces
- Summary Management with Materialized Views
- About Materialized Views
- Materialized View Formats
- Grouping Sets
- Concatenated Rollup
- Materialized Views and OLAP Metadata
- Dimension Materialized Views
- Creating Dimension Materialized Views
- Number of Dimension Materialized Views
- Fact Materialized Views
- Number of Fact Materialized Views
- Choosing the Right Format for Materialized Views
- Query Performance
- Build Times
- Partial Materialization
- MV Size
- Lineage (Key)
- Summary of DBMS_AW Subprograms
- EXECUTE Procedure
- Guidelines for Using Quotation Marks in OLAP DML Commands
- Effect of the OUTFILE Command
- Example
- GETLOG Function
- INTERP_SILENT Procedure
- Guidelines for Using Quotation Marks in OLAP DML Commands
- Example
- INTERP Function
- Guidelines for Using Quotation Marks in OLAP DML Commands
- Effect of the OUTFILE Command
- Example
- INTERPCLOB Function
- Guidelines for Using Quotation Marks in OLAP DML Commands
- Effect of the OUTFILE Command
- Example
- OLAP_EXPRESSION Function
- View Used in These Examples
- Time Series Function With a WHERE Clause
- Numeric Calculation With an ORDER BY Clause
- PRINTLOG Procedure
- Description
- Preliminary Steps
- Measures
- Dimensions
- Hierarchies
- Hierarchy Dimensions
- Hierarchy Relations
- Level Dimensions
- In-Hierarchy Variables
- Grouping IDs
- Parent Grouping IDs
- Family Relations
- Attributes
- Basic Steps
- Defining a Row
- Creating a Table
- Using OLAP_TABLE in a SELECT Statement
- OLAP_TABLE Reference
- Syntax
- Parameters
- AW_ATTACH Parameter
- Table_Name Parameter
- OLAP_Command Parameter
- Limit_Map Parameter
- MEASURE column FROM {measure | AW_EXPR expression}
- DIMENSION [column FROM] dimension...
- WITH...
- HIERARCHY [column FROM] hierarchy_relation[(hierarchy_dimension 'hierarchy')]...
- INHIERARCHY inhierarchy_variable
- GID column FROM gid_variable
- PARENTGID column FROM gid_variable
- FAMILYREL col1, col2, coln FROM {expression1, expression2, expressionn | family_relation USING level_dimension } [LABEL label_variable]
- ATTRIBUTE column FROM attribute_variable
- ROW2CELL column
- LOOP sparse_dimension
- PREDMLCMD olap_command
- POSTDMLCMD olap_command
- Examples
- Creating a View
- Creating Views of Embedded Total Dimensions
- Creating Views of Embedded Total Measures
- Creating Views in Rollup Form
- OLAP Metadata Entities
- Constructing a Dimension
- Procedure: Construct an OLAP Dimension
- Constructing a Cube
- Procedure: Construct an OLAP Cube
- Mapping OLAP Metadata
- Mapping to Columns
- Joining Fact Tables with Dimension Tables
- Validating OLAP Metadata
- Structural Validation
- Cubes
- Dimensions
- Mapping Validation
- Cubes
- Dimensions
- Invoking the Procedures
- Security Checks and Error Conditions
- Case Requirements for Parameters
- Creating and Saving Metadata
- Viewing OLAP Catalog Metadata
- Example: Creating OLAP Metadata for a Dimension Table
- Example: Creating OLAP Metadata for a Fact Table
- Access to OLAP Catalog Views
- Views of the Dimensional Model
- Views of Mapping Information
- ALL_OLAP2_CUBES
- ALL_OLAP2_CUBE_MEASURES
- ALL_OLAP2_CUBE_DIM_USES
- ALL_OLAP2_CUBE_MEAS_DIM_USES
- ALL_OLAP2_DIMENSIONS
- ALL_OLAP2_DIM_HIERARCHIES
- ALL_OLAP2_DIM_LEVELS
- ALL_OLAP2_DIM_ATTRIBUTES
- ALL_OLAP2_DIM_LEVEL_ATTRIBUTES
- ALL_OLAP2_DIM_ATTR_USES
- ALL_OLAP2_DIM_HIER_LEVEL_USES
- ALL_OLAP2_CATALOGS
- ALL_OLAP2_CATALOG_ENTITY_USES
- ALL_OLAP2_ENTITY_DESC_USES
- ALL_OLAP2_CUBE_MEASURE_MAPS
- ALL_OLAP2_DIM_LEVEL_ATTR_MAPS
- ALL_OLAP2_LEVEL_KEY_COLUMN_USES
- ALL_OLAP2_JOIN_KEY_COLUMN_USES
- ALL_OLAP2_HIER_CUSTOM_SORT
- ALL_OLAP2_FACT_TABLE_GID
- ALL_OLAP2_FACT_LEVEL_USES
- When to Use the AW_ACCESS Package
- Prerequisites
- Process Overview
- Preparing the Analytic Workspace
- Specifying the Source and Target Objects
- Defining Dimension Views
- Defining Fact Views
- Example: Creating Views
- Example: Input Files for Mapping Variables to Views
- Geography Dimension Standard Hierarchy View
- Product Dimension View
- Channel Dimension View
- Time Standard Hierarchy Input File
- Sales and Costs Fact Views
- Example: Script for the Product View
- Example: Product View
- Summary of CWM2_OLAP_AW_ACCESS Subprograms
- CreateAWAccessStructures_FR Procedure
- CreateAWAccessStructures Procedure
- Summary of CWM2_OLAP_AW_CREATE Subprograms
- AW_DIMENSION_CREATE Procedure
- AW_DIM_DEFINE_LOAD Procedure
- AW_DIM_FILTER_LOAD Procedure
- AW_DIMENSION_REFRESH Procedure
- AW_DIMENSION_CREATE_ACCESS Procedure
- AW_CUBE_CREATE Procedure
- AW_CUBE_DEFINE_LOAD Procedure
- AW_CUBE_FILTER_LOAD Procedure
- AW_CUBE_MEASURE_LOAD Procedure
- AW_CHOOSE_LEVEL_TUPLES Procedure
- AW_DEFINE_AGG_PLAN Procedure
- AW_CUBE_REFRESH Procedure
- AW_CUBE_CREATE_ACCESS Procedure
- Understanding Cubes
- Summary of CWM2_OLAP_CUBE Subprograms
- ADD_DIMENSION_TO_CUBE Procedure
- CREATE_CUBE Procedure
- DROP_CUBE Procedure
- LOCK_CUBE Procedure
- REMOVE_DIMENSION_FROM_CUBE Procedure
- SET_CUBE_NAME Procedure
- SET_DEFAULT_CUBE_DIM_CALC_HIER Procedure
- SET_DESCRIPTION Procedure
- SET_DISPLAY_NAME Procedure
- SET_MV_SUMMARY_CODE Procedure
- SET_SHORT_DESCRIPTION Procedure
- Example: Creating a Cube
- Understanding Dimensions
- Summary of CWM2_OLAP_DIMENSION Subprograms
- CREATE_DIMENSION Procedure
- DROP_DIMENSION Procedure
- LOCK_DIMENSION Procedure
- SET_DEFAULT_DISPLAY_HIERARCHY Procedure
- SET_DESCRIPTION Procedure
- SET_DIMENSION_NAME Procedure
- SET_DISPLAY_NAME Procedure
- SET_PLURAL_NAME Procedure
- SET_SHORT_DESCRIPTION Procedure
- Example: Creating a CWM2 Dimension
- Understanding Dimension Attributes
- Summary of CWM2_OLAP_DIMENSION_ATTRIBUTE Subprograms
- CREATE_DIMENSION_ATTRIBUTE Procedure
- DROP_DIMENSION_ATTRIBUTE Procedure
- LOCK_DIMENSION_ATTRIBUTE Procedure
- SET_DESCRIPTION Procedure
- SET_DIMENSION_ATTRIBUTE_NAME Procedure
- SET_DISPLAY_NAME Procedure
- SET_SHORT_DESCRIPTION Procedure
- Example: Creating a Dimension Attribute
- Understanding Hierarchies
- Summary of CWM2_OLAP_HIERARCHY Subprograms
- CREATE_HIERARCHY Procedure
- DROP_HIERARCHY Procedure
- LOCK_HIERARCHY Procedure
- SET_DESCRIPTION Procedure
- SET_DISPLAY_NAME Procedure
- SET_HIERARCHY_NAME Procedure
- SET_SHORT_DESCRIPTION Procedure
- SET_SOLVED_CODE Procedure
- Example: Creating a Hierarchy
- Understanding Levels
- Summary of CWM2_OLAP_LEVEL Subprograms
- ADD_LEVEL_TO_HIERARCHY Procedure
- CREATE_LEVEL Procedure
- DROP_LEVEL Procedure
- LOCK_LEVEL Procedure
- REMOVE_LEVEL_FROM_HIERARCHY Procedure
- SET_DESCRIPTION Procedure
- SET_DISPLAY_NAME Procedure
- SET_LEVEL_NAME Procedure
- SET_PLURAL_NAME Procedure
- SET_SHORT_DESCRIPTION Procedure
- Example: Creating a Level
- Understanding Level Attributes
- Summary of CWM2_OLAP_LEVEL_ATTRIBUTE Subprograms
- CREATE_LEVEL_ATTRIBUTE
- DROP_LEVEL_ATTRIBUTE Procedure
- LOCK_LEVEL_ATTRIBUTE Procedure
- SET_DESCRIPTION Procedure
- SET_DISPLAY_NAME Procedure
- SET_LEVEL_ATTRIBUTE_NAME Procedure
- SET_SHORT_DESCRIPTION Procedure
- Example: Creating a Level Attribute
- Understanding Measures
- Summary of CWM2_OLAP_MEASURE Subprograms
- CREATE_MEASURE Procedure
- DROP_MEASURE Procedure
- LOCK_MEASURE Procedure
- SET_DESCRIPTION Procedure
- SET_DISPLAY_NAME Procedure
- SET_MEASURE_NAME Procedure
- SET_SHORT_DESCRIPTION Procedure
- Example: Creating a Measure
- The OLAP API Metadata Reader Views
- Summary of CWM2_OLAP_METADATA_REFRESH Subprograms
- MR_REFRESH Procedure
- Prerequisites
- Parent-Child Dimensions
- Solved, Level-Based Dimensions
- Example: Creating a Solved, Level-Based Dimension Table
- Grouping ID Column
- Embedded Total Key Column
- Summary of CWM2_OLAP_PC_TRANSFORM Subprograms
- CREATE_SCRIPT Procedure
- Understanding OLAP Metadata Mapping
- Summary of CWM2_OLAP_TABLE_MAP Subprograms
- MAP_DIMTBL_HIERLEVELATTR Procedure
- MAP_DIMTBL_HIERLEVEL Procedure
- MAP_DIMTBL_HIERSORTKEY Procedure
- MAP_DIMTBL_LEVELATTR Procedure
- MAP_DIMTBL_LEVEL Procedure
- MAP_FACTTBL_LEVELKEY Procedure
- MAP_FACTTBL_MEASURE Procedure
- REMOVEMAP_DIMTBL_HIERLEVELATTR Procedure
- REMOVEMAP_DIMTBL_HIERLEVEL Procedure
- REMOVEMAP_DIMTBL_HIERSORTKEY Procedure
- REMOVEMAP_DIMTBL_LEVELATTR Procedure
- REMOVEMAP_DIMTBL_LEVEL Procedure
- REMOVEMAP_FACTTBL_LEVELKEY Procedure
- REMOVEMAP_FACTTBL_MEASURE Procedure
- Example: Mapping a Dimension
- Example: Mapping a Cube
- Summary of CWM2_OLAP_VALIDATE Subprograms
- VALIDATE_DIMENSION Procedure
- VALIDATE_CUBE Procedure
- Understanding the OLAP Classification System
- Summary of CWM_CLASSIFY Subprograms
- ADD_CATALOG_ENTITY Procedure
- ADD_DESCRIPTOR_ENTITY_TYPE Procedure
- ADD_ENTITY_DESCRIPTOR_USE Procedure
- CREATE_CATALOG Function
- CREATE_DESCRIPTOR Function
- CREATE_DESCRIPTOR_TYPE Procedure
- DROP_CATALOG Procedure
- DROP_DESCRIPTOR Procedure
- DROP_DESCRIPTOR_TYPE Procedure
- LOCK_CATALOG Procedure
- REMOVE_CATALOG_ENTITY Procedure
- REMOVE_DESCRIPTOR_ENTITY_TYPE Procedure
- REMOVE_ENTITY_DESCRIPTOR_USE Procedure
- SET_CATALOG_DESCRIPTION Procedure
- SET_CATALOG_PARENT Procedure
- Example: Creating a Measure Folder
- Creating Materialized Views for Dimensions
- Statistics and Bitmap Indexes
- Statistics
- Bitmap Indexes
- The CREATE Statement for a Dimension Materialized View
- Sample Script for the TIMES_DIM Dimension
- Table Structure of Sample TIMES_DIM Dimension Materialized View
- Using the DBMS_ODM Package
- Procedure: Create and Run Scripts to Generate Grouping Set Materialized Views
- Partitioning, Statistics, and Indexes
- Partitioning
- Statistics
- Bitmap Indexes
- Sample Script for the COST Cube
- Summary of DBMS_ODM Subprograms
- CREATEDIMLEVTUPLE Procedure
- CREATECUBELEVELTUPLE Procedure
- CREATEFACTMV_GS Procedure
- CREATEDIMMV_GS Procedure
- Using the OLAP Summary Advisor Wizard
- Procedure: Run the OLAP Summary Advisor
- Partitioning, Statistics, and Indexes
- Partitioning
- Statistics
- Bitmap Indexes
- The MV CREATE Statement With Concatenated Rollup
- Sample Script for the COST Cube
- Administration
- Authentication of Users
- Management Tools
- Data Transfer
- Localization
- Applications Support
- Programming Environment
- Communications
- Metadata
- Programming Language Changes
- New Commands
- Obsolete Commands
- UPDATE and COMMIT
- How to Upgrade an Express Database
