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

The Oracle 9i OLAP Services Developer s Guide to the OLAP DML provides anoverview of the programming environment for the OLAP DML. describes the OLAP DML data objects, and explains how to use the key commands in the OLAP DML. It also describes how to write and debug OLAP DML programs and illustrates programming strategies for accessing and working with data. Send Us Your Comments......Page 9 Preface......Page 11 Conventions......Page 13 Documentation Accessibility......Page 14 What Is the OLAP DML?......Page 15 Using the OLAP DML......Page 19 How Do I Use the OLAP DML to Analyze Data?......Page 20 Where Do I Go From Here?......Page 23 2 Defining and Working with Analytic Workspaces......Page 25 Defining an Analytic Workspace......Page 26 Gaining Access to a Workspace from OLAP Worksheet......Page 28 Gaining Access to a Workspace from a Java Application......Page 30 Using the OLAP DML to Work with Analytic Workspaces......Page 33 Saving Analytic Workspace Changes......Page 37 Minimizing Analytic Workspace Growth......Page 38 Sharing Analytic Workspaces......Page 41 Working with AUTOGO Programs......Page 43 Adding Security to an Analytic Workspace......Page 44 Obtaining Analytic Workspace Information......Page 47 Overview: Defining OLAP DML objects......Page 53 Defining Dimensions......Page 56 Defining Relations......Page 59 Defining Variables......Page 63 Defining Variables That Handle Sparse Data Efficiently......Page 67 Defining Hierarchical Dimensions and Variables That Use Them......Page 72 Defining Metadata......Page 75 Changing the Definition of an Object......Page 76 4 Working with Expressions......Page 79 OLAP DML Data Types......Page 80 Using OLAP DML Objects in Expressions......Page 84 OLAP DML Operators......Page 88 Introducing Expressions......Page 89 Expressions and Dimensionality......Page 92 Specifying a Single Value for the Dimension of an Expression......Page 94 Using Functions in Expressions......Page 100 Numeric Expressions......Page 101 Text Expressions......Page 105 Boolean Expressions......Page 106 Conditional Expressions......Page 115 Substitution Expressions......Page 117 Working with NA Values......Page 118 Overview: Populating an Analytic Workspace......Page 123 Maintaining Dimensions and Composites......Page 125 Assigning Values to Data Objects......Page 135 Calculating and Analyzing Data......Page 140 Aggregating Data......Page 141 6 Limiting an Application’s View of the Data......Page 143 Introducing Dimension Status......Page 144 Limiting Using a Simple List of Values......Page 147 Limiting Using a Boolean Expression......Page 149 Limiting to the Top or Bottom Values of a Sorted Dimension......Page 153 Limiting to the Values of a Related Dimension......Page 155 Limiting Based on the Position of a Value in a Dimension......Page 157 Limiting Based on a Relationship Within a Hierarchy......Page 158 Limiting Composites and Conjoint Dimensions......Page 163 Working with Null Status......Page 166 Working with Valuesets......Page 167 Using Models to Calculate Data......Page 173 Creating a Nested Hierarchy of Models......Page 176 Basic Modeling Commands......Page 178 Compiling a Model......Page 180 Running a Model......Page 183 Debugging a Model......Page 185 Modeling for Multiple Scenarios......Page 187 8 Designing Programs......Page 191 Introduction to OLAP DML Programs......Page 192 Invoking Programs......Page 193 Defining and Editing Programs......Page 195 Using Variables in Programs......Page 198 Passing Arguments......Page 201 Writing User-Defined Functions......Page 206 Controlling the Flow of Execution......Page 209 Directing Output......Page 213 Preserving the Session Environment......Page 215 Handling Errors......Page 219 Compiling Programs......Page 225 Testing Programs......Page 227 Overview: Debugging in OLAP DML......Page 229 Debugging with a Debugging File......Page 230 Debugging with OLAP Worksheet......Page 233 OLAP DML Debugger Commands......Page 234 Working with watch points......Page 238 10 Using Embedded SQL......Page 241 Using Relational Data......Page 242 Obtaining Access to the Relational Database......Page 243 Supported SQL Commands......Page 244 Checking for Errors......Page 245 Fetching Data into an Analytic Workspace......Page 246 Declaring a Cursor......Page 248 Opening a Cursor......Page 250 Fetching the Selected Data......Page 251 Using Dimensions as Output Host Variables......Page 254 Writing OLAP DML Data to a Relational Table......Page 255 Matching Oracle9i Data Types......Page 258 Using the Special Features of an OCI Connection......Page 260 Example: SQL Program......Page 262 11 Reading Data from Files......Page 281 Introducing Data-Reading Programs......Page 282 Reading Files......Page 283 Specifying File Names in the OLAP DML......Page 285 Reading Data from Files......Page 286 Reading and Maintaining Dimension Values......Page 289 Processing Input Data......Page 298 Processing Records Individually......Page 299 Processing Several Values for One Variable......Page 302 12 Writing Reports......Page 307 Introducing the Reporting Commands......Page 308 Creating Report Rows......Page 310 Creating Report Columns......Page 312 Retrieving Data for Rows......Page 313 Controlling the Default Format of Report Output......Page 317 Modifying the Layout of Columns......Page 318 Creating Headings......Page 322 Performing Calculations in a Report......Page 326 Creating Paginated Reports......Page 331 Creating Headings on Each Page......Page 336 Guidelines for Writing a Report Program......Page 339 What is Analytic Workspace Metadata?......Page 341 Analytic Workspace Metadata Prerequisites......Page 342 Metadata That Describes Dimension Hierarchies......Page 350 Metadata That Describes Dimension Hierarchy Levels......Page 356 Metadata That Describes Dimension Attributes......Page 360 Metadata That Describes Other Objects......Page 363 Glossary......Page 367 Index......Page 395