Designing a Single-Server Application

To implement a multidimensional database, first you install Hyperion Essbase OLAP Server, and then you design and create an application. You analyze data sources and define requirements very carefully and then decide whether a single-server application or a partitioned, distributed approach best serves your needs. For criteria that you can review to decide whether to partition an application, see Designing Partitioned Applications

Using a case study, this chapter provides an overview of the database planning process and discusses working rules that you can follow to design a single-server, multidimensional database solution for your organization. For detailed information about building applications and databases, see Creating Applications and Databases

This chapter includes the following sections:

• Designing an Application
• Case Study: The Beverage Company
• Planning and Analyzing
• Drafting an Outline
• Loading Test Data
• Defining and Testing Calculations
• Defining Reports
• Verifying the Design

Designing an Application

As illustrated in Figure 5-1, designing an application is a cyclic process that moves from a planning stage to verification stage.

Figure 5-1: The Database Design Cycle

The database design process includes the following basic steps:

1. Analyze business needs and plan the database. In this step you determine the information needs that the application and database must satisfy. You identify source data, user information, and access needs.
2. Define the database outline. The outline determines the structure of the database--what information is stored and how different pieces of information relate to one another.
3. Check system requirements. How you meet system requirements and define system parameters affects the efficiency and performance of the database. Make sure that you have allocated enough disk space for the database and that the index and data file caches in memory are of adequate size (see Estimating Disk and Memory Requirements for a Database and Specifying Hyperion Essbase Kernel Settings).
4. Load test data into the database. After an outline and a security plan are in place, you load the database with test data to enable the later steps of the process.
5. Define calculations. Now you test the consolidations in the outline and write and test formulas and calc scripts for specialized calculations.
6. Define reports. Users access data through printed or online reports and spreadsheets and even on the World Wide Web. If you plan to provide predefined reports to users, design the report layouts and run the reports.
7. Verify with users. It is important to ensure that the database satisfies the goals of the application. You should solicit and carefully consider the opinions of the users. Do the calculations give them the information they need? Are they able to generate reports quickly? Are they satisfied with consolidation times? In short, ask users if the database works for them.
8. Repeat the process. To fine-tune the design, repeat steps 1 through 7.

Case Study: The Beverage Company

This chapter bases the database planning process on the needs of a fictitious company called The Beverage Company (TBC) and uses TBC as an example to demonstrate how to build a Hyperion Essbase database. The examples follow a variation of the Sample Basic application that is included with the Hyperion Essbase installation.

TBC manufactures, markets, and distributes soft drink products internationally. Analysts at TBC prepare budget forecasts and compare performance to budget forecasts on a monthly basis. The financial measures the analysts track are profit and loss and inventory data.

TBC uses spreadsheet packages to prepare budget data and perform variance reporting. Because TBC plans and tracks a variety of products over several markets, the process of deriving and analyzing data is quite tedious. Last month, analysts spent most of their time entering and re-keying data and preparing reports.

TBC has determined that Hyperion Essbase is the best tool for creating a centralized repository for financial data. The data repository will reside on a server that is accessible to analysts throughout the organization. Users will have access to the server and will be able to load data from various sources and retrieve data when they need it. TBC has a variety of users, so TBC expects that different users will have different security levels for accessing data.

Planning and Analyzing

The design and operation of a Hyperion Essbase multidimensional database plays a key role in achieving a well-tuned system that enables you to analyze a business efficiently. Given the size and performance volatility of multidimensional databases, developing an optimized database is critical. A detailed plan that outlines data sources, user needs, and prospective database elements can save you development and implementation time.

The planning and analysis phase involves three tasks:

• Identifying the data that you want to include in the database
• Identifying users' requirements
• Creating and testing a business model for the data

When designing a multidimensional application, consider these factors:

• How information flows within the company--who uses what data for what purposes
• The types of reporting the company does--what types of data must be included in the outline to serve user reporting needs

Before you create a database and build its outline, you must create a Hyperion Essbase application to contain it. Applications that use the optional Currency Conversion module generally consist of a main database and a separate currency database (see Designing and Building Currency Conversion Applications).

Identifying Source Data

First, you need to evaluate the source data that you want to include in the database. Think about where the data resides and how often you plan to update the database with the data. This up-front research saves you time when you create the database outline and load data into the Hyperion Essbase database.

Determine the scope of the database. If an organization has thousands of product families containing hundreds of thousands of products, you may want to store data values only for product families. Interview members from each user department to find out what data they process, how they process data today, and how they want to process data in the future.

Carefully define reporting and analysis needs. Does the data support these needs? If not, what additional data do you need and where will you find the needed data?

