Case Study: Designing a Single-Server, Multidimensional Database

In This Section:

Process for Designing a Database

Case Study: The Beverage Company

Analyzing and Planning

Drafting Outlines

Checking System Requirements

Loading Test Data

Defining Calculations

Defining Reports

Verifying the Design

Some information in this chapter applies only to block storage databases and is not relevant to aggregate storage databases. Also see Comparison of Aggregate and Block Storage.

Process for Designing a Database

To implement a multidimensional database, you install Essbase, and then you design and create an application and databases. You analyze data sources and define requirements carefully and decide whether a single-server approach or a partitioned, distributed approach better serves your needs. For criteria that you can review to decide whether to partition an application, see Guidelines for Partitioning a Database.

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. See Creating Applications and Databases.

Figure 26, The Database Design Cycle illustrates the cyclical process of designing a database, which includes the following basic steps:

  1. Analyze business needs and design a plan.

    The application and database that you create must satisfy the information needs of your users and your organization. Therefore, you identify source data, define user information access needs, review security considerations, and design a database model. See Analyzing and Planning.

  2. Draft a database outline.

    The outline determines the structure of the database—what information is stored and how different pieces of information interrelate. See Drafting Outlines.

  3. Check system requirements.

    How you meet system requirements and define system parameters affects the efficiency and performance of the database. See Checking System Requirements.

  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. See Loading Test Data.

  5. Define calculations.

    You test outline consolidations and write and test formulas and calculation scripts for specialized calculations. See Defining Calculations.

  6. Define reports.

    Users access data through print and online reports and spreadsheets or on the Web. If you plan to provide predefined reports to users, you design report layouts and run reports. See Defining Reports.

  7. Verify with users.

    To ensure that the database satisfies your user goals, solicit and carefully consider user opinions. See Verifying the Design.

  8. Repeat the process.

    To fine-tune the design, repeat steps 1 through 7.

Figure 26. The Database Design Cycle

This image illustrates the eight basic steps for designing a database, as described in the text preceding the image.

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 an Essbase database. TBC is a variation of the Sample.Basic application that is included with the Essbase installation.

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

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 tedious. Last month, analysts spent most of their time entering and rekeying data and preparing reports.

TBC has determined that 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 can access the server and load data from various sources and retrieve data as needed. TBC has a variety of users, so TBC expects that different users will have different security levels for accessing data.

Analyzing and Planning

The design and operation of an Essbase multidimensional database are key to achieving a well-tuned system that enables you to analyze business information 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 these tasks:

When designing a multidimensional application, consider these factors:

  • How information flows within the company—who uses which 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

    Note:

    Defining only one database per application enables enhanced memory usage and ease of database administration. Applications that use the optional Essbase Currency Conversion module are an exception to this recommendation. Currency conversion applications generally consist of a main database and a separate currency database (see Designing and Building Currency Conversion Applications).

Analyzing Source Data

First, evaluate the source data to be included 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 time when you create the database outline and load data into the 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.

  • How do users want to view and analyze data?

  • How much detail should the database contain?

  • Does the data support the desired analysis and reporting goals?

  • If not, what additional data do you need, and where can you find it?

Determine the location of the current data.

  • Where does each department currently store data?

  • Is data in a form that Essbase can use?

  • Do departments store data in relational databases on Windows or UNIX servers, or in Excel spreadsheets?

  • Who updates the database and how frequently?

  • Do those who need to update data have access to it?

Ensure that the data is ready to load into Essbase.

  • Does data come from a single source or multiple sources?

  • Is data in a format that Essbase can use? For a list of valid data sources that you can load into Essbase, see Data Sources.

  • Is all data that you want to use readily available?

Identifying User Requirements

Discuss information needs with users. Review the information they use and the reports they must generate for review by others. Determine the following requirements:

  • What types of analysis do users require?

  • What summary and detail levels of information do users need?

  • Do some users require access to information that other users should not see?

Planning for Security in a Multiple User Environment

Consider user information needs when you plan how to set up security permissions. End your analysis with a list of users and permissions.

Use this checklist to plan for security:

  • Who are the users and what permissions should they have?

  • Who should have load data permissions?

  • Which users can be grouped, and as a group, given similar permissions?

See User Management and Security in EPM System Security Mode.

Creating Database Models

Next, create a model of the database on paper. To build the model, identify the perspectives and views that are important to your business. These views translate into the dimensions of the database model.

