HYPERION® ESSBASE® – SYSTEM 9 RELEASE 9.3.
Essbase Spreadsheet Add-in User’s Guide for Excel, 9.3.1 Copyright © 1989, 2007, Oracle and/or its affiliates. All rights reserved. Authors: Keely Costedoat The Programs (which include both the software and documentation) contain proprietary information; they are provided under a license agreement containing restrictions on use and disclosure and are also protected by copyright, patent, and other intellectual and industrial property laws.
Contents Chapter 1. Introduction to Release 9.3.1 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7 Migration to Release 9.3.1 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7 New Features in Release 9.x . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7 Duplicate Member Name Support . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Members . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18 Attributes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19 Formulas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19 Aliases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Repeating Member Labels . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63 Working with Duplicate Member Names . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65 Creating Queries Using Essbase Query Designer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67 About Creating and Changing Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 68 Creating Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Linking a URL to a Data Cell . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 138 Accessing and Editing Linked Reporting Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . 140 Connecting to Multiple Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 144 Ways to View Active Database Connections . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 145 Ways to Access Linked Partitions . . . . . . . . . . . . . . . . . . .
1 Introduction to Release 9.3.1 In This Chapter Migration to Release 9.3.1......................................................................................................... 7 New Features in Release 9.x....................................................................................................... 7 New Features in Release 7.x.......................................................................................................
you can choose to simply display "Albany" under New York and California in both dimensions, and view the qualified member name in the cell comment. Alternatively, you can choose to display the qualified member name for Albany in both dimensions directly on the worksheet. See “Working with Duplicate Member Names” on page 65 for more information on duplicate member name support.
When the new "Limit to Connected Sheets" check box is selected, the selections you make in the Mouse Actions group are only valid for Essbase only for worksheets that are connected to an Essbase application and database. Likewise, if the current worksheet is connected to a different type of data source, such as a Hyperion Planning data source connected using Smart View, mouse clicks are ignored by Spreadsheet Add-in.
versions of Essbase, portions of the sample applications used the currencies of Germany, France, and Spain. References to these currencies have been replaced by references to the euro (EUR), Swiss franc (CHF), and Swedish Krona (SEK), respectively. Also, where applicable, the abbreviated names of the currencies used in the sample applications have been changed to reflect international standards for the representation of currency units.
Introduction to Essbase 2 In This Chapter About Essbase .....................................................................................................................11 Typical Users of Essbase ..........................................................................................................12 Components of the Client-Server Environment..................................................................................12 Essbase Application Products ...........................................
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. 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.
Figure 1 Essbase Components The 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. Essbase Spreadsheet Add-in Essbase Spreadsheet Add-in is a software program that merges seamlessly with Microsoft Excel.
Note: Supported network environments and technical requirements are discussed in detail in the Hyperion Essbase - System 9 Installation Guide, which is included with the Essbase package. Essbase Application Products Several optional products, designed to extend and enhance the scope of OLAP applications, can be implemented using Essbase. The following sections describe these products.
Essbase Currency Conversion Essbase Currency Conversion translates, analyzes, and reports on foreign financial data. Any exchange rate scenario can be modeled, and you can even perform ad hoc currency conversions of data, directly from the spreadsheet. The Currency Conversion product is compliant with Financial Accounting Standards Board 52 (FASB52). For more information on conversions, see “Working with Currency Conversions” on page 155.
markets, budgets, and so on. Each dimension contains additional categories that have various relationships one to another. An Essbase application contains an unlimited number of dimensions, so you can analyze large amounts of data from multiple viewpoints. Figure 2 shows four views of multidimensional data. You can retrieve and analyze the multidimensional data with the Spreadsheet Add-in software.
Database Outlines Understanding the database outline is the key to understanding Essbase. To define a multidimensional database, you design its database outline. The database outline contains the database organization (structure), the database members, and the database rules, as shown in Figure 4: Figure 4 Essbase Database Outline The application designer or Essbase system administrator usually creates the database outline.
dimension and an accounts dimension. Other dimensions may categorize products, markets, and scenarios. Using dimensional organization, you can define any consolidation structure or any slice of data that is relevant to the application. Essbase supports an unlimited number of dimensions. Members Members are the names of the elements within a dimension. A dimension can contain an unlimited number of members.
Attributes Attributes describe characteristics of data, such as the size and color of products. Through attributes, you can group and analyze members of dimensions based on their characteristics. Attribute dimensions must be associated with base dimensions. For more information, see the Hyperion Essbase - System 9 Database Administrator's Guide. Formulas Each database member can be associated with one or more formulas in the database outline.
Introduction to Essbase
Basic Tutorial 3 In This Chapter Getting Acquainted with Spreadsheet Add-in ...................................................................................21 Preparing to Begin the Tutorial....................................................................................................28 Retrieving Data .....................................................................................................................33 Pivoting, Retaining, and Suppressing Data.....................................
Registering Spreadsheet Add-in You may register Essbase Spreadsheet Add-in for Excel. This registers Essbase Spreadsheet Addin with Excel and includes it in your computer’s registry entries. Similarly, you may unregister Spreadsheet Add-in from Excel. Unregistering is similar to uninstalling in that it clears the registry entries and removes the Essbase menu from Excel; however, it leaves the Essbase Spreadsheet Add-in components on your computer.
4 Click OK twice to close the dialog boxes. Adding Essbase Spreadsheet Add-in to Excel modifies the Windows Registry to point to where the Spreadsheet Add-in file is installed. 5 Start Spreadsheet Add-in. After the Excel startup screen goes away, the Spreadsheet Add-in startup screen is displayed. If you do not see the Spreadsheet Add-in startup screen or if Excel does not contain an Essbase menu, see the Hyperion Essbase - System 9 Installation Guide for troubleshooting information.
Depending on how software is installed on your PC, the file may not be available or may be located in a different directory. If you cannot locate the file, contact the Essbase system administrator. Note: Upon using Excel, two dialog boxes may be displayed warning that the esstoolb.xls file may contain macros. If so, click the Enable Macros button on the first dialog box, and click OK on the second dialog box. The macros must be enabled for the Essbase toolbar to work.
Button Purpose Description Navigate with or without data Toggles the Navigate Without Data feature, which tells Essbase to retrieve or not to retrieve data when you perform navigational operations, such as pivot, drill down, drill up, keep only, and remove only. This button serves the same function as the Navigate Without Data check box in the Essbase Options dialog box (Global tab). Retrieve Retrieves data into the active worksheet.
Button Purpose Description Select Members Enables you to select members from the multidimensional database outline. Click the Member Selection button to display the Essbase Member Selection dialog box. Attach Linked Objects Enables you to attach comments or files to data cells. Click the Attach Linked Objects button to display the linked objects browser dialog box. Accessing Online Help Spreadsheet Add-in includes a context-sensitive online help system.
operations. Click refers to use of the primary mouse button. The term right-click refers to use of the secondary mouse button. ● Select chooses the object that is under the cursor when you press and release the primary mouse button. Select a worksheet cell, for example, by moving the cursor to the cell and pressing and releasing the primary mouse button. ● Click (that is, both click and right-click) describes a quick press-and-release action on a command object.
Figure 6 Essbase Options Dialog Box—Global Tab 4 Click OK to return to the worksheet. For information about setting the primary mouse button to display the Linked Objects Browser dialog box when you double-click a data cell, see the Spreadsheet Add-in online help.
Figure 7 Check Box for Compatibility Between Spreadsheets When Spreadsheet Add-in and Smart View are installed on the same computer, and this check box is selected, mouse clicks are ignored by Spreadsheet Add-in if the sheet being acted upon is connected to a data source other than an Essbase application and database (for example, a Hyperion Planning data source). 5 Click OK to return to the worksheet.
Figure 8 Initial Settings for Display Options 4 Select the Zoom tab. 5 Select the appropriate check boxes and option buttons so that your display of the Zoom tab matches Figure 9: Figure 9 Initial Settings for Zoom Options 6 Select the Mode tab. 7 Select the appropriate check boxes and option buttons so that your display of the Mode tab matches Figure 10.
Figure 10 Initial Settings for Mode Options Note: If you are already connected to an Essbase database, Essbase Options also displays a Style tab. Skip this tab for now. 8 Select the Global tab. 9 Select the appropriate check boxes and option buttons so that your display of the Global tab matches Figure 11.
Note: You should have already selected the appropriate boxes for Mouse Actions, as described in “Enabling Mouse Actions” on page 26. 10 Click OK to save the settings for this session and close Essbase Options. Following Guidelines During the Tutorial Keep in mind the following guidelines during this tutorial: 32 Basic Tutorial ● Optional tasks that should not be performed as part of the tutorial are displayed in lightshaded boxes. These tasks are included for your future reference.
Reviewing the Sample Basic Database The Sample Basic database used in this tutorial is based on a hypothetical company in the beverage industry. The major products of the company are various kinds of sodas. These products are sold in U.S. markets, which are categorized by state and region. Financial data for the company is collected monthly and is summarized by quarter and by year. The company uses Essbase to calculate financial and accounting data, such as sales, cost of goods sold, and payroll.
To complete the steps that follow, you need to know the name of the server to which you want to connect, your username, and your password. If you do not have this information, contact the Essbase system administrator. Note: Essbase does not support multiple instances of Excel. ➤ To connect to an Essbase Server, application, and database: 1 Select Essbase > Connect. The Essbase System Login dialog box is displayed, as shown in Figure 12.
For this tutorial, you use the Sample Basic database. If the Sample Basic database was installed as part of the Essbase installation, it is shown in the list. If Sample Basic is not shown in the Application/Database list box, ask the Essbase system administrator to install it. Figure 13 Available Application and Database Pairs 6 In the Application/Database list box, double-click Sample Basic. You can also select Sample Basic from the list box and click OK.
● Spreadsheet Add-in requests data from the server. ● The server processes the request and prepares the data. ● The server transmits the data to Spreadsheet Add-in. ● The spreadsheet application receives the data from Essbase and organizes it in a worksheet. To help you monitor these operations, Essbase uses three custom cursors, as described in Table 3. Table 3 Cursor Essbase Custom Cursor When Displayed Spreadsheet Add-in requests information from Essbase Server.
When you retrieve data into an empty worksheet, Essbase returns data from the top levels of each database dimension. The top level is used as a starting point to navigate, or drill down, into levels of detailed data. In the Sample Basic database, the following five dimensions are retrieved: Measures, Product, Market, Scenario, and Year. Tip: You can retrieve data by double-clicking in a data cell, selecting Essbase > Retrieve, or by clicking the Retrieve button on the Essbase toolbar.
To disable Flashback: 1. Select Essbase > Options and click the Global tab. 2. Clear the Enable FlashBack check box. Drilling Down to More Detail You can drill down to various levels of multidimensional data in the worksheet. For example, if you want to view data for a specific quarter or month rather than an aggregate data value for the whole year, you can drill down on the Year dimension to see more detailed data.
Figure 16 Result of Drilling Down on the Market Dimension (Nested Rows) Because worksheets can accommodate more rows than columns, Essbase is preset to retrieve data into rows when you drill down on a member. You can change this default behavior and display the results of a drill-down across columns. Drilling across columns applies only to the top-level member of a dimension (for example, Market or Scenario).
consists of level 0 attribute members. Level 0 attribute members are the lowest level attributes that are associated with members of a base dimension. The Pkg_Type attribute dimension, for instance, has two level 0 members, Bottle and Can. You can extract information on all products sold in a can by entering manually the name Can in the worksheet. You can also use Essbase Query Designer or the Essbase Member Selection dialog box to select the attribute and display it in the worksheet.
members in columns pivot to rows, and level 0 attribute members already in rows remain in rows. ● A drill-down on non-level 0 attribute members is the same as the current drill- down behavior for other types of members. The drill-down behavior for non-level 0 attribute members is the same as the current drill-down behavior for other types of members. See the Spreadsheet Add-in online help for examples of drilling down on level 0 attribute members.
Figure 21 Result of Drilling Up on East Customizing Drill-Down and Drill-Up Behavior You can customize the behavior of the Zoom In and Zoom Out commands in the Essbase Options dialog box. The following steps illustrate some drill-down and drill-up techniques. ➤ To retrieve all members of a dimension with a single drill-down operation: 1 Select Essbase > Options and select the Zoom tab. Essbase displays the Zoom Tab. A portion of the Zoom tab is shown in Figure 22.
Essbase retrieves all members of Market and Scenario, as shown in Figure 23. For the Market dimension, Essbase drilled down two levels to get to the bottom-most members, which are individual states. The Scenario dimension contains only one member level, so the members of Scenario would also be retrieved if you selected Next Level in the Zoom In option group. Figure 23 Result of Drilling Down to All Member Levels 5 Select Essbase > Options and select the Zoom tab.
You do not need to save the worksheet. Pivoting, Retaining, and Suppressing Data After you retrieve data into the worksheet, you may want to manipulate the data in various ways. For example, you may want to move rows and columns to different positions in the worksheet, or you may want to tell Essbase to suppress or to retain specific data during data retrievals.
Figure 25 shows the spreadsheet view before pivoting. Figure 25 View Before Pivoting 5 In cell C3, select Year and select Essbase > Pivot. Essbase pivots the Year dimension to a column group next to Market (above the Scenario members), as shown in Figure 26. Figure 26 Result of Pivoting a Row Group to a Column Group 6 As another example, in cell C2, select Actual. 7 Right click and drag Actual to product 100 in cell A3.
Figure 27 Pivoting a Column Group to a Row Group Figure 28 shows the result of Essbase pivoting the Scenario members (Actual, Budget, Variance, and Variance%) from a column group to a row group that is displayed to the left of the Product members. Figure 28 Result of Pivoting a Column Group to a Row Group ➤ To transpose the order of row groups: 1 In cell A2, select Actual. 2 Right-click and drag Actual to Profit in cell C2. Figure 29 shows the spreadsheet before the pivot operation.
Figure 30 Result of Pivoting the Order of Row Groups In this example, notice that both the source cell and the destination cell are now members. Whenever the source cell and the destination cell are members of different row groups, Essbase exchanges the member groups. You must select a destination cell that contains a member name to exchange row members. You can also exchange column members by choosing a destination cell in another column that contains a member name.
Figure 32 Result of Retaining a Data Subset (Adjacent Cells) Occasionally, the data that you want to remove from the worksheet does not lie in an adjacent range of cells. ➤ To select and retain nonadjacent cells: 1 Press and hold down the Alt key, and, in cell D1, zoom in (double-click) on Year. 2 Select Qtr2 in cell E2. 3 Press and hold down the Ctrl key and select Qtr4 in cell G2 (see Figure 33). Figure 33 Selecting Nonadjacent Members for the Keep Only Command 4 Select Essbase > Keep Only.
Figure 34 Result of Retaining a Data Subset (Nonadjacent Cells) Removing a Data Subset The Remove Only command is the counterpart to the Keep Only command. With Remove Only, you can remove selected member rows or columns and retain all other data in the worksheet view. ➤ To remove a data subset from the current worksheet view: 1 In cell B7, select Ratios. 2 Press and hold Ctrl, and, in cell B9, select Measures. 3 Select Essbase > Remove Only.
This feature is especially useful when dealing with dynamic calculation members, which are usually specified by the application designer. By activating Navigate Without Data, you are effectively telling Essbase not to calculate values dynamically (that is, calculate the database at retrieval time) while you are creating the spreadsheet report. Dynamic calculation is discussed in more detail in “Retrieving Dynamic Calculation Members” on page 117.
Figure 37 Result of Pivoting (Navigate Without Data Enabled) 5 In cell G1, click the secondary mouse button on Market and drag Market to product 100 (cell A4). Essbase executes the pivot without retrieving data. The result is shown in Figure 38. Figure 38 Result of Pivoting (Navigate Without Data Enabled) Navigating without data also works with the Keep Only and Remove Only commands.
Figure 40 Result of Remove Only (Navigate Without Data Enabled) ➤ To turn off Navigate Without Data when you are ready to retrieve data: 1 Select Essbase > Navigate Without Data. Essbase removes the check mark next to the menu item. You can also disable Navigate Without Data by clearing the appropriate option in the Essbase Options dialog box (Global tab) or by clicking the Navigate Without Data button on the Essbase toolbar. 2 In cell A3, drill down (double-click) on Market.
● Zero data values A missing value is not the same as a zero value that is loaded into the Essbase database. When data does not exist for a data cell in Essbase, a value of #Missing is returned to the worksheet. If any cell in a row contains a value, that row is not suppressed on a retrieval. Using Essbase, you can suppress missing and zero values from the display in the worksheet. In addition, you can tell Essbase to suppress underscore characters that are in some member names.
Essbase suppresses product 100-30 from the South member group, as shown in Figure 43. Figure 43 Result of Suppressing Missing Data Values 7 Select File > Close to close the worksheet. You do not need to save the worksheet. After you enable the Suppress #Missing Rows feature in the Essbase Options dialog box, any missing values suppressed during a data retrieval are not retrieved again by disabling the feature.
This section of the tutorial starts with a new worksheet. Formatting Text and Cells In a spreadsheet report, many hierarchical levels of database information are displayed. By defining and applying visual cues, or styles, to the text and cells in the worksheet, you can easily keep track of specific database members, dimensions, and cell functions. Styles are an effective way of viewing and distinguishing data in Spreadsheet Add-in.
Figure 44 Essbase Options Dialog Box, Style Tab In the Members group box, you can define styles for various types of database members, such as parent, child, and shared members. 6 In the Members group box, select the Parent check box. Clicking this box defines a font and color style for parent member names. Essbase defines a default color of navy for all parent members. You can select a font format by clicking the Format button to the right of the Members group box and using the Font dialog box.
9 Click OK again. Even though you have defined styles, they are not enabled until you select the Use Styles check box from the Essbase Options dialog box and refresh the worksheet. 10 Select Essbase > Options, and select the Display tab. 11 In the Cells option group, select the Use Styles check box to enable the styles, and then click OK. 12 Select Essbase > Retrieve to refresh the worksheet and apply the styles. Essbase displays parent member names in bold, navy font.
Figure 47 Selecting a Background Color from the Style Tab 5 Click the Format button that is to the right of the Dimensions group box. Essbase displays the Font dialog box. 6 From the Font style list box, select Bold, and then click OK. Essbase displays an example of the selected style in the Sample box. 7 From the list of dimensions, select the Measures dimension, and from the Background Color drop-down list, select Fuschia.
Essbase redisplays the worksheet and implements the newly defined styles. For example, members of the Scenario dimension are displayed with a red background. Figure 48 Dimensions with Styles Applied Applying Styles to Data Cells You can apply styles to data cells, such as read-only cells, read/write cells, linked object cells, and Essbase Integration Server drill-through cells to distinguish them from other cells in the worksheet.
● Linked object cells ● Integration Server Drill-Through cells ● Read-only cells ● Read/write cells ● Parent member cells ● Child member cells ● Shared member cells ● Cells containing formulas ● Dynamic calculation member cells ● Attribute cells ● Dimension cells The only way you can apply a background color to data is to define a style for dimensions.
1. Select all cells in the worksheet. 2. From the Excel menu bar, select Edit > Clear > Formats. To turn off styles: 1. Select Essbase > Options and, select the Display tab. 2. In the Cells option group, clear the Use Styles check box, and click OK. Note: If you turn styles off without clearing them from the worksheet, the styles remain in the current worksheet view when you refresh the view.
Figure 49 Enabling Aliases in the Essbase Options Display Tab 6 Click OK. 7 Select Essbase > Retrieve to refresh the worksheet and display the alias names. The result is shown in Figure 50. Essbase changes the Product codes (100, 200, and so forth) to their predefined aliases (Colas, Root Beer, Cream Soda, and so forth). In the Sample Basic database, Product is the only dimension with predefined aliases.
➤ To display the name and alias of a member: 1 In cell B8, double-click the secondary mouse button to drill up on Qtr2. 2 In cell C2, select Colas, and then select Essbase > Pivot. 3 In cell C3. select Year, and then select Essbase > Pivot. 4 Select Essbase > Options, and select the Display tab. 5 In the Aliases option group, select the check box for Use Both Member Names and Aliases for Row Dimensions. Be sure that Use Aliases is already checked.
Figure 52 Enabling the Repeat Member Labels Option 4 In cell E1, drill down (double-click) on Year. Essbase displays a member label in every column and row cell, as shown in Figure 53. For the Sample Basic database that you are using for this tutorial, repeating member labels is probably not necessary because the database is relatively small. This feature is particularly helpful for keeping track of member labels when scrolling through large worksheets.
a. Select Essbase > Options, and select the Display tab. b. In the Cells option group, clear the Repeat Member Labels check box, and then click OK. c. Select File > Close to close the worksheet. You do not need to save the worksheet. Working with Duplicate Member Names An Essbase database may contain duplicate member names. Users can view the qualified name of a member either directly on a worksheet or by using the Comment functionality of Excel.
An Example Scenario For this example, the member name “Albany” appears under both the New York and California members in a Market dimension as well as in a Customer dimension. With duplicate member name support, Essbase can simply display “Albany” under New York and California in both dimensions.
Creating Queries Using Essbase Query Designer So far, you have discovered how to retrieve data and navigate through Spreadsheet Add-in in an ad hoc fashion. Essbase also provides a query designer so that you can define a database query for retrieving dimensions and database members into the worksheet. Before Essbase actually retrieves data, Essbase Query Designer provides a series of panels so that you can request the data that you want to view in the worksheet.
● “Deleting Queries” on page 77 ● “Viewing Messages and Confirmations” on page 77 ● “Accessing Help” on page 78 Note: Excel query functionality is not supported in Spreadsheet Add-in. Use the Essbase Query Designer to define database queries. About Creating and Changing Queries To access any of the Essbase Query Designer panels, select the appropriate feature listed in the navigation panel. As you create a query or make changes to an existing query, the changes are reflected in the navigation panel.
Figure 54 Essbase Query Designer Displaying Welcome Panel 2 In the navigation panel, select [Book1]Sheet1, right-click, and select New > Query. The layout panel of Essbase Query Designer is displayed, as shown in Figure 55. Figure 55 Essbase Query Designer Displaying Layout Panel 3 Define the worksheet layout by dragging the dimension tiles in the properties panel as follows: a. Drag Market and Product to the Row location. b. Drag Measures to the Page location. c.
Figure 56 Changing the Worksheet Layout 4 In the navigation panel, select the Measures dimension by selecting the Measures icon. Alternatively, doubleclick the Measures tile in the layout panel. The member select properties panel, where you can select a member from the Measures dimension, is displayed. Note: You can select only one member from the dimension in the Page location. 5 In the Members list box, select Profit, right-click, and select Add to Selection Rules.
Figure 57 Essbase Query Designer Displaying the Member Select Panel Note: In Essbase Query Designer, after you make your selections, you do not need to confirm them; for example, you do not have to click OK. If you do not select members from any given dimension, Essbase uses the top member of the dimension. 6 Select members of the Year dimension as follows: a. In the navigation panel, click the Year icon. Alternatively, double-click the Year tile in the layout panel.
Figure 58 Adding Members to the Selection Rules 7 Select members of the Scenario dimension as follows: a. In the navigation panel, select Scenario. Alternatively, double-click the Scenario tile in the layout panel. The members of the Scenario dimension are displayed in the member select properties panel. b. Select Actual, right-click, and select Add to Selection Rules. Actual is added to the Selection Rules list box. c. In the same manner, add Budget to the Selection Rules list box.
Figure 59 f. Selecting Members of Product To view the list of all product codes that will be retrieved into the worksheet, select any of the entries in the Selection Rules list box (for example, 200), right-click, and select Preview. Essbase displays the Member Selection Preview dialog box, as shown in Figure 60, with the selected product dimensions listed. Figure 60 g. Selected Members of Product Dimension Click Close to close the Member Selection Preview dialog box.
c. To pick the second generation of the Market dimension, in the Member list box, select Region, right-click, and select Add to Selection Rules. Alternatively, double-click Region to add it to the selection rules. Region is displayed in the Selection Rules list box. d. To view the list of members that will be retrieved into the worksheet, in the Selection Rules list box, select Region, right-click, and select Preview.
Figure 62 Essbase Query Designer Save As Query Dialog Box 3 Click the File System button. Essbase displays the Save As dialog box, as shown in Figure 63. Figure 63 Save As Dialog Box 4 Select a location, in the File name text box, type Basic1 and then click Save. As shown in Figure 64, Essbase Query Designer displays information about the query that you just saved. You will use the Basic1 query again in Chapter 4.
Figure 64 Essbase Query Designer Displaying Query Information Panel The next topic describes how to apply this query. Applying Queries ➤ To apply a query in Essbase Query Designer: 1 In the navigation panel, select [Book1]Sheet1, Basic1. 2 Right-click the Basic1 query, and select Apply Query. The result of the query is displayed in the worksheet, as shown in Figure 65.
Essbase > Retrieve. When Essbase returns the data to the worksheet, you are free to further investigate the data by performing Zoom, Keep Only, Remove Only, and Pivot operations. Note: The XLS file that results from applying the query can be saved for possible use as an Essbase data load data file. Deleting Queries You can delete a query only from the location where you saved that query.
Accessing Help Access online help or the tutorial for Essbase Query Designer by using the help panel. To access the help panel, in the navigation panel, select Help. For more information on a particular topic, click the Online Help button in the properties panel. To access the online tutorial, click the Tutorial button in the properties panel (shown in Figure 67). Note: The Tutorial button launches ssxleqd.pdf, which links to the tutorial for Essbase Query Designer.
Note: The book may be a number other than 1. For example, it may be [Book5], if four worksheets are already open. 4 Type your password, and click OK. Select Sample Basic, and click OK. 5 Select [Book1]Sheet2 (or Sheet3), right-click, and select Connect. The Essbase System Login dialog box is displayed. 6 Type your password, and click OK. Select Samppart Company, and click OK. Note: You are restricted to one connection per worksheet.
Figure 68 Results of Query with Options Applied 5 Select File > Close to close the worksheet. You do not need to save the worksheet. Selecting Members An Essbase database may contain hundreds or even thousands of members, making it difficult to remember each member name. You can use the Essbase Member Selection dialog box to find and select members and to define the layout of members in the worksheet.
4 Select Product again, and select Essbase > Member Selection. Essbase displays the Essbase Member Selection dialog box, as shown in Figure 70. In the Essbase Member Selection dialog box, Essbase displays the Product dimension in the Dimension dropdown list and its children, Colas, Root Beer, Cream Soda, Fruit Soda, and Diet Drinks, in the Members list box. Figure 70 Essbase Member Selection Dialog Box 5 Select Colas and click the Member Information button.
7 In the Essbase Member Selection dialog box, click Add to add Colas to the Rules list box. Alternatively, you can double-click an item in the Members list box to add the item. 8 Select Product, and click the Find button. Essbase displays the Find Member dialog box (see Figure 72). In the Find Member dialog box, you can do pattern-match searches for members in the selected dimension. You can use wildcard patterns—trailing asterisk *, and single-character match, ?.
18 In the Subset Dialog box, in the first drop-down list, select Caffeinated. In the second drop-down list, select Is. In the third drop-down list, select Caffeinated_True. 19 Click the Add as AND Condition button. Essbase displays Caffeinated = Caffeinated_True in the Conditions list box, as shown in Figure 73. Figure 73 Subset Dialog Box (Before Adding Conditions) When you use Add as AND Condition, the subsetting condition in the Conditions list box is evaluated using AND logic.
The Add ( and Add ) buttons add a left parenthesis and right parenthesis, respectively, to selected items. Use parentheses for grouping multiple subsetting conditions to determine the order of priority for analyzing the conditions. Each item in the Conditions list box can have either the left or right parenthesis, but not both. In this example, Essbase first evaluates members that are less than or equal to 32 ounces and are packaged in a bottle.
Figure 75 Member Selection That Results from Subsetting Conditions 29 Click Close to close the Member Preview dialog box. 30 Click OK to close the Subset dialog box and return to the Essbase Member Selection dialog box. The conditions that you set in the Subset dialog box are displayed in the Rules list box. 31 Select Diet Cream and click the Move Item Up button to change the order in which Diet Cream is displayed in the worksheet.
Figure 77 Members to be Retrieved in the Worksheet 33 After previewing the list, click Close. 34 Click OK to close the Essbase Member Selection dialog box and insert the new members into the worksheet (see Figure 78). Figure 78 Result of Selecting Members Note: The FlashBack command cannot undo a Member Selection action. 35 Starting with Diet Cream, type Year next to each product (see Figure 79).
Figure 80 Result After Retrieving with Member Selection Saving and Disconnecting After performing basic retrieval, navigation, and formatting tasks, you can save worksheets and disconnect from Essbase. This section instructs you in the following procedures: ● “Saving a Worksheet” on page 87 ● “Disconnecting from Essbase” on page 87 ● “Logging Off” on page 88 Saving a Worksheet At any point during the Essbase session, you can save the active worksheet with the commands, File > Save or File > Save As.
Figure 81 Essbase Disconnect Dialog Box 2 From the list, select a worksheet name, and then click Disconnect. 3 Repeat step 2 until you have disconnected all active sheets. 4 Click Close to close the Essbase Disconnect dialog box. Note: You can also disconnect from the server by closing the spreadsheet application. An abnormal shutdown of a Spreadsheet Add-in session, such as a power loss or system failure, does not disconnect your server connection.
4 Advanced Tutorial In This Chapter Preparing to Begin the Tutorial....................................................................................................89 Performing Advanced Retrieval Tasks ............................................................................................94 Using Linked Reporting Objects ................................................................................................ 133 Connecting to Multiple Databases ............................................
➤ To connect to an Essbase Server, Essbase application and database: 1 Select Essbase > Connect. The Essbase System Login dialog box is displayed, as shown in Figure 82. Figure 82 Essbase System Login Dialog Box Note: To complete the steps that follow, you need to know the name of the Essbase Server, your username, and your password. If you do not have this information, contact the Essbase system administrator.
Figure 83 Available Application and Database Pairs 6 In the Application/Database list box, double-click Sample Basic, or select Sample Basic and then click OK. If the application is not already running, Essbase automatically starts it. There may be a brief pause as the application loads; the time required to start an application depends on the number of databases, the sizes of the databases, and the sizes of the indexes of the databases that are contained within the applications.
Figure 84 Initial Settings for Display Options 4 Select the Zoom tab. 5 Select the appropriate check boxes and option buttons so that your display matches Figure 85. Figure 85 Initial Settings for Zoom Options 6 Select the Mode tab. 7 Select the appropriate check boxes and option buttons so that your display matches Figure 86.
Figure 86 Initial Settings for Mode Options 8 Skip the Style tab and select the Global tab. 9 Select the appropriate check boxes and option buttons so that your display matches Figure 87. Figure 87 Initial Settings for Global Options 10 Select OK to save the settings for this session and close the Essbase Options dialog box. The settings in the Essbase Options dialog box may change as you access the various sample spreadsheet files as part of the tutorial.
Performing Advanced Retrieval Tasks The tutorial in Chapter 3 describes how to perform basic data retrieval and navigation tasks in Spreadsheet Add-in.
➤ To become familiar with the capabilities of Essbase Query Designer, work with the query, Basic1, that you saved in Chapter 3, and perform the following steps: 1 Select Essbase > Query Designer. The query information panel of Essbase Query Designer is displayed. 2 In the navigation panel, select [Book1]Sheet1. 3 Right-click and select Open Query. The Open Query dialog box is displayed. 4 From the location that you specified in Chapter 3, select the Basic1 file. 5 Click OK.
● A “Dimension being ranked” drop-down list box to specify the dimension to which ranking should be applied. ● A “Column used for ranking” drop-down list box to specify the data column on which data values are based. ● A “Data Restrictions” list box to specify standard data comparison operations, such as greater than, less than, and equal to.
You can further filter the data output by specifying data comparison operations in the Data Restrictions list box. 11 In the navigation panel, select the Data Filtering icon. The data filters that you specified are displayed in the properties panel. 12 In the Data Restrictions list box, double-click. The data restriction settings are displayed in the properties panel. 13 Select the A value of option and type 500 in the value text box.
Figure 90 Data Filtering 29 Select the Data Filtering icon, right-click, and select Apply Query. Essbase retrieves data for all the quarters. Notice that the retrieved data for Qtr1, Actual is less than or equal to 500 or is less than Qtr2, Actual. The results should be displayed as shown in Figure 91: Figure 91 Data Filtering Results If you wanted to delete all data restrictions, select the Data Filtering icon in the navigation panel, right-click, and select Delete All Data Restrictions.
To delete a particular data restriction, select the data restriction in the query outline, right-click, and select Delete Data Restriction. Alternatively, select the data restriction in the Data Restriction box, right-click, and select Delete Data Restriction. Sorting Data In the data sort panel, you can sort the output from the Basic1 query in ascending or descending order. ➤ To sort the query: 1 From the navigation panel, select the Data Sorting icon.
The selection defaults to Qtr1, Actual. The sort order defaults to Ascending in the Ordering list box. 3 Click Ascending. A down arrow is displayed next to Ascending. 4 Click the down arrow next to Ascending. Descending is displayed below Ascending, as shown in the properties panel in Figure 93. 5 In the Ordering drop-down list, select Descending. Figure 93 Specifying Data Sorting Order 6 Double-click on the statement (double click to create a new sort rule). A new data sorting rule is added.
Figure 94 Result of Filtering and Sorting Data Note: The values that you are ranking and sorting must be the same. For example, you cannot specify Product in the “Dimension being ranked” drop-down list box and Market in the “Dimension being sorted” drop-down list box. If you specify different values, Essbase Query Designer automatically changes both values to the last specified value. 10 To close the worksheet, select File > Close. You do not need to save the worksheet.
If you retrieve data into an asymmetric report, Essbase must perform additional internal processing to maintain the asymmetric layout. This processing may increase the retrieval time on large reports. For more information regarding optimizing reports, see the Hyperion Essbase - System 9 Database Administrator's Guide. As part of the default Essbase installation, the Asymm.xlsl sample file is provided that illustrates how to create asymmetric reports. ➤ To view the sample file, Asymm.
Figure 96 Result of Pivoting in an Asymmetric Report Essbase combines the Product members into all unique members. For example, Root Beer, which is displayed twice in Figure 95 on page 102, is displayed only once in the current view. Colas, which is displayed in only one market in Figure 95, now is displayed in East and West. Essbase also removes the blank line between Product row groups. A pivot action always eliminates any rows or columns in which all cells are empty.
2. Select Essbase > Options. 3. In the Essbase Options dialog box, select the Zoom tab. 4. In the Sampling Percentage text box, type an integer between 1 and 100 to represent the approximate percentage amount of the Essbase cube to query during a Zoom In operation. The default value is 100. Note: If Hybrid Analysis is enabled and in use, the sampling percentage also applies to queries on the underlying relational database. 5.
● “Retrieving Data into Formatted Worksheets” on page 105 ● “Pivoting Data on Formatted Worksheets” on page 107 Observing the Rules for Working with Formatted Worksheets Observe the following rules when retrieving data into a formatted worksheet: Rule 1 In the worksheet, no numeric cells can be located before the first Essbase data cell. For example, in Figure 97 on page 106, the first Essbase data cell is B6. Neither any cell in rows 1 through 5 nor cell A6 can contain numeric values.
Note: Depending on how software is installed on your machine, the file may not be available or may be located in a different directory. Contact the Essbase system administrator for more information. Figure 97 A Sample Formatted Worksheet 3 Select Essbase > Options and select the Display tab. 4 In the Cells option group, make sure that Adjust Columns is checked. 5 Select the Mode tab. 6 In the Formula Preservation group, select the Retain on Retrieval check box to enable Formula Preservation mode.
Figure 98 Essbase Unknown Member Message In this example, the first unknown member detected is Market: (in cell A1). Essbase does not recognize the colon (:) that follows Market in the cell. If you click Yes, Essbase displays the next unknown member; if you click No, Essbase continues with the retrieval. Note: If you work with formatted worksheets often, you may want to configure Essbase so that this message is not displayed.
database elements represented in the worksheet. The worksheet also retains labels in areas that are not overwritten by pivoted data. Note: Essbase prevents pivot operations on worksheets that contain formulas when Formula Preservation mode is active. As part of the default Essbase installation, the Inv.xls sample file is provided which illustrates how to pivot data in a worksheet. The sample file was saved with the Retain on Retrieval option disabled so that you can pivot on its worksheets.
Figure 101 Result of Pivoting on a Formatted Worksheet 6 Close the file without saving it. Preserving Formulas When Retrieving Data In “Retrieving Data into Formatted Worksheets” on page 105, you used the Retain on Retrieval option to preserve formatting and formulas in an existing worksheet. Collectively, the Formula Preservation options enable you to retain formulas during data retrievals, keep and remove only operations, and drill operations.
➤ To preserve formulas when retrieving or retaining data: 1 Select File > New or click to open a new worksheet. 2 Select Essbase > Retrieve. 3 In cell A2, drill down (double-click) on Year. 4 Press and hold down the Alt key and, in cell E1, drill down (double-click) on Scenario. 5 Select cell G3 and enter the following formula in the cell: =B3/B$7*100, as shown in Figure 102. The $ in front of the 7 anchors the formula to the Year member. Figure 102 Entering a Formula into a Cell 6 Press Enter.
Figure 104 Result of Removing Columns With Retain on Keep and Remove Only Selected 11 Select Essbase > Options and select the Mode tab. 12 In the Formula Preservation group box, select the check box for Retain on Zooms and click OK. 13 In cell A3, drill down (double-click) on Qtr1. Essbase drills down on Qtr1 and moves the formula down with the Qtr1 member. The result is shown in Figure 105. Figure 105 Result of Drilling Down with Retain on Zooms Enabled 14 Select Essbase > FlashBack.
b. Clear all of the Formula Preservation options. c. Select File > Close to close the worksheet. You do not need to save the worksheet. Retrieving a Range of Data In a typical worksheet, you can select a range of cells by dragging the mouse across the worksheet. You can also select a range of cells and tell Essbase to restrict the data retrieval to the selected range in the worksheet. Retrieving a range of data is particularly useful in the following situations: ● A worksheet contains multiple reports.
Figure 107 Selected Range of Cells for Retrieval 3 Select Essbase > Retrieve to update the selected range. Essbase updates only the data in the selected range of cells, as shown in Figure 108. Figure 108 Result of Retrieving First Range of Data 4 Select cells B12 through G16, as shown in Figure 109. Figure 109 Selecting Cells for Retrieval 5 Select Essbase > Retrieve once again to update the selected range. Essbase updates the data in the selected range, as shown in Figure 110.
Figure 110 Result of Retrieving Second Range of Data 6 Close the file without saving it. Retrieving Data by Using a Function The Essbase cell retrieve function, EssCell, retrieves a single database value into a worksheet cell. Enter an EssCell function directly into a worksheet or select an EssCell function from the spreadsheet menu bar. Note: You must be connected to a database to use EssCell.
Figure 111 Worksheet Containing EssCell Functions In Excel, cells B16 and B17 contain the EssCell function. If you select either of these cells, you can view the syntax for the EssCell function in the formula bar at the top of the worksheet. The EssCell function in Excel is defined in a cell as follows: =EssCell(mbrList) In Excel, mbrList is one of the following factors: ● A null value. If the parameters of the function are empty, Essbase returns the data value from the top of each dimension.
Figure 112 Retrieval on a Worksheet Containing the EssCell Function Now update the EssCell functions to retrieve data for a different state. 4 Change the contents of cell A2 from Texas to Florida. The values in cells B16 and B17 are updated as soon as you update the cell. The update occurs because Excel has recalculated the worksheet (if you have configured Excel to calculate changes automatically). The remaining data cells do not change.
Table 4 EssCell Messages Message Reason #N/A The worksheet is not connected to a database. #VALUE! A member name in the list or reference is invalid. #NAME? A text name in the function does not contain double quotation marks. 6 Close the file without saving it. EssCell functions are already defined in the sample file that you used for this tutorial task. This task is optional. Optional tasks do not need to be performed as part of the tutorial. They are provided for information only.
Because there may be a performance impact on retrieving data for dynamic calculation members, define visual cues, or styles, for these members so that you can identify them in Spreadsheet Add-in. As part of the defaultEssbase installation, the Asymm.xls sample file is provided that illustrates how to use Dynamic Calculation members. ➤ To view the Asymm.xls file: 1 Select File > Open. 2 From the \AnalyticServices\client\sample directory, open the Asymm.xls file.
Figure 115 Result of Retrieving Dynamic Calculation Members Note: Occasionally, other styles that you have set will override a style for dynamic calculation members. For example, if a parent member is also a dynamic calculation member, and if you have set styles for both parent and dynamic calculations, the style for the parent member overrides the style for the dynamic calculation member. You must remove the style for parent members in order to see the style for dynamic calculation members.
5 Select Essbase > Retrieve. 6 In cell B1, drill down on (double-click) Measures. 7 In cell B2, click Year. 8 Type Q-T-D in cell B2 and press Enter to enter a predefined Dynamic Time Series member (Q-T-D). The result is shown in Figure 116. Figure 116 Entering a Dynamic Time Series Member into a Worksheet Note: For a list of other possible Dynamic Time Series members, see the Spreadsheet Add-in online help. 9 Select Essbase > Options and select the Display tab.
11 Click OK. 12 Select Essbase > Retrieve. Note: The Retrieve & Lock, Zoom In, and Zoom Out commands are not supported with Dynamic Time Series members. Essbase displays data for the Q-T-D member, as shown in Figure 118. The data values in the worksheet are the aggregated values for April and May, because May is the month that you specified as the latest month in the quarter-to-date Dynamic Time Series.
The Sample Basic database that you are using for this tutorial does not contain predefined substitution variables. If the application designer had set a substitution variable in the database that you are using, you could enter a substitution variable directly into a worksheet. For example, you could open a blank worksheet and type member names, as shown in Figure 119. Figure 119 Entering a Substitution Variable in a Worksheet Notice the substitution variable (CurMnth) in cell A2.
In both retrieval modes, enter member names directly into the worksheet. The following sections describe the similarities and differences between the two modes. Using Advanced Interpretation Mode Essbase Server server contains an advanced spreadsheet interpretation engine that scans a worksheet and interprets its content when fulfilling retrieval requests.
Figure 122 Retrieving Data into a Free-Form Report Now define a free-form report that does not contain all the dimensions from the database. In Advanced Interpretation mode, you may need to enter a dummy data value, such as 0, into the first data cell to indicate to Essbase where the data starts in the worksheet. Be sure to use a numeric value as the dummy value. For example: 6 Select Essbase > FlashBack. 7 Delete cells A1, B1, and C1.
Report script commands are most useful for defining member references that can bring back the most current member information dynamically. For example, if you need to create a report that shows every product, including the products added since the last retrieval, standard retrieval mode reflects changes only when you drill down on the product. If you use the report script command
Figure 125 Typing Member Names In a Free-Form Report 7 Select Essbase > Retrieve. Essbase retrieves data for the members and creates a default view according to the location of the labels. Note that in Figure 126, three members were pivoted from row groups to column groups. Figure 126 Result of Retrieving in Free-Form Retrieval Mode 8 Select File > Close to close the worksheet. You do not need to save the worksheet.
Figure 129 Result of a Retrieve with a Report Script Command Note: When Essbase completes the retrieval, the report script command is overwritten by the data it returns. You can use FlashBack to restore the previous view in Free-Form mode. 5 Select File > Close to close the worksheet. You do not need to save the worksheet. Using Attributes in Free-Form Reporting Essbase enables you to retrieve data selectively by specifying attributes that are associated with a base dimension.
Figure 131 Result of Using Attributes in Free-Form Reports 4 You can drill down to data on the level 0 attribute members of the Caffeinated attribute dimension. The results should look like Figure 132. Figure 132 Drilling Down on Attributes in Free-Form Reports 5 To drill down further for data on profits for the first quarter for all members of the East base dimension, double-click cell E1. The results should look like Figure 133.
2 Select Essbase > Options and select the Mode tab. 3 In the Retrieval option group, select Advanced Interpretation. Click OK. 4 Enter member names as shown in Figure 134. Figure 134 Entering Member Names in a Free-Form Report 5 Enter a generation name as shown in Figure 135. Family is a generation name in the Product dimension. The name is already defined in the Sample Basic database. Figure 135 Entering a Generation Name in a Free-Form Report 6 Select Essbase > Retrieve.
As shown in Figure 138, Essbase retrieves data for the level 0 members of the Year dimension, which are the individual months (Jan, Feb, Mar, and so forth). Figure 138 Result of Free-Form Retrieval with Level Name 9 Select File > Close to close the worksheet. You do not need to save the worksheet. Retrieving Data Using Visual Explorer Visual Explorer provides Essbase users with powerful analytics in a highly graphical format.
Visual Explorer is launched directly from the Essbase menu in Spreadsheet Add-in for Excel, using the “Visualize & Explore” command. Using the technique of dragging interface elements, Essbase users can quickly summarize and visualize data from an Essbase database. Visual Explorer then offers users the option to pass data back to the Excel worksheet from which it was launched, or to insert the data into a new Excel worksheet where additional analysis can be performed.
Note: If you save the connection name, it is displayed on subsequent logins in the Connect to Data dialog box (see step 3.b. You are now ready to use Visual Explorer. 4 Under Marks, select Bar from the drop-down list. 5 Drag Year from the Dimension section of the Data window and drop it on the Column shelf. 6 Drag Sales from the Measures section of the Data window and drop it on the Column shelf, placing it to the right of Quarter.
Figure 140 Bar Chart Showing Product 100-10 East as Most Profitable by Sales You are now ready to pass the data from the visual worksheet into an Excel worksheet in Spreadsheet Add-in. This task is optional. Optional tasks do not need to be performed as part of the tutorial. They are provided for information only. In Visual Explorer you can further analyze the data in the Sample Basic database by completing any of the following tasks: ● Drag State from the Database Schema and drop it on the Page shelf.
Resource Locator) can then be retrieved by the Spreadsheet Add-in users who have access to the database. Note: If your organization has licensed and implemented the Essbase Partitioning option, you can also access linked partitions from cells in Spreadsheet Add-in. For more information on linked partitions, see “Ways to Access Linked Partitions” on page 145.
Figure 141 Selecting a Data Cell for Linking an External File 5 Select Essbase > Linked Objects. Essbase displays the Linked Objects Browser dialog box, as shown in Figure 142. Figure 142 Linking a File 6 In the Linked Objects Browser dialog box, click the Attach button. Essbase displays the Attach Linked Object dialog box. 7 Under the Attachment Type group box, select File, as shown in Figure 143.
10 Click Open. 11 Under File Description, enter a brief description for the file as indicated in Figure 144. Figure 144 Selecting and Describing a File to Link Note: Entering text in the File Description text box is optional. 12 Click OK to close the dialog box and link the file to the cell. Essbase copies the file to the server and establishes a link to the current data cell. 13 Click Close to close the Linked Objects Browser dialog box. Do not close the Asymm.xls file.
Figure 145 Result of Applying a Style to a Linked Reporting Object Cell Leave the file (Asymm.xls) open for the next tutorial task. Linking a Cell Note to a Data Cell In addition to linking external files to a data cell in Spreadsheet Add-in, you can also link individual cell notes that contain information on particular data cells. Cell notes can consist of no more than 599 characters.
Essbase copies the note to the server and establishes a link to the current data cell. 7 Click Close to close the Linked Objects Browser dialog box. 8 Select Essbase > Retrieve to refresh the worksheet and apply the style that you defined for linked objects. Now Essbase displays two data cells (C5 and D5) in purple, italic font to represent a cell that contains a linked reporting object. The result is shown in Figure 147. Figure 147 Result of Creating a Linked Cell Note Leave the Asymm.
Figure 148 Selecting a Data Cell for Linking to a URL 2 Select Essbase > Linked Objects. Essbase displays the Linked Objects Browser dialog box. 3 In the Linked Objects Browser dialog box, click Attach. Essbase displays the Attach Linked Object dialog box. 4 Under the Attachment Type option group, select URL, as shown in Figure 149. Figure 149 Linking a URL to a Data Cell 5 Enter a URL in the Location text box and a brief description in the URL Description text box, as shown in Figure 150.
6 Click OK to close the dialog box and link the URL to the cell. Essbase copies the URL string to the server and establishes a link to the current data cell. Note: The syntax for the URL is not checked at the time of creation; Essbase checks the syntax when the user accesses the URL from the worksheet. The default Web browser checks for the existence, or validity, of the URL. 7 Click Close to close the Linked Objects Browser dialog box.
Figure 151 Accessing a Linked External File 4 Click View/Launch to view the linked file. The Budasmp.txt file is opened from the source application, as shown in Figure 152. Figure 152 Viewing the Contents of a Linked External File Note: You can edit the contents of a file in the source application. After the edits are made and the file is saved, you can re-attach the edited file by clicking the Edit button in the Linked Objects Browser dialog box.
The Linked Objects Browser dialog box displays the cell note that is linked to the selected data cell. 3 In the Linked Objects Browser dialog box, select the cell note, as shown in Figure 153. Figure 153 Accessing a Linked Cell Note 4 Click Edit to edit the contents of the cell note. Essbase displays the Edit Cell Note dialog box with the selected cell note displayed, as shown in Figure 154.
Essbase saves the edits to the cell note on the server. 7 Click Close to close the Linked Objects Browser dialog box. Leave the file (Asymm.xls) open for the next tutorial task. Accessing a Linked URL If you followed the steps in “Linking a URL to a Data Cell” on page 138, you can access and edit the URL that you created. ➤ To access the URL: 1 In the Asymm.xls file, select cell E5. 2 Select Essbase > Linked Objects.
Figure 157 Preparing to Edit the Contents of a Linked URL 3 Edit the URL location and description as shown in Figure 158. Figure 158 Editing the Contents of a Linked URL 4 Click OK to close the Edit URL dialog box and save the edits that you made. Essbase saves the edits to the URL on the server. 5 Click View/Launch to view the new URL. The Web browser launches and connects to the new URL. 6 Close the Web browser. 7 Click Close to close the Linked Objects Browser dialog box. 8 Close the Asymm.
This task is optional. Optional tasks do not need to be performed as part of the tutorial. They are provided for information only. To access multiple databases: 1. Select Essbase > Connect. 2. In the Essbase System Login dialog box, select the server that you want to access from the Server list box (or type in the name of the server). 3. Press Tab to move to the Username text box and type your username. 4. Press Tab to move to the Password text box and type your password. 5.
Note: The Partitioning product also enables the Essbase application designer to set up transparent or remote partitions. For more information on partitioning, see the Hyperion Essbase - System 9 Database Administrator's Guide. You can set visual cues, or styles, for cells tagged as linked objects. These cells are access points to the linked partition within the linked database.
● Select Essbase > Linked Objects to open the Linked Objects Browser dialog box. ● In the Essbase Options dialog box, select the Enable Linked Object Browsing check box to enable doubleclicking to view linked objects. 3. Select the partition that you want to connect to and click View/Launch. Essbase creates a new worksheet that contains the dimensions and members for the cell in the linked partition. You need the proper privileges to access a linked partition.
To update the server with data values from the worksheet, use the Send command on the Essbase menu. After updating the server, the Send command automatically unlocks data (unless you are in Update Mode). You must clear Update Mode to stop the automatic locking of blocks. You can unlock data blocks in two ways: ● The Unlock command unlocks all blocks that you have locked.
Figure 160 P&L Worksheet After Sending New Values to the Server 6 Close P&l.xls without saving it. Essbase Essbase provides a spreadsheet sheet update logging feature that tracks and logs all data updates sent from Spreadsheet Add-in to the server. The Essbase system administrator enables this feature for extra protection against data loss. For more information on spreadsheet update logging, see the Hyperion Essbase - System 9 Database Administrator's Guide or contact the Essbase system administrator.
Figure 161 Essbase Calculation Dialog Box The Essbase Calculation dialog box contains the following items: ● The Connection Information text box displays the active database connection. ● The Select Calc Script list box contains the server-based calculation scripts to which you have access. ● The Database State status box indicates the current calculation state of the database. The following states are possible: ❍ Calculating Indicates that a calculation is currently running on the database.
Using the Essbase Cascade feature, you can create multiple worksheet files based on a single database view. You can specify at what level of detail you want to replicate the worksheets to tailor the information to each recipient’s needs. The Sample Basic database contains data for beverage products sold in different states across the U.S. For example, assume that you want all product managers to review and respond to a proposed budget and to return their changes to the finance department.
Figure 162 Cascade Information Tab The replicated, or cascaded, spreadsheet reports now provide data for members at the same level as Central (East, West, and South) and for members at the level below Root Beer (Old Fashioned, Diet Root Beer, Sarsaparilla, and Birch Beer). 9 Click the Destination Options tab. 10 In the Destination Directory text box, type C:\temp as the name of the directory where you want the cascaded worksheets to be stored.
Figure 163 Destination Options Tab When you assign a prefix or suffix in the Naming Information group box, the worksheet files that are generated as a result of executing the Cascade command are named with the prefix or suffix that you specify. The default is to generate worksheet names that are numbered 1 through n, where n is the total number of worksheets created. The syntax for the file names is PrefixnSuffix.xls for Excel. If you do not specify a prefix or suffix, Essbase creates the worksheets 1.
15 Select the Copy Formatting check box to copy the formatting of the source worksheet into each cascaded worksheet. Copy formatting copies only the visual cues set using Essbase and the cell formatting that you set using the worksheet. It does not copy formulas, column formatting, worksheet formatting, or graphs. 16 In the Header and Footer text boxes, specify a header or footer name to be used for all of the cascaded worksheets.
Working with Currency Conversions Organizations with offices in different countries generally do business in the currency of the host country (known as the local currency). Such organizations must convert data entered in local currencies to a common currency for consolidation and analysis. The Essbase Currency Conversion product can be purchased separately for Essbase.
Connecting to the Sample Currency Databases To complete the following exercises, the Sample Interntl and Sample Xchgrate databases must be installed on the server. Contact the Essbase system administrator if these application and database pairs are unavailable. ➤ To retrieve data from the Sample Interntl database: 1 Select Essbase > Connect. 2 Select the Sample Interntl database and click OK to complete the connection.
The worksheet contains data that is converted to U.S. dollars. Values for New York remain the same, but German values are converted. Essbase converts the values by using the exchange rates from the Sample Xchgrate database. 6 From the \AnalyticServices\client\sample directory, open Rates.xls. 7 Connect to the Sample Xchgrate database. 8 Select Essbase > Retrieve. The portion of the result is shown in Figure 168.
➤ To perform an ad hoc conversion on data in the Convert.xls file: 1 From the \AnalyticServices\client\sample directory, open Convert.xls. The worksheet contains data that is already converted to U.S. dollars. 2 Select Essbase > Connect and connect to the Sample Interntl database. 3 Select Essbase > Retrieve. 4 Select Essbase > Currency Report. Essbase displays the Essbase Currency Report dialog box, as shown in Figure 169.
8 Click the Clear button in the Essbase Currency Report dialog box to make currency reporting unavailable and return to standard retrieval mode. Performing a currency report retrieval does not change values in the database. The process performs a temporary conversion as part of the retrieval. Converted data values may not always balance, because the ad hoc conversion is performed on values that were previously calculated or previously consolidated in another currency.
Advanced Tutorial
Using Drill-Through 5 In This Chapter About Drill-Through .............................................................................................................. 161 Before You Start.................................................................................................................. 163 Using Drill-Through............................................................................................................... 169 Disconnecting from Essbase ........................................
Figure 171 Example of a Drill-Through Sheet The dimensional attributes of the cell are as follows: Actual, Profit, New York, Feb, and Product. The combination of one or more of these attributes becomes the basis for a drill-through query that returns data from the relational source. From Spreadsheet Add-in, you can access a predefined drill-through report that is based on the dimension or member intersections of Essbase data cells in the sheet.
by drill-through users. If a report can be customized, you use the Drill-Through Wizard to customize it. The Drill-Through Wizard is a graphical user interface that steps you through the following customization tasks: ● Selecting columns to retrieve from the relational data source Decide which columns from the predefined report you need to see. ● Selecting the display order for columns Change the default display order of columns across the sheet.
● You must install the following components on your client computer: ❍ A 32-bit version of Excel ❍ Essbase Spreadsheet Add-in ❍ The Drill-through module The drill-through module is installed automatically when you install Spreadsheet Addin. This module is transparent until you invoke it from the Linked Objects Browser. For more information on installation, contact the Essbase system administrator. ● The Essbase system administrator must install the Essbase Server.
● If you make a mistake during the tutorial, select Essbase > FlashBack to return to the previous spreadsheet view. Setting Essbase Options Before you begin the tutorial, make sure that the spreadsheet options are set to the initial settings, as illustrated in Figure 173 through Figure 177. If your option settings are different, the illustrations presented in this chapter may not match the spreadsheet view.
Figure 174 Initial Settings for Zoom Options 6 Select the Mode tab. 7 Select the appropriate check boxes and option buttons so that your display matches Figure 175. Figure 175 Initial Settings for Mode Options 8 Select the Style tab. 9 Select the appropriate check boxes and option buttons so that your display matches Figure 176.
Figure 176 Initial Settings for Style Options 10 Select the Global tab. 11 Select the appropriate check boxes and option buttons so that your display matches Figure 177. Figure 177 Initial Settings for Global Options 12 Click OK to save the settings for this session and close the Essbase Options dialog box.
About the Samples Used in This Tutorial The sample database used for this tutorial contains the following dimensions: Scenario, Product, Market, Measures, and Year. The sample spreadsheet file shown in Figure 178 provides a particular view from the sample database. Figure 178 View from Sample Database For this spreadsheet view, detail-level data exists in a relational data source—data that is not available from Essbase.
● The Product Detail drill tab displays the results for Product Detail when you run a drillthrough report on cell D5.
➤ To access the sample file and sample database: 1 Start the spreadsheet application. 2 Select File > Open and open the Essdt.xls file from the \AnalyticServices\client \sample directory. The sample file should look like Figure 179. In this example, the Market Detail tab is selected. The default tab that is selected when you first open the file may be different. Figure 179 Sample Spreadsheet File for Drill-Through The sample file shows data for specific members of an Essbase database.
Figure 180 Font Dialog Box Selection In the Essbase Options dialog box, Essbase displays an example of the selected style in the Sample box. Figure 181 shows how the Essbase Options Style tab looks with the style for drill-through cells defined. Figure 181 Sample Style for Drill-Through Data Cells 9 In the Essbase Options dialog box, select the Display tab, and then select the Use Styles check box, as shown in Figure 182.
Figure 182 Setting the Use Styles Option 10 Click OK to close the Essbase Options dialog box. 11 Select Essbase > Retrieve to display the new style in the spreadsheet. In Figure 183, the sample drill-through report is associated with the data cells for Actual, Profit, and Product at the month and Eastern state levels, so that these data cells are displayed in blue, bold, and italic font.
● The same physical table and column in the relational source database ● The same member level in the underlying OLAP metaoutline ● The same hierarchy A multi-cell drill-through operation is valid only if all three criteria noted above are met. A message is displayed if the combination of cells you select is not valid for performing a multicell drill-through operation.
develops drill-through reports specifies whether you can customize a report and whether you need to log in to access the drill-through report and the relational data source. 5 Follow the steps in “Selecting Drill-Through Reports to View or Customize” on page 174 to select a report to customize.
Figure 186 Initial Drill-Through Report for Measures Detail 2 Select Essbase > Connect and connect to the appropriate sample database. Note: A specific sample database for drill-through is not automatically provided with Integration Services. Contact the person at your organization who installs Integration Services to set up a database for you. 3 Select Essbase > Options and select the Style tab to define styles for this sheet.
Figure 187 Sample Style for Drill-Through Data Cells 7 In the Essbase Options dialog box, select the Display tab and select the Use Styles check box, as shown in the Cells group box in Figure 188. Figure 188 Setting the Use Styles Option 8 Click OK to close the Essbase Options dialog box. 9 Select Essbase > Retrieve to display the new style in the spreadsheet.
Figure 189 Sample File with Drill-Through Style Applied ➤ To access the sample drill-through report from Spreadsheet Add-in: 1 Select any drill-through cell; for example, cell G6, as shown in Figure 190. Figure 190 Selecting the Drill-Through Cell for the Measures Detail Report If Integration Services is not running, the drill-through process does not launch properly. If drill-through is not launching properly, contact the Essbase system administrator.
4 Select the Measures detail report in the Available Reports list, as shown in Figure 191. Figure 191 Selecting the Sample Drill-Through Report 5 Click Customize. Note: The Customize button may or may not be selectable for any given report, depending on how the report was defined in Integration Services Console. Integration Services displays the first screen of the Drill-Through Wizard, as shown in Figure 192.
Figure 193 Select Columns and Display Order Dialog Box 7 Follow the steps in the topic, “Selecting and Ordering Columns” on page 179, to select and order rows for the customized report. Selecting and Ordering Columns Using the Drill-Through Wizard, you can customize predefined drill-through reports. The first task in using the Drill-Through Wizard is selecting and ordering columns to retrieve from the relational database.
Figure 194 2 Click Selecting Columns to Remove from the Drill-Through Report to move the selected column from the Selected Columns list back to the Available Columns list. Note: To move a column from one list to another, click to another, click or or . To move all columns from one list . 3 Click Next to display the Select Data Sort Order dialog box, and follow the steps in the topic, “Ordering Data” on page 180 to further customize the report.
2 Click to move the Time.TRANSDATE column to the Column list, as shown in Figure 195, so that you can define a sort order for the column. Note: To move a column from one list to another, click to another, click Figure 195 or or . To move all columns from one list . Moving a Column to the Column List for Sorting 3 In the Column list, double-click the Time.TRANSDATE column to change the data sort order from Ascending to Descending, as shown in Figure 196.
Note that this task is optional. Optional tasks do not need to be performed as part of the tutorial. They are provided for information only. To change the data sort order for multiple columns at one time, perform these tasks: a. Hold down the Ctrl key and select the desired columns from the Column list. b. Click Order By. Integration Services displays the Order By dialog box. Figure 197 Order By Dialog Box c. Select Ascending or Descending and click OK to return to the Select Data Sort Order dialog box.
As shown in Figure 198, the columns in the Column list box are those that you selected in “Selecting and Ordering Columns” on page 179. Figure 198 Select Data Filters Dialog Box If there is a filter already attached to the column, it is displayed in the Condition column. The full string of the filter is displayed in the lower Condition text box. 2 With the MEASURES.CHILD column selected, click Add condition. The Set Filter on Column dialog box is displayed, as shown in Figure 199.
Note: You can select multiple values at one time only if you have selected In or Not In as the filter operator. For more information on filter operators, see the Drill-Through online help. 5 Click the Browse button next to the Condition text box to open the Select Filter Values from the List dialog box, which lists all possible values for that column. The Select Filter Values from the List dialog box is displayed.
Figure 201 Defining a Filter for a Column The filter defined above causes all children of Measures, except Misc data, to show in the drillthrough report. The Add button becomes unselectable after you create the first filter, but becomes selectable when you create another filter. In this tutorial, you are creating only one filter. The And and Or options are used when combining multiple filters.
To clear a filter for a selected column, select the filter and click Clear. To clear all filters for all columns, click Clear All. You can save the filter that you just created and then apply it to the MEASURES.CHILD column, so that all children of Measures, except Misc, are included in the report. ➤ To save the filter that you just created: 1 In the Select Data Filters dialog box, click Add new filter. The Filter Name dialog box is displayed.
Figure 204 Customized Drill-Through Report In this sample, the customized drill-through report reflects the specifications that you set using the Drill-Through Wizard: ● The Time.TRANSDATE column is sorted in descending order, displaying the transaction dates in reverse chronological order. ● All children of Measures, Additions, COGS, Marketing, Payroll, Sales, and Opening Inventory, except Misc, are displayed as you specified in the filtering part of the DrillThrough Wizard.
Oracle's Hyperion® Essbase® – System 9 may return an error message when you attempt to disconnect after using drill-through. If an error message is returned, select Essbase > Retrieve from the sheet and then disconnect. 2 Select a sheet name from the list and click Disconnect. 3 Repeat step 2 until you have disconnected from all active sheets. 4 Click Close to close the Essbase Disconnect dialog box. Note: You can also disconnect from the server by closing the spreadsheet application.
A B C D E F G H I K L M N O P Q R S T U V W X Z Index Symbols #Missing strings, suppressing, 53 #NoAccess strings, suppressing, 53 * wildcard character, 82 ? wildcard character, 82 A access to databases, 33, 89 to Essbase data, 33, 89 to linked partitions, 145 to linked reporting objects, 140 to multiple applications, 34 to multiple databases, 144 to online help, 26 using drill-through reports, 169 using Essbase commands, 32 ad hoc reports, 11, 35, 107, 157 Add button, 183 Add-in M
A B C D E F G H I database status, 150 databases, 117, 149 dynamic, 117 reducing calculation time, 117 scripts, 150 specifying latest time period, 119 substitution variables, 121 with Dynamic Time Series, 119 with EssCell, 116 canceling data retrievals, 37 Cascade command, 150 Cascade Information page, 151 Cascade Options dialog box, 151 cascading sheets creating table of contents, 154 destination, 152 format, 153 level of detail, 151 names, 152 output type, 152 cell notes accessing linked, 141 lin
A B C D E F G H I K L Lock, 147 Member Selection, 81 Navigate Without Data, 49, 52 Pivot, 44 Remove Only, 49 Retrieve, 36 Retrieve & Lock, 147 Send, 148 Undo, 37 Unlock, 148 Zoom In, 38 Zoom Out, 41 comparison operators, 80, 96 compatibility with Hyperion Smart View for Office, 8, 28 concurrent database access, 34, 144 updates to server, 147 conditional retrievals, 94 Connect button, 24 Connect command, 34, 90, 144 connecting, 33.
A B C D E F G H I K L selected members, 49 styles, 60 descendants (defined), 18 descending sort order in drill-through reports, 180 with Query Designer, 99 Destination Options page, 152 destinations cascaded sheets, 152 Query Designer queries, 74 detaching. See disconnecting dialog boxes, Help buttons, 26 dimensions alternate, 15 applying styles, 57 currency conversion and, 155 described, 15, 17 drilling down on, 38, 39 drilling up on, 41 naming elements, 17 pivoting, 44 disabling data retrieval.
A B C D E F G H I K L M Enable Hybrid Analysis option in Zoom page (Essbase Options dialog box), 30 enabling compatibility with Hyperion Smart View for Office, 28 Essbase toolbar, 23 FlashBack setting, 38 mouse actions, 27, 140, 146 Navigate Without Data, 50 Essbase architecture, 12 Cascade Options dialog box, 151 connecting to. See connecting Disconnect dialog box, 87 disconnecting from.
A B C D E F G H I K L M EssCell, 114 expanding when drilling, 109 in cells, 104, 109 preservation effects on other operations, 109 enabling, 106, 109 restrictions with, 108 free-form reporting entering generation and level names in, 128 in Advanced Interpretation mode, 123 in Free-Form mode, 124 retrieving data, 122 Free-Form retrieval mode, 124 Free-Form retrieval mode guidelines, 125 functions.
A B C D E F G H I on to a relational data source, 177 on to Essbase. See connecting on to Integration Server, 177 logging data updates from spreadsheet, 149 logical operators, 83 logouts, forced or auto, 88 M macro functions EssCell, 114 Spreadsheet Toolkit, 14, 26 main menu (Essbase), 23 manual calculation mode, 116 Member Information dialog box, 81 member names aliases for, 61 alternate.
A B C D E F G H I numeric values, preserving, 105 O ODBC clients, 14 OLAP defined, 11 server.
A B C D E F G H I K L R read-only cells, 59 read/write cells, 59 registering Spreadsheet Add-in, 22 relational data source, 177 relational databases, accessing using drill-through, 161 remote databases . See linked partitions Remove Only button, 25 Remove Only command described, 49 in Formula Preservation mode, 109 Remove Unselected Groups option, 109 removing.
A B C D E F G H I K L S sample databases connecting to, 35, 156 described, 33, 155 in drill-through, 168 files for tutorial, location, 89 Sample Basic connecting to, 35 described, 33 Sample Data (Zoom In) command, 103 Sample directory, 89 sampling, 103 Save As command, 87 Save command, 87 saving drill-through filters, 186 queries, 74 worksheets, 87 saving queries, 74 secondary mouse button, 26 select (defined), 27 Select Calc Script option, 150 Select Columns and Display Order dialog box, 179 Sel
A B C D E F G H I style options, 55 suppressing missing and zero values, 53 zoom options, 42 SQL Interface, 14 starting data retrieval, 36 drill-through, 169 Essbase, 23 Spreadsheet Add-in, 23 status of database connections, 145 Style page (Essbase Options dialog box), 55 styles applying to data cells, 59 to dimension members, 57 to dimensions, 57 to drill-through cells, 169 to linked partition cells, 146 to linked reporting object cells, 136 to members, 55 to parent members, 55 to Query Designer r
A B C D E F G H I Use Styles option, 57 username, entering, 90 users, 12 V V2.x mode, 125 viewing active connections, 145 aliases, 61 aliases and names, 62 data in spreadsheet, 36 Dynamic Calculation members, 118 Essbase menu, 23 Essbase toolbar, 23 fewer members. See drill linked partitions, 145 linked reporting objects, 140 more members.