Where does each department currently store data? Is it in a form Hyperion Essbase can use? Do departments store data in a DB2 database on an IBM mainframe, in a relational database on a UNIX-based server, or in a PC-based database or spreadsheet?

Consider who updates the database and how frequently. Do the individuals who need to update data have access to the data?

Finally, make sure that all the data you want is ready to load in the necessary format. You can save hours of time by making sure that data is readily available and easy for Hyperion Essbase to import. For a list of valid data sources that you can import into Hyperion Essbase, see Performing a Data Load

Checklist: Select Data for the Hyperion Essbase Database

• How much detail should the database contain? Does the data support desired analysis and reporting goals?
• Where is the data? Does it come from a single source or from multiple sources?
• Is all the data you want to use readily available?
• Is the data in a format that Hyperion Essbase can import?

Identifying User Requirements

Given the data sources, what types of analysis do users require? What summary and detail levels of information do they need? Do some users require access to information that others should not see?

Be sure to discuss information needs with users. Review the information they use now and the reports they must generate for review by others.

Creating a Business Model

You are now ready to create a model of the business on paper. To build the model, you need to identify the perspectives and views that are important to the business. These views translate into the dimensions of the database model.

Most businesses choose to analyze include the following areas:

• Time periods
• Accounts
• Scenarios
• Products
• Distribution channels
• Geographical regions
• Business units

Identifying Analysis Objectives

After you identify the major areas of information in a business, the next step in designing a Hyperion Essbase application is deciding how the application enables analysis of data:

• If by time, which time periods are needed? Does the analysis need to include just the current year or multiple years? Quarterly and monthly data? By season?
• If by geographical region, how do you define these regions? By sales territories? By geographical boundaries such as states and cities?
• If by product line, do you need to review data for each specific product or can you summarize data into product classes?

Regardless of the business area, you need to determine the perspective and detail needed in the analysis. Each business area you analyze provides a different view of the data.

Determining Dimensions and Members

You can represent each of the business views as a separate dimension in the database. If you need to analyze a business area by classification or attribute, such as by the size or color of products, you can use attribute dimensions to represent the classification views. For information about attribute dimensions, see Working with Attributes

The dimensions that you choose determine what types of analysis you can perform on the data. With Hyperion Essbase, you can use as many dimensions as you need for your analysis. A typical Hyperion Essbase database contains at least seven standard dimensions and many more attribute dimensions. Dimensions that are not attribute dimensions are called standard dimensions.

After you determine the dimensions of the business model, choose the elements or items within the perspective of each dimension. These elements become the members within the dimensions. For example, a perspective of time may include the time periods that you want to analyze, such as quarters, and within quarters, months. Each quarter and month becomes a member of the dimension that you create for time. Quarters and months represent a two-level hierarchy of members and their children. Months within a quarter consolidate to a total for each quarter.

Next, consider the relationships among the business areas. The structure of a Hyperion Essbase database makes it easy for users to analyze information from many different perspectives. A financial analyst, for example, may ask the following questions:

• What are sales for a particular month? How does this figure compare to sales in the same month over the last five years?
• By what percentage is profit margin increasing?
• How close are actual values to budgeted values?

In other words, the analyst may want to examine information from three different perspectives: time, account, and scenario. The sample database shown in Figure 5-2 represents these three perspectives as three dimensions, with one dimension represented along each of the three axes:

• A time dimension, which consists of the individual months Jan, Feb, and Mar and the total for Qtr1, is displayed along the X-axis.
• An accounts dimension, which consists of accounting figures such as Sales, COGS, Margin, and Margin%, is displayed along the Y-axis.
• Another dimension which provides a different point of view, such as Budget for budget values and Actual for actual values, is displayed along the Z-axis.

  Figure 5-2: Cube Representing Three Database Dimensions

  

The cells within the cube, where the members intersect, contain the data relevant to all three intersecting members; for example, the actual sales in January.

Table 5-1 shows a summary of TBC's business areas that the planner determined would be dimensions. These are the major business areas to be analyzed. The planner created three columns, with the dimensions in the left column and members in the two right columns. The members in column 3 are subcategories of the members in column 2. In some cases, members in column 3 are broken into another level of subcategories; for example, the Margin and Total Expenses members of the Measures dimension.

In addition, some desired views are actually ways to view product information. For these views, the planner added two attribute dimensions to enable product analysis based on size and packaging.

Checklist: Create a Business Model

• What are the candidates for dimensions?
• Do any of the dimensions classify or describe other dimensions? These are candidates for attribute dimensions.
• Do users want to qualify their view of a dimension? The categories by which they qualify a dimension are candidates for attribute dimensions.
• What are candidates for members?
• How many levels does the data require?
• How does the data consolidate?

