Oracle9i OLAP Services. Developer's Guide to the OLAP DML

410 Pages·2001·4.105 MB·English

Oracle9i OLAP Services
Developer’s Guide to the OLAP DML
Release 1 (9.0.1)
June 2001
Part No. A86720-01
Oracle9i OLAP Services Developer’s Guide to the OLAP DML, Release 1 (9.0.1)
Part No. A86720-01
Copyright © 1999, 2001 Oracle Corporation. All rights reserved.
The Programs (which include both the software and documentation) contain proprietary information of
Oracle Corporation; they are provided under a license agreement containing restrictions on use and
disclosure and are also protected by copyright, patent, and other intellectual and industrial property
laws. Reverse engineering, disassembly, or decompilation of the Programs is prohibited.
The information contained in this document is subject to change without notice. If you find any problems
in the documentation, please report them to us in writing. Oracle Corporation does not warrant that this
document is error free. Except as may be expressly permitted in your license agreement for these
Programs, no part of these Programs may be reproduced or transmitted in any form or by any means,
electronic or mechanical, for any purpose, without the express written permission of Oracle Corporation.
If the Programs are delivered to the U.S. Government or anyone licensing or using the Programs on
behalf of the U.S. Government, the following notice is applicable:
Restricted Rights Notice Programs delivered subject to the DOD FAR Supplement are “commercial
computer software” and use, duplication, and disclosure of the Programs, including documentation,
shall be subject to the licensing restrictions set forth in the applicable Oracle license agreement.
Otherwise, Programs delivered subject to the Federal Acquisition Regulations are “restricted computer
software” and use, duplication, and disclosure of the Programs shall be subject to the restrictions in FAR
52.227-19, Commercial Computer Software - Restricted Rights (June, 1987). Oracle Corporation, 500
Oracle Parkway, Redwood City, CA 94065.
The Programs are not intended for use in any nuclear, aviation, mass transit, medical, or other inherently
dangerous applications. It shall be the licensee’s responsibility to take all appropriate fail-safe, backup,
redundancy, and other measures to ensure the safe use of such applications if the Programs are used for
such purposes, and Oracle Corporation disclaims liability for any damages caused by such use of the
Programs.
Oracle is a registered trademark, and Express is a trademark or registered trademark of Oracle
Corporation. Other names may be trademarks of their respective owners.
Contents
Send Us Your Comments .................................................................................................................. ix
Preface............................................................................................................................................................ xi
Conventions........................................................................................................................................... xiii
Documentation Accessibility............................................................................................................ xiv
1 Basic Concepts
What Is the OLAP DML?................................................................................................................... 1-1
Using the OLAP DML........................................................................................................................ 1-5
How Do I Use the OLAP DML to Analyze Data?........................................................................ 1-6
Where Do I Go From Here?.............................................................................................................. 1-9
2 Defining and Working with Analytic Workspaces
Defining an Analytic Workspace..................................................................................................... 2-2
How to Gain Access to an Analytic Workspace............................................................................ 2-4
Gaining Access to a Workspace from OLAP Worksheet............................................................. 2-4
Gaining Access to a Workspace from a Java Application........................................................... 2-6
Using the OLAP DML to Work with Analytic Workspaces....................................................... 2-9
Saving Analytic Workspace Changes........................................................................................... 2-13
Minimizing Analytic Workspace Growth.................................................................................... 2-14
Sharing Analytic Workspaces......................................................................................................... 2-17
Working with AUTOGO Programs............................................................................................... 2-19
Adding Security to an Analytic Workspace................................................................................. 2-20
Obtaining Analytic Workspace Information............................................................................... 2-23
iii
3 Defining Data Objects
Overview: Defining OLAP DML objects....................................................................................... 3-1
Defining Dimensions......................................................................................................................... 3-4
Defining Relations............................................................................................................................. 3-7
Defining Variables............................................................................................................................ 3-11
Defining Variables That Handle Sparse Data Efficiently......................................................... 3-15
Defining Hierarchical Dimensions and Variables That Use Them........................................ 3-20
Defining Metadata............................................................................................................................ 3-23
Changing the Definition of an Object.......................................................................................... 3-24
4 Working with Expressions
OLAP DML Data Types..................................................................................................................... 4-2
Using OLAP DML Objects in Expressions.................................................................................... 4-6
OLAP DML Operators..................................................................................................................... 4-10
Introducing Expressions.................................................................................................................. 4-11
Expressions and Dimensionality................................................................................................... 4-14
Specifying a Single Value for the Dimension of an Expression.............................................. 4-16
Using Functions in Expressions..................................................................................................... 4-22
Numeric Expressions........................................................................................................................ 4-23
Text Expressions................................................................................................................................ 4-27
Boolean Expressions......................................................................................................................... 4-28
Conditional Expressions.................................................................................................................. 4-37
Substitution Expressions................................................................................................................. 4-39
Working with NA Values................................................................................................................ 4-40
5 Populating OLAP DML Data Objects
Overview: Populating an Analytic Workspace............................................................................. 5-1
Maintaining Dimensions and Composites.................................................................................... 5-3
Assigning Values to Data Objects................................................................................................. 5-13
Calculating and Analyzing Data.................................................................................................... 5-18
Aggregating Data.............................................................................................................................. 5-19
iv
6 Limiting an Application’s View of the Data
Introducing Dimension Status......................................................................................................... 6-2
Limiting Using a Simple List of Values......................................................................................... 6-5
Limiting Using a Boolean Expression............................................................................................. 6-7
Limiting to the Top or Bottom Values of a Sorted Dimension................................................. 6-11
Limiting to the Values of a Related Dimension.......................................................................... 6-13
Limiting Based on the Position of a Value in a Dimension..................................................... 6-15
Limiting Based on a Relationship Within a Hierarchy............................................................. 6-16
Limiting Composites and Conjoint Dimensions....................................................................... 6-21
Working with Null Status............................................................................................................... 6-24
Working with Valuesets................................................................................................................... 6-25
7 Working with Models
Using Models to Calculate Data...................................................................................................... 7-1
Creating a Nested Hierarchy of Models......................................................................................... 7-4
Basic Modeling Commands.............................................................................................................. 7-6
Compiling a Model............................................................................................................................. 7-8
Running a Model.............................................................................................................................. 7-11
Debugging a Model.......................................................................................................................... 7-13
Modeling for Multiple Scenarios.................................................................................................. 7-15
8 Designing Programs
Introduction to OLAP DML Programs........................................................................................... 8-2
Invoking Programs............................................................................................................................. 8-3
Defining and Editing Programs....................................................................................................... 8-5
Using Variables in Programs............................................................................................................ 8-8
Passing Arguments........................................................................................................................... 8-11
Writing User-Defined Functions.................................................................................................... 8-16
Controlling the Flow of Execution................................................................................................ 8-19
Directing Output............................................................................................................................... 8-23
Preserving the Session Environment............................................................................................ 8-25
Handling Errors................................................................................................................................. 8-29
Compiling Programs........................................................................................................................ 8-35
Testing Programs............................................................................................................................... 8-37
v
9 Debugging Programs
Overview: Debugging in OLAP DML............................................................................................ 9-1
Debugging with a Debugging File.................................................................................................. 9-2
Debugging with OLAP Worksheet.................................................................................................. 9-5
OLAP DML Debugger Commands................................................................................................. 9-6
Working with watch points............................................................................................................. 9-10
10 Using Embedded SQL
Using Relational Data...................................................................................................................... 10-2
Obtaining Access to the Relational Database............................................................................. 10-3
Supported SQL Commands............................................................................................................ 10-4
Checking for Errors.......................................................................................................................... 10-5
Fetching Data into an Analytic Workspace.................................................................................. 10-6
Declaring a Cursor............................................................................................................................ 10-8
Opening a Cursor............................................................................................................................ 10-10
Fetching the Selected Data............................................................................................................ 10-11
Closing a Cursor.............................................................................................................................. 10-14
Using Dimensions as Output Host Variables............................................................................ 10-14
Writing OLAP DML Data to a Relational Table....................................................................... 10-15
Matching Oracle9i Data Types..................................................................................................... 10-18
Using the Special Features of an OCI Connection................................................................... 10-20
Example: SQL Program.................................................................................................................. 10-22
11 Reading Data from Files
Introducing Data-Reading Programs............................................................................................ 11-2
Reading Files..................................................................................................................................... 11-3
Specifying File Names in the OLAP DML.................................................................................. 11-5
Reading Data from Files.................................................................................................................. 11-6
Reading and Maintaining Dimension Values............................................................................. 11-9
Processing Input Data.................................................................................................................... 11-18
Processing Records Individually................................................................................................. 11-19
Processing Several Values for One Variable.............................................................................. 11-22
vi
12 Writing Reports
Introducing the Reporting Commands........................................................................................ 12-2
Creating Report Rows...................................................................................................................... 12-4
Creating Report Columns............................................................................................................... 12-6
Retrieving Data for Rows................................................................................................................ 12-7
Controlling the Default Format of Report Output................................................................... 12-11
Modifying the Layout of Columns.............................................................................................. 12-12
Creating Headings.......................................................................................................................... 12-16
Performing Calculations in a Report.......................................................................................... 12-20
Creating Paginated Reports.......................................................................................................... 12-25
Creating Headings on Each Page................................................................................................. 12-30
Guidelines for Writing a Report Program.................................................................................. 12-33
A Creating and Using Analytic Workspace Metadata
What is Analytic Workspace Metadata?......................................................................................... A-1
Analytic Workspace Metadata Prerequisites................................................................................. A-2
Metadata That Describes Dimension Hierarchies..................................................................... A-10
Metadata That Describes Dimension Hierarchy Levels........................................................... A-16
Metadata That Describes Dimension Attributes........................................................................ A-20
Metadata That Describes Other Objects...................................................................................... A-23
Glossary
Index
vii
viii
Send Us Your Comments
Oracle9i OLAP Services Developer’s Guide to the OLAP DML, Release 1 (9.0.1)
Part No. A86720-01
Oracle Corporation welcomes your comments and suggestions on the quality and usefulness of this
publication. Your input is an important part of the information used for revision.
n Did you find any errors?
n Is the information clearly presented?
n Do you need more information? If so, where?
n Are the examples correct? Do you need more examples?
n What features did you like most about this manual?
If you find any errors or have any other suggestions for improvement, please indicate the document
title and part number, and the chapter, section, and page number (if available). You can send com-
ments to us in the following ways:
n FAX - 781-684-5880. Attn: Oracle OLAP Services
n Postal service:
Oracle Corporation
OLAP Services Documentation Manager
200 Fifth Avenue
Waltham, MA 02451-8720
USA
If you would like a reply, please give your name, address, telephone number, and (optionally) elec-
tronic mail address.
If you have problems with the software, please contact your local Oracle Support Services.
ix
x