ORACLE® ESSBASE SPREADSHEET ADD-IN

[Pages:240]ORACLE? ESSBASE SPREADSHEET ADD-IN

RELEASE 11.1.1

USER'S GUIDE

Spreadsheet Add-in User's Guide, 11.1.1

Copyright ? 1991, 2008, Oracle and/or its affiliates. All rights reserved.

Authors: EPM Information Development Team

This software and related documentation are provided under a license agreement containing restrictions on use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license, transmit, distribute, exhibit, perform, publish or display any part, in any form, or by any means. Reverse engineering, disassembly, or decompilation of this software, unless required by law for interoperability, is prohibited.

The information contained herein is subject to change without notice and is not warranted to be error-free. If you find any errors, please report them to us in writing.

If this software or related documentation is delivered to the U.S. Government or anyone licensing it on behalf of the U.S. Government, the following notice is applicable: U.S. GOVERNMENT RIGHTS: Programs, software, databases, and related documentation and technical data delivered to U.S. Government customers are "commercial computer software" or "commercial technical data" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, the use, duplication, disclosure, modification, and adaptation shall be subject to the restrictions and license terms set forth in the applicable Government contract, and, to the extent applicable by the terms of the Government contract, the additional rights set forth in FAR 52.227-19, Commercial Computer Software License (December 2007). Oracle USA, Inc., 500 Oracle Parkway, Redwood City, CA 94065.

This software is developed for general use in a variety of information management applications. It is not developed or intended for use in any inherently dangerous applications, including applications which may create a risk of personal injury. If you use this software in dangerous applications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy and other measures to ensure the safe use of this software. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of this software in dangerous applications.

This software and documentation may provide access to or information on content, products and services from third parties. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to third party content, products and services. Oracle Corporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to your access to or use of third party content, products or services.

Contents

Chapter 1. Introduction to Essbase . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7 About Essbase . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7 Typical Users of Essbase . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8 Components of the Client-Server Environment . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8 Essbase Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8 Spreadsheet Add-in . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9 The Network . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9 Essbase Application Products . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9 Spreadsheet Toolkit . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9 Essbase Partitioning Option . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9 Essbase Structured Query Language Interface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9 Essbase Application Programming Interface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10 Essbase Currency Conversion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10 Integration Services . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10 Developer Products . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10 Data Mining . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10 The Multidimensional Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11 Database Outlines . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12 Dimensions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14 Members . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14 Attributes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15 Formulas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15 Aliases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15 Consolidations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15

Chapter 2. Basic Tutorial . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17 About the Basic Tutorial . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17 Getting Acquainted with Spreadsheet Add-in . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17 Registering Spreadsheet Add-in . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18 Loading Spreadsheet Add-in . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18 Starting Spreadsheet Add-in . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19 Installing the Essbase Toolbar for Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19

Contents iii

Using the Essbase Toolbar . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20 Accessing Online Help . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21 Enabling Mouse Actions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22 Tutorial Prerequisites . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24 Enabling Compatibility with Smart View . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24 Setting Essbase Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24 Following Guidelines During the Tutorial . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28 Reviewing the Sample Basic Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29 Retrieving Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29 Connecting to a Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30 Changing a Password (Optional) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32 Retrieving Data from a Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32 Canceling a Data Retrieval Request . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34 Restoring the Previous Database View . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34 Drilling Down to More Detail . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35 Drilling Up to Less Detail . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38 Customizing Drill-Down and Drill-Up Behavior . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39 Pivoting, Retaining, and Suppressing Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42 Pivoting Rows and Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42 Retaining a Data Subset . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46 Removing a Data Subset . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48 Navigating Through the Worksheet Without Retrieving Data . . . . . . . . . . . . . . . . . . 49 Suppressing Missing Values, Zero Values, and Underscore Characters . . . . . . . . . . . . 53 Formatting the Worksheet . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55 Formatting Text and Cells . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56 Displaying Aliases for Member Names . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63 Displaying Both Member Names and Aliases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64 Repeating Member Labels . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65 Working with Duplicate Member Names . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67 Creating Queries Using Query Designer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69 About Creating and Changing Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 70 Creating Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71 Saving Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76 Applying Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78 Deleting Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79 Viewing Messages and Confirmations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79 Accessing Help . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 80 Connecting to Multiple Databases from Query Designer . . . . . . . . . . . . . . . . . . . . . . 80 Applying Worksheet Options to Query Designer Results . . . . . . . . . . . . . . . . . . . . . . 81

iv Contents

Selecting Members . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 82 Saving and Disconnecting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89

Saving a Worksheet . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90 Disconnecting from Essbase . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90 Logging Off . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 91 On to Advanced Tasks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 91