Analyzing Database Design

While the initial dimension design is still on paper, you should review the design according to a set of guidelines. These guidelines help you to fine-tune the database and leverage the multidimensional technology. These rules are processes or questions that help you achieve an efficient design and meet consolidation and calculation goals.

Keep in mind that the number of members needed to describe a potential data point should determine the number of dimensions. As you analyze the design, if you are not sure that you should delete a dimension, keep it and apply more analysis rules until you feel confident about deleting or keeping it.

Examine dimension relationships

For simplicity, the examples in this section show alternative arrangements for what was initially designed as two dimensions. You can apply the same logic to all combinations of dimensions.

Consider the design for a company that sells products to multiple customers over multiple markets; the markets are unique to each customer:

             Cust A  Cust B  Cust C

New York     100     N/A     N/A
Illinois     N/A     150     N/A
California   N/A     N/A     30

Cust A is only in New York, Cust B is only in Illinois, and Cust C is only in California. In this situation, the company can define the data in one standard dimension:

Market
       New York
              Cust A
       Illinois
             Cust B
       California
             Cust C

However, if you look at a larger sampling of data, you may see that there can be many customers in each market. Cust A and Cust E are in New York; Cust B, Cust M, and Cust P are in Illinois; Cust C and Cust F are in California. In this situation, the company typically defines the large dimension, Customer, as a standard dimension and Market as an attribute dimension. The company associates the members of the Market dimension as attributes of the members of the Customer dimension. The members of the Market dimension describe locations of the customers.

Customer (Standard dimension)
       Cust A   (Attribute:New York)
       Cust B   (Attribute:Illinois)
       Cust C   (Attribute:California)
       Cust E   (Attribute:New York)
       Cust F   (Attribute:California)
       Cust M   (Attribute:Illinois)
       Cust P   (Attribute:Illinois)
Market (Attribute dimension)
       New York
       Illinois
       California

Consider another situation. Again, the company sells products to multiple customers over multiple markets. This time, the company can ship to a customer that has locations in different markets.

             Cust A  Cust B  Cust C

New York     100      75     N/A
Illinois     N/A     150     N/A
California   150     N/A      30

Cust A is in New York and California. Cust B is in New York and Illinois. Cust C is only in California. Using an attribute dimension does not work in this situation; a customer cannot have more than one attribute. Therefore, the company designs the data in two standard dimensions.

Customer
       Cust A
       Cust B
       Cust C
Market

       New York
       Illinois
       California

Examine dimension combinations

Break each combination of two dimensions into a two-dimensional matrix. For example, proposed dimensions at TBC (as listed in Table 5-1) include the following combinations:

• Year vs. Measures
• Year vs. Product
• Year vs. Market
• Year vs. Scenario
• Measures vs. Product
• Measures vs. Market
• Measures vs. Scenario
• Market vs. Product
• Market vs. Scenario
• Scenario vs. Product
• Ounces vs. Pkg Type

As attribute dimensions associated with the Product dimension, Ounces and Pkg Type should be considered with the Product dimension.

To help visualize each dimension, you can draw a matrix and include a few of the first generation members. Figure 5-3 shows a simplified set of matrixes for three dimensions.

Figure 5-3: Analyzing Dimensional Relationships

For each combination of dimensions, ask three questions:

• Does it add analytic value?
• Does it add utility for reporting?
• Are there many unused combinations?

For each combination, the answers to the questions help determine if the combination is valid for the database. Ideally, the answers to all of the questions should be yes. If all answers are not yes, you should consider rearranging the data into dimensions that are more meaningful. As you go through this process, be sure to discuss information needs with users.

Avoid repetition in the outline

The repetition of elements in an outline often indicates a need to split dimensions. Here is an example of repetition and a solution:

Separating Budget and Actual and placing them into another dimension simplifies the outline and provides a simpler view of the budget and actual figures of the other dimensions in the database.

As a way to analyze members by their characteristics or attributes, one approach to outline design repeats elements as shared members. The first example uses shared members to analyze diet beverages. You can avoid the repetition of the first example and simplify the design of the outline by creating a Diet attribute dimension, as shown in the second example.

Attribute dimensions also provide additional analytic capabilities. For guidelines on when to use attribute dimensions, see Attribute Design Considerations.

Avoid interdimensional irrelevance

Interdimensional irrelevance occurs when many members of a dimension are irrelevant across other dimensions. Hyperion Essbase defines irrelevant data as data that Hyperion Essbase stores only at the summary (dimension) level. In such a situation, you may be able to remove a dimension from the database and add its members to another dimension or split the model into separate databases.

