Designing a Single-Server, Multidimensional Database

To implement a multidimensional database, first you install Essbase, 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,

The information in this post is designed for block storage databases. Some of the information is not relevant to aggregate storage databases.

Process for Designing a Database

As illustrated in the below figure, designing an application is a cyclic process that moves from a planning stage to a verification stage.

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.

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.

3. Check system requirements.

How you meet system requirements and define system parameters affects the efficiency and performance of the database.

4. Load test data into the database.

After an outline and a security plan are in place, you load the database with test data to enable the later steps of the process.

5. Define calculations.

You test outline consolidations and write and test formulas and calculation scripts for specialized 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.

7. Verify with users.

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

8. Repeat the process.

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

Case Study: The Beverage Company

This chapter bases the database planning process on the needs of a fictitious company called The Beverage Company (TBC) and uses TBC as an example to demonstrate how to build 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 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 Essbase is the best tool for creating a centralized repository for financial data. The data repository will reside on a server that is accessible to analysts throughout the organization. Users will have access to the server and will be able to load data from various sources and retrieve data 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 play 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:

● “Analyzing Source Data”

● “Identifying User Requirements”

● “Planning for Security in a Multiple User Environment”

● “Creating Database Models”

When designing a multidimensional application, consider these factors:

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

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.

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 the needed data?

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 a DB2 database on an IBM mainframe, in a relational database

on a UNIX-based server, or in a PC-based database or spreadsheet?

● Who updates the database and how frequently?

● Do the individuals who need to update data have access to the data?

Make sure that the data is ready to load into Essbase.

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

● Is data in a format that Essbase can use? For a list of valid data sources that you can load

into Essbase,

● 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

You must 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?

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 choose to 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”

● “Determining Dimensions and Members”

● “Analyzing Database Design”

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? Does the analysis need to include only the current year or multiple years? Does the analysis need to include quarterly and monthly data? Does the analysis need to 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, do you need to review data for each specific product? Can you summarize data into product classes?

Regardless of the business area, you need to determine the perspective and detail needed in the analysis. Each business area 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 (non-attribute 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:

● “Relationships Among Dimensions”

● “Example Dimension-Member Structure”

● “Checklist for Determining Dimensions and Members”

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 different perspectives. A financial analyst, for example, may ask the following questions:

● What are sales for a particular month? How does this figure compare to sales in the same month over the last five years?

● By what percentage is profit margin increasing?

● How close are actual values to budgeted values?

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

● A time dimension, which consists of the individual months Jan, Feb, and Mar and the total for Qtr1, is displayed along the X-axis.

● An accounts dimension, which consists of accounting figures such as Sales, COGS, Margin, and Margin%, is displayed along the Y-axis.

● Another dimension which provides a different point of view, such as Budget for budget values and Actual for actual values, is displayed along the Z-axis.

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.

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

The number of members needed to describe a potential data point should determine the number of dimensions. 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 improve your database design:

● “Dense and Sparse Dimensions”

● “Standard and Attribute Dimensions”

● “Dimension Combinations”

● “Repetition in Outlines”

● “Interdimensional Irrelevance”

● “Reasons to Split Databases ”

● “Checklist to Analyze the Database Design”

Dense and Sparse Dimensions

Which dimensions are sparse and which dense affects performance. Refer to the previous posts regarding the Sparse and Dense Dimensions

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

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. The below figure shows a simplified set of matrixes for three dimensions.

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 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, discuss information needs with users.

Read this aritcle completely...

Types Of Dimensions in Hyperion Essbase

Standard Dimensions and Attribute Dimensions

Essbase has two types of dimensions: standard dimensions and attribute dimensions. This chapter primarily considers standard dimensions because Essbase does not allocate storage for attribute dimension members. Instead it dynamically calculates the members when the user requests data associated with them.

An attribute dimension is a special type of dimension that is associated with a standard dimension.

Sparse and Dense Dimensions

Most data sets of multidimensional databases have two characteristics:

● Data is not smoothly and uniformly distributed.

● Data does not exist for the majority of member combinations. For example, all products may not be sold in all areas of the country.

Essbase maximizes performance by dividing the standard dimensions of an application into two types:

1) Dense dimensions and

2) Sparse dimensions.

This division allows Essbase to cope with data that is not smoothly distributed, without losing the advantages of matrix-style access to the data.

Essbase speeds up data retrieval while minimizing the memory and disk requirements.

Most multidimensional databases are inherently sparse: they lack data values for the majority of member combinations. A sparse dimension is a dimension with a low percentage of available data positions filled.

For example, the Sample Basic database shown in Figure A includes the Year, Product, Market, Measures, and Scenario dimensions. Product represents the product units, Market represents the geographical regions in which the products are sold, and Measures represents the accounts data. Because not every product is sold in every market, Market and Product are chosen as sparse dimensions.

Most multidimensional databases also contain dense dimensions. A dense dimension is a dimension with a high probability that one or more cells is occupied in every combination of dimensions. For example, in the Sample Basic database, accounts data exists for almost all products in all markets, so Measures is chosen as a dense dimension. Year and Scenario are also chosen as dense dimensions. Year represents time in months, and Scenario represents whether the accounts values are budget or actual values.

Note:

In Figure A, Caffeinated, Intro Date, Ounces, Pkg Type and Population are attribute dimensions.

Fig A:

Selection of Dense and Sparse Dimensions

In most data sets, existing data tends to follow predictable patterns of density and sparsity. If you match patterns correctly, you can store the existing data in a reasonable number of fairly dense data blocks, rather than in many highly sparse data blocks.

By default, a new dimension is set sparse. To help you determine whether dimensions should be dense or sparse, Essbase provides an automatic configuration feature.

Essbase can make recommendations for the sparse-dense configuration of dimensions based on the following factors:

● The time and accounts tags on dimensions

● The probable size of the data blocks

● Characteristics that you attribute to the dimensions

You can apply a recommended configuration or you can turn off automatic configuration and manually set the sparse or dense property for each dimension. Attribute dimensions are always sparse dimensions. Keep in mind that you can associate attribute dimensions only with sparse standard dimensions.

Note:

The automatic configuration of dense and sparse dimensions provides only an estimate. It cannot take into account the nature of the data you will load into your database or multiple user considerations.

Dense-Sparse Configuration for Sample Basic

Consider the Sample Basic database that is provided with Essbase. The Sample Basic database represents data for The Beverage Company (TBC).

TBC does not sell every product in every market; therefore, the data set is reasonably sparse.

Data values do not exist for many combinations of members in the Product and Market dimensions. For example, if Caffeine Free Cola is not sold in Florida, then data values do not exist for the combination Caffeine Free Cola (100-30)->Florida.So, Product and Market are sparse dimensions. Therefore, if no data values exist for a specific combination of members in these dimensions, Essbase does not create a data block for the combination.

However, consider combinations of members in the Year, Measures, and Scenario dimensions.

Data values almost always exist for some member combinations on these dimensions. For example, data values exist for the member combination Sales->January->Actual because at least some products are sold in January. Thus, Year and, similarly, Measures and Scenario are dense dimensions.

The sparse-dense configuration of the standard dimensions in the Sample Basic database may be summarized as follows:

● The sparse standard dimensions are Product and Market.

● The dense standard dimensions are Year, Measures, and Scenario.

Essbase creates a data block for each unique combination of members in the Product and Market dimensions (for more information on data blocks, each data block represents data from the dense dimensions. The data blocks are likely to have few empty cells.

For example, consider the sparse member combination Caffeine Free Cola (100-30), New York, illustrated by Figure B:

● If accounts data (represented by the Measures dimension) exists for this combination for January, it probably exists for February and for all members in the Year dimension.

● If a data value exists for one member on the Measures dimension, it is likely that other accounts data values exist for other members in the Measures dimension.

● If Actual accounts data values exist, it is likely that Budget accounts data values exist.

Fig B:

Dense and Sparse Selection Scenarios

The following scenarios show how a database is affected when you select different standard dimensions. Assume that these scenarios are based on typical databases with at least seven dimensions and several hundred members:

Scenario 1: All Sparse Standard Dimensions

If you make all dimensions sparse, Essbase creates data blocks that consist of single data cells that contain single data values. An index entry is created for each data block and, therefore, in this scenario, for each existing data value.

This configuration produces an index that requires a large amount of memory. The more index entries, the longer Essbase searches to find a specific block.

Fig C:

Scenario 2: All Dense Standard Dimensions

If you make all dimensions dense, as shown in Figure D, Essbase creates one index entry and one very large, very sparse block. In most applications, this configuration requires thousands of times more storage than other configurations. Essbase needs to load the entire memory when it searches for a data value, which requires enormous amounts of memory.

Fig D:

Scenario 3: Dense and Sparse Standard Dimensions

Based upon your knowledge of your company’s data, you have identified all your sparse and dense standard dimensions. Ideally, you have approximately equal numbers of sparse and dense standard dimensions. If not, you are probably working with a non-typical data set and you need to do more tuning to define the dimensions.

Essbase creates dense blocks that can fit into memory easily and creates a relatively small index as shown in Figure E. Your database runs efficiently using minimal resources.

Fig E:

Scenario 4: A Typical Multidimensional Problem

Consider a database with four standard dimensions: Time, Accounts, Region, and Product. In the following example, Time and Accounts are dense dimensions, and Region and Product are sparse dimensions.

The two-dimensional data blocks shown in Figure F represent data values from the dense dimensions: Time and Accounts. The members in the Time dimension are J, F, M, and Q1. The members in the Accounts dimension are Rev, Exp, and Net.

Fig F:

Essbase creates data blocks for combinations of members in the sparse standard dimensions (providing at least one data value exists for the member combination). The sparse dimensions are Region and Product. The members of the Region dimension are East, West, South, and Total US. The members in the Product dimension are Product A, Product B, Product C, and Total Product.

Figure G shows 11 data blocks. No data values exist for Product A in the West and South, for Product B in the East and West, and for Product C in the East. Therefore, Essbase has not created data blocks for these member combinations. The data blocks that Essbase has created have very few empty cells.

Fig G:

This example effectively concentrates all sparseness into the index and concentrates all data into fully utilized blocks. This configuration provides efficient data storage and retrieval.

Next consider a reversal of the dense and sparse dimension selections. In the following example, Region and Product are dense dimensions, and Time and Accounts are sparse dimensions. As shown in Figure H, the two-dimensional data blocks represent data values from the dense dimensions: Region and Product.

Fig H:

Essbase creates data blocks for combinations of members in the sparse standard dimensions (providing at least one data value exists for the member combination). The sparse standard dimensions are Time and Accounts.

Figure I show 12 data blocks. Data values exist for all combinations of members in the Time and Accounts dimensions; therefore, Essbase creates data blocks for all the member combinations. Because data values do not exist for all products in all regions, the data blocks have many empty cells. Data blocks with many empty cells store data inefficiently.

Fig I:

Read this aritcle completely...

Understanding Multidimensional Databases

Essbase contains multidimensional databases that support analysis and management reporting applications. This chapter discusses multidimensional concepts and terminology.

Note: The information in this chapter is designed for block storage databases. Some of the information is not relevant to aggregate storage databases.

OLAP and Multidimensional Databases

Online analytical processing (OLAP) is a multidimensional, multi-user, client-server computing environment for users who need to analyze enterprise data. OLAP applications span a variety of organizational functions. Finance departments use OLAP for applications such as budgeting, activity-based costing (allocations), financial performance analysis, and financial modelling.

Sales departments use OLAP for sales analysis and forecasting. Among other applications, marketing departments use OLAP for market research analysis, sales forecasting, promotions analysis, customer analysis, and market/customer segmentation. Typical manufacturing OLAP applications include production planning and defect analysis.

Important to all of the applications mentioned in the previous paragraph is the ability to provide managers with the information they need to make effective decisions about an organization's strategic directions. A successful OLAP application provides information as needed, that is, it provides “just-in-time” information for effective decision-making. Providing “just-in-time” information requires more than a base level of detailed data. “Just-intime” information is computed data that usually reflects complex relationships and is often calculated on the fly. Analyzing and modelling complex relationships are practical only if response times are consistently short. In addition, because the nature of data relationships may not be known in advance, the data model must be flexible. A truly flexible data model ensures that OLAP systems can respond to changing business requirements as needed for effective decision making.

Although OLAP applications are found in widely divergent functional areas, they all require the following key features:

● Multidimensional views of data

● Calculation-intensive capabilities

● Time intelligence

Key to OLAP systems are multidimensional databases. Multidimensional databases not only consolidate and calculate data; they also provide retrieval and calculation of a variety of data subsets. A multidimensional database supports multiple views of data sets for users who need to analyze the relationships between data categories. For example, a marketing analyst might want answers to the following questions:

● How did Product A sell last month? How does this figure compare to sales in the same month over the last five years? How did the product sell by branch, region, and territory?

● Did this product sell better in particular regions? Are there regional trends?

● Did customers return Product A last year? Were the returns due to product defects? Did the company manufacture the products in a specific plant?

● Did commissions and pricing affect how salespeople sold the product? Did particular salespeople do a better job of selling the product?

With a multidimensional database, the number of data views is limited only by the database outline, the structure that defines all elements of the database. Users can pivot the data to see information from a different viewpoint, drill down to find more detailed information, or drill up to see an overview.

Dimensions and Members

This section introduces the concepts of outlines, dimensions and members within a multidimensional database. If you understand dimensions and members, you are well on your way to understanding the power of a multidimensional database.

A dimension represents the highest consolidation level in the database outline. The database outline presents dimensions and members in a tree structure to indicate a consolidation relationship. For example, in the below Figure, Time is a dimension and Qtr1 is a member.

Essbase has two types of dimensions: Standard dimensions and Attribute dimensions.

Standard dimensions represent the core components of a business plan and often relate to departmental functions. Typical standard dimensions are Time, Accounts, Product Line, Market, and Division. Dimensions change less frequently than members.

Attribute dimensions are a special type of dimensions that are associated with standard dimensions. Through attribute dimensions, you group and analyze members of standard dimensions based on the member attributes (characteristics). For example, you can compare the profitability of non-caffeinated products that are packaged in glass to the profitability of non-caffeinated products that are packaged in cans.

Members are the individual components of a dimension. For example, Product A, Product B, and Product C might be members of the Product dimension. Each member has a unique name. Essbase can store the data associated with a member (referred to as a stored member in this chapter) or it can dynamically calculate the data when a user retrieves it.

Outline Hierarchies

All Essbase database development begins with creating a database outline. A database outline accomplishes the following:

● Defines the structural relationships between members in an Essbase database

● Organizes all the data in the database

● Defines the consolidations and mathematical relationships between items

Essbase uses the concept of members to represent data hierarchies. Each dimension consists of one or more members. The members, in turn, may consist of other members. When you create a dimension, you tell Essbase how to consolidate the values of its individual members. Within the tree structure of the database outline, a consolidation is a group of members in a branch of the tree.

For example, many businesses summarize their data monthly, rolling up monthly data to obtain quarterly figures, and rolling up quarterly data to obtain annual figures. Businesses may also summarize data by zip code, by city, state, and country. Any dimension can be used to consolidate data for reporting purposes.

In the Sample Basic database included with Essbase Server, for example, the Year dimension consists of five members: Qtr1, Qtr2, Qtr3, and Qtr4, each storing data for an individual quarter, plus Year, storing summary data for the entire year. Qtr1 consists of four members: Jan, Feb, and Mar, each storing data for an individual month, plus Qtr1, storing summary data for the entire quarter. Likewise, Qtr2, Qtr3, and Qtr4 consist of the members that represent the individual months plus the member that stores the quarterly totals.

The database outline in the below figure uses a hierarchical structure to represent the data consolidations and relationships in Qtr1.

Some dimensions consist of relatively few members, while others may have hundreds or even thousands of members. Essbase does not limit the number of members within a dimension and enables the addition of new members as needed.

Dimension and Member Relationships

Essbase uses the terms defined in the following sections to describe a database outline. These terms are used throughout Essbase documentation.

Essbase uses hierarchical and family history terms to describe the roles and relationships of the members in an outline. You can describe the position of the members of the branches in

Parents, Children, and Siblings

The above figure illustrates the following parent, child, and sibling relationships:

● A parent is a member that has a branch below it. For example, Margin is a parent member for Sales and Cost of Goods Sold.

● A child is a member that has a parent above it. For example, Sales and Cost of Goods Sold are children of the parent Margin.

● Siblings are child members of the same immediate parent, at the same generation.

For example, Sales and Cost of Goods Sold are siblings (they both have the parent Margin), but Marketing (at the same branch level) is not a sibling because its parent is Total Expenses.

Descendants and Ancestors

The above figure illustrates the following descendant and ancestral relationships:

● Descendants are members in branches below a parent. For example, Profit, Inventory, and Ratios are descendants of Measures. The children of Profit, Inventory, and Ratios are also descendants of Measures.

● Ancestors are members in branches above a member. For example, Margin, Profit, and Measures are ancestors of Sales.

Roots and Leaves

● The root is the top member in a branch. Measures is the root for Profit, Inventory, Ratios, and the children of Profit, Inventory, and Ratios.

● Leaf members have no children. They are also referred to as level 0 members. For example, Opening Inventory, Additions, and Ending Inventory are leaf members.

Generations and Levels

● Generation refers to a consolidation level within a dimension. A root branch of the tree is

generation 1. Generation numbers increase as you count from the root toward the leaf member. In the above figure, Measures is generation 1, Profit is generation 2, and Margin is generation 3. All siblings of each level belong to the same generation; for example, both Inventory and Ratios are generation 2.

Level also refers to a branch within a dimension; levels reverse the numerical ordering used for generations. Levels count up from the leaf member toward the root. The root level number varies depending on the depth of the branch. In the example in previous above figure, Sales and Cost of Goods Sold are level 0. All other leaf members are also level 0. Margin is level 1, and Profit is level 2. Notice that the level number of Measures varies depending on the branch.

For the Ratios branch, Measures is level 2. For the Total Expenses branch, Measures is level 3.

Generation and Level Names

To make reports easier to maintain, you can assign a name to a generation or level and then use the name as shorthand for all members in that generation or level. Because changes to an outline are automatically reflected in a report, when you use generation and level names, you do not need to change the report if a member name is changed or deleted from the database outline.

Read this aritcle completely...