Chapter 3. Advanced Tutorial . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 93 About the Advanced Tutorial . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 93 Connecting to a Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 93 Setting Essbase Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95 Performing Advanced Retrieval Tasks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99 Filtering Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100 Sorting Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 104 Retrieving Data into Asymmetric Reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 107 Drilling Down to a Sample of Members (Optional) . . . . . . . . . . . . . . . . . . . . . . . . . 109 Working with Formatted Worksheets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111 Preserving Formulas When Retrieving Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 116 Retrieving a Range of Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 119 Retrieving Data by Using a Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 122 Retrieving Dynamic Calculation Members . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 125 Specifying the Latest Time Period for Dynamic Time Series . . . . . . . . . . . . . . . . . . . 128 Using Free-Form Reporting to Retrieve Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131 Retrieving Data Using Visual Explorer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 140 Using Linked Reporting Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 144 Linking a File to a Data Cell . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 145 Linking a Cell Note to a Data Cell . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 148 Linking a URL to a Data Cell . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 149 Accessing and Editing Linked Reporting Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . 151 Connecting to Multiple Databases (Optional) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 156 Ways to View Active Database Connections . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 157 Access Methods for Linked Partitions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 158 Updating Data on Essbase Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 159 Database Calculation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 162 Creating Multiple Worksheets from Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 163 Working with Currency Conversions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 167 Retrieving Currency Conversion Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 167 Connecting to the Sample Currency Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . 168 Performing Ad Hoc Currency Reporting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 170

Contents v

Chapter 4. Drill-Through Tutorial . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173 About the Drill-Through Tutorial . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173 Understanding Drill-Through . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173 About the Drill-Through Wizard . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 175 Before You Start . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 176 Setting Essbase Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 177 About the Samples Used in This Tutorial . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 182 Accessing Drill-Through Reports from the Spreadsheet . . . . . . . . . . . . . . . . . . . . . . . . . 183 Selecting Drill-Through Reports to View or Customize . . . . . . . . . . . . . . . . . . . . . . . . . 189 Selecting and Ordering Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 194 Ordering Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 196 Filtering Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 198 Disconnecting from Essbase . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 204 Glossary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 205 Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 229

vi Contents

Introduction to Essbase

1

In This Chapter

About Essbase ...................................................................................................................... 7 Typical Users of Essbase ........................................................................................................... 8 Components of the Client-Server Environment................................................................................... 8 Essbase Application Products ..................................................................................................... 9 The Multidimensional Database ..................................................................................................11 Database Outlines .................................................................................................................12

About Essbase

Oracle Essbase is multidimensional database software that is optimized for planning, analysis, and management-reporting applications. Essbase uniquely blends an innovative technical design with an open, client-server architecture. The product enables you to extend decision support systems beyond ad hoc queries and reports on historical performance to dynamic, operational systems that combine historical analysis and future planning.

By consolidating and staging historical and projected data for detailed analysis, you gain perspectives about your business that enable you to take appropriate actions.

Essbase provides both power and flexibility. Thus, it can be used for a broad range of online analytical processing (OLAP) applications, including those in this list:

Budgeting

Forecasting and seasonal planning

Financial consolidations and reporting

Customer and product profitability analysis

Price, volume, and mix analysis

Executive information systems

Essbase enables you and others in the organization to share, access, update, and analyze enterprise data from any perspective and at any level of detail without learning new tools, query languages, or programming skills.

About Essbase 7

Typical Users of Essbase

Essbase can be used in many different applications. Financial analysts have found the product to be invaluable in budget analysis, currency conversion, and consolidation. Cost accountants apply its powerful capabilities to evaluate allocation and elimination scenarios. Product managers and analysts use it to plan and analyze multiple product lines and distribution channels. You can also use the product as a repository database for spreadsheet data. Anyone who uses a spreadsheet is a potential user of Essbase. Because Essbase is applicable to such a broad variety of environments, individuals using it at an organization may fill one or more roles in implementing and running applications. This guide refers to specific roles by three titles. A role may be performed, however, by one person or by several people working collaboratively. System administrator-- Typically has experience in networking, installing software

packages, and administering system functions. In addition to installing the Essbase software, the Essbase system administrator may also set up Essbase user accounts, set up the security system, and maintain the Essbase Server. Application designer--Sets up the Essbase database, creates the database outline, and develops calculation and report scripts. The responsibilities of the Essbase system administrator and the application designer may overlap in some areas. The application designer has probably developed spreadsheet or database applications and understands the operational problems and the tools being employed to solve them. User--Interacts with Essbase databases through spreadsheets, using Microsoft Office Excel (Excel). Users are typically analysts and managers who use spreadsheet programs as their primary tool for viewing and analyzing data.

Components of the Client-Server Environment

Client-server computing refers to the architecture in which individual computers are connected to a powerful server by means of a local area network (LAN). The computer acts as a client by requesting data from the server. The server processes the request and returns the desired result to the client. Essbase is built as a client-server system. System performance and multiuser capabilities are greatly enhanced in the Essbase client-server environment.

Essbase Server

Essbase Server is a multidimensional database that supports analysis of an unlimited number of data dimensions and an unlimited number of members within these dimensions, developed using a true client-server architecture, All data, the database outline, the calculations, and the data security controls reside on the Essbase Server.

8 Introduction to Essbase

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download