For example, TBC considered analyzing salaries as a member of the Measures dimension. But salary information often proves to be irrelevant in the context of a corporate database. Most salaries are confidential and apply to specific individuals. The individual and the salary typically represent one cell, with no reason to intersect with any other dimension.

TBC considered separating employees into a separate dimension. Table 5-3 shows an example of how TBC analyzed the proposed Employee dimension for interdimensional irrelevance. Members of the proposed Employee dimension are compared with members of the Measures dimension. Only the Salary measure is relevant to individual employees.

Split databases if necessary

TBC agreed that in context with other dimensions, individual employees were irrelevant. They also agreed that adding an Employee dimension substantially increases database storage needs. Consequently, they decided to create a separate Human Resources (HR) database. The new HR database contains a group of related dimensions and includes salaries, benefits, insurance, and 401(k) plans.

There are many reasons for splitting a database; for example, suppose that a company maintains an organizational database that contains several international subsidiaries located in several time zones. Each subsidiary relies on time-sensitive financial calculations. You may want to split the database for groups of subsidiaries in the same time zone to ensure that their financial calculations are timely. You can also use a partitioned application to separate information by subsidiary.

Checklist: Analyze the Database

• Have you minimized the number of dimensions?
• For each dimensional combination, did you ask:
  • Does it add analytic value?
  • Does it add utility for reporting?
  • Are there any unused combinations?
• Did you avoid repetition in the outline?
• Did you avoid interdimensional irrelevance?
• Did you split the databases as necessary?

Planning for Security in a Multi-User Environment

The time to think about the type of security privileges you plan to issue for a Hyperion Essbase database is when you consider user information needs. As a Hyperion Essbase administrator, you frequently load external data into the database. Determine who else should have privileges for these operations. End the plan with a list of users and privileges. See Managing Security at Global and User Levels to learn more about assigning user privileges.

Checklist: Plan for Security

• Who are the users and what privileges should they have?
• Who should have load data privileges?
• Which users can be grouped, and as a group, given similar privileges?

Drafting an Outline

At this point, you can create the application and database and build the first draft of the outline in Hyperion Essbase. The draft defines all dimensions, members, and consolidations. Use the outline to design consolidation requirements and identify where you need formulas and calculation scripts.

The TBC planners issued the following draft for a database outline. In this plan, the bold headings are the dimensions: Year, Measures, Product, Market, Scenario, Pkg Type, and Ounces. Observe how TBC anticipated consolidations, calculations and formulas, and reporting requirements. The planners also used product codes rather than product names to describe products.

• Year--TBC needs to collect data monthly and summarize the monthly data by quarter and year. Monthly data, stored in members such as Jan, Feb, and Mar, consolidates to quarters. Quarterly data, stored in members such as Qtr1 and Qtr2, consolidates into Year.

• Measures--Sales, Cost of Goods Sold, Marketing, Payroll, Miscellaneous, Opening Inventory, Additions, and Ending Inventory are standard measures. Hyperion Essbase can calculate Margin, Total Expenses, Profit, Total Inventory, Profit %, Margin %, and Profit per Ounce from these measures. TBC needs to calculate Measures on a monthly, quarterly, and yearly basis.

• Product--The Product codes are 100-10, 100-20, 100-30, 200-10, 200-20, 200-30, 200-40, 300-10, 300-20, 300-30, 400-10, 400-20, and 400-30. Each product consolidates to its respective family: 100, 200, 300, and 400.

  TBC wants to enable analysis by size and package by associating each product with members of the Ounces and Pkg Type attribute dimensions.

• Market--Several states make up a region, and four regions make up a market. The states are New York, Massachusetts, Connecticut, Florida, New Hampshire, Oregon, Washington, California, Utah, Nevada, Texas, Louisiana, New Mexico, Oklahoma, Illinois, Ohio, Wisconsin, Missouri, Iowa, and Colorado. Each state consolidates into its respective region: East, West, South, and Central. Each region consolidates into Market.

• Scenario--TBC derives and tracks budget data versus actual data. Managers must monitor and track budgets and actuals, as well as the variance and variance percentage between them.

• Pkg Type--TBC wants to see the effect that product packaging has on sales and profit. Establishing the Pkg Type attribute dimension enables users to analyze product information based on whether a product is packaged in bottles or cans.

• Ounces--TBC sells products in different sizes in ounces in different market areas. Establishing the Ounces attribute dimension helps users to monitor which sizes sell better in which markets.

Building an Outline

An outline includes more than dimensions and members. Dimensions and members have specific properties that provide access to built-in functionality. The properties of dimensions and members define the roles of the dimensions and members in the design of the multidimensional structure. These properties include the following:

• Dimension types
• Generation and level names
• Aliases
• Properties
• Field types
• Consolidation operators
• Formulas
• Attribute associations

The following section introduces many of these terms and explains how to use them.

Defining Dimension and Member Properties

In the outline, the TBC database dimensions and members have special tags. Hyperion Essbase calls these tags properties.

As shown in Figure 5-4, the Year dimension is tagged as time, the Measures dimension is tagged as accounts and label only, and the Scenario dimension is tagged as label only. Ounces and Pkg Type are attribute dimensions. The Products dimension is associated with two attribute dimensions, Ounces and Pkg Type.

Figure 5-4: TBC Dimensions and Related Properties

A dimension type is a property that Hyperion Essbase provides that adds special functionality to a dimension. The most commonly used dimension types are time, accounts, and attribute. Table 5-4 defines each of the Hyperion Essbase dimension types.

When you define members in standard dimensions, Hyperion Essbase automatically tags the members with the addition (+) consolidation, meaning that during consolidation members are added. For example, Jan, Feb, and Mar figures are added, and the result stored in the parent, Qtr1.

As appropriate, you can change a member consolidation property to one of the following operators: +, -, *, /, %, and ~ (no consolidation). For more information on consolidation properties, see Consolidation Paths.

With Hyperion Essbase, you can specify data storage properties for members; these properties define where and when consolidations are stored. For example, by default, members are tagged as store data. Hyperion Essbase sums store data members and stores the result at the parent level. You can change the default logic for each member by changing the data storage property tag for the member. Members with the label only tag, for example, do not have data associated with them. Members with the label only tag exist only for purposes of data grouping or navigation. Because members of this type contain no data, they cannot be consolidated.

For example, in the Measures dimension, the member Ratios has three children, Margin%, Profit%, and Profit per Ounce. The member Ratios defines a category of members. When consolidated, Margin%, Profit%, and Profit per Ounce do not roll up to a meaningful figure for Ratios. Hence, Ratios is tagged as label only.

Table 5-5 describes Hyperion Essbase data storage properties.

Attribute dimensions and members are tagged as Dynamic Calc. Attribute dimensions and members have no data values of their own. The data values you see associated with attribute dimensions and members are dynamically calculated from the associated base dimension members. You cannot change the storage property for attribute dimensions and members.

For more details on the types of properties you can assign to dimensions and members and for instructions for creating the components of a database, see the following chapters:

• Creating Applications and Databases
• Creating and Changing Database Outlines
• Setting Dimension and Member Properties
• Working with Attributes

Checklist: Define Dimension and Member Properties

• Can you identify a time dimension?
• Can you identify an accounts dimension?
• Does the data include foreign currencies? If so, did you identify a currency partition dimension?
• Can you identify qualities or characteristics of dimensions that should be defined as separate attribute dimensions?
• What members require special data storage properties?

Designing an Outline to Optimize Performance

When you design an outline, you must position attribute dimensions at the end of the outline. You should position dense dimensions before sparse dimensions.

The position of dimensions in an outline and the storage properties of dimensions can affect two areas of performance: how quickly calculations are run and how long it takes users to retrieve information.

Optimizing Query Performance

To optimize query performance, use the following guidelines when you design an outline:

• If the outline contains attribute dimensions, make sure that the attribute dimensions are the only sparse Dynamic Calc dimensions in the outline.
• In the outline, place the most queried sparse dimensions before the less queried sparse dimensions.

The outline shown in Figure 5-5 is designed for optimum query performance:

• Because the outline contains attribute dimensions, the storage property for the all of the standard dimensions and all of their members is set as store data.
• As the most-queried sparse dimension, the Product dimension is the first of the sparse dimensions. Base dimensions are typically queried more than other dimensions.

  Figure 5-5: Designing an Outline for Optimized Query Times

  

Optimizing Calculation Performance

To optimize calculation performance, order the sparse dimensions in the outline by their number of members, starting with the dimension that contains the fewest members.

For more information about factors that affect calculation performance, see Designing for Calculation Performance.

The outline shown in Figure 5-6 is designed for optimum calculation performance:

• The smallest standard dimension that is sparse, Market, is the first of the sparse dimensions in the outline.
• The largest standard dimension that is sparse, Product, is immediately above the first attribute dimension. If the outline did not contain attribute dimensions, the Product dimension would be at the end of the outline.

  Figure 5-6: Designing an Outline for Optimized Calculation Times

  

Meeting the Needs of Both Calculation and Retrieval

Even though they contain the same dimensions, the example outlines are different. To determine the best outline sequence for a situation, you must prioritize the data retrieval requirements of the users against the time needed to run calculations on the database. How often do you expect to update and recalculate the database? What is the nature of user queries? What is the expected volume of user queries?

A possible workaround is to position the dimensions in the outline initially to optimize calculations. After you run the calculations, you can manually resequence the dimensions to optimize retrieval. When you save the outline after you reposition its dimensions, choose to restructure the database by index only. Before you run calculations again, remember to resequence the dimensions in the outline to optimize calculations.

Loading Test Data

Before you can test calculations, consolidations, and reports, you need data in the database. During the design process, loading mocked-up data or a subset of actual business data provides flexibility and shortens the time it takes to test and analyze results.

Detailed instructions for loading data are in the following chapters:

• Introducing Data Loading
• Setting up a Rules File to Manipulate Records
• Manipulating Fields Using a Rules File
• Performing a Data Load

When you are satisfied with a database design, you should test the loading of the complete set of real data with which you will populate the final database. To load the actual data, you may be able to use the data load rules files that you created for the test data. This final test may reveal problems with the source data that you did not anticipate during earlier phases of the database design process.

Defining and Testing Calculations

Calculations are essential to derive certain types of data. Data that is derived from a calculation is called calculated data; basic noncalculated data is called input data.

This section uses the Product and Measures dimensions in TBC's application to illustrate several types of common calculations, Such calculations are found in many Hyperion Essbase applications. You learn about the following:

• Consolidation paths
• Consolidation order
• Calculations of time and accounts dimensions
• Formulas

Consolidation Paths

Consolidation is the most frequently used calculation in Hyperion Essbase. This section uses the Product dimension to illustrate consolidations.

TBC's application has several consolidation paths:

• Individual products roll up to product families, and product families consolidate into Product. The TBC outline also requires multiple consolidation paths; some products must consolidate in multiple categories.
• States roll up to regions, and regions consolidate into Market.
• Months roll up into quarters, and quarters consolidate into Year.

Unary operators define how Hyperion Essbase rolls up data for each member in a branch to the parent. Hyperion Essbase gives members a default operator of addition (+), meaning that it adds the value of a member to the values of other members in the branch. For example, it adds 100-10, 100-20, and 100-30 and stores the result in their parent, 100, as shown in Figure 5-7.

Figure 5-7: TBC Product Dimension

The Product dimension contains mostly (+), operators, which indicate that each group of members is added together and rolled up to the parent. Diet has a tilde (~), which indicates that Hyperion Essbase does not include the Diet member in the consolidation to the parent, Product. The Diet member consists entirely of members it shares or duplicates. The TBC product management group wants to be able to isolate Diet drinks in reports, so TBC created a separate Diet member that does not impact the overall consolidation.

For more information on consolidation and calculations, see Setting Dimension and Member Properties

Consolidation Ordering

Hyperion Essbase calculates the data in a branch in top-down order. For example, if you have, in order, two members tagged with an addition symbol (+) and a third member tagged with a multiplication symbol (*). Hyperion Essbase adds the first two and multiplies the sum by the third. Be aware that Hyperion Essbase always begins with the top member when it consolidates, so the order and the labels of the members is very important. Table 5-6 shows the Hyperion Essbase unary operators. For more information, see Introducing Member Consolidation Properties.

Shared Members and Consolidation

Shared members also affect consolidation paths. The shared member concept lets two members with the same name share the same data. The shared member stores a pointer to data contained in the other member, so Hyperion Essbase only stores the data once. Shared members must be in the same dimension. You can share data with two or more members.

Checklist: Define Consolidations

• Have you identified the consolidations in the outline?
• Did you tag each member with the proper unary operator?
• Did you specify a shared member tag for designated members?
• Would shared members be more efficient if designed as an attribute dimension?

Time and Accounts Calculations

The Measures dimension is the most complex dimension in the TBC outline because it uses both time and accounts data. It also contains several formulas and special tags to help Hyperion Essbase calculate the outline. This section discusses the formulas and tags that TBC included in the Measures dimension (the dimension tagged as accounts).

Take a moment to look closely at the Measures dimension tags defined by TBC (in Figure 5-8). You see that you already know about many of the properties in the Measures dimension. You have learned about positive (+), negative (-), and tilde (~) unary operators, as well as accounts and label only tags. The Inventory and Ratios member names assist the user in data navigation and do not contain data and, therefore, receive a label only tag. The Measures dimension itself also has a label only tag. Some members of Measures have a Dynamic Calc tag. Dynamic calculations are discussed in Dynamic Calculations.

Figure 5-8: TBC Measures Dimension

For details on Hyperion Essbase calculations, see Introduction to Database Calculations through Using Intelligent Calculation to Optimize Calculation

Accounts Dimension Calculation

This section discusses two forms of calculations for a dimension tagged as accounts: time balance and variance reporting.

Time Balance Properties

Note the two tags in the Measures dimension: TB first and TB last. These tags, called time balance tags or properties, provide instructions to Hyperion Essbase about how to calculate the data in a dimension tagged as accounts. To use these tags, you must have a dimension tagged as accounts and a dimension tagged as time. The first, last, average, and expense tags are available exclusively for use with accounts dimension members.

In the TBC Measures dimension, Opening Inventory data represents the inventory that TBC carries at the beginning of each month. The quarterly value for Opening Inventory is equal to the Opening value for the quarter. Opening Inventory requires the time balance tag, TB first.

Ending Inventory data represents the inventory that TBC carries at the end of each month. The quarterly value for Ending Inventory is equal to the ending value for the quarter. Ending Inventory requires the time balance tag, TB last. Table 5-7 shows the time balance tags for the accounts dimension.

Table 5-8 shows how consolidation in the time dimension is affected by time balance properties in the accounts dimension.

Normally, the calculation of a parent in the time dimension is based on the consolidation and formulas of its children. However, if a member in an accounts branch is marked as TB first, then any parent in the time dimension matches the member marked as TB first.

For more information on time balance tags, see Introducing Time Balance Properties.

Variance Reporting

One of TBC's Hyperion Essbase requirements is the ability to perform variance reporting on actual versus budget data. The variance reporting calculation requires that any item that represents an expense to the company must have an expense reporting tag. Inventory members, Total Expense members, and the COGS member each receive an expense reporting tag for variance reporting.

Hyperion Essbase provides two variance reporting properties: expense and non-expense. The default is non-expense. Variance reporting properties define how Hyperion Essbase calculates the difference between actual and budget data in members with the @VAR or @VARPER function in their member formulas.

When you tag a member as expense, the @VAR function calculates Budget - Actual. For example, if the budgeted amount is $100 and the actual amount is $110, the variance is -10.

Without the expense reporting tag, the @VAR function calculates Actual - Budget. For example, if the budgeted amount is $100 and the actual amount is $110, the variance is 10.

Formulas

You can define formulas to calculate relationships between members in the database outline. You can either apply the formulas to members in the outline, or you can place the formulas in a calc script. In this section, you learn how TBC optimized the performance of its database by using formulas.

Functions are predefined routines that perform specialized calculations and return sets of members or sets of data values. Formulas are composed of operators and functions, as well as dimension names, member names, and numeric constants.

The operators include the following:

• Mathematical operators that perform arithmetic operations
• Conditional operators that build logical conditions into calculations
• Cross-dimensional operators that point to data values of specific database member combinations

The Hyperion Essbase functions include over 100 predefined routines to extend the calculation capabilities of Hyperion Essbase. The main functions include the following:

• Boolean functions, which provide a conditional test by returning a TRUE or FALSE value
• Mathematical functions, which perform specialized mathematical calculations
• Relationship functions, which look up data values within a database during a calculation based on the current member's position
• Range functions, which declare a range of members as an argument to another function or command
• Financial functions, which perform specialized financial calculations
• Member set functions, which generate lists of members based on a specified member
• Allocation functions, which allocate values that are input at a parent level across child members
• Forecasting functions, which manipulate data for the purposes of smoothing data, interpolating data, or calculating future values
• Statistical functions, which calculate advanced statistics
• Date and time functions, which use date and time characteristics in calculation formulas
• Calculation mode functions, which specifies the calculation mode that Hyperion Essbase uses to calculate a formula

The Measures dimension uses the following formulas:

Margin = Sales - COGS
Total Expenses = Marketing + Payroll + Miscellaneous
Profit = Margin - Total Expenses
Profit % = Profit % Sales
Margin % = Margin % Sales
Profit per Ounce = Profit/@ATTRIBUTEVAL(Ounces)

Hyperion Essbase uses unary operators to calculate the Margin, Total Expenses, and Profit members. The Margin% formula uses a % operator, which means "express Margin as a percentage of Sales." The Profit% formula uses the same % operator. The Profit per Ounce formula uses a division operator (/) and a function (@ATTRIBUTEVAL) to calculate profitability by ounce for products sized in ounces.

For a complete list of operators, functions, and syntax, see the online Technical Reference in the DOCS directory. For a discussion of how to use formulas, see Developing Formulas

Dynamic Calculations

When you design the overall database calculation, you may want to define a member as a Dynamic Calc member. When you tag a member as Dynamic Calc, Hyperion Essbase calculates the combinations of that member when you retrieve the data, instead of pre-calculating the member combinations during the regular database calculation. Dynamic calculations shorten regular database calculation time, but may increase retrieval time for dynamically calculated data values.

As shown in Figure 5-9, TBC's Measures dimension contains several members that are tagged as Dynamic Calc: Profit, Margin, Total Expenses, Margin %, and Profit %.

Figure 5-9: TBC Measures Dimension, Dynamic Calc Tags

When an overall database calculation is performed, these members and their corresponding formulas are not calculated. Rather, these members are calculated when a user requests them, for example, from Hyperion Essbase Spreadsheet Add-in. Hyperion Essbase does not store the calculated values; it recalculates the values for any subsequent retrieval. However, you can choose to store dynamically calculated values after the first retrieval. For more information, see Understanding Dynamic Calc Members.

To decide when to calculate data values dynamically, consider your needs for the following:

• Optimum regular calculation time (batch calculation)
• Low disk space usage
• Reduced database restructure time
• Speedy data retrieval for users
• Reduced backup time

For more information about dynamic calculations, see Dynamically Calculating Data Values

Two-Pass Calculations

In the TBC database, both Margin% and Profit% contain the label two-pass. This default label indicates that some member formulas need to be calculated twice to produce the desired value. The two-pass property works only on members from the dimension tagged as accounts or on Dynamic Calc and Dynamic Calc And Store members. The following examples illustrate why Profit% has a two-pass tag.

Hyperion Essbase loads data into the system as follows:

Hyperion Essbase calculates Measures first. The data then looks like this:

Next, Hyperion Essbase calculates the Year dimension. The data rolls up across the dimension.

The result in Profit% ->Qtr1 of 30% is not correct. However, because TBC tagged Profit% as two-pass calc, Hyperion Essbase recalculates profit percent at each occurrence of the member Profit%. The data is then correct and is displayed as follows:

Checklist: Define the Calculations

• Which dimensions require formulas or calculations?
• Does the default calculation logic achieve accurate results?
• What type of calculations are required?
• Which members require variance reporting?
• Do members that require variance reporting have a time or accounts tag?

Defining Reports

To be sure the design meets user information requirements, you need to view data as users view it. Users typically view data through spreadsheets, printed reports, or reports published on the Web. There are many tools available through Hyperion and Hyperion partners for producing the reporting systems that users use.

Hyperion Essbase provides several tools that can help you during the design process to display and format the data quickly and test whether the database design meets user needs. You can use the Report Writer of Hyperion Essbase Application Manager to write report scripts quickly. Those familiar with spreadsheets can use the spreadsheet add-ins for Microsoft Excel and Lotus 1-2-3. See Quick Start to Report Scripts and the appropriate spreadsheet user's guide.

During the design phase, you should check for such things as the following:

• Grouping and sequencing of data. Do the intersections enabled by the design provide the data that users need?
• Levels of totals. In spreadsheets this term refers to the consolidation levels that are available to a user who drills down and up through the hierarchy of the outline design.
• Attribute reporting. Does the database design facilitate an analysis that is based on the characteristics or attributes of specific dimensions or members? For example, do you need to compare sales by specific combinations of size and packaging, such as comparing the sales of 16-ounce bottled colas with the sales of 32-ounce bottled colas? How can you take advantage of the Attribute Calculations dimension that Hyperion Essbase provides for use with attribute dimensions? For more information about attributes, see Working with Attributes

If you provide predesigned reports for users, this is the time to use the appropriate tool to create those reports against the test data. The reports that you design should provide the information that meets the original objectives of the application. The reports should be easy to use. They should provide the right combinations of data and the right amount of data. Reports with too many columns and rows are very hard to use. It may be better to create a number of different reports instead of one or two all-inclusive ones.

Verifying the Design

After you have analyzed the data and created a preliminary design, you need to check all aspects of the design with the users. You should have already checked to see if the database satisfies the users' analysis and reporting needs. Make sure you check with the users to ensure that the database satisfies all goals of the application.

Near the end of the design cycle, you need to test with real data. Did the outline build correctly? Did all the data load? If the database fails in any area, repeat the steps of the design cycle to identify the cause of the problem.

Hyperion Essbase provides several sources of information to help you isolate problems. Sources include event logs, exception error logs, and the information displayed on the Database Information dialog box. This manual, Hyperion Essbase Database Administrator's Guide, also provides helpful information. Look in sections relevant to the problem; for example, sections about security, calculations, or reports. You can also use the index of this guide to find help for solving problems. Look up such terms as troubleshooting, log files, optimizing, performance, recovery, resources, errors, and warnings.

Most likely, you will need to repeat one or more steps of the design process to arrive at the ideal database solution.

Copyright © 1991-2000 Hyperion Solutions Corporation. All rights reserved.