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.
0 comments:
Post a Comment