Go to main content
1/17
Contents
Title and Copyright Information
Preface
Audience
Documentation Accessibility
Related Documents
Conventions
Changes in This Release for Oracle OLAP User's Guide
Changes in Oracle Database 12c Release 2 (12.2)
Changes in Oracle Database 12
c
Release 1 (12.1)
New Features
Desupported Features
Other Changes
1
Overview
1.1
OLAP Technology in the Oracle Database
1.1.1
Full Integration of Multidimensional Technology
1.1.2
Ease of Application Development
1.1.3
Ease of Administration
1.1.4
Security
1.1.5
Unmatched Performance and Scalability
1.1.6
Reduced Costs
1.2
Developing Reports and Dashboards Using SQL Tools and Application Builders
1.3
Overview of the Dimensional Data Model
1.3.1
Cubes
1.3.2
Measures
1.3.3
Dimensions
1.3.4
Hierarchies and Levels
1.3.4.1
Level-Based Hierarchies
1.3.4.2
Value-Based Hierarchies
1.3.5
Attributes
2
Getting Started with Oracle OLAP
2.1
Installing the Sample Schema
2.2
Database Management Tasks
2.3
Granting Privileges to DBAs and Application Developers
2.4
Getting Started with Analytic Workspace Manager
2.4.1
Installing Analytic Workspace Manager
2.4.2
Opening Analytic Workspace Manager
2.4.3
Defining a Database Connection
2.4.4
Opening a Database Connection
2.4.5
Showing the Analytic Workspace Attachment Modes
2.4.6
Installing Plug-ins
2.5
Upgrading Metadata From Oracle OLAP 10
g
3
Creating Dimensions and Cubes
3.1
Designing a Dimensional Model for Your Data
3.2
Introduction to Analytic Workspace Manager
3.3
Creating a Dimensional Data Store Using Analytic Workspace Manager
3.3.1
Adding Functionality to Dimensional Objects
3.3.2
When Does Analytic Workspace Manager Save Changes?
3.4
Creating Dimensions
3.4.1
Requirements of a Dimension
3.4.1.1
Dimension Members Must Be Unique
3.4.1.2
Time Dimensions Have Special Requirements
3.4.2
Creating a Dimension
3.4.3
Creating Levels
3.4.4
Creating Hierarchies
3.4.5
Creating Attributes
3.4.5.1
Automatically Defined Attributes
3.4.5.2
User-Defined Attributes
3.4.5.3
Unique Key Attributes
3.4.6
Creating Measure Dimensions
3.4.7
Mapping Dimensions
3.4.7.1
Dimension Mapping Window
3.4.7.2
Source Data Query
3.4.8
Loading Data Into Dimensions
3.4.9
Displaying the Dimension View
3.4.10
Displaying the Default Hierarchy
3.5
Creating Cubes
3.5.1
Creating Measures
3.5.2
Mapping Cubes
3.5.3
Partitioning a Cube
3.5.3.1
Selecting Partitions
3.5.3.2
Analyzing Partition Members
3.5.4
Loading Data Into Cubes
3.5.5
Displaying the Data in a Cube
3.5.6
Displaying the Cube View Descriptions
3.6
Choosing a Data Maintenance Method
3.6.1
Creating and Executing Custom Cube Scripts
3.6.1.1
Creating Cube Scripts
3.6.1.2
Running a Cube Script
3.6.2
Creating and Executing Maintenance Scripts
3.6.2.1
Creating Maintenance Scripts
3.6.2.2
Running Maintenance Scripts
3.6.3
Adding Materialized View Capability to a Cube
3.7
Supporting Multiple Languages
3.8
Defining Measure Folders
3.9
Saving and Re-Creating Dimensional Objects with Object Definitions
3.9.1
Creating Dimensional Objects From XML Templates
3.9.2
Saving Object Definitions to XML Templates
3.9.3
Creating Analytic Workspaces from EIF Files
3.9.4
Saving Analytic Workspaces to EIF Files
3.10
Copying and Pasting Dimensional Objects
4
Querying Dimensional Objects
4.1
Exploring the OLAP Views
4.1.1
Cube Views
4.1.1.1
Discovering the Names of the Cube Views
4.1.1.2
Discovering the Columns of a Cube View
4.1.1.3
Displaying the Contents of a Cube View
4.1.2
Dimension and Hierarchy Views
4.1.2.1
Discovering the Names of Dimension and Hierarchy Views
4.1.2.2
Discovering the Columns of a Dimension View
4.1.2.3
Displaying the Contents of a Dimension View
4.1.2.4
Discovering the Columns of a Hierarchy View
4.1.2.5
Displaying the Contents of a Hierarchy View
4.2
Creating Basic Queries
4.2.1
Applying a Filter to Every Dimension
4.2.2
Allowing the Cube to Aggregate the Data
4.2.3
Query Processing
4.3
Creating Hierarchical Queries
4.3.1
Drilling Down to Children
4.3.2
Drilling Up to Parents
4.3.3
Drilling Down to Descendants
4.3.4
Drilling Up to Ancestors
4.4
Using Calculations in Queries
4.5
Using Attributes for Aggregation
4.5.1
Aggregating Measures Over Attributes
4.5.2
Aggregating Calculated Measures Over Attributes
4.6
Joining Cubes to Tables and Views
4.7
Viewing Execution Plans
4.7.1
Generating Execution Plans
4.7.2
Types of Execution Plans
4.8
Querying the Data Dictionary
5
Enhancing Your Database with Analytic Content
5.1
What Is a Calculated Measure?
5.2
Functions for Defining Calculations
5.2.1
Arithmetic Operators
5.2.2
Analytic Functions
5.2.3
Single-Row Functions
5.3
Creating Calculated Measures
5.3.1
Modifying a Template
5.3.2
Choosing a Range of Time Periods
5.4
Using Calculation Templates
5.4.1
Arithmetic Calculations
5.4.2
Index
5.4.3
Prior and Future Periods
5.4.4
Period to Date
5.4.5
Share
5.4.6
Rank
5.4.7
Parallel Period
5.4.8
Moving Calculations
5.4.9
Cumulative Calculations
5.4.10
Nested Calculations
5.5
Creating User-Defined Expressions
5.5.1
Using the OLAP Expression Syntax
5.5.2
Expression Syntax Example Using an Arithmetic Operator
5.5.3
Free-Form Calculation Example Using an Analytic Function
5.5.4
Expression Syntax Analytic Functions
5.6
Creating Calculated Measures Using the OLAP DML
5.6.1
Selecting an OLAP DML Calculation Type
5.6.2
OLAP DML Expression Examples
5.6.3
OLAP DML Function Example
6
Developing Reports and Dashboards
6.1
Developing OLAP Applications
6.2
Developing a Report Using BI Publisher
6.2.1
Creating an OLAP Report in BI Publisher
6.2.2
Creating a Template in Microsoft Word
6.2.3
Generating a Formatted Report
6.2.4
Adding Dimension Choice Lists in BI Publisher
6.2.4.1
Creating a List of Values for a BI Publisher Report
6.2.4.2
Creating a Menu
6.2.4.3
Editing the Query in BI Publisher
6.3
Developing a Dashboard Using Application Express
6.3.1
Creating an OLAP Application in Application Express
6.3.2
Adding Dimension Choice Lists in Application Express
6.3.2.1
Creating a Region
6.3.2.2
Creating a List of Values in Application Express
6.3.2.3
Creating the Choice List
6.3.2.4
Editing the Query in Application Express
6.3.3
Drilling on Dimension Columns
6.3.3.1
Creating Hidden Items
6.3.3.2
Editing the Query to Use Bind Variables
6.3.3.3
Adding Links to the Dimension Columns
7
Administering Oracle OLAP
7.1
Setting Database Initialization Parameters
7.2
Storage Management
7.2.1
Creating an Undo Tablespace
7.2.2
Creating Permanent Tablespaces for OLAP Use
7.2.3
Creating Temporary Tablespaces for OLAP Use
7.2.4
Spreading Data Across Storage Resources
7.3
Dictionary Views and System Tables
7.3.1
Static Data Dictionary Views
7.3.2
System Tables
7.3.3
Analytic Workspace Tables
7.3.4
Maintenance Logs
7.4
Partitioned Cubes and Parallelism
7.4.1
Querying Metadata for Cube Partitioning
7.4.2
Creating and Dropping Partitions
7.4.3
Parallelism
7.5
Analyzing Cubes and Dimensions
7.6
Monitoring Analytic Workspaces
7.6.1
Dynamic Performance Views
7.6.2
Basic Queries for Monitoring the OLAP Option
7.6.2.1
Is the OLAP Option Installed in the Database?
7.6.2.2
What Analytic Workspaces Are in the Database?
7.6.2.3
How Big Is the Analytic Workspace?
7.6.2.4
When Were the Analytic Workspaces Created?
7.6.3
OLAP DBA Scripts
7.6.4
Scripts for Monitoring Performance
7.6.5
Monitoring Disk Space
7.7
About Backing Up and Recovering Analytic Workspaces
7.8
About Copying Analytic Workspaces
7.9
About Saving Dimensional Object Definitions
7.9.1
About XML Templates
7.9.2
About EIF Files
7.10
Cube Materialized Views
7.10.1
Acquiring Information From the Data Dictionary
7.10.1.1
Identifying Cube Materialized Views
7.10.1.2
Identifying the Refresh Logs
7.10.2
Initiating a Data Refresh
7.10.2.1
Using DBMS_CUBE
7.10.2.2
Using DBMS_MVIEW
7.10.3
Refresh Methods
7.10.3.1
Refresh Method Descriptions
7.10.3.2
Fast Solve Refreshes
7.10.4
Using Query Rewrite
7.10.5
Acquiring Additional Information About Cube Materialized Views
8
Security
8.1
Security of Multidimensional Data in Oracle Database
8.1.1
Security Management
8.1.2
Types of Security
8.1.3
About the Privileges
8.1.4
Layered Security
8.2
Setting Object Security
8.2.1
Using SQL to Set Object Security
8.2.1.1
Setting Object Security on an Analytic Workspace
8.2.1.2
Setting Object Security on Dimensions
8.2.1.3
Setting Object Security on Cubes
8.2.2
Using Analytic Workspace Manager to Set Object Security
8.2.2.1
Setting Object Security on an Analytic Workspace
8.2.2.2
Setting Object Security on Dimensions
8.2.2.3
Setting Object Security on Cubes
8.3
Creating Data Security Policies on Dimensions and Cubes
8.4
Creating OLAP Data Security Roles
9
Advanced Aggregations
9.1
What Is Aggregation?
9.2
Aggregation Operators
9.2.1
Basic Operators
9.2.2
Scaled and Weighted Operators
9.2.3
Hierarchical Operators
9.3
When Does Aggregation Order Matter?
9.3.1
Using the Same Operator for All Dimensions of a Cube
9.3.1.1
Order Has No Effect
9.3.1.2
Order Changes the Aggregation Results
9.3.1.3
Order May Be Important
9.3.2
Example: Mixing Aggregation Operators
9.4
Example: Aggregating the Units Cube
9.4.1
Selecting the Aggregation Operators and Hierarchies
9.4.2
Choosing the Percentage of Precomputed Values
A
Designing a Dimensional Model
A.1
Case Study Scenario
A.1.1
Reporting Requirements
A.1.2
Business Goals
A.1.3
Information Requirements
A.1.3.1
Business Analysis Questions
A.1.3.2
Summary of Information Requirements
A.2
Identifying Required Business Facts
A.3
Designing a Dimensional Model for Global Computing
A.3.1
Identifying Dimensions
A.3.2
Identifying Levels
A.3.3
Identifying Hierarchies
A.3.4
Identifying Stored Measures
B
Keyboard Shortcuts
B.1
Menu Bar
B.2
Navigation Tree
B.3
Property Sheets
B.4
Shuttle Keys
B.5
Mapping Canvas
Glossary
Index
Scripting on this page enhances content navigation, but does not change the content in any way.