Case Study: Designing a Single-Server, Multidimensional Database Skip Navigation
Essbase® Analytic Services Database Administrator's Guide | Update Contents | Previous | Next | Print | ? |
Information Map

Case Study: Designing a Single-Server, Multidimensional Database


To implement a multidimensional database, first you install Essbase Analytic Services, and then you design and create an application and databases. You analyze data sources and define requirements very carefully and then decide whether a single-server approach or a partitioned, distributed approach best serves your needs. For criteria that you can review to decide whether to partition an application, see Deciding Whether to Partition 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. For detailed information about building applications and databases, see Creating Applications and Databases.

Note: The information in this chapter is designed for block storage databases. Some of the information is not relevant to aggregate storage databases. For detailed information on the differences between aggregate and block storage, see Comparison of Aggregate and Block Storage.

This chapter includes the following topics:

Process for Designing a Database

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

Figure 36: The Database Design Cycle

The database design process 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 relate to one another. 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 World Wide Web. If you plan to provide predefined reports to users, you design report layouts and run reports. See Defining Reports.

  7. Verify with users.

    You want to ensure that the database satisfies your user goals. You must solicit and carefully consider the opinions of users. See Verifying the Design.

  8. Repeat the process.

    To fine-tune the design, you 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 an Analytic Services database. The examples follow a variation of the Sample Basic application that is included with the Analytic Services 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 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 Analytic Services 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 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 Analytic Services multidimensional database plays a key role in 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 three tasks:

When designing a multidimensional application, consider these factors:

Note: The best practices recommendation is to define only one database per application. There are several reasons for this recommendation, including enhanced memory usage and ease of database administration. Applications that use the optional Analytic Services 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, 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 time when you create the database outline and load data into the Analytic Services 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.

Determine the location of the current data.

Make sure that the data is ready to load into Analytic Services.

Identifying User Requirements

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

Planning for Security in a Multiple User Environment

The time to think about the type of security permissions you plan to issue for an Analytic Services database is when you consider user information needs. End your analysis with a list of users and permissions.

Use this checklist to plan for security:

See Managing Security for Users and Applications for information about assigning user permissions.

Creating Database Models

You are now ready to 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 choose to analyze the following areas:

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 Analytic Services database is deciding how the database enables data analysis:

Regardless of the business area, you need to 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 of the business views 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 Analytic Services, you can use as many dimensions as you need for analysis. A typical Analytic Services database contains at least seven standard dimensions (non-attribute dimensions) and many more attribute dimensions.

When you have an idea of 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 Analytic Services database makes it easy for users to analyze information from many different perspectives. A financial analyst, for example, may ask the following questions:

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

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

Qtr2

Apr, May, Jun

Qtr3

Jul, Aug, Sep

Qtr4

Oct, Nov, Dec

Measures

Profit

Margin: Sales, COGS

Total Expenses: Marketing, Payroll, Miscellaneous

Inventory

Opening Inventory, Additions, Ending Inventory

Ratios

Margin %, Profit %, Profit per Ounce

Product

Colas (100)

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

Root Beer (200)

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

Cream Soda (300)

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

Fruit Soda (400)

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

Market

East

Connecticut, Florida, Massachusetts, New Hampshire, New York

West

California, Nevada, Oregon, Utah, Washington

South

Louisiana, New Mexico, Oklahoma, Texas

Central

Colorado, Illinois, Iowa, Missouri, Ohio, Wisconsin

Scenario

Actual

 

Budget

 

Variance

 

Variance %

 



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

 



Checklist for Determining Dimensions and Members

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

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

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.

Use the information in the following topics to analyze and, as needed, to improve your database design:

Dense and Sparse Dimensions

You need to decide which dimensions are sparse and which dense. These decisions affect performance. For a basic introduction, see Sparse and Dense Dimensions. For a comprehensive discussion of storage and performance, see Designing an Outline to Optimize Performance.

Standard and Attribute Dimensions

For simplicity, the examples in this topic 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. 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 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. 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 member cannot have more than one attribute member. 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) include the following combinations:

As attribute dimensions associated with the Product dimension, Ounces and Pkg Type can 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 38 shows a simplified set of matrixes for three dimensions.

Figure 38: Analyzing Dimensional Relationships

For each combination of dimensions, ask three questions:

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

Repetition in Outlines

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

Repetition
No Repetition

Accounts
   Budget
     Profit
        Margin
           Sales
           COGS
        Expenses
   Actual
     Profit
        Margin
           Sales
           COGS
        Expenses

Accounts
   Profit
      Margin
         Sales
         COGS
      Expenses
Scenario
    Budget
    Actual



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.

The left column of this table uses shared members in the Diet dimension to analyze diet beverages. You can avoid the repetition of the left column and simplify the design of the outline by creating a Diet attribute dimension, as shown in the second example.

Repetition
No Repetition

Product
    100 (Alias: Colas)
          100-10 (Alias: Cola)
          100-20 (Alias: Diet Cola)
    200 (Alias: Root Beer)
          200-20 (Alias: Diet Root Beer)
          200-30 (Alias: Birch Beer)
    300 (Alias Cream Soda)
          300-10 (Alias: Dark Cream)
          300-20 (Alias: Diet Cream)
    Diet (Alias: Diet Drinks)
          100-20 (Alias: Diet Cola)
          200-20 (Alias: Diet Root Beer)      
          300-20 (Alias: Diet Cream)

Product (Diet)
    100 (Alias: Colas)
          100-10 (Alias: Cola) (Diet: False)
          100-20 (Alias: Diet Cola) (Diet: True)
    200 (Alias: Root Beer)
          200-20 (Alias: Diet Root Beer) (Diet: True)
          200-30 (Alias: Birch Beer) (Diet: False)
    300 (Alias Cream Soda)
          300-10 (Alias: Dark Cream) (Diet: False)
          300-20 (Alias: Diet Cream) (Diet: True)
Diet Attribute (Type: Boolean)
     True
     False



Attribute dimensions also provide additional analytic capabilities. For a review of the advantages of using attribute dimensions, see Designing Attribute Dimensions.

Interdimensional Irrelevance

Interdimensional irrelevance occurs when many members of a dimension are irrelevant across other dimensions. Analytic Services defines irrelevant data as data that Analytic Services 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 4 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.


Table 4: Interdimensional Irrelevance Example  


Joe Smith
Mary Jones
Mike Garcia
All Employees

Revenue

 

 

 

x

Variable Costs

 

 

 

x

COGS

 

 

 

x

Advertising

 

 

 

x

Salaries

x

x

x

x

Fixed Costs

 

 

 

x

Expenses

 

 

 

x

Profit

 

 

 

x



Reasons to Split Databases

As discussed in the previous topic, Interdimensional Irrelevance, TBC agreed that, in context with other dimensions, individual employees were irrelevant. They also agreed that adding an Employee dimension substantially increased 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 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:

Drafting Outlines

At this point, you can create the application and database and build the first draft of the outline in Analytic Services. 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, you must create an Analytic Services application to contain it.

The TBC planners issued the following draft for a database outline. In this plan, the bold words 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.

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

An outline is comprised of 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:

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

Dimension Types

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

Figure 39: TBC Dimensions and Related Properties

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

Table 5 defines each Analytic Services 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 Analytic Services 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 type of currency used in each member.

For example, Canada has three markets-Vancouver, Toronto, and Montreal. They use the same currency type, 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 US dollars, and the local currency members may contain values that are based on the currency type of their region.



Member Storage Properties

With Analytic Services, 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. Analytic Services 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 label only member. Members with the label only tag, for example, do not have data associated with them.

Table 6 describes Analytic Services data storage properties.


Table 6: Analytic Services Data Storage Properties  

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 its parent or child if an implied shared relationship exists.

Label only

Although a label only member has no data associated with it, it can still 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

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.

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:

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

Figure 40: 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 information about factors that affect calculation performance, see Designing for Calculation Performance.

The outline shown in Figure 41 is designed for optimum calculation performance:

Figure 41: 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 of Figure 40 and Figure 41 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 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, remember to resequence the dimensions in the outline to optimize calculation.

Checking System Requirements

After you determine the approximate number of dimensions and members in your Analytic Services database, 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:

After you run your preliminary test, if you are satisfied with your database design, test the loading of the complete set of real data with which you will populate the final database, using the test rules files if possible. This final test may reveal problems with the source data that you did not anticipate during earlier phases of the database design process.

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 Analytic Services databases:

For details on Analytic Services calculations, see the following chapters:

Consolidation of Dimensions and Members

When you define members of standard dimensions, Analytic Services automatically tags the members with the addition (+) consolidator, meaning that during consolidation members are added. As appropriate, you can change a member consolidation property to one of the following operators: -, *, /, %, and ~ (no consolidation).

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

The TBC application has several consolidation paths:

The following topics discuss consolidation in greater detail:

Consolidation operators define how Analytic Services rolls up data for each member in a branch to the parent. For example, using the default operator (+), Analytic Services adds 100-10, 100-20, and 100-30 and stores the result in their parent, 100, as shown in Figure 42.

Figure 42: TBC Product Dimension

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

The following topics discuss consolidation in more detail:

Effect of Position and Operator on Consolidation

Analytic Services calculates the data of 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 (*). Analytic Services adds the first two and multiplies the sum by the third.

Be aware that Analytic Services always begins with the top member when it consolidates, so the order and the labels of the members is very important. For an example of how Analytic Services applies operators, see Calculating Members with Different Operators.

Table 7 shows the Analytic Services consolidation operators.


Table 7: Consolidation Operations  

Operator
Description

+

The default operator. When a member has the + operator, Analytic Services adds the member to the result of previous calculations performed on members of the branch.

-

When a member has the - operator, Analytic Services multiplies the member by -1 and then adds the product to the result of previous calculations performed on members of the branch.

*

When a member has the * operator, Analytic Services multiplies the member by the result of previous calculations performed on members of the branch.

/

When a member has the / operator, Analytic Services divides the member into the result of previous calculations performed on members of the branch.

%

When a member has the % operator, Analytic Services divides the member into the sum of previous calculations performed on members of the branch. The result is multiplied by 100.

~

When a member has the ~ operator, Analytic Services does not use it 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 Analytic Services stores the data only once. Shared members must be in the same dimension. Data can be shared by two or more members.

Checklist for Consolidation

Use the following checklist to help define consolidation:

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 Analytic Services calculate the outline. This topic 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 43). Many of the properties of the Measures dimension are discussed in previous topics of this chapter: positive (+), negative (-), and tilde (~) consolidation operators as well as accounts and label only tags:

Figure 43: TBC Measures Dimension

Accounts Dimension Calculations

This topic discusses two forms of calculations for a dimension tagged as accounts:

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 Analytic Services about how to calculate the data in a dimension tagged as accounts. To use the 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 8 shows 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.



Table 9 shows how consolidation in the time dimension is affected by time balance properties in the accounts dimension; details are shown only for first quarter:


Table 9: TBC Consolidations Affected by Time Balance Properties

Accounts -> Time
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, then any parent in the time dimension matches the member marked as TB First.

For examples of the use of time balance tags, see Setting Time Balance Properties.

Variance Reporting

One of the TBC Analytic Services 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.

Analytic Services provides two variance reporting properties-expense and non-expense. The default is non-expense. Variance reporting properties define how Analytic Services 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

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 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 are composed of operators and functions, as well as dimension names, member names, and numeric constants.

Analytic Services supports the following operators:

The Analytic Services functions include over 100 predefined routines to extend the calculation capabilities of Analytic Services. Analytic Services supports the following functions:

The Measures dimension uses the following formulas:

Analytic Services 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 Technical Reference. For a comprehensive 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, Analytic Services 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 44, the TBC Measures dimension contains several members that are tagged as Dynamic Calc-Profit, Margin, Total Expenses, Margin %, and Profit %.

Figure 44: TBC Measures Dimension, Dynamic Calc Tags

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

For a comprehensive discussion of dynamic calculation, 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 of the dimension tagged as accounts and on members tagged as Dynamic Calc and Dynamic Calc and Store. The following examples illustrate why Profit % (based on the formula Profit%Sales) has a two-pass tag.

Analytic Services loads data into the system as follows:


Measures -> Year
Jan
Feb
Mar
Qtr1

Profit

100

100

100

.

Sales

1000

1000

1000

.

Profit %

.
.
.
.


Analytic Services calculates Measures first. The data then looks as follows:


Measures -> Year
Jan
Feb
Mar
Qtr1

Profit

100

100

100

.

Sales

1000

1000

1000

.

Profit %

10%

10%

10%

 



Next, Analytic Services calculates the Year dimension. The data rolls up across the dimension.


Measures -> Year
Jan
Feb
Mar
Qtr1

Profit

100

100

100

300

Sales

1000

1000

1000

3000

Profit %

10%

10%

10%

30%



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


Measures -> Year
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:

Note: The triggers feature provided by Analytic Services enables efficient monitoring of data changes in a database. For more information, see Understanding Triggers Definitions. Triggers is licensed separately from Analytic Services.

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.

Analytic Services provides several tools that can help you during the design process to display and format data quickly and to test whether the database design meets user needs. You can use Administration Services Console Report Script Editor to write report scripts quickly. Those familiar with spreadsheets can use the Spreadsheet Add-in or Spreadsheet Services (Spreadsheet Services requires Deployment Services).

During the design phase, check for the following things:

If you provide predesigned reports for users, now is the time to use the appropriate tool to create those 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. 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 reports.

Verifying the Design

After you analyze the data and create 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 that you check with the users to ensure that the database satisfies all of their goals.

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.

Near the end of the design cycle, you need to 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.

Analytic Services provides several sources of information to help you isolate problems. Sources include application and Analytic 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. You can also use the index of this guide to find help for 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.



Hyperion Solutions Corporation link