Most businesses analyze the following areas:

  • Time periods

  • Accounting measures

  • Scenarios

  • Products

  • Distribution channels

  • Geographical regions

  • Business units

Use the following topics to help you gather information and make decisions.

Identifying Analysis Objectives

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

  • If analyzing by time, which time periods are needed? Should the analysis include only the current year or multiple years? Should the analysis include quarterly and monthly data? Should it include data by season?

  • If analyzing by geographical region, how do you define the regions? Do you define regions by sales territories? Do you define regions by geographical boundaries such as states and cities?

  • If analyzing by product line, should you review data for each product? Can you summarize data into product classes?

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

Determining Dimensions and Members

You can represent each business view as a separate standard 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.

The dimensions that you choose determine what types of analysis you can perform on the data. With Essbase, you can use as many dimensions as you need for analysis. A typical Essbase database contains at least seven standard dimensions (nonattribute dimensions) and many more attribute dimensions.

When you know approximately what dimensions and members you need, review the following topics and develop a tentative database design:

After you determine the dimensions of the database model, choose the elements or items within the perspective of each dimension. These elements become the members of their respective 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.

Relationships Among Dimensions

Next, consider the relationships among the business areas. The structure of an Essbase database makes it easy for users to analyze information from many 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 perspectives—time, account, and scenario. The sample database in Figure 27, Cube Representing Three Database Dimensions represents these three perspectives as three dimensions, with one dimension represented along each of the three axes:

  • A time dimension—which comprises Jan, Feb, 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 27. Cube Representing Three Database Dimensions

This image shows a cube representing three dimensions, as described in the text preceding the image.

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.

Example Dimension-Member Structure

Table 2 shows a summary of the TBC business areas that the planner determined would be dimensions. The dimensions represent 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 divided into another level of subcategories; for example, the Margin of the Measures dimension is divided into Sales and COGS.

Table 2. TBC Sample Dimensions

Dimensions

Members

Child Members

Year

Qtr1

Jan, Feb, Mar

Year

Qtr2

Apr, May, Jun

Year

Qtr3

Jul, Aug, Sep

Year

Qtr4

Oct, Nov, Dec

Measures

Profit

Margin: Sales, COGS

Total Expenses: Marketing, Payroll, Miscellaneous

Measures

Inventory

Opening Inventory, Additions, Ending Inventory

Measures

Ratios

Margin %, Profit %, Profit per Ounce

Product

Colas (100)

Cola (100‑10), Diet Cola (100‑20), Caffeine Free Cola (100‑30)

Product

Root Beer (200)

Old Fashioned (200‑10), Diet Root Beer (200‑20), Sarsaparilla (200‑30), Birch Beer (200‑40)

Product

Cream Soda (300)

Dark Cream (300‑10), Vanilla Cream (300‑20), Diet Cream Soda (300‑30)

Product

Fruit Soda (400)

Grape (400‑10), Orange (400‑20), Strawberry (400‑30)

Market

East

Connecticut, Florida, Massachusetts, New Hampshire, New York

Market

West

California, Nevada, Oregon, Utah, Washington

Market

South

Louisiana, New Mexico, Oklahoma, Texas

Market

Central

Colorado, Illinois, Iowa, Missouri, Ohio, Wisconsin

Scenario

Actual

N/A

Scenario

Budget

N/A

Scenario

Variance

N/A

Scenario

Variance %

N/A

In addition, the planner added two attribute dimensions to enable product analysis based on size and packaging:

Table 3. TBC Sample Attribute Dimensions

Dimensions

Members

Child Members

Ounces

Large

Small

64, 32, 20

16, 12

Pkg Type

Bottle

Can

N/A

Checklist for Determining Dimensions and Members

Use the following checklist when determining the dimensions and members of your model database:

  • What are the candidates for dimensions?

  • Do any of the dimensions classify or describe other dimensions? These dimensions 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 the 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. The guidelines help you fine-tune the database and leverage the multidimensional technology. The guidelines are processes or questions that help you achieve an efficient design and meet consolidation and calculation goals.

The number of members needed to describe a potential data point should determine the number of dimensions. If you are not sure whether you should delete a dimension, keep it and apply more analysis rules until you feel confident about deleting or keeping it.

Use the information in the following topics to analyze and improve your database design.

Dense and Sparse Dimensions

Which dimensions are sparse and which dense affects performance. For an introduction, see Sparse and Dense Dimensions. See Designing an Outline to Optimize Performance.

Standard and Attribute Dimensions

For simplicity, the examples in this topic show alternative arrangements for what initially was 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. 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 many customers can be 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 the smaller dimension, 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, but 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 member cannot have multiple attribute members. Therefore, the company designs the data in two standard dimensions:

Customer
  Cust A
  Cust B
  Cust C
Market
  New York
  Illinois
  California
Dimension Combinations

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

  • Year across Measures

  • Year across Product

  • Year across Market

  • Year across Scenario

  • Measures across Product

  • Measures across Market

  • Measures across Scenario

  • Market across Product

  • Market across Scenario

  • Scenario across Product

  • Ounces across Pkg Type

Ounces and Pkg Type, as attribute dimensions associated with the Product dimension, can be considered with the Product dimension.

To help visualize each dimension, draw a matrix and include a few of the first-generation members. Figure 28, Analyzing Dimensional Relationships shows a simplified set of matrixes for three dimensions.

Figure 28. Analyzing Dimensional Relationships

This image shows a simplified set of matrixes for three dimensions, illustrating how to analyze dimensional relationships.

For each combination of dimensions, ask three questions:

  • Does it add analytic value?

  • Does it add utility for reporting?

  • Does it avoid an excess of unused combinations?

For each combination, the answers to the questions help determine whether the combination is valid for the database. Ideally, the answer to each question is yes. If not, consider rearranging the data into more-meaningful dimensions. As you work through this process, discuss information needs with users.

Repetition in Outlines

The repetition of elements in an outline often indicates a need to split dimensions. The following examples show you how to avoid repetition.

In Figure 29, Example of Eliminating Repetition By Creating a Scenario Dimension, the left column, labeled “Repetition,” shows Profit, Margin, Sales, COGS, and Expenses repeated under Budget and Actual in the Accounts dimension. The right column, labeled “No Repetition,” separates Budget and Actual into another dimension (Scenario), leaving just one set of Profit, Margin, Sales, COGS, and Expenses members in the Accounts dimension. This approach simplifies the outline and provides a simpler view of the budget and actual figures of the other dimensions in the database.

Figure 29. Example of Eliminating Repetition By Creating a Scenario Dimension

This image provides one solution to a repetition problem, as described in the text preceding the image.

In Figure 30, Example of Eliminating Repetition By Creating an Attribute Dimension, the left column, labeled “Repetition,” uses shared members in the Diet dimension to analyze diet beverages. Members 100–20, 200–20, and 300–20 are repeated: once under Diet, and once under their respective parents. The right column, labeled “No Repetition,” simplifies the outline by creating a Diet attribute dimension of type Boolean (True or False). All members are shown only once, under their respective parents, and are tagged with the appropriate attribute (“Diet: True” or “Diet: False”).

Figure 30. Example of Eliminating Repetition By Creating an Attribute Dimension

This image provides one solution to a repetition problem, as described in the text preceding the image.

Attribute dimensions also provide additional analytic capabilities. See Designing Attribute Dimensions.

Interdimensional Irrelevance

Interdimensional irrelevance occurs when many members of a dimension are irrelevant across other dimensions. Essbase defines irrelevant data as data that 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 irrelevant in the context of a corporate database. Most salaries are confidential and apply to 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 4 shows an example of how TBC analyzed the proposed Employee dimension for interdimensional irrelevance. Members of the proposed Employee dimension (represented in the table header row) are compared with members of the Measures dimension (represented in the left-most column). The Measures dimension members (such as Revenue) apply to All Employees; only the Salary measure is relevant to individual employees.

Table 4. Example of Interdimensional Irrelevance

 

Joe Smith

Mary Jones

Mike Garcia

All Employees

Revenue

Irrelevance

Irrelevance

Irrelevance

Relevance

Variable Costs

Irrelevance

Irrelevance

Irrelevance

Relevance

COGS

Irrelevance

Irrelevance

Irrelevance

Relevance

Advertising

Irrelevance

Irrelevance

Irrelevance

Relevance

Salaries

Relevance

Relevance

Relevance

Relevance

Fixed Costs

Irrelevance

Irrelevance

Irrelevance

Relevance

Expenses

Irrelevance

Irrelevance

Irrelevance

Relevance

Profit

Irrelevance

Irrelevance

Irrelevance

Relevance

Reasons to Split Databases

Because individual employee information is irrelevant to the other information in the database, and also because adding an Employee dimension would substantially increase database storage needs, TBC created 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 in several time zones. Each subsidiary relies on time-sensitive financial calculations. You can split the database for groups of subsidiaries in the same time zone to ensure that financial calculations are timely. You can also use a partitioned application to separate information by subsidiary.

Checklist to Analyze the Database Design

Use the following checklist to analyze the database design:

  • 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?

    • Does it avoid an excess of unused combinations?

  • Did you avoid repetition in the outline?

  • Did you avoid interdimensional irrelevance?

  • Did you split the databases as necessary?

Drafting Outlines

Now you can create the application and database and build the first draft of the outline in 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.

Note:

Before you create a database and build its outline, create an Essbase application in which to add the database.

The TBC planners issued the following draft for a database outline. In this plan, Year, Measures, Product, Market, Scenario, Pkg Type, and Ounces are dimension names. 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 to Year.

  • Measures. Sales, Cost of Goods Sold, Marketing, Payroll, Miscellaneous, Opening Inventory, Additions, and Ending Inventory are standard measures. 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). Each consolidation allows TBC to analyze by size and package, because each product is associated with members of the Ounces and Pkg Type attribute dimensions.

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

  • Scenario. TBC derives and tracks budget 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 markets. Establishing the Ounces attribute dimension helps users monitor which sizes sell better in which markets.

The following topics present a review of the basics of dimension and member properties and a discussion of how outline design affects performance.

Dimension and Member Properties

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:

For a complete list of dimension and member properties, see Setting Dimension and Member Properties.

Dimension Types

A dimension type is a property that Essbase provides that adds special functionality to a dimension. The most commonly used dimension types: time, accounts, and attribute. This topic uses the following dimensions of the TBC database to illustrate dimension types.

Database:Design 
  Year (Type: time)
  Measures (Type: accounts)
  Product
  Market
  Scenario
  Pkg Type (Type: attribute)
  Ounces (Type: attribute)

Table 5 defines each Essbase dimension type.

Table 5. Dimension Types  

Dimension Types

Description

None

Specifies no particular dimension type.

Time

Defines the time periods for which you report and update data. You can tag only one dimension as time. The time dimension enables several accounts dimension functions, such as first and last time balances.

Accounts

Contains items that you want to measure, such as profit and inventory, and makes Essbase built-in accounting functionality available. Only one dimension can be defined as accounts.

For discussion of two forms of account dimension calculation, see Accounts Dimension Calculations.

Attribute

Contains members that can be used to classify members of another, associated dimension.

For example, the Pkg Type attribute dimension contains a member for each type of packaging, such as bottle or can, that applies to members of the Product dimension.

Country

Contains data about where business activities take place. In a country dimension, you can specify the currency used in each member.

For example, Canada has three markets—Vancouver, Toronto, and Montreal, which use the same currency, Canadian dollars.

Currency partition

Separates local currency members from the base currency defined in the application. This dimension type is used only in the main database and is only for currency conversion applications. The base currency for analysis may be U.S. dollars, and the local currency members may contain values that are based on the currency type of their region.

Member Storage Properties

You can specify data storage properties for members; data storage properties define where and when consolidations are stored. For example, by default, members are tagged as store data. Essbase sums the values of 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. For example, you can change a store data member to a label only member. Members with the label only tag, for example, do not have data associated with them.

Table 6 describes the effect that Essbase data storage properties have on members.

Table 6. Essbase Data Storage Properties

Data Storage Properties

Effects on Members

Store data

The member stores data. Store data is the default storage property.

Dynamic Calc

The data associated with the member is not calculated until requested by a user. The calculated data is not stored; it is discarded after the request is completed.

Dynamic Calc and Store

The data associated with the member is not calculated until it is requested by a user. The calculated data is then stored.

Shared member

The data associated with the member comes from another member with the same name.

Never share

The data associated with the member is duplicated with the parent and its child if an implied shared relationship exists.

Label only

Although a label only member has no data associated with it, a label only member can display a value. The label only tag groups members and eases navigation and reporting. Typically, label only members are not calculated.

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.

Checklist for 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?

  • Which members require special data storage properties?

Designing an Outline to Optimize Performance

Position attribute dimensions at the end of the outline. 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.

Use the following topics to understand performance optimization basics.

Optimizing Query Performance

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

  • If the outline contains attribute dimensions, ensure that the attribute dimensions are the only sparse Dynamic Calc dimensions in the outline.

  • In the outline, place the more-queried sparse dimensions before the less-queried sparse dimensions.

The outline in Figure 31, Designing an Outline for Optimized Query Times is designed for optimum query performance:

  • Because the outline contains attribute dimensions, the storage property for standard dimensions and all standard dimensions 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 31. Designing an Outline for Optimized Query Times

This image illustrates an outline that was designed for optimum query performance, as described in the text preceding the image.

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.

See Designing for Calculation Performance.

The outline in Figure 32, Designing an Outline for Optimized Calculation Times 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 32. Designing an Outline for Optimized Calculation Times

This image illustrates an outline that was designed for optimum calculation performance, as described in the text preceding the image.

Meeting the Needs of Both Calculation and Retrieval

Although they contain the same dimensions, the example outlines in Figure 31, Designing an Outline for Optimized Query Times and Figure 32, Designing an Outline for Optimized Calculation Times are different. To determine the best outline sequence for a situation, 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 initially to position the dimensions in the outline to optimize calculation. 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, resequence the dimensions in the outline to optimize calculation.

Checking System Requirements

Now you are ready to determine the system requirements for the database.

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 real data provides flexibility and shortens the time required to test and analyze results.

Detailed instructions for loading data are in the following chapters:

If you are satisfied with your database design after the preliminary test, test load the complete set of real data with which you will populate the final database. Use the test rules files if possible. This final test may reveal source data problems that were not anticipated during earlier design process phases.

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

The following topics use the Product and Measures dimensions of the TBC application to illustrate several types of common calculations that are found in many Essbase databases.

For information on block storage calculations, see the following chapters:

Consolidation of Dimensions and Members

When you define members of standard dimensions, Essbase automatically tags the members with the + (plus sign representing addition) consolidator, meaning that during consolidation members are added. As appropriate, you can change a member consolidation property to one of the following operators: - (dash representing subtraction), * (asterisk representing multiplication), / (forward slash representing division), % (percent sign representing percentage), and ~ (tilda representing no consolidation).

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

The TBC 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.

The following topics discuss consolidation in greater detail:

Consolidation operators define how Essbase rolls up data for each member in a branch to the parent. For example, using the default addition (+) operator, Essbase adds 100‑10, 100‑20, and 100‑30 and stores the result in their parent, 100, as shown in Figure 33, TBC Product Dimension.

Figure 33. TBC Product Dimension

This image shows consolidation operators in an outline, as described in the text preceding the image.

The Product dimension contains mostly addition (+) operators, which indicate that each group of members is added and rolled up to the parent. Diet has a tilde (~) operator, which indicates that Essbase does not include the Diet member in the consolidation to the parent, Product. The Diet member consists entirely of members that are shared. 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 overall consolidation.

Effect of Position and Operator on Consolidation

Essbase calculates the data of a branch in top-down order. For example, if you have, in order, two members tagged with an addition (+) operator and a third member tagged with a multiplication (*) operator, Essbase adds the first two and multiplies that sum by the third.

Because Essbase always begins with the top member when it consolidates, the order and the labels of the members is important. See Calculating Members with Different Operators.

Table 7 defines Essbase consolidation operators.

Table 7. Consolidation Operations

Consolidation Operator

Description

+

The default operator. Essbase adds the member to the result of previous calculations performed on members of the branch.

Essbase multiplies the member by ‑1 and then adds the product to the result of previous calculations performed on members of the branch.

*

Essbase multiplies the member by the result of previous calculations performed on members of the branch.

/

Essbase divides the member into the result of previous calculations performed on members of the branch.

%

Essbase divides the member into the sum of previous calculations performed on members of the branch. The result is multiplied by 100.

~

Essbase does not use the value of the member in the consolidation to its parent.

Consolidation of Shared Members

Shared members also affect consolidation paths. The shared member concept enables two members with the same name to share the same data. The shared member stores a pointer to data contained in the other member, so Essbase stores the data only once. Shared members must be in the same dimension. Data can be shared by multiple members.

Checklist for Consolidation

Use the following checklist to help define consolidation:

  • Did you identify the consolidations in the outline?

  • Did you tag each member with the proper consolidation operator?

  • Did you specify a shared member tag for designated members?

  • Would shared members be more efficient if designed within an attribute dimension (other than shared)?

Tags and Operators on Example Measures Dimension

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

Look closely at the Measures dimension tags defined by TBC (in Figure 34, TBC Measures Dimension). Many of the properties of the Measures dimension are discussed in previous topics of this chapter: addition (+), subtraction (–), and no consolidation (~) operators, and accounts and label only tags:

  • The Inventory and Ratios member names assist the user in data navigation. They do not contain data and therefore receive a label only tag.

  • The Measures dimension itself has a label only tag. Some members of Measures have a Dynamic Calc tag. Dynamic calculations are discussed in Dynamic Calculations.

  • Some members of Measures have a time balance tag (TB First or TB Last). Time balance tags are discussed in Setting Time Balance Properties.

Figure 34. TBC Measures Dimension

This image shows member properties, such as consolidation operators and tags, in the TBC Measures dimension, as described in the text preceding the image.

Accounts Dimension Calculations

This topic discusses two forms of calculations for a dimension tagged as accounts, time balance properties and variance reporting.

Time Balance Properties

Note the two tags in the Measures dimension of Table 9—TB first and TB last. These tags, called time balance tags or properties, provide instructions to Essbase about how to calculate the data in a dimension tagged as accounts. Using the tags requires 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 equals 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 equals the ending value for the quarter. Ending Inventory requires the time balance tag, TB last. Table 8 defines the time balance tags for the accounts dimension.

Table 8. Accounts Member Tags

Tags

Description

Time Balance Last

The value for the last child member is carried to the parent. For example, March is carried to Qtr1.

Time Balance First

The value for the first child is carried to the parent. For example, Jan is carried to Qtr1.

In Table 9, Qtr1 (second column from the right) and Year (right-most column) show how consolidation in the time dimension is affected by time balance properties in the accounts dimension. Data is shown for the first quarter only.

Table 9. TBC Consolidations Affected by Time Balance Properties

Dimensions

Jan

Feb

Mar

Qtr1

Year

Accounts Member1

11

12

13

36

Qtr1 + Qtr2 + Qtr3 + Qtr4

Accounts Member2 (TB First)

20

25

21

20

20

Accounts Member3 (TB Last)

25

21

30

30

Value of Qtr4

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

For examples, see Setting Time Balance Properties.

Variance Reporting

One TBC Essbase requirement 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.

Essbase provides two variance reporting properties—expense and nonexpense. The default is nonexpense. Variance reporting properties define how 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 and Functions

Formulas calculate relationships between members in the database outline. You can apply formulas to members in the outline, or you can place formulas in a calculation script. This topic explains 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 comprise operators and functions, as well as dimension names, member names, and numeric constants.

Essbase supports the following operators:

  • 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 Essbase functions include more than 100 predefined routines to extend the calculation capabilities of Essbase. Essbase supports the following functions:

  • 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 position of the current member

  • Range functions, which declare a range of members as an argument to another function or to a command

  • Financial functions, which perform specialized financial calculations

  • Member set functions, which are based on a specified member and which generate lists of members

  • Allocation functions, which allocate values that are input at a parent level across child members

  • Forecasting functions, which manipulate data for the purpose 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 specify the calculation mode that 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(@NAME(Ounces))

Essbase uses consolidation 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.

Note:

In the Profit per Ounce formula, the @NAME function is also used to process the string “Ounces” for the @ATTRIBUTEVAL function.

For a complete list of operators, functions, and syntax, see the Oracle Essbase Technical Reference. Also see Developing Formulas for Block Storage Databases.

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, Essbase calculates the combinations of that member when you retrieve the data, instead of precalculating 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.

In Figure 35, TBC Measures Dimension, Dynamic Calc Tags, the TBC Measures dimension contains several members tagged as Dynamic Calc—Profit, Margin, Total Expenses, Margin %, and Profit %.

Figure 35. TBC Measures Dimension, Dynamic Calc Tags

This image shows the members in the TBC Measures dimension that are tagged as Dynamic Calc, as described in the text preceding the image.

When an overall database calculation is performed, the Dynamic Calc members and their corresponding formulas are not calculated. These members are calculated when a user requests them, for example, from Spreadsheet Add-in. 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.

To decide when to calculate data values dynamically, consider your priorities in the following areas:

  • Optimum regular calculation time (batch calculation)

  • Low disk space usage

  • Reduced database restructure time

  • Speedy data retrieval for users

  • Reduced backup time

See Dynamically Calculating Data Values.

Two-Pass Calculations

In the TBC database, Margin % and Profit % contain the label two-pass. This default label indicates that some member formulas must be calculated twice to produce the desired value. The two-pass property works only on members of the dimension tagged as accounts and on members tagged as Dynamic Calc and Dynamic Calc and Store.

The following example illustrates why Profit % (based on the formula Profit % Sales) has a two-pass tag. The tables have five columns (column headers are labeled left to right as Dimension, Jan, Feb, Mar, and Qtr1) and three rows (labeled as Profit, Sales, and Profit %). Jan, Feb, Mar, and Qtr1 are members of the Year dimension. Profit, Sales, and Profit % are members of the Measures (accounts) dimension.

Table 10, Data Loaded into Essbase defines the initial data to load into Essbase. The data values for Profit -> Jan, Profit -> Feb, and Profit -> Mar are 100. The data value for Sales -> Jan, Sales -> Feb, and Sales -> Mar are 1000.

Table 10. Data Loaded into Essbase

Dimension

Jan

Feb

Mar

Qtr1

Profit

100

100

100

N/A

Sales

1000

1000

1000

N/A

Profit %

N/A

N/A

N/A

N/A

First, Essbase calculates the Measures dimension. In Table 11, Data After Essbase Calculates the Measures Dimension, the data values for Profit % -> Jan, Profit % -> Feb, and Profit % -> Mar are 10%.

Table 11. Data After Essbase Calculates the Measures Dimension

Dimension

Jan

Feb

Mar

Qtr1

Profit

100

100

100

Sales

1000

1000

1000

Profit %

10%

10%

10%

N/A

Next, Essbase calculates the Year dimension. The data rolls up across the dimension. In Table 12, Data After Essbase Calculates the Year Dimension, the data values for Profit -> Qtr1 (300) and Sales -> Qtr1 (3000) are correct. The data value for Profit % -> Qtr1 (30%) is incorrect because Profit % is tagged as a two-pass calculation.

Table 12. Data After Essbase Calculates the Year Dimension

Dimension

Jan

Feb

Mar

Qtr1

Profit

100

100

100

300

Sales

1000

1000

1000

3000

Profit %

10%

10%

10%

30%

Essbase then recalculates profit percentage at each occurrence of the member Profit %. In Table 13, Data After Essbase Recalculates Profit Percentage, the data value for Profit % -> Qtr1 (10%) is correct after the second pass.

Table 13. Data After Essbase Recalculates Profit Percentage

Dimension

Jan

Feb

Mar

Qtr1

Profit

100

100

100

300

Sales

1000

1000

1000

3000

Profit %

10%

10%

10%

10%

Checklist for Calculations

Use the following checklist when you define a calculation:

  • Does the default calculation logic achieve accurate results?

  • Which members require formulas?

  • Which members require time balance tags?

  • Which members require variance reporting?

  • Which members require two-pass calculation?

  • Which members can be tagged as Dynamic Calc?

Note:

The Essbase triggers feature enables efficient monitoring of data changes in a database. See Understanding Triggers Definitions.

Defining Reports

To ensure that the design meets user information requirements, you must view data as users view it. Users typically view data through spreadsheets, printed reports, or reports published on the Web. Oracle and its partners offer many tools for producing the reporting systems that users use.

Several tools can help you display and format data quickly, and test whether the database design meets user needs. You can use the Report Script Editor in Administration Services Console to write report scripts quickly. Those familiar with spreadsheets can use the Spreadsheet Add-in or Smart View (Smart View requires Provider Services).

During the design phase, check for the following things:

  • Grouping and sequencing of data. Do the intersections enabled by the design provide the data that users need?

  • Levels of totals. What consolidation levels are required by, for example, a Spreadsheet Add-in 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?

Be sure to use the appropriate tool to create and test predesigned use reports against the test data. The reports that you design should provide information that meets your original objectives. The reports should be easy to use, providing the right combinations of data and the right amount of data. Because reports with too many columns and rows are difficult to use, you may need to create several reports instead of one all‑inclusive report.

Verifying the Design

After you analyze the data and create a preliminary design, check all aspects of the design with users. You should already have verified that the database satisfies the users’ analysis and reporting needs. Ensure that the database satisfies all of their goals.

Do the calculations provide 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.

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

Essbase provides several sources of information to help you isolate problems. Sources include application and Essbase Server logs, exception logs, and database information accessible from Administration Services. Look at documentation topics relevant to your problem; for example, topics about security, calculations, reports, or general error messages. Use the index of this guide for help in solving problems. Look up such terms as troubleshooting, logs, 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.