Oracle Essbase 9 Implementation Guide Table Of Contents

Table of Contents

• Preface

• Chapter 1: Installing Oracle Essbase
• Installing the Essbase analytic server
• Installing Essbase Administration Services
• Starting the EAS
• Installing the Essbase Add-in for Microsoft Excel
• A typical network setup
• Summary

• Chapter 2: Essbase Data and Design Considerations
• Introduction to OLAP
• Determining the data requirements
• Determine data storage options
• Types of Essbase applications
• Aggregate Storage Option (ASO)
• Block Storage Option (BSO)
• Unicode and Non-Unicode applications
• Creating your first Essbase application
• Essbase Application Properties
• Startup section
• Security
• Minimum access level
• Types of Essbase databases
• The normal (non-currency) database
• Essbase currency database
• Database components
• The database outline
• Linked Reporting Objects
• Partitions
• Calculation scripts
• Report scripts
• Database load rules files
• Allowing duplicate member names
• Create your first Essbase database
• General tab
• Dimensions tab
• Statistics tab
• Caches tab
• Transactions tab
• Storage tab
• Currency tab
• Modifications tab
• Types of Essbase users
• Summary

• Chapter 3: Building the Essbase Outline
• Before we begin
• The Essbase outline—the foundation
• Dimensions and members
• Outline member descriptors
• Generations and Levels
• Generation
• Level
• Types of dimensions
• Standard dimension
• The Account dimension type
• The Time dimension type
• The Country dimension type
• No dimension type or general dimension
• The Currency Partition dimension type
• The Attribute dimension
• Dense and Sparse dimensions
• Build your first outline
• Member properties
• Member consolidations
• Valid consolidation operators
• Member storage
• Member formulas
• Member alias
• Alias table
• Build your first data rules file
• Step 1: Start the Data Prep Editor
• Step 2: Associate the Dimension Build Rules file
• Step 3: Open data load file or the SQL data source file
• Step 4: Set the Data Source Properties
• Step 5: Set the View to Dimension build fields
• Step 6: Select Dimension build method
• Generation reference
• Level reference
• Parent-child reference
• Step 7: Format file
• Step 8: Associate fields with dimensions
• Step 9: Validate the Dimension Build rules file
• Update your outline using a rules file
• Update your outline using the EAS Outline Editor
• Update using MaxL Shell
• Executing MaxL from EAS editor
• Executing MaxL from command prompt
• Attribute dimensions
• User Defined Attributes (UDA)
• Dynamic Time Series
• Shared members
• Summary

• Chapter 4: Loading Data into Essbase
• Make your data Essbase-friendly
• Essbase-friendly thoughts
• Essbase-friendly example
• Types of data sources
• Type of data
• Types of files used for data loads
• Relational databases
• Data load methods
• Data file freeform (no load rule)
• Essbase export and import (no load rule)
• Structured data load (load rule used)
• Microsoft Excel Lock and Send (no load rule)
• Building your first data load rules file
• Step 1: Starting the Data Prep Editor
• Step 2: Associating the data load rules file
• Step 3: Opening data load file or the SQL data source file
• Step 4: Setting the View to Data Load Fields
• Step 5: Setting the Data Source Properties
• Step 6: Updating the Data Load Settings
• Step 7: Setting the Data Load Values
• Clearing Data Combinations
• Header Definition
• Step 8: Associating fields with Data Load Properties
• Global properties
• Data Load Properties
• Step 9: Validating the data load rules file
• Step 10: Saving the data load rules file
• Loading data into your database
• Using the EAS to load data into your Essbase cube
• Loading data using MaxL
• Data Load vs. Dimension Build
• Summary

• Chapter 5: Calculating your Essbase Cube
• Calculating your database
• The Essbase calculation script
• Essbase outline member formula
• Calculation types explained
• Calculation Scripts
• Stored data member formula
• Dynamic Calc and Dynamic Calc and Store
• Essbase calculation terminology
• Default database calculation script
• Calc All
• Calculate/Aggregate dimension
• Essbase Calc commands and functions
• Data declarations
• Control flow
• FIX/ENDFIX
• EXCLUDE/ENDEXCLUDE
• Functional
• SET command functions
• Conditionals
• IF/ENDIF
• Boolean
• Relationship functions
• Operators
• Member set
• Range (Financial)
• Forecasting
• Statistical
• Date and Time
• Miscellaneous
• Order of calculation
• Two-Pass Calc
• Using Substitution Variables
• Substitution Variables using EAS
• Substitution Variables using MaxL
• Create variables at the server level
• Create variables at the application level
• Create variables at the database level
• Displaying the Substitution Variable and its value
• Displaying the Substitution Variable in the SQL editor
• Building your first Calculation Script
• Writing and saving a Calculation Script
• Executing your Calculation Scripts
• Running Calculation Scripts manually using EAS
• Running a Calculation Script using an Essbase Command Script (EssCmd)
• What the EssCmd script looks like
• Running a Calculation Script using a MaxL Script
• Running a Calculation Script using the Essbase API
• Running a Calculation Script from Microsoft Excel
• Running a Calculation Script through the Essbase Add-In
• Running a Calculation Script using Microsoft Excel VBA
• Summary

