OLAP Spreadsheet Add-in User's Guide for Excel

232 Pages·2000·1.76 MB·English

(cid:1)(cid:2)(cid:3)
IBM® DB2® OLAP Server™ and Starter Kit
OLAP Spreadsheet Add-in User’s Guide
for Excel
Version 7
SC27-0786-00

(cid:1)(cid:2)(cid:3)
IBM® DB2® OLAP Server™ and Starter Kit
OLAP Spreadsheet Add-in User’s Guide
for Excel
Version 7
SC27-0786-00
Beforeusingthisinformationandtheproductitsupports,besuretoreadthegeneralinformationunder“Notices”on
page215.
ThisdocumentcontainsproprietaryinformationofIBM.Itisprovidedunderalicenseagreementandisprotectedby
copyrightlaw.Theinformationcontainedinthispublicationdoesnotincludeanyproductwarranties,andany
statementsprovidedinthismanualshouldnotbeinterpretedassuch.
OrderpublicationsthroughyourIBMrepresentativeortheIBMbranchofficeservingyourlocalityorbycalling
1-800-879-2755intheUnitedStatesor1-800-IBM-4YOUinCanada.
WhenyousendinformationtoIBM,yougrantIBManonexclusiverighttouseordistributetheinformationinany
wayitbelievesappropriatewithoutincurringanyobligationtoyou.
©CopyrightInternationalBusinessMachinesCorporation1998,2000.Allrightsreserved.
USGovernmentUsersRestrictedRights–Use,duplicationordisclosurerestrictedbyGSAADPScheduleContract
withIBMCorp.
©1991–2000HyperionSolutionsCorporation.AllRightsReserved.
Contents
Purpose . . . . . . . . . . . . . v StartingHyperionEssbaseSpreadsheet
Audience . . . . . . . . . . . . . v Add-in. . . . . . . . . . . . . 13
DocumentStructure . . . . . . . . . vi InstallingtheHyperionEssbaseToolbar. . 14
SampleDatabasesandFiles . . . . . . vi UsingtheHyperionEssbaseToolbar. . . 15
OnlineHelp . . . . . . . . . . . . vii AccessingOnlineHelp . . . . . . . 17
Conventions. . . . . . . . . . . . vii EnablingMouseActions . . . . . . . 17
PreparingtoBegintheTutorial. . . . . . 19
IntroducingRelease6 . . . . . . . . ix SettingHyperionEssbaseOptions. . . . 19
MigratingtoRelease6. . . . . . . . . ix FollowingGuidelinesDuringtheTutorial 23
What’sNewinRelease6. . . . . . . . ix ReviewingtheSampleBasicDatabase . . 24
RetrievingData. . . . . . . . . . . 24
Chapter1.IntroducingHyperionEssbase . 1 ConnectingtoaDatabase . . . . . . 25
TypicalUsersofHyperionEssbase. . . . . 1 ChangingaPassword. . . . . . . . 27
ComponentsoftheClient-ServerEnvironment 2 RetrievingDatafromaDatabase . . . . 27
TheServer . . . . . . . . . . . . 2 CancelingaDataRetrievalRequest . . . 29
HyperionEssbaseSpreadsheetAdd-in . . 2 RestoringthePreviousDatabaseView . . 29
TheNetwork . . . . . . . . . . . 3 DrillingDowntoMoreDetail . . . . . 30
HyperionEssbaseApplicationProducts . . . 3 DrillingUptoLessDetail . . . . . . 33
HyperionEssbaseSpreadsheetToolkit. . . 3 CustomizingDrill-DownandDrill-Up
HyperionEssbasePartitioningOption. . . 3 Behavior . . . . . . . . . . . . 34
HyperionEssbaseStructuredQuery Pivoting,Retaining,andSuppressingData. . 36
LanguageInterface . . . . . . . . . 3 PivotingRowsandColumns . . . . . 36
HyperionEssbaseApplicationProgramming RetainingaDataSubset . . . . . . . 40
Interface . . . . . . . . . . . . 4 RemovingaDataSubset. . . . . . . 42
HyperionEssbaseCurrencyConversion . . 4 NavigatingThroughtheWorksheet
HyperionIntegrationServer . . . . . . 4 WithoutRetrievingData . . . . . . . 43
HyperionObjects. . . . . . . . . . 4 SuppressingMissingValues,ZeroValues,
HyperionWebGateway . . . . . . . 4 andUnderscoreCharacters . . . . . . 46
TheMultidimensionalDatabase. . . . . . 5 FormattingtheWorksheet . . . . . . . 49
DefinitionofMultidimensional . . . . . 5 FormattingTextandCells . . . . . . 49
DatabaseOutlines . . . . . . . . . . 7 DisplayingAliasesforMemberNames . . 56
Dimensions . . . . . . . . . . . 8 DisplayingBothMemberNamesand
Members . . . . . . . . . . . . 8 Aliases. . . . . . . . . . . . . 58
Attributes . . . . . . . . . . . . 9 RepeatingMemberLabels . . . . . . 58
Formulas . . . . . . . . . . . . 9 CreatingQueriesUsingHyperionEssbase
Aliases . . . . . . . . . . . . . 9 QueryDesigner. . . . . . . . . . . 60
Consolidations . . . . . . . . . . 9 CreatingandChangingQueries . . . . 61
CreatingQueries . . . . . . . . . 62
Chapter2.ABasicHyperion DeletingQueries . . . . . . . . . 71
EssbaseTutorial . . . . . . . . . . 11 ViewingMessagesandConfirmations . . 71
AccessingHelp . . . . . . . . . . 72
GettingAcquaintedwithHyperionEssbase
ConnectingtoMultipleDatabasesfrom
SpreadsheetAdd-in . . . . . . . . . 12
HyperionEssbaseQueryDesigner . . . 73
AddingHyperionEssbaseSpreadsheet
ApplyingWorksheetOptionstoHyperion
Add-in. . . . . . . . . . . . . 12
EssbaseQueryDesignerResults . . . . 74
© Copyright IBM Corp. 1998, 2000 iii
SelectingMembers. . . . . . . . . . 75 PerformingAdHocCurrencyReporting 158
SavingandDisconnecting . . . . . . . 83
SavingaWorksheet . . . . . . . . 83 Chapter4.UsingHyperionIntegration
DisconnectingfromHyperionEssbase . . 83 ServerDrill-Through . . . . . . . . 161
LoggingOff . . . . . . . . . . . 84 WhatIsDrill-Through? . . . . . . . . 161
MovingontoAdvancedTasks. . . . . . 84 WhatIstheDrill-ThroughWizard? . . . 163
BeforeYouStart . . . . . . . . . . 164
Chapter3.AnAdvancedHyperionEssbase SettingHyperionEssbaseOptions . . . 165
Tutorial . . . . . . . . . . . . . 85 AbouttheSamplesUsedinThisTutorial 170
PreparingtoBegintheTutorial. . . . . . 85 UsingDrill-Through. . . . . . . . . 171
ConnectingtoaDatabase . . . . . . 85 AccessingDrill-Throughfromthe
SettingHyperionEssbaseOptions. . . . 87 Spreadsheet. . . . . . . . . . . 171
PerformingAdvancedRetrievalTasks . . . 91 SelectingDrill-ThroughReportstoView
FilteringData . . . . . . . . . . 92 orCustomize . . . . . . . . . . 175
SortingData . . . . . . . . . . . 96 SelectingandOrderingColumns. . . . 177
RetrievingDataintoAsymmetricReports 99 OrderingData . . . . . . . . . . 180
WorkingwithFormattedWorksheets . . 101 FilteringData . . . . . . . . . . 182
PreservingFormulasWhenRetrieving DisconnectingfromHyperionEssbase . . . 187
Data . . . . . . . . . . . . . 106
RetrievingaRangeofData . . . . . 109 Appendix.UsingtheDB2Library. . . . 189
RetrievingDatabyUsingaFunction . . 112 DB2PDFFilesandPrintedBooks . . . . 189
RetrievingDynamicCalculationMembers 115 DB2Information . . . . . . . . . 189
SpecifyingtheLatestTimePeriodfor PrintingthePDFBooks. . . . . . . 198
DynamicTimeSeries. . . . . . . . 117 OrderingthePrintedBooks . . . . . 199
UsingFree-FormReportingtoRetrieve DB2OnlineDocumentation . . . . . . 200
Data . . . . . . . . . . . . . 121 AccessingOnlineHelp . . . . . . . 200
UsingLinkedReportingObjects . . . . . 130 ViewingInformationOnline . . . . . 202
LinkingaFiletoaDataCell . . . . . 131 UsingDB2Wizards . . . . . . . . 204
LinkingaCellNotetoaDataCell . . . 134 SettingUpaDocumentServer . . . . 205
LinkingaURLtoaDataCell. . . . . 135 SearchingInformationOnline. . . . . 206
AccessingandEditingLinkedReporting
Objects . . . . . . . . . . . . 137 Index . . . . . . . . . . . . . 207
ConnectingtoMultipleDatabases . . . . 144
ViewingActiveDatabaseConnections . . 145 Notices. . . . . . . . . . . . . 215
AccessingLinkedPartitions . . . . . . 145 Trademarks. . . . . . . . . . . . 216
UpdatingDataontheServer . . . . . . 147
CalculatingaDatabase . . . . . . . . 150
ContactingIBM . . . . . . . . . . 217
CreatingMultipleWorksheetsfromData . . 151
ProductInformation . . . . . . . . . 217
WorkingwithCurrencyConversions . . . 155
RetrievingCurrencyConversionData . . 156
ConnectingtotheSampleCurrency
Databases . . . . . . . . . . . 156
iv OLAPSpreadsheetAdd-inUser’sGuideforExcel
Purpose
This guide provides all the information that you need to use Hyperion
Essbase SpreadsheetAdd-in for Microsoft Excel. It explains the features and
options and discusses the concepts, processes, procedures, formats, tasks, and
examples that you need to use the software.
Hyperion Essbase is an online analytical processing (OLAP) solution that
satisfies the complex calculation requirements of financial, accounting, and
marketing professionals. Hyperion Essbase operates in a client-server
computing environment on a local area network (LAN). In this environment,
multiple users can use their desktop computers to retrieve and analyze
centralized data.
You can create reports from the data residing on Hyperion Essbase OLAP
Server in several ways:
v Generate database reports through a spreadsheet interface, called Hyperion
Essbase SpreadsheetAdd-in, as explained in this guide.
v Use Hyperion EssbaseApplication Manager Report Writer to create a report
script and run a report. For more information, see the Hyperion Essbase
Database Administrator’s Guide.
v Use Hyperion EssbaseApplication Programming Interface (API) to create
and run database reports. For more information, see the Hyperion Essbase
online API Reference.
v Use reporting tools, such as Hyperion Reporting for Hyperion Essbase.
Audience
This guide is for Hyperion Essbase end users who are responsible for some or
all of the following tasks:
v Starting Hyperion Essbase and connecting and disconnecting from
Hyperion Essbase databases
v Retrieving data from a database into a worksheet
v Drilling down, drilling up, and navigating through a worksheet to analyze
and arrange data from multiple viewpoints
v Working with linked reporting objects and linked partitions
v Updating data on the Hyperion Essbase server
v Using the worksheet to load and calculate data in the database
v Creating multiple sheets from data
v Working with currency conversions
© Copyright IBM Corp. 1998, 2000 v
Document Structure
The main sections of this guide are structured as tutorials that take you
step-by-step through basic and advanced tasks in Hyperion Essbase
SpreadsheetAdd-in. This document contains the following information:
v “Introducing Release 6”on pageix provides information on migrating from
previous versions of Hyperion Essbase to Release 6 and lists all new
features and enhancements.
v “Chapter1. Introducing Hyperion Essbase”on page1 introduces you to
basic concepts of retrieving and manipulating data through the spreadsheet
interface.
v “Chapter2.ABasic Hyperion EssbaseTutorial”on page11 provides a
step-by-step tutorial of basic data navigation, ad hoc retrieval, and
reporting techniques.
v “Chapter3.AnAdvanced Hyperion Essbase Tutorial”on page85 describes
advanced reporting and retrieval techniques for users that need special
reports or formatted data views.
v “Chapter4. Using Hyperion Integration Server Drill-Through”on page161
provides a brief overview of the Hyperion Integration Server Drill-Through
product.
v The Indexcontains a list of terms and their page numbers. Select or look up
an index entry to view the page to which the entry refers.
Note: The Hyperion Essbase SpreadsheetAdd-in online help provides a
comprehensive section on Hyperion Essbase Spreadsheet Toolkit. This
section enables you to customize and automate your use of Hyperion
Essbase by using Excel macros and Visual Basic forApplications (VBA)
functions. For more information on the Hyperion Essbase Spreadsheet
Add-in online help, see Chapter 2, “Accessing Online Help.”
The Spreadsheet Add-in User’s Guide for Excel is provided in the
\essbase\docs\client directory in .pdf format for online viewing and
printing inAdobe®Acrobat Reader® (Release 4 or higher). The .pdffile is
named Essexcel.pdf. You can downloadAdobeAcrobat Reader from the
Hyperion Essbase CD-ROM or from http://www.adobe.com. On the Hyperion
Essbase CD-ROM, theAcrobat Reader executable file is located in theAdobe
directory under the appropriate platform and language-version subdirectories.
To installAdobeAcrobat Reader, launch the executable file, follow the
prompts, and provide the information requested.
Sample Databases and Files
This book provides tutorial steps that are based on sample databases and files
that are provided with the Hyperion Essbase server software. You use the
Sample Basic database for most tasks in “Chapter2.ABasic Hyperion
EssbaseTutorial”on page11 and “Chapter3.AnAdvanced Hyperion Essbase
vi OLAPSpreadsheetAdd-inUser’sGuideforExcel
Tutorial”on page85. You use a sample Hyperion Essbase Currency
Conversion application for the section on currency conversion. See “Chapter3.
AnAdvanced Hyperion Essbase Tutorial”on page85. For information on the
Sample Basic database, see “Chapter2.ABasic Hyperion EssbaseTutorial”on
page11.
In addition to these sample applications and databases, you also use several
sample Excel files in the advanced tutorial in “Chapter3.AnAdvanced
Hyperion Essbase Tutorial”on page85. The individual at your organization
who installs the server is responsible for making these applications, databases,
and files available to you. Contact the Hyperion Essbase system administrator
for more information.
Online Help
To access online help, click the Help button in any Hyperion Essbase dialog
box or select Essbase Help from the ExcelEssbaseHelp menu.
To print an online help topic, display the topic and select File > Print, or
right-click and select Print from the pop-up menu.
Conventions
The following table shows the conventions that are used in this document:
Purpose vii
Item Meaning
=> Arrowsindicatethebeginningofaprocedure,whichconsistsofone
ormoresequentialsteps.
Brackets[] Inexamples,bracketsindicatesthattheenclosedelementsare
optional.
Bold Boldtextindicateswordsorcharactersthatyoutypeexactlyasthey
appearonthepage.Boldinproceduralstepshighlightmajorinterface
elements.
CAPITALLETTERS Capitallettersdenotefilenames,functions,DOScommands,and
variousIDs.Forexample,thetextmightrefertotheGDC.INIfile.
Exampletext Textshowninthisfontindicatesthatthematerialshownisan
exampleforyouruse.
Ctrl+0 Keystrokecombinationsindicatethatyoushouldpressthekeys
simultaneously(yetstillsomewhatsequentially)asshown.
Italics Italictextindicatesavariablefieldincommandsyntax.Substitute
yourownvaluesinplaceofthevariableshowninitalics.Titlesof
otherbooksarealsoinitalics.Anditalicsindicateimportantterms
andspecialemphasis.
Ellipses(...) Useofanellipsesindicatesanomissionofirrelevantmaterial,which
istypicallyusedinanexampletoshowonlytherelevantmaterial.
Mouseorientation Thisdocumentprovidesexamplesandproceduresusinga
right-handedmouse.Ifyouareusingaleft-handedmouse,adjustthe
proceduresaccordingly.
Menucommands Menucommandsarepresentedinthefollowingformat:Menuname>
Menucommand>ExtendedmenucommandForexample:File>Desktop
>Accounts
n,x Thevariablenindicatesthatyoumustsupplyagenericnumber;the
variablexindicatesthatyoumustsupplyagenericletter.
viii OLAPSpreadsheetAdd-inUser’sGuideforExcel