Oracle Essbase 9 Implementation Guide电子书

Oracle Essbase 9 Implementation Guide

Table of Contents

Oracle Essbase 9 Implementation Guide

Credits

About the Authors

About the Reviewer

Acknowledgements

Preface

A brief history on Essbase

Why Essbase

What this book covers

Who this book is for

Versions covered in this book

Data warehousing concepts

The fathers of the data warehouse

What is a data warehouse

Different types of data warehouses

Data warehouse data modeling

The Third Normal Form (3NF)

The Dimensional Data Model

Where does Essbase fit in this

Conventions

Let's get started

Reader feedback

Customer support

Errata

Piracy

Questions

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

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

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

Syntax of Import Dimension statement

Executing MaxL from command prompt

Attribute dimensions

User Defined Attributes (UDA)

Dynamic Time Series

Shared members

Summary

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

Microsoft Excel files

Text files

Essbase data export text files

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

Data values

Sign flip

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

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

Basic FIX and ENDFIX examples

EXCLUDE/ENDEXCLUDE

Basic EXCLUDE/ENDEXCLUDE examples

Functional

SET command functions

Conditionals

IF/ENDIF

Boolean

Relationship functions

Operators

Math

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

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

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

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

EssCmd processing from command prompt

EssCmd processing in batch mode

Essbase MaxL scripts

Logging on to the Essbase server

Working with an Essbase application in MaxL

Creating an application

Altering the application

Using the SET properties statement

Using load/unload database

Clear application log

Enable/Disable start up/auto startup

Display application

Drop application

Working with an Essbase database from MaxL

Creating or replacing a database

Altering a database

Enable/Disable commands

Archive commands

Set commands

Reset database

Rename 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

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

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

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

A. 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