Case Study: Designing a Single-Server, Multidimensional Database
In This Section:
Some information in this chapter applies only to block storage databases and is not relevant to aggregate storage databases. Also see Comparison of Aggregate and Block Storage.
To implement a multidimensional database, you install Essbase, and then you design and create an application and databases. You analyze data sources and define requirements carefully and decide whether a single-server approach or a partitioned, distributed approach better serves your needs. For criteria that you can review to decide whether to partition an application, see Guidelines for Partitioning a Database.
Using a case study, this chapter provides an overview of the database planning process and discusses working rules that you can follow to design a single-server, multidimensional database solution for your organization. See Creating Applications and Databases.
Figure 26, The Database Design Cycle illustrates the cyclical process of designing a database, which includes the following basic steps:
- 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. - Draft a database outline.
The outline determines the structure of the database—what information is stored and how different pieces of information interrelate. See Drafting Outlines. - Check system requirements.
How you meet system requirements and define system parameters affects the efficiency and performance of the database. See Checking System Requirements. - 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. - Define calculations.
You test outline consolidations and write and test formulas and calculation scripts for specialized calculations. See Defining Calculations. - Define reports.
Users access data through print and online reports and spreadsheets or on the Web. If you plan to provide predefined reports to users, you design report layouts and run reports. See Defining Reports. - Verify with users.
To ensure that the database satisfies your user goals, solicit and carefully consider user opinions. See Verifying the Design. - Repeat the process.
To fine-tune the design, repeat steps 1 through 7.
This chapter bases the database planning process on the needs of a fictitious company called The Beverage Company (TBC) and uses TBC as an example to demonstrate how to build an Essbase database. TBC is a variation of the Sample.Basic application that is included with the Essbase installation.
TBC manufactures, markets, and distributes soft drink products internationally. Analysts at TBC prepare budget forecasts and compare performance to budget forecasts monthly. The financial measures that analysts track are profit and loss and inventory.
TBC uses spreadsheet packages to prepare budget data and perform variance reporting. Because TBC plans and tracks a variety of products over several markets, the process of deriving and analyzing data is tedious. Last month, analysts spent most of their time entering and rekeying data and preparing reports.
TBC has determined that Essbase is the best tool for creating a centralized repository for financial data. The data repository will reside on a server that is accessible to analysts throughout the organization. Users can access the server and load data from various sources and retrieve data as needed. TBC has a variety of users, so TBC expects that different users will have different security levels for accessing data.
The design and operation of an Essbase multidimensional database are key to achieving a well-tuned system that enables you to analyze business information efficiently. Given the size and performance volatility of multidimensional databases, developing an optimized database is critical. A detailed plan that outlines data sources, user needs, and prospective database elements can save you development and implementation time.
- How information flows within the company—who uses which data for what purposes
- The types of reporting the company does—what types of data must be included in the outline to serve user reporting needs
Defining only one database per application enables enhanced memory usage and ease of database administration. Applications that use the optional Essbase Currency Conversion module are an exception to this recommendation. Currency conversion applications generally consist of a main database and a separate currency database (see Designing and Building Currency Conversion Applications).
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.
- Does data come from a single source or multiple sources?
- Is data in a format that Essbase can use? For a list of valid data sources that you can load into Essbase, see Data Sources.
- Is all data that you want to use readily available?
Consider user information needs when you plan how to set up security permissions. End your analysis with a list of users and permissions.
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.
After you identify the major areas of information in a business, the next step in designing an Essbase database is deciding how the database enables data analysis:
- If analyzing by time, which time periods are needed? Should the analysis include only the current year or multiple years? Should the analysis include quarterly and monthly data? Should it include data by season?
- If analyzing by geographical region, how do you define the regions? Do you define regions by sales territories? Do you define regions by geographical boundaries such as states and cities?
- If analyzing by product line, should you review data for each product? Can you summarize data into product classes?
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.
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:
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.
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
- 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?
While the initial dimension design is still on paper, you should review the design according to a set of guidelines. The guidelines help you fine-tune the database and leverage the multidimensional technology. The guidelines are processes or questions that help you achieve an efficient design and meet consolidation and calculation goals.
The number of members needed to describe a potential data point should determine the number of dimensions. If you are not sure whether you should delete a dimension, keep it and apply more analysis rules until you feel confident about deleting or keeping it.
Which dimensions are sparse and which dense affects performance. For an introduction, see Sparse and Dense Dimensions. See Designing an Outline to Optimize Performance.
For simplicity, the examples in this topic show alternative arrangements for what initially was designed as two dimensions. You can apply the same logic to all combinations of dimensions.
Consider the design for a company that sells products to multiple customers over multiple markets; the markets are unique to each customer:
Cust A Cust B Cust C New York 100 N/A N/A Illinois N/A 150 N/A California N/A N/A 30
Cust A is only in New York, Cust B is only in Illinois, and Cust C is only in California. The company can define the data in one standard dimension:
Market
New York
Cust A
Illinois
Cust B
California
Cust C
However, if you look at a larger sampling of data, you may see that many customers can be in each market. Cust A and Cust E are in New York; Cust B, Cust M, and Cust P are in Illinois; Cust C and Cust F are in California. In this situation, the company typically defines the large dimension, Customer, as a standard dimension and the smaller dimension, Market, as an attribute dimension. The company associates the members of the Market dimension as attributes of the members of the Customer dimension. The members of the Market dimension describe locations of the customers.
Customer (Standard dimension)
Cust A (Attribute:New York)
Cust B (Attribute:Illinois)
Cust C (Attribute:California)
Cust E (Attribute:New York)
Cust F (Attribute:California)
Cust M (Attribute:Illinois)
Cust P (Attribute:Illinois)
Market (Attribute dimension)
New York
Illinois
California
Consider another situation. Again, the company sells products to multiple customers over multiple markets, but the company can ship to a customer that has locations in different markets:
Cust A Cust B Cust C New York 100 75 N/A Illinois N/A 150 N/A California 150 N/A 30
Cust A is in New York and California. Cust B is in New York and Illinois. Cust C is only in California. Using an attribute dimension does not work in this situation; a customer member cannot have 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
Break each combination of two dimensions into a two-dimensional matrix. For example, proposed dimensions at TBC (as listed in Table 2, TBC Sample Dimensions) include the following combinations:
Ounces and Pkg Type, as attribute dimensions associated with the Product dimension, can be considered with the Product dimension.
To help visualize each dimension, draw a matrix and include a few of the first-generation members. Figure 28, Analyzing Dimensional Relationships shows a simplified set of matrixes for three dimensions.
For each combination, the answers to the questions help determine whether the combination is valid for the database. Ideally, the answer to each question is yes. If not, consider rearranging the data into more-meaningful dimensions. As you work through this process, discuss information needs with users.
The repetition of elements in an outline often indicates a need to split dimensions. The following examples show you how to avoid repetition.
In Figure 29, Example of Eliminating Repetition By Creating a Scenario Dimension, the left column, labeled “Repetition,” shows Profit, Margin, Sales, COGS, and Expenses repeated under Budget and Actual in the Accounts dimension. The right column, labeled “No Repetition,” separates Budget and Actual into another dimension (Scenario), leaving just one set of Profit, Margin, Sales, COGS, and Expenses members in the Accounts dimension. This approach simplifies the outline and provides a simpler view of the budget and actual figures of the other dimensions in the database.
In Figure 30, Example of Eliminating Repetition By Creating an Attribute Dimension, the left column, labeled “Repetition,” uses shared members in the Diet dimension to analyze diet beverages. Members 100–20, 200–20, and 300–20 are repeated: once under Diet, and once under their respective parents. The right column, labeled “No Repetition,” simplifies the outline by creating a Diet attribute dimension of type Boolean (True or False). All members are shown only once, under their respective parents, and are tagged with the appropriate attribute (“Diet: True” or “Diet: False”).
Attribute dimensions also provide additional analytic capabilities. See Designing Attribute Dimensions.
Interdimensional irrelevance occurs when many members of a dimension are irrelevant across other dimensions. Essbase defines irrelevant data as data that Essbase stores only at the summary (dimension) level. In such a situation, you may be able to remove a dimension from the database and add its members to another dimension or split the model into separate databases.
For example, TBC considered analyzing salaries as a member of the Measures dimension. But salary information often proves irrelevant in the context of a corporate database. Most salaries are confidential and apply to individuals. The individual and the salary typically represent one cell, with no reason to intersect with any other dimension.
TBC considered separating employees into a separate dimension. Table 4 shows an example of how TBC analyzed the proposed Employee dimension for interdimensional irrelevance. Members of the proposed Employee dimension (represented in the table header row) are compared with members of the Measures dimension (represented in the left-most column). An “X” in a cell indicates relevance. Only the Salary measure is relevant to individual employees.
Because individual employee information is irrelevant to the other information in the database, and also because adding an Employee dimension would substantially increase database storage needs, TBC created a separate Human Resources (HR) database. The new HR database contains a group of related dimensions and includes salaries, benefits, insurance, and 401(k) plans.
There are many reasons for splitting a database; for example, suppose that a company maintains an organizational database that contains several international subsidiaries in several time zones. Each subsidiary relies on time-sensitive financial calculations. You can split the database for groups of subsidiaries in the same time zone to ensure that financial calculations are timely. You can also use a partitioned application to separate information by subsidiary.
Now you can create the application and database and build the first draft of the outline in Essbase. The draft defines all dimensions, members, and consolidations. Use the outline to design consolidation requirements and identify where you need formulas and calculation scripts.
Before you create a database and build its outline, create an Essbase 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.
- Year. TBC needs to collect data monthly and summarize the monthly data by quarter and year. Monthly data, stored in members such as Jan, Feb, and Mar, consolidates to quarters. Quarterly data, stored in members such as Qtr1 and Qtr2, consolidates to Year.
- Measures. Sales, Cost of Goods Sold, Marketing, Payroll, Miscellaneous, Opening Inventory, Additions, and Ending Inventory are standard measures. Essbase can calculate Margin, Total Expenses, Profit, Total Inventory, Profit %, Margin %, and Profit per Ounce from these measures. TBC needs to calculate Measures on a monthly, quarterly, and yearly basis.
- Product. The Product codes are 100‑10, 100‑20, 100‑30, 200‑10, 200‑20, 200‑30, 200‑40, 300‑10, 300‑20, 300‑30, 400‑10, 400‑20, and 400‑30. Each product consolidates to its respective family (100, 200, 300, and 400). Each consolidation allows TBC to analyze by size and package, because each product is associated with members of the Ounces and Pkg Type attribute dimensions.
- Market. Several states make up a region; four regions make up a market. The states are Connecticut, Florida, Massachusetts, New Hampshire, New York, California, Nevada, Oregon, Utah, Washington, Louisiana, New Mexico, Oklahoma, Texas, Colorado, Illinois, Iowa, Missouri, Ohio, and Wisconsin. Each state consolidates into its region—East, West, South, or Central. Each region consolidates into Market.
- Scenario. TBC derives and tracks budget versus actual data. Managers must monitor and track budgets and actuals, as well as the variance and variance percentage between them.
- Pkg Type. TBC wants to see the effect that product packaging has on sales and profit. Establishing the Pkg Type attribute dimension enables users to analyze product information based on whether a product is packaged in bottles or cans.
- Ounces. TBC sells products in different sizes in ounces in different markets. Establishing the Ounces attribute dimension helps users monitor which sizes sell better in which markets.
The following topics present a review of the basics of dimension and member properties and a discussion of how outline design affects performance.
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:
- Dimension types and attribute associations. See Dimension Types.
- Data storage properties. See Member Storage Properties.
- Consolidation operators. See Consolidation of Dimensions and Members.
- Formulas. See Formulas and Functions.
For a complete list of dimension and member properties, see Setting Dimension and Member Properties.
A dimension type is a property that Essbase provides that adds special functionality to a dimension. The most commonly used dimension types: time, accounts, and attribute. This topic uses the following dimensions of the TBC database to illustrate dimension types.
Database:Design Year (Type: time) Measures (Type: accounts) Product Market Scenario Pkg Type (Type: attribute) Ounces (Type: attribute)
Table 5 defines each Essbase dimension type.
| Dimension Types | Description |
|---|---|
| None | Specifies no particular dimension type. |
| Time | Defines the time periods for which you report and update data. You can tag only one dimension as time. The time dimension enables several accounts dimension functions, such as first and last time balances. |
| Accounts | Contains items that you want to measure, such as profit and inventory, and makes Essbase built-in accounting functionality available. Only one dimension can be defined as accounts. For discussion of two forms of account dimension calculation, see Accounts Dimension Calculations. |
| Attribute | Contains members that can be used to classify members of another, associated dimension. For example, the Pkg Type attribute dimension contains a member for each type of packaging, such as bottle or can, that applies to members of the Product dimension. |
| Country | Contains data about where business activities take place. In a country dimension, you can specify the currency used in each member. For example, Canada has three markets—Vancouver, Toronto, and Montreal, which use the same currency, Canadian dollars. |
| Currency partition | Separates local currency members from the base currency defined in the application. This dimension type is used only in the main database and is only for currency conversion applications. The base currency for analysis may be U.S. dollars, and the local currency members may contain values that are based on the currency type of their region. |
You can specify data storage properties for members; data storage properties define where and when consolidations are stored. For example, by default, members are tagged as store data. Essbase sums the values of store data members and stores the result at the parent level.
You can change the default logic for each member by changing the data storage property tag for the member. For example, you can change a store data member to a label only member. Members with the label only tag, for example, do not have data associated with them.
Table 6 describes the effect that Essbase data storage properties have on members.
Table 6. Essbase Data Storage Properties
- Can you identify a time dimension?
- Can you identify an accounts dimension?
- Does the data include foreign currencies? If so, did you identify a currency partition dimension?
- Can you identify qualities or characteristics of dimensions that should be defined as separate attribute dimensions?
- Which members require special data storage properties?
Position attribute dimensions at the end of the outline. Position dense dimensions before sparse dimensions.
The position of dimensions in an outline and the storage properties of dimensions can affect two areas of performance—how quickly calculations are run and how long it takes users to retrieve information.
The outline in Figure 31, Designing an Outline for Optimized Query Times is designed for optimum query performance:
- Because the outline contains attribute dimensions, the storage property for standard dimensions and all standard dimensions members is set as store data.
- As the most-queried sparse dimension, the Product dimension is the first of the sparse dimensions. Base dimensions are typically queried more than other dimensions.
To optimize calculation performance, order the sparse dimensions in the outline by their number of members, starting with the dimension that contains the fewest.
The outline in Figure 32, Designing an Outline for Optimized Calculation Times is designed for optimum calculation performance:
- The smallest standard dimension that is sparse, Market, is the first of the sparse dimensions in the outline.
- The largest standard dimension that is sparse, Product, is immediately above the first attribute dimension. If the outline did not contain attribute dimensions, the Product dimension would be at the end of the outline.
Although they contain the same dimensions, the example outlines in Figure 31, Designing an Outline for Optimized Query Times and Figure 32, Designing an Outline for Optimized Calculation Times are different. To determine the best outline sequence for a situation, prioritize the data retrieval requirements of the users against the time needed to run calculations on the database. How often do you expect to update and recalculate the database? What is the nature of user queries? What is the expected volume of user queries?
A possible workaround is initially to position the dimensions in the outline to optimize calculation. After you run the calculations, you can manually resequence the dimensions to optimize retrieval. When you save the outline after you reposition its dimensions, choose to restructure the database by index only. Before you run calculations again, resequence the dimensions in the outline to optimize calculation.
- Ensure that you have enough disk space.
See Determining Disk Space Requirements. - Ensure that you have enough memory.
See Estimating Memory Requirements. - Ensure that your caches are set correctly.
See Optimizing Essbase Caches.
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.
If you are satisfied with your database design after the preliminary test, test load the complete set of real data with which you will populate the final database. Use the test rules files if possible. This final test may reveal source data problems that were not anticipated during earlier design process phases.
Calculations are essential to derive certain types of data. Data that is derived from a calculation is called calculated data; basic noncalculated data is called input data.
The following topics use the Product and Measures dimensions of the TBC application to illustrate several types of common calculations that are found in many Essbase databases.
When you define members of standard dimensions, Essbase automatically tags the members with the + (plus sign representing addition) consolidator, meaning that during consolidation members are added. As appropriate, you can change a member consolidation property to one of the following operators: - (dash representing subtraction), * (asterisk representing multiplication), / (forward slash representing division), % (percent sign representing percentage), and ~ (tilda representing no consolidation).
Consolidation is the most frequently used calculation in Essbase. This topic uses the Product dimension to illustrate consolidations.
- Individual products roll up to product families, and product families consolidate into Product. The TBC outline also requires multiple consolidation paths; some products must consolidate in multiple categories.
- States roll up to regions, and regions consolidate into Market.
- Months roll up into quarters, and quarters consolidate into Year.
Consolidation operators define how Essbase rolls up data for each member in a branch to the parent. For example, using the default addition (+) operator, Essbase adds 100‑10, 100‑20, and 100‑30 and stores the result in their parent, 100, as shown in Figure 33, TBC Product Dimension.
The Product dimension contains mostly addition (+) operators, which indicate that each group of members is added and rolled up to the parent. Diet has a tilde (~) operator, which indicates that Essbase does not include the Diet member in the consolidation to the parent, Product. The Diet member consists entirely of members that are shared. The TBC product management group wants to be able to isolate Diet drinks in reports, so TBC created a separate Diet member that does not impact overall consolidation.
Essbase calculates the data of a branch in top-down order. For example, if you have, in order, two members tagged with an addition (+) operator and a third member tagged with a multiplication (*) operator, Essbase adds the first two and multiplies that sum by the third.
Because Essbase always begins with the top member when it consolidates, the order and the labels of the members is important. See Calculating Members with Different Operators.
Table 7 defines Essbase consolidation operators.
Table 7. Consolidation Operations
Shared members also affect consolidation paths. The shared member concept enables two members with the same name to share the same data. The shared member stores a pointer to data contained in the other member, so Essbase stores the data only once. Shared members must be in the same dimension. Data can be shared by multiple members.
The Measures dimension is the most complex dimension in the TBC outline because it uses both time and accounts data. It also contains formulas and special tags to help Essbase calculate the outline. This topic discusses the formulas and tags that TBC included in the Measures dimension (the dimension tagged as accounts).
Look closely at the Measures dimension tags defined by TBC (in Figure 34, TBC Measures Dimension). Many of the properties of the Measures dimension are discussed in previous topics of this chapter: addition (+), subtraction (–), and no consolidation (~) operators, and accounts and label only tags:
- The Inventory and Ratios member names assist the user in data navigation. They do not contain data and therefore receive a label only tag.
- The Measures dimension itself has a label only tag. Some members of Measures have a Dynamic Calc tag. Dynamic calculations are discussed in Dynamic Calculations.
- Some members of Measures have a time balance tag (TB First or TB Last). Time balance tags are discussed in Setting Time Balance Properties.
This topic discusses two forms of calculations for a dimension tagged as accounts, time balance properties and variance reporting.
Note the two tags in the Measures dimension of Table 9—TB first and TB last. These tags, called time balance tags or properties, provide instructions to Essbase about how to calculate the data in a dimension tagged as accounts. Using the tags requires a dimension tagged as accounts and a dimension tagged as time. The first, last, average, and expense tags are available exclusively for use with accounts dimension members.
In the TBC Measures dimension, Opening Inventory data represents the inventory that TBC carries at the beginning of each month. The quarterly value for Opening Inventory equals the Opening value for the quarter. Opening Inventory requires the time balance tag, TB first.
Ending Inventory data represents the inventory that TBC carries at the end of each month. The quarterly value for Ending Inventory equals the ending value for the quarter. Ending Inventory requires the time balance tag, TB last. Table 8 defines the time balance tags for the accounts dimension.
Table 8. Accounts Member Tags
In Table 9, Qtr1 (second column from the right) and Year (right-most column) show how consolidation in the time dimension is affected by time balance properties in the accounts dimension. Data is shown for the first quarter only.
Table 9. TBC Consolidations Affected by Time Balance Properties
| Dimensions | Jan | Feb | Mar | Qtr1 | Year |
|---|---|---|---|---|---|
| Accounts Member1 | 11 | 12 | 13 | 36 | Qtr1 + Qtr2 + Qtr3 + Qtr4 |
| Accounts Member2 (TB First) | 20 | 25 | 21 | 20 | 20 |
| Accounts Member3 (TB Last) | 25 | 21 | 30 | 30 | Value of Qtr4 |
Normally, the calculation of a parent in the time dimension is based on the consolidation and formulas of children of the parent. However, if a member in an accounts branch is marked as TB First, any parent in the time dimension matches the member marked as TB First.
For examples, see Setting Time Balance Properties.
One TBC Essbase requirement is the ability to perform variance reporting on actual versus budget data. The variance reporting calculation requires that any item that represents an expense to the company must have an expense reporting tag. Inventory members, Total Expense members, and the COGS member each receive an expense reporting tag for variance reporting.
Essbase provides two variance reporting properties—expense and nonexpense. The default is nonexpense. Variance reporting properties define how Essbase calculates the difference between actual and budget data in members with the @VAR or @VARPER function in their member formulas.
Formulas calculate relationships between members in the database outline. You can apply formulas to members in the outline, or you can place formulas in a calculation script. This topic explains how TBC optimized the performance of its database by using formulas.
Functions are predefined routines that perform specialized calculations and return sets of members or sets of data values. Formulas comprise operators and functions, as well as dimension names, member names, and numeric constants.
The Essbase functions include more than 100 predefined routines to extend the calculation capabilities of Essbase. Essbase supports the following functions:
- Boolean functions, which provide a conditional test by returning a TRUE or FALSE value
- Mathematical functions, which perform specialized mathematical calculations
- Relationship functions, which look up data values within a database during a calculation based on the position of the current member
- Range functions, which declare a range of members as an argument to another function or to a command
- Financial functions, which perform specialized financial calculations
- Member set functions, which are based on a specified member and which generate lists of members
- Allocation functions, which allocate values that are input at a parent level across child members
- Forecasting functions, which manipulate data for the purpose of smoothing data, interpolating data, or calculating future values
- Statistical functions, which calculate advanced statistics
- Date and time functions, which use date and time characteristics in calculation formulas
- Calculation mode functions, which specify the calculation mode that Essbase uses to calculate a formula
Essbase uses consolidation operators to calculate the Margin, Total Expenses, and Profit members. The Margin% formula uses a % operator, which means “express Margin as a percentage of Sales.” The Profit% formula uses the same % operator. The Profit per Ounce formula uses a division operator (/) and a function (@ATTRIBUTEVAL) to calculate profitability by ounce for products sized in ounces.
In the Profit per Ounce formula, the @NAME function is also used to process the string “Ounces” for the @ATTRIBUTEVAL function.
For a complete list of operators, functions, and syntax, see the Oracle Essbase Technical Reference. Also see Developing Formulas.
When you design the overall database calculation, you may want to define a member as a Dynamic Calc member. When you tag a member as Dynamic Calc, Essbase calculates the combinations of that member when you retrieve the data, instead of precalculating the member combinations during the regular database calculation. Dynamic calculations shorten regular database calculation time but may increase retrieval time for dynamically calculated data values.
In Figure 35, TBC Measures Dimension, Dynamic Calc Tags, the TBC Measures dimension contains several members tagged as Dynamic Calc—Profit, Margin, Total Expenses, Margin %, and Profit %.
When an overall database calculation is performed, the Dynamic Calc members and their corresponding formulas are not calculated. These members are calculated when a user requests them, for example, from Spreadsheet Add-in. Essbase does not store the calculated values; it recalculates the values for any subsequent retrieval. However, you can choose to store dynamically calculated values after the first retrieval.
To decide when to calculate data values dynamically, consider your priorities in the following areas:
In the TBC database, Margin % and Profit % contain the label two-pass. This default label indicates that some member formulas must be calculated twice to produce the desired value. The two-pass property works only on members of the dimension tagged as accounts and on members tagged as Dynamic Calc and Dynamic Calc and Store.
The following example illustrates why Profit % (based on the formula Profit % Sales) has a two-pass tag. The tables have five columns (column headers are labeled left to right as Dimension, Jan, Feb, Mar, and Qtr1) and three rows (labeled as Profit, Sales, and Profit %). Jan, Feb, Mar, and Qtr1 are members of the Year dimension. Profit, Sales, and Profit % are members of the Measures (accounts) dimension.
Table 10, Data Loaded into Essbase defines the initial data to load into Essbase. The data values for Profit -> Jan, Profit -> Feb, and Profit -> Mar are 100. The data value for Sales -> Jan, Sales -> Feb, and Sales -> Mar are 1000.
Table 10. Data Loaded into Essbase
| Dimension | Jan | Feb | Mar | Qtr1 |
|---|---|---|---|---|
| Profit | 100 | 100 | 100 | |
| Sales | 1000 | 1000 | 1000 | |
| Profit % |
First, Essbase calculates the Measures dimension. In Table 11, Data After Essbase Calculates the Measures Dimension, the data values for Profit % -> Jan, Profit % -> Feb, and Profit % -> Mar are 10%.
Table 11. Data After Essbase Calculates the Measures Dimension
| Dimension | Jan | Feb | Mar | Qtr1 |
|---|---|---|---|---|
| Profit | 100 | 100 | 100 | |
| Sales | 1000 | 1000 | 1000 | |
| Profit % | 10% | 10% | 10% |
Next, Essbase calculates the Year dimension. The data rolls up across the dimension. In Table 12, Data After Essbase Calculates the Year Dimension, the data values for Profit -> Qtr1 (300) and Sales -> Qtr1 (3000) are correct. The data value for Profit % -> Qtr1 (30%) is incorrect because Profit % is tagged as a two-pass calculation.
Table 12. Data After Essbase Calculates the Year Dimension
| Dimension | Jan | Feb | Mar | Qtr1 |
|---|---|---|---|---|
| Profit | 100 | 100 | 100 | 300 |
| Sales | 1000 | 1000 | 1000 | 3000 |
| Profit % | 10% | 10% | 10% | 30% |
Essbase then recalculates profit percentage at each occurrence of the member Profit %. In Table 13, Data After Essbase Recalculates Profit Percentage, the data value for Profit % -> Qtr1 (10%) is correct after the second pass.
- Does the default calculation logic achieve accurate results?
- Which members require formulas?
- Which members require time balance tags?
- Which members require variance reporting?
- Which members require two-pass calculation?
- Which members can be tagged as Dynamic Calc?
The Essbase triggers feature enables efficient monitoring of data changes in a database. See Understanding Triggers Definitions.
To ensure that the design meets user information requirements, you must view data as users view it. Users typically view data through spreadsheets, printed reports, or reports published on the Web. Oracle and its partners offer many tools for producing the reporting systems that users use.
Several tools can help you display and format data quickly, and test whether the database design meets user needs. You can use the Report Script Editor in Administration Services Console to write report scripts quickly. Those familiar with spreadsheets can use the Spreadsheet Add-in or Smart View (Smart View requires Provider Services).
- Grouping and sequencing of data. Do the intersections enabled by the design provide the data that users need?
- Levels of totals. What consolidation levels are required by, for example, a Spreadsheet Add-in user who drills down and up through the hierarchy of the outline design?
- Attribute reporting. Does the database design facilitate an analysis that is based on the characteristics or attributes of specific dimensions or members? For example, do you need to compare sales by specific combinations of size and packaging, such as comparing the sales of 16‑ounce bottled colas with the sales of 32‑ounce bottled colas?
Be sure to use the appropriate tool to create and test predesigned use reports against the test data. The reports that you design should provide information that meets your original objectives. The reports should be easy to use, providing the right combinations of data and the right amount of data. Because reports with too many columns and rows are difficult to use, you may need to create several reports instead of one all‑inclusive report.
After you analyze the data and create a preliminary design, check all aspects of the design with users. You should already have verified that the database satisfies the users’ analysis and reporting needs. Ensure that the database satisfies all of their goals.
Do the calculations provide the information they need? Are they able to generate reports quickly? Are they satisfied with consolidation times? In short, ask users if the database works for them.
Near the end of the design cycle, test with real data. Does the outline build correctly? Does all data load? If the database fails in any area, repeat the steps of the design cycle to identify the cause of the problem.
Essbase provides several sources of information to help you isolate problems. Sources include application and Essbase Server logs, exception logs, and database information accessible from Administration Services. Look at documentation topics relevant to your problem; for example, topics about security, calculations, reports, or general error messages. Use the index of this guide for help in solving problems. Look up such terms as troubleshooting, logs, optimizing, performance, recovery, resources, errors, and warnings.
No hay comentarios:
Publicar un comentario