Essbase® Analytic Services Database Administrator's Guide | | Update Contents | Previous | Next | Print | ? | |
Information Map | |
This appendix helps you estimate disk and memory requirements. This appendix contains the following sections:
This appendix uses a worksheet approach to help you keep track of the many components that you calculate. If you are using the printed version of this book, you can photocopy the worksheets. Otherwise, you can simulate the worksheets on your own paper. Labels, such as DA and MA help you keep track of the various calculated disk and memory component values.
Note: The calculations in this appendix apply only to block storage databases.
You need to understand the units of storage that Analytic Services uses in order to size a database. This discussion assumes that you are familiar with the following basic concepts before you continue:
An Analytic Services database consists of many different components. In addition to an outline file and a data file, Analytic Services uses several types of files and memory structures to manage data storage, calculation, and retrieval operations.
Table 1 describes the major components that you must consider when you estimate the disk and memory requirements of a database. "Yes" means the type of storage indicated is relevant, "No" means the type of storage is not relevant.
Analytic Services uses disk space for its server software and for each database. Before estimating disk storage requirements for a database, you must know how many dimensions the database includes, the sparsity and density of the dimensions, the number of members in each dimension, and how many of the members are stored members.
To calculate the disk space required for a database, perform these tasks:
Note: The database sizing calculations in this chapter assume an ideal scenario with an optimum database design and unlimited disk space. The amount of space required is difficult to determine precisely because most multidimensional applications are sparse.
Before estimating disk space requirements for a database, you must calculate the factors to be used in calculating the estimate. Later in the chapter you use these values to calculate the components of a database. For each database, you add together the sizes of its components.
Table 2 lists the sections that provide instructions to calculate these factors. Go to the section indicated, perform the calculation, then write the calculated value in the Value column.
Database Sizing Factor |
Label |
Value |
---|---|---|
The potential number of data blocks is the maximum number of data blocks possible in the database.
If the database is already loaded, you can see the potential number of blocks on the Statistics tab of the Database Properties dialog box of Administration Services.
If the database is not already loaded, you must calculate the value.
To determine the potential number of data blocks, assume that data values exist for all combinations of stored members.
a * b * c * d * e * f * g (and so on) = potential number of blocks
Enter Sparse Dimension Name |
Enter Number of Stored Members |
---|---|
The Sample Basic database contains the following sparse dimensions:
Therefore, there are 19 * 25 = 475 potential data blocks.
As compared with the potential number of blocks, the term existing blocks refers to those data blocks that Analytic Services actually creates. For Essbase to create a block, at least one value must exist for a combination of stored members from sparse dimensions. Because many combinations can be missing, the number of existing data blocks is usually much less than the potential number of data blocks.
To see the number of existing blocks for a database that is already loaded, look for the number of existing blocks on the Statistics tab of the Database Properties dialog box of Administration Services. Write the value in the cell labeled DB in Table 2.
If the database is not already loaded, you must estimate a value.
To estimate the number of existing data blocks, perform these tasks:
number of existing blocks = estimated density * potential number of blocks
The following three examples show different levels of sparsity and assume 100,000,000 potential data blocks:
.05 (estimated density) * 100,000,000 (potential blocks) = 5,000,000 existing blocks
.15 (estimated density) * 100,000,000 (potential blocks) = 15,000,000 existing blocks
.50 (estimated density) * 100,000,000 (potential blocks) = 50,000,000 existing blocks
The potential, expanded (uncompressed) size of each data block is based on the number of cells in a block and the number of bytes used for each cell. The number of cells in a block is based on the number of stored members in the dense dimensions. Analytic Services uses eight bytes to store each intersecting value in a block.
To see the number of existing blocks for a database that is already loaded, look for the size of an expanded data block on the Statistics tab of the Database Properties dialog box of Administration Services.
If the database is not already loaded, you must estimate the value.
To determine the size of an expanded data block, perform these tasks:
a * b * c * d * e * f * g (and so on) = the total number of cells
(Total number of cells) * 8 bytes per cell
= expanded block size
Enter Dense Dimension Name |
Number of Stored Members |
---|---|
The Sample Basic database contains the following dense dimensions:
Perform the following calculations to determine the potential size of a data block in Sample Basic:
12 * 8 * 2 = 192 data cells 192 data cells * 8 bytes = 1,536 bytes (potential data block size)
Compression affects the actual disk space used by a data file. The four types of compression, bitmap, run-length encoding (RLE), zlib, and index-value affect disk space differently. For a comprehensive discussion of data compression unrelated to estimating size requirements, see Data Compression.
If you are not using compression or if you have enabled RLE compression, skip this calculation and proceed to Stored Data Files.
Note: Due to sparsity also existing in the block, actual (compressed) block density varies widely from block to block. The calculations in this discussion are only for estimation purposes.
To calculate an average compressed block size when bitmap compression is enabled, perform the following tasks:
expanded block size * block density
= compressed block size
Assume an expanded block size of 1,536 bytes and a block density of 25% (.25):
1,536 bytes * .25 = 384 bytes (compressed block size)
To estimate the disk-space requirement for a database, make a copy of Table 5 or use a separate sheet of paper as a worksheet for a single database. If multiple databases are on a server, repeat this process for each database. Write the name of the database on the worksheet.
Each row of this worksheet refers to a section that describes how to size that component. Perform each calculation and write the results in the appropriate cell in the Size column. The calculations use the factors that you wrote in Table 2.
Database Name: |
|
Database Component |
Size |
Work Areas (sum of DE through DH) |
|
Linked Reporting Objects Considerations, if needed |
|
Total disk space required for the database. Total the size values from DE through DJ and write the result to Table 6. |
After writing all the sizes in the Size column, add them together to determine the disk space requirement for the database. Add the database name and size to the list in Table 6. Table 6 is a worksheet for determining the disk space requirement for all databases on the server.
Repeat this exercise for each database on the server. After estimating disk space for all databases on the server, proceed to Estimating the Total Analytic Server Disk Space Requirement.
The following sections describe the calculations to use to estimate components that affect the disk-space requirements of a database.
The size of the stored database depends on whether or not the database is compressed and the compression method chosen for the database. Analytic Services provides five compression-method options: bitmap, run-length encoding (RLE), zlib, index-value, and none.
Calculating the size of a compressed database is complex for a number of reasons including the following:
For a comprehensive discussion of data compression unrelated to estimating size requirements, see Data Compression. The calculations in this discussion are for estimation purposes only.
The calculation for the space required to store the compressed data files (ess
xxxxx
.pag
) uses the following factors:
To calculate database size when the compression option is none, use the following formula:
Number of blocks * (72 bytes + size of expanded data block)
Write the result in cell labeled DE in Table 5. Proceed to Index Files.
Because the compression method used can vary per block, the following calculation formulas are, at best, general estimates of the database size.
To estimate database size when the compression option is bitmap, use the following formula:
Number of blocks * (72 bytes + size of expanded data block/64)
Write the result in cell labeled DE in Table 5. Proceed to Index Files.
To estimate database size when the compression option is Index-value, use the following formula:
Number of blocks * (72 bytes + (1.5 * database density * expanded data block size)
Write the result in cell labeled DE in Table 5. Proceed to Index Files.
To estimate database size when the compression option is RLE, use the formula for calculating Bitmap Compression.
When the compression method is RLE, Analytic Services automatically uses the bitmap or Index-value method for a block if it determines better compression can be gained. Estimating using the bitmap calculation estimates the maximum size.
Write the result in cell labeled DE in Table 5. Proceed to Index Files.
To estimate database size when the compression option is zlib, use the formula for calculating Bitmap Compression:
It is very difficult to determine the size of a data block when zlib compression is used. Individual blocks could be larger or smaller than if compressed using other compression types. Calculating using the bitmap compression formula at least provides an approximation to use for this exercise.
Write the result in cell labeled DE in Table 5. Proceed to Index Files.
The calculation for the space required to store the index files (ess
xxxxx
.ind
) uses the following factors:
To calculate the total size of a database index, including all index files, perform the following calculation. Write the size of the compressed data files to the cell labeled DF in Table 5.
number of existing blocks * 112 bytes = the size of database index
Assume a database with 15,000,000 blocks.
15,000,000 blocks * 112 = 1,680,000,000 bytes
Note: If the database is already loaded, select the Storage tab on the Database Properties window.
If you are using bitmap or RLE compression, a certain amount of fragmentation occurs. The amount of fragmentation is based on individual database and operating system configurations and cannot be precisely predicted.
As a rough estimate, calculate 20% of the compressed database size (value DE from Table 5) and write the result to the cell labeled DG in the same table.
Calculating fragmentation allowance assuming a compressed database size of 5,769,000,000 bytes:
5,769,000,000 bytes * .2 = 1,153,800,000 bytes
The space required by an outline can have two components.
To estimate the size of the outline file, perform these tasks:
If the database includes few aliases or very short aliases and short member names, use a smaller number within this range. If you know that the member names or aliases are very long, use a larger number within this range.
Because the name-length factor is an estimated average, the following formula provides only a rough estimate of the main area of the outline.
number of members
* name-length factor
=size of main area of outline
Note: See Limits, for the maximum sizes for member names and aliases.
For memory space requirements calculated later in this chapter, use the size of the main area of the outline.
Note: Within the count of members, do not include Label Only members and shared members.
(number of base-dimension members
* sum of count of attribute-dimension members)/8
= size of attribute association area for a base dimension
main area of outline + total attribute association area
Assume the outline has the following characteristics:
Perform the following calculations:
name-length factor of 400 bytes * 26,000 members = 10,400,000 bytes
201,250 bytes + 3,750 bytes = 205,000 bytes
10,400,000 bytes
+ 205,000 bytes
= 10,605,000 bytes (outline disk space requirement)
Note: Do not use this procedure to calculate outline memory space requirements. Use the process described in Outline Size Used in Memory.
Three different processes create temporary work areas on the disk:
To create these temporary work areas, Analytic Services may require disk space equal to the size of the entire database. Restructuring and migration need additional work space the size of the outline. Because none of these activities occur at the same time, a single allocation can represent all three requirements.
To calculate the size of a work area used for restructuring, migration, and recovery, calculate the sum of the sizes of the following database components from Table 5:
Use the following formula to calculate the size of the work area:
work area = size of compressed data files + size of index files + fragmentation allowance + outline size
Write the result of this calculation to the cell labeled DI in Table 5.
You can use the Linked Reporting Objects (LROs) feature to associate objects with data cells. The objects can be flat files, HTML files, graphics files, and cell notes. For a comprehensive discussion of linked reporting objects, see Linking Objects to Analytic Services Data.
Two aspects of LROs affect disk space:
Note: You can set a limit on the size of a linked object, if the linked object is a file (as opposed to a cell note). For a discussion of why and how to limit LRO file sizes, see Limiting LRO File Sizes.
To estimate the disk space requirements for linked reporting objects, perform the following tasks:
Assume the database uses 1500 LROs which are composed of the following:
Perform the following calculations:
512,000 bytes + 12,288,000 bytes = 12,800,000 bytes total LRO disk space requirement
The earlier calculations in this chapter estimate the data storage requirement for a single database. Often, more than one database resides on the server.
In addition to the data storage required for each database, the total Analytic Services data storage requirement on a server includes Analytic Services software. Allow approximately 200 MB (209,715,200 bytes) for the base installation of Analytic Services software and sample applications. The allowance varies by platform and file management system. For details, see the Essbase Analytic Services Installation Guide.
To estimate the total server disk space requirement, perform the following tasks:
List of Databases (From Table 5) |
Size |
---|---|
Total Analytic Server disk requirement in megabytes (MB): DN divided by 1,048,576 bytes |
The minimum memory requirement for running Analytic Services is 64 MB. On UNIX systems, the minimum requirement is 128 MB. Based on the number of applications and databases and the database operations on the server, the amount of memory you require may be more.
Analytic Services provides a memory management feature that enables you to specify the maximum memory to be used for all server activities, or a maximum memory that can be used to manage specific applications. For additional information about this feature, see the Memory Manager Configuration section of the Technical Reference.
If you use the memory management feature to limit the amount of memory available to the server, you do not need to calculate a memory requirement. The total memory required on the computer is equal to the sum of the operating system memory requirement plus the Analytic Server limit you specify in the MEMORYLIMIT configuration setting in the config.mem
memory configuration file.
To estimate the memory required on Analytic Server, use the Worksheet for Total Memory Requirements, Table 11, to collect and total server memory requirements. To calculate the requirements for this worksheet, review the following topics:
If you use the memory management feature to control the amount of memory used by Analytic Server for all applications, do not calculate application and database memory requirements. See Estimating Memory Requirements.
The approach to determining the amount of memory required for an application varies, depending on whether or not you set memory limits on individual applications. As appropriate to your individual applications, follow the instructions in the following topics:
If you use the memory management feature to limit the amount of memory available for individual applications, you do not need to calculate the memory requirements for those applications. For information about setting memory maximums for individual applications, see the Memory Manager Configuration section of the Technical Reference.
To determine the maximum amount of memory that can be used by applications for which memory limits are established in application memory configuration files, list the applications in Table 7 and write the associated memory limit in the Maximum Size column.
Application Name: |
Maximum Size, in Megabytes (MB) |
---|---|
Total the memory values and write the result to the cell labeled ML in Worksheet for Total Server Memory Requirement.
For application memory use that is not controlled by Memory Manager, you need to calculate overall memory used at application startup plus the memory requirements for each database.
Each open application has the following memory requirements at startup:
Multiply the number of applications that will be running simultaneously on the server by the appropriate startup requirement and write the resulting value to the cell labeled MM in Table 11. Do not include in this calculation applications for which the amount of memory used is controlled by Memory Manager.
Calculate memory requirements for each database on Analytic Server.
Note: Do not include in this calculation databases within applications for which you use the memory management feature to limit the amount of memory available to them.
For each database, make a copy of Table 11 or use a separate sheet of paper as a worksheet for a single database. If multiple databases are on Analytic Server, repeat this process for each database. Write the name of the database on the worksheet.
Each row links to information that describes how to size that component. Perform each calculation and note the results in the appropriate cell in the Size column. Some calculations use the factors that you wrote in Table 9. After filling in all the sizes in the Size column, add them together to determine the memory requirement for that database.
After estimating disk space for all databases on the server, proceed to Estimating the Total Analytic Server Disk Space Requirement.
Database Name: |
Size in Megabytes (MB) |
Memory Requirement: |
|
|
|
|
|
|
|
|
|
Operational Requirements: |
|
|
|
|
|
Summarize the size values from MA through MF for an estimate of the total memory required for a database. |
|
Divide the value from MG by 1,048,576 bytes for the total database memory requirement in megabytes (MB). |
In Table 11, enter the name of the database and the total memory requirement in megabytes, MH.
Before you start the estimate, calculate factors to be used in calculating the estimate.
Table 9 lists sizing factors with references to sections in this chapter and other chapters that provide information to determine these sizes. Go to the section indicated, perform the calculation, then return to Table 9 and write the size, in bytes, in the Value column of this table.
Later in this chapter, you can refer to Table 9 for values to use in various calculations.
Database Sizing Factor |
Value |
---|---|
The number of cells in a logical block. (See The Number of Cells in a Logical Block.) |
|
The number of threads allocated through the SERVERTHREADS ESSCMD. (See the Technical Reference.) |
|
Potential stored-block size. (See Size of Expanded Data Block.) |
The calculations in this chapter do not account for other factors that affect how much memory is used. The following factors have complex implications and are not included within the discussion:
essbase.cfg
file in combination with the SET LOCKBLOCK setting, which specifies which CALCLOCKBLOCK setting to use.The attribute association area included in disk space calculations is not a sizing factor for memory. Calculate only the main area of the outline.
For memory size requirements, outline size is calculated using the following factors:
To calculate the outline memory requirement, multiply the number of members by a name-length factor between 300 and 400 bytes and write the result to the cell labeled MA in Table 8.
If the database includes few aliases or very short aliases and short member names, use a smaller number within the 300-400 byte range. If you know that the names or aliases are very long, use a larger number within this range.
Because the name-length factor is an estimated average, the following formula provides only a rough estimate of the main area of the outline:
memory size of outline = number of members * name-length factor
Note: See Limits, for the maximum sizes for member names and aliases.
Assuming the outline has 26,000 members and a median name-length, use the following calculation to estimate the outline size used in memory:
26,000 members * 350 bytes per member = 9,100,000 bytes
At startup, Essbase sets aside memory for the index cache, the size of which can be user-specified. To determine the size of the index cache, see Sizing Caches and write the size in the cell labeled MB in Table 8.
Analytic Services uses additional memory while it works with the caches.
The calculation for this cache-related overhead uses the following factors:
To calculate the cache-related overhead at startup, perform the following tasks:
index cache size * .5 = index cache-related overhead
((# of server threads allocated to the Analytic Server process * 3) * 256) + 5242880 bytes = additional cache overhead
cache-related overhead = index cache-related overhead + additional cache overhead
The term logical block refers to an expanded block in memory.
To determine the cell count of a logical block, multiply together all members of each dense dimension (including Dynamic Calc and Dynamic Calc and Store members but excluding Label Only and shared members).
a * b * c * d * e * f * g = the total number of cells
Enter Dense Dimension Name |
Number of Members |
---|---|
Excluding Label Only and shared members, the dense dimensions in Sample Basic contain 17 (Year), 14 (Measures), and 4 (Scenario) members. The calculation for the cell count of a logical block in Sample Basic is as follows:
17 * 14 * 4 = 952 cells
At application startup time, Analytic Services sets aside an area of memory based on the following factors:
To calculate the data structure area in memory, perform the following tasks:
Number of threads * ((Number of members in the outline * 26 bytes) + (Logical block cell count * 36 bytes))
Assuming 20 threads for the Sample Basic database, the startup area in memory required for data structures is calculated as follows:
20 threads * ((79 members * 26 bytes) + (952 cells * 36 bytes)) = 726,520 bytes 726,520 bytes / 1,048,576 bytes = .7 MB
In addition to startup memory requirements, operations such as queries and calculations require additional memory. Because of many variables, the only way to estimate memory requirements of operations is to run sample operations and monitor the amount of memory used during these operations. This topic provides guidelines for the following estimation tasks:
Analytic Services processes requests for database information (queries) from a variety of sources. For example, Analytic Services processes queries from the Spreadsheet Add-in and from Report Writer. Analytic Services uses additional memory when it retrieves the data for these queries, especially when Analytic Services must perform dynamic calculations to retrieve the data. This section describes Analytic Services memory requirements for query processing.
Analytic Services is a multithreaded application in which queries get assigned to threads. Threads are automatically created when Analytic Services is started. In general, a thread exists until you shut down Analytic Server. For an explanation of how Analytic Services uses threads, see Multithreading.
As Analytic Services processes queries, it cycles through the available threads. For example, assume 20 threads are available at startup. As each query is processed, Analytic Services assigns each succeeding query to the next sequential thread. After it has assigned the 20th thread, Analytic Services cycles back to the beginning, assigning the 21st query to the first thread.
While processing a query, a thread allocates some memory, and then releases most of it when the query is completed. Some of the memory is released to the operating system and some of it is released to the dynamic calculator cache for the database being used. However, the thread holds on to a portion of the memory for possible use in processing subsequent queries. As a result, after a thread has processed its first query, the memory held by the thread is greater than it was when Analytic Services first started.
Analytic Services uses the maximum amount of memory for query processing when both of these conditions are true:
In the example where 20 threads are available at startup, the maximum amount of memory is used for queries when at least 20 queries have been processed and the maximum number of simultaneous queries are in process.
To estimate query memory requirements by observing actual queries, perform the following tasks:
Use the following variables when you calculate the formula in Estimating the Maximum Memory Usage for A Query Before and After Processing:
The potential number of threads available is based on the number of licensed ports that are purchased. The actual number of threads available depends on settings you define for the Agent or the server. Use the number of threads on the system as the value for Total#Threads in later calculations.
Determine the maximum number of concurrent queries and use this value for Max#ConcQueries in later calculations. This value cannot exceed the value for Total#Threads.
The memory usage of individual queries depends on the size of each query and the number of data blocks that Analytic Services needs to access to process each query. To estimate the memory usage, calculate the additional memory Analytic Services uses during processing and after processing each query.
Decide on several queries that you expect to use the most memory. Consider queries that must process large numbers of members; for example, queries that perform range or rank processing.
To estimate the memory usage of a query, perform the following tasks:
essbase.cfg
setting DYNCALCACHEMAXSIZE to 0 (zero). Turning off the dynamic calculator cache enables measurement of memory still held by a thread by ensuring that, after the query is complete, the memory used for blocks during dynamic calculations is released by the ESSSVR process to the operating system.The amount of additional memory required for data retrievals will not exceed the following:
Max#ConcQueries
*
MAXAdditionalMemDuringP
+
(
Total#Threads
-
Max#ConcQueries
)
*
MAXAdditionalMemAfterP
Write the result of this calculation, in bytes, to the cell labeled ME in Table 8.
Because this calculation method assumes that all of the concurrent queries are maximum-sized queries, the result may exceed your actual requirement. It is difficult to estimate the actual types of queries that will be run concurrently.
To adjust the memory used during queries, you can set values for the retrieval buffer and the retrieval sort buffer. For a review of methods, see Setting the Retrieval Buffer Size and Setting the Retrieval Sort Buffer Size.
If you cannot perform this test with actual queries, you can calculate a very rough estimate for operational query requirements. Requirements for each retrieval vary considerably. As a generalization, this estimate uses the following fixed factors:
This estimate also uses the following variables:
You can then use the following two calculations for the memory needed for retrievals:
retrieval buffer (10,240 bytes) + retrieval sort buffer (20,480 bytes) + formatting buffer (144,000 bytes) + dynamic calc area + data cache size + data file cache size
Member storage area for the largest dimension + (number of retrievals * sum of buffer and work areas used in each retrieval)
Summarize the calculations and write the result, in bytes, to the cell labeled ME in Table 8.
To estimate the maximum memory needed for concurrent queries, assume the following values for this example:
With SET LOCKBLOCK set as 100 blocks, the calculation is:
100 blocks * 7616-byte block size = 761,600 bytes
10000 members * 40 bytes/member = 400,000 bytes
Estimated memory for retrievals is as follows:
184,000 bytes + (20 concurrent inquiries * (10,240 bytes + 20,480 bytes + 144,000 bytes + 761,600 bytes + 3,145,728 bytes + 400,000 bytes)) = 75,824,960 bytes
For existing calculation scripts, you can use the memory monitoring tools provided for the operating system on the server to observe memory usage. Run the most complex calculation and take note of the memory usage both before and while running the calculation. Calculate the difference and use that figure as the additional memory requirement for the calculation script.
For a comprehensive discussion of calculation performance, see Optimizing Calculations.
If you cannot perform a test with a calculation script, you can calculate a very rough estimate for the operational requirement of a calculation by adding the following values:
For the total calculation requirement, summarize the amount of memory needed for all calculations that will be run simultaneously and write that total to the cell labeled MF in Table 8.
Note: The size and complexity of the calculation scripts affect the amount of memory required. The effects are difficult to estimate.
You can use Table 11 as a worksheet on which to calculate an estimate of the total memory required on the server.
Component |
Memory Required, in Megabytes (MB) |
---|---|
Sum of memory maximums established for individual applications (See Application Memory Limited by Memory Manager.) |
|
Sum of application startup memory requirements (See Startup Memory Requirement for Applications.) |
|
In rows a through g below, list concurrent databases (from copies of Table 8) and enter their respective memory requirements (MH) in the column to the right. |
|
To estimate the total Analytic Services memory requirement on a server, perform the following tasks:
config.mem
memory configuration file. No further calculation is necessary.Note: In addition, be sure to consider memory requirements for client software, such as Essbase Administration Services, that may be installed on the Analytic Server computer. See the appropriate documentation for details.
If cache memory locking is enabled, the total memory requirement should not exceed two-thirds of available RAM; otherwise, system performance can be severely degraded. If cache memory locking is disabled, the total memory requirement should not exceed available RAM.
If there is insufficient memory available, you can redefine the cache settings and recalculate the memory requirements. This process can be iterative. For guidelines and considerations, see Fine Tuning Cache Settings. In some cases, you may need to purchase additional RAM.
![]() |