Table of Contents
Preface
Chapter 1: Installing Oracle Essbase
Chapter 2: Essbase Data and Design Considerations
Chapter 3: Building the Essbase Outline
Chapter 4: Loading Data into Essbase
Chapter 5: Calculating your Essbase Cube
Chapter 6: Using your Essbase Cube
Chapter 7: Getting the most out of the Microsoft Excel Add-in
Chapter 8: Automating your Essbase Cube
Chapter 9: Advanced Techniques
Chapter 10: Essbase Analytics Option
Chapter 11: Essbase System 9 Components
Appendix: A New Essbase Companion—Oracle Smart View
Index
- 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
- Essbase Application Properties
- 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
- Standard dimension
- 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
- Essbase-friendly thoughts
- Types of data sources
- Type of data
- Types of files used for data loads
- Relational databases
- Type of data
- 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
- Make your data Essbase-friendly
- 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
- Calculating your database
- 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
- How do you use your data in the real world
- 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
- Using your Essbase database
- 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
- Connecting to Essbase
- 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
- Reporting with the Microsoft Excel Add-in
- 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
- Essbase command scripts (EssCmd)
- 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
- MDX functions for ASO
- Pros and cons of ASO and BSO
- Pros and cons of BSO
- Pros
- Cons
- Pros and cons of ASO
- Pros
- Cons
- Pros and cons of BSO
- Summary
- What is ASO
- 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
- Overview of System 9 components
- 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
- Reporting with Oracle Smart View