• Chapter 6: Using your Essbase Cube
• Using your Essbase database
• How do you use your data in the real world
• Ad hoc data
• Canned reporting
• Export data
• Forecast analysts
• Planning analysts
• Budget analysts
• Financial analysts
• The real target users of your Essbase data
• Ways to extract your Essbase data
• The Essbase Report Script
• How to create an Essbase Report Script
• Report script commands and functions
• Report layout commands
• Data range commands
• Data ordering command
• Member selection and sorting commands
• Format commands
• Column or row calculation
• Member names and aliases
• Building your first Essbase report script
• Executing your report scripts
• Run reports using EAS
• Running a report script using an Essbase command script
• Running calc using a MaxL script
• Previewing data in EAS
• Cubeview
• Properties
• Summary

• Chapter 7: Getting the most out of the Microsoft Excel Add-in
• Reporting with the Microsoft Excel Add-in
• Connecting to Essbase
• Connecting to Essbase from Microsoft Excel
• Disconnecting from Essbase
• Launching the Essbase Query Designer
• Retrieving data from Essbase
• Setting the add-in spreadsheet options
• Display tab
• Zoom tab
• Mode tab
• Global tab
• Selecting Essbase members for your query
• Using the Keep Only function
• Using the Remove Only function
• Zooming in on your data
• Zooming out on your data
• Pivot Essbase members on your spreadsheet
• Flashback: The Essbase Add-in Undo
• Locking the data and retrieving
• Locking the data
• Unlocking the locked data
• Sending your data to the database
• Running a database calculation
• Retrieving your sheet without data
• Zooming in on sample data
• Linking objects to your data
• Creating graphical data representations
• Using the currency conversion tool
• Custom Microsoft Excel workbook reporting
• A final word on the Essbase add-in
• Using the Essbase Query Designer
• Where do I find the Essbase Query Designer
• Creating a query with Essbase Query Designer
• Page dimensions
• Row dimensions
• Column dimensions
• Sample query
• Report script by-product
• Summary

• Chapter 8: Automating your Essbase Cube
• Essbase command scripts (EssCmd)
• Creating an Essbase command script
• EssCmd commands and categories
• Coding a basic EssCmd
• Always remember EssCmd logging
• Connecting to an Essbase server
• What about error checking
• Adding some functional commands
• The finished script
• Executing an EssCmd
• Essbase MaxL scripts
• Logging on to the Essbase server
• Working with an Essbase application in MaxL
• Creating an application
• Altering the application
• Display application
• Drop application
• Working with an Essbase database from MaxL
• Creating or replacing a database
• Altering a database
• Display database
• Drop database
• Working with data in MaxL
• Working with database calculations in MaxL
• Create calculation
• Display calculation
• Execute calculation
• Drop calculation
• Working with user privileges in MaxL
• Create user
• Alter user
• Display user
• Drop user
• Grant user
• Working at the System level with MaxL
• Alter system properties
• Display system properties
• Substitution variables
• Executing a MaxL statement
• Executing MaxL from Command Prompt
• Executing MaxL from EAS
• Essbase Application Programming Interface (API)
• Installing the Essbase API
• What you should know to use the Essbase API
• What functions are available in the Essbase API
• Essbase API programming tips
• Essbase nested coding style examples
• Essbase API function declarations
• How to code an API function
• Essbase API code sample blocks
• The sample API subroutine explained
• Summary

• Chapter 9: Advanced Techniques
• Performance tuning your database
• The shape of your database outline
• The hourglass outline
• Database block size
• Database configuration settings
• Data retrieval buffers
• Data cache settings
• Data load and storage settings
• Partitioning databases
• Analytic server configuration file
• Configuration categories
• Configuration settings to consider
• Ports and connections
• Logging and error handling
• Calculation
• Data import/export
• Memory management
• essbase.cfg memory settings
• Summary

• Chapter 10: Essbase Analytics Option
• What is ASO
• Creating an aggregate storage Application|Database
• Hierarchies
• Stored hierarchies
• Dynamic hierarchies
• Outline paging
• Aggregation
• MDX query language
• MDX functions for ASO
• MDX function examples
• MDX query syntax
• Executing an MDX query
• Tuples and Sets
• Pros and cons of ASO and BSO
• Pros and cons of BSO
• Pros
• Cons
• Pros and cons of ASO
• Pros
• Cons
• Summary

• Chapter 11: Essbase System 9 Components
• Overview of System 9 components
• Essbase Analytic Services (Essbase agent)
• Essbase Planning
• Essbase analytics
• Hyperion Application Link/Oracle Application Link
• Oracle Business Rules
• Oracle Reports
• Essbase Shared Services
• Oracle Essbase Provider Services
• Essbase Smart Office
• Oracle Essbase Financial Reporting
• Smart View for Microsoft Office
• Summary

• Appendix: A New Essbase Companion—Oracle Smart View
• Reporting with Oracle Smart View
• Adding a data source with the connection manager
• Retrieving data using Smart View in Microsoft Excel
• POV Manager
• Submitting data and calc scripts in Smart View
• Using Smart View in other Microsoft Office products

• Index