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?