Essbase® Analytic Services Database Administrator's Guide | | Update Contents | Previous | Next | Print | ? | |
Information Map | |
This chapter provides information about creating aggregate storage applications, databases, and outlines and discusses the differences between aggregate storage databases and block storage databases in regard to the creation of applications, databases, and outlines. To use the information in these topics, you should be familiar with application, database, and outline concepts for block storage databases. For information about these concepts, see Creating Applications and Databases, Creating and Changing Database Outlines, Setting Dimension and Member Properties, and Working with Attributes.
Aggregate storage applications and databases and block storage applications and databases differ in both concept and design. Some block storage outline features do not apply to aggregate storage. For example, the concept of dense and sparse dimensions does not apply. Also, in aggregate storage outlines, formulas are allowed only within the dimension tagged as accounts and must be written in MDX syntax. For a full list of differences, see Comparison of Aggregate and Block Storage.
A new sample application (ASOsamp), a data file, and a rules file are provided to demonstrate aggregate storage functionality.
This chapter includes the following topics:
This topic provides a high-level workflow for creating an aggregate storage application.
You must create an aggregate storage application to contain an aggregate storage database. An aggregate storage application can contain only one database. You can create an aggregate storage application, database, and outline in the following ways:
Note: An aggregate storage outline cannot be converted to a block storage outline.
For information on loading dimensions and members into an aggregate storage outline, see Building Dimensions in Aggregate Storage Databases and Loading Data into Aggregate Storage Databases.
Aggregate storage application and database information differs from block storage information, and specific naming restrictions apply to aggregate storage applications and databases. For information on the differences, see Inherent Differences Between Aggregate Storage and Block Storage.
To convert a block storage outline to an aggregate storage outline, use any of the following methods:
Tool |
Topic |
Location |
---|---|---|
Note: Do not use the file system to copy a block storage outline into an aggregate storage application. Use the Aggregate Storage Outline Conversion Wizard in Essbase Administration Services to convert the outline.
To create an aggregate storage application, use any of the following methods:
Tool |
Topic |
Location |
---|---|---|
To create an aggregate storage database use any of the following methods:
Tool |
Topic |
Location |
---|---|---|
When creating aggregate storage applications, databases, and outlines, you need to consider differences between aggregate storage and block storage and issues specific to aggregate storage. For information about such differences and issues, see the following topics:
Aggregate storage database outlines are pageable. This feature may significantly reduce memory usage for very large database outlines. For aggregate storage databases, Essbase Analytic Services preloads part of the database outline into memory. Then, during data retrieval, Analytic Services pages other parts of the outline into memory as required.
When you create an aggregate storage database, the outline is created in a pageable format. When you use the Aggregate Storage Outline Conversion Wizard to convert an existing block storage outline to aggregate storage, the outline is automatically converted to a pageable format.
Paging an outline into memory enables Analytic Services to handle very large outlines (for example, 10 million or more members), but potentially increases data retrieval time. You can customize outline paging to obtain the optimum balance between memory usage and data retrieval time. For configuration information, see Optimizing Outline Paging.
Note: Aggregate storage databases that have pageable outlines contain memory pages, and therefore their outline files may be larger than binary, block storage database outline files.
The maximum size of a buildable outline (the number of members) depends on a number of factors:
Table 1 shows the amount of addressable memory available for Analytic Services for different operating systems.
Analytic Services uses about 40 MB of memory on startup. In addition, the various caches require the following memory allocations:
Therefore, the initial memory footprint for Analytic Services is about 90 MB. In addition, memory has to be allocated to process incoming query requests. A typical amount of memory to reserve for this purpose is about 300 MB. The total memory allocated for Analytic Services is therefore 390 MB.
On a Windows system with 1.85 GB of addressable memory, the amount available to build and load the outline is about 1.46 GB (1.85 GB - 390 MB = 1.46 GB).
The maximum size of an outline depends on whether the outline is built using a dimension build or is built from an outline already loaded into Analytic Services.
To build the outline by using a dimension build, Analytic Services allocates about 100 bytes per member, plus the size of the member name, plus the size of all alias names for the member (up to 10 aliases are allowed).
For a sample outline (using a single byte codepage) where the average member name is 15 characters and there is one alias (of 20 characters) per member, the memory requirement for each member that is added is:
100 + 15 + 20 bytes = 135 bytes
The total number of members that can be added in a dimension build is the available memory (1.46 GB, or 153092060 bytes) divided by the number of bytes per member (135), which equals approximately 11 million members.
On systems with more than 2 GB of addressable memory, the outline can be larger in proportion to the extra memory that is available.
When the dimension build is complete, a databaseName
.otn
file is saved in the database directory. The .otn
file is used as input for the outline restructuring process, which replaces the old outline with the new one. During restructuring, two copies of the outline are loaded into memory, the old one (potentially empty), and the new one, so the maximum size of an outline that can be restructured depends on the size of the old outline.
In a dimension build, which starts with an empty outline, only one outline is loaded into memory.
The memory requirement for an outline loaded into Analytic Services at runtime or during restructuring is different from the memory requirements for a dimension build. Analytic Services allocates about 60 bytes per member, plus the size of the member name plus 5 bytes, plus the size of all alias names for the member (up to 10 aliases are allowed) plus 5 bytes. For a sample outline where the average member name is 15 characters and there is one alias (of 20 characters) per member, the memory requirement for each member that is added is:
60 + 15 + 5 + 20 + 5 bytes = 105 bytes per member
Assuming 1.46 GB of available memory, the maximum size of an outline that can be loaded is one with 14 million members (1.46 GB / 105 bytes).
The 14 million members are the sum of two outlines that are loaded during restructuring. For example, if an existing outline has 5 million members, the new outline can have a maximum of 9 million members. In an incremental dimension build, it is recommended to build the smaller dimensions first and the larger ones last to allow for a maximum outline size.
Depending on how you want to balance memory usage and data retrieval time, you can customize outline paging for aggregate storage outlines by using one or more of the following settings in the essbase.cfg
file:
For specific information on these configuration file settings for outline paging, see the Technical Reference.
When Analytic Services loads an outline, it attempts to load into memory the namespaces for both member names and all alias tables to allow optimal performance during name lookup. Name lookup is used during data load, and during report, spreadsheet, and MDX queries.
If the available memory does not allow all namespaces to be preloaded, the alias name space is left on disk and paged in on demand. If there is still not enough memory, the member namespace is also left on disk and paged in on demand.
If memory calculations indicate that it is possible to build and restructure a particular outline if one or both namespaces are not preloaded, you can set one or both of the PRELOADMEMBERNAMESPACE and PRELOADALIASNAMESPACE configuration settings to FALSE to turn off preloading the namespaces.
Not preloading the namespaces will have a severe performance impact but could be a temporary measure to be build a very large outline. After the outline is built and restructured Analytic Services can be restarted with the namespace settings set back to their default TRUE (on) setting.
For aggregate storage outlines, member data resides in pages in the outline (.otl
) file. These pages are brought into memory whenever some member information is needed. To maximize performance for outline paging, pages are loaded into the outline paging cache. The outline paging cache uses a 'least recently used' algorithm (LRU), which means pages loaded into the cache to satisfy a member data request remain in the cache until they have to make room for new pages to be brought in.
Page content is organized to provide maximum locality of reference, that is, a good number of a member's siblings are most likely on the same page. Therefore, subsequent requests for the same data or even similar data are executed much more quickly than they would be otherwise.
The default cache size is 8MB and the page size for all supported operating systems is 8192 bytes. Therefore, 1024 pages can be loaded into the cache at any one time.
For very large outlines, performance may be improved by increasing the cache size. To find out if it makes sense to increase the cache you must determine the cache hit rate (the percent of requested pages that are found in the cache).
To determine the cache hit rate, use any of the following methods:
Tool |
Topic |
Location |
---|---|---|
For example, use the following MaxL statement to see information on the effectiveness of the current setting for outline paging cache size (including the number of pages read into the cache, the number of pages found in the cache, and the hit rate):
query application APP-NAME get opg_cache statistics;.
If the hit rate is low, increasing the cache size might make sense. However, increasing the cache size requires additional memory, which impacts the number of members that can be loaded.
For aggregate storage outlines, the outline file (.otl
) is pageable and contains outline data organized in pages of 8192 bytes. Pageable outline files are larger than traditional outline files. Although the data is packed in a way to provide maximum page fill, empty space in the pages results in larger files.
The outline paging file is organized into the following sections, each of which contains a specific type of outline data:
Each dimension in the outline contains all of these sections except for the namespaces. For each alias table there is one member namespace and one alias namespace. The sections are organized as balanced binary trees (BTREE).
You cannot view the content of outline paging files directly. Rather, you must use a MaxL statement or ESSCMD command.
To view information about the outline paging file, use any of the following methods:
Tool |
Topic |
Location |
---|---|---|
For example, to display information about the outline paging file, including BTREE information, number of keys, number of pages, and number of levels in the tree, use the following MaxL statement:
query database DBS-NAME get opg_state of TABLE-TYPE for dimension DIM-NAME
Note: The information about the outline paging file is mostly valuable to the Analytic Services technical support team. However, you might find the number of keys to be useful. In any given section, the number of keys is equivalent to the number of records in that section. For example, the number of keys in the Member Formula section is the number of formulas for that dimension. Likewise, the number of keys in the Member Data section is the number of members in that dimension, so you can use query database to see the number of members for each dimension in the outline.
For aggregate storage databases, only the accounts dimension (the dimension tagged as accounts) can contain member formulas. All calculations performed on the accounts dimension happen at data retrieval time (when the database is queried). In most cases, the need for formulas on the dimension dictates which dimension you choose to tag as the accounts dimension. However, if there is more than one option for which dimension you tag as accounts, use the following criteria in the specified order:
If you are converting a block storage outline to an aggregate storage outline and the dimension currently tagged as accounts contains only the addition (+) operator, consider choosing a different dimension as the accounts dimension or having no accounts dimension. Analytic Services uses the accounts dimension to enable data compression, which is most effective when the accounts dimension is dense. Therefore, an outline with no accounts dimension may be a good choice for outlines that have the following characteristics:
This topic provides information on the differences between aggregate storage databases and block storage databases in regard to attribute dimensions. To use the information in this topic, you should be familiar with attribute dimension concepts for block storage databases. For information about these concepts, see Working with Attributes.
The following information applies to attribute dimensions when used on aggregate storage databases:
Note: Calculation order may affect calculation results. Aggregate storage calculation order and block storage calculation order may differ. For information on aggregate storage calculation, see Loading, Calculating, and Retrieving Aggregate Storage Data.
Aggregate storage outline files have the same file extension (.otl
) as block storage database outline files and are stored in an equivalent directory structure. When you save an outline, Analytic Services verifies it for errors. You can also verify the accuracy of an outline before you save it. Some block storage database features do not apply to aggregate storage databases, and the verification process considers the rules for aggregate storage databases. For a full list of outline feature differences, see Comparison of Aggregate and Block Storage.
To verify an aggregate storage outline, use the following method:
Tool |
Topic |
Location |
---|---|---|
Formulas calculate relationships between members in a database outline. If you are familiar with using formulas on block storage outlines, keep in mind the following differences when using formulas on aggregate storage outlines:
Analytic Services provides a native calculation language (referred to as the Calc language, or Calc) to write formulas on block storage outlines. To write formulas for aggregate storage outlines, the MDX (Multidimensional Expressions) language is required.
The current chapter concentrates on using MDX to write formulas on aggregate storage databases. For information about using MDX to write queries, see Writing MDX Queries. For information about writing formulas for block storage outlines, see Developing Formulas.
For a reference to MDX functions and syntax, see the MDX section of the Technical Reference.
An MDX formula must always be an MDX numeric value expression. In MDX, a numeric value expression is any combination of functions, operators, and member names that does one of the following:
A numeric value expression is different from a set expression. A set expression is used on query axes and describes members and member combinations. A numeric value expression is used to specify a value.
A numeric value expression is used in queries to build calculated members. Calculated members are logical members created for analytical purposes in the WITH section of the query, but which do not exist in the outline.
The following query defines a calculated member and uses a numeric value expression to provide a value for it:
WITH MEMBER [Measures].[Prod Count] AS 'Count ( Crossjoin ( {[Units]}, {[Products].children} ) )' SELECT {[Geography].children} ON COLUMNS, { Crossjoin ( {[Units]}, {[Products].children} ), ([Measures].[Prod Count], [Products]) } ON ROWS FROM ASOSamp.Sample
In the sample query, the WITH clause defines a calculated member, Product Count, in the Measures dimension, as follows:
WITH MEMBER [Measures].[Prod Count]
The numeric value expression follows the WITH clause and is enclosed in single quotation marks. In the sample query, the numeric value expression is specified as follows:
'Count ( Crossjoin ( {[Units]}, {[Products].children} ) )'
Note: For an explanation of the syntax rules used to build the numeric value expression in the example, see the documentation in the Technical Reference for the Count, CrossJoin, and Children functions.
A numeric value expression can also be used as an MDX formula to calculate the value of an existing outline member.
Therefore, rather than creating the example query, you can create an outline member on the Measures dimension called Prod Count that is calculated in the outline in the same way that the hypothetical Prod Count was calculated in the sample query.
To create a calculated member with a formula:
Assuming that you created the example Prod Count member, you would use the following formula, which is the equivalent of the numeric value expression used to create the calculated member in the example query:
Count(Crossjoin ( {[Units]}, {[Products].children}))
When you retrieve data from the aggregate storage database, the formula is used to calculate the member value.
Before applying formulas to members in the outline, you can write MDX queries that contain calculated members. When you can write an MDX query that returns the calculated member results that you want, you are ready to apply the logic of the numeric value expression to an outline member and validate and test the expression. For information about writing MDX queries, see Writing MDX Queries. For syntax information about MDX, see the MDX section of the Technical Reference.
Formula calculation is much simpler for aggregate storage databases than for block storage databases. Analytic Services calculates formulas in aggregate storage outlines only when data is retrieved.
Calculation order may affect calculation results. For information on calculation order for aggregate storage databases, see Calculation Order.
When you create member formulas for aggregate storage outlines, make sure that you observe the following rules:
[]
) if the member name meets any of the following conditions: [100]
. Braces are recommended for all member names, for clarity and code readability.-
), an asterisk (*
), or a slash (/
). (Actual,
Sales)
.See Rules for Specifying Sets for more information about sets.
Enclose sets in curly braces, for example:
{ [Year].[Qtr1], [Year].[Qtr2], [Year].[Qtr3], [Year].[Qtr4] }
You use Formula Editor to create formulas. Formula Editor is a tab in the Member Properties dialog box in Outline Editor. Formulas are plain text. You can type the formulas directly into the formula text area, or you can create a formula in the text editor of your choice and paste it into Formula Editor.
You can also include formulas in a dimension build data source. For information, see Setting Field Type Information.
To create a formula, use the following method:
Tool |
Topic |
Location |
---|---|---|
Analytic Services includes MDX-based syntax checking that tells you about syntax errors in formulas. For example, Analytic Services tells you if you have mistyped a function name or specified a non existent member. Unknown names can be validated against a list of function names. If you are not connected to Analytic Server or to the application associated with the outline, Analytic Services may connect you to validate unknown names.
Syntax checking occurs when you save a formula. Errors are displayed in the Messages panel. If an error occurs, you are given a choice to save or not save the formula. If you save a formula with errors, you are warned when you verify or save the outline. When you calculate a formula with errors, the formula is ignored and the member is given a value of $MISSING.
A syntax checker cannot tell you about semantic errors in a formula. Semantic errors occur when a formula does not work as you expect. One way to find semantic errors in a formula is to place the numeric value expression that defines the formula into a query and run the query to verify that the results are as you expect. See Using MDX Formulas to see how to place a formula into a query.
You can use MDX Script Editor to create a query. MDX Script editor provides features such as color coding and auto-completion of MDX syntax. See "About MDX Script Editor" in Essbase Administration Services Online Help.
To display a formula, use any of the following methods:
Tool |
Topic |
Location |
---|---|---|
The following topics discuss and give examples of how to write a variety of formulas for members in aggregate storage outlines:
You can apply a mathematical operation to a formula to create a basic equation. For example, the following formula is applied to the Avg Units/Transaction member in the ASOsamp Sample database:
[Units]/[Transactions]
The formula in Avg Units/Transaction divides the number of units by the number of transactions to arrive at the average number of units per transaction.
In aggregate storage outlines, members cannot be tagged as expense items. Therefore, functions in Calc, such as @VAR and @VARPER, which determine the difference between two members by considering expense tags, are not relevant in aggregate storage outlines.
The MDX subtraction operator can be used to calculate the difference between two members. For example, the following formula can be applied to a new member, called Price Diff, in ASOsamp Sample to calculate the difference between the price paid and the original price:
[Price Paid]-[Original Price]
ASOsamp Sample provides a formula on a member called % of Total. This member formula identifies the percentage of the Measures total that is produced by Transactions. The formula for % of Total is as follows:
Transactions/(Transactions,Years,Months, [Transaction Type],[Payment Type],Promotions,Age, [Income Level],Products,Stores,Geography)
The formula specifies a member (Transactions) divided by a tuple (Transactions, Years, ...). The formula lists a top member from every dimension to account for all Transaction data in the cube; that is, not Transaction data for the Curr Year member but Transaction data for all members of the Years dimension, not Transaction data for months in the first two quarters but Transaction for all months, and so on. In this way, the value of % of Total represents the percentage of the Measures total that are produced by Transactions.
You can define a formula that uses a conditional test or a series of conditional tests to determine the value for a member. Use the IIF function to perform a test with a single else condition. You can nest IIF functions to create a more complex query.
The example specifies a formula for a member that represents the price the company must pay for credit card transactions, which includes a 5% charge. The following example assumes that the Credit Price member has been added to the Measures dimension of the ASOsamp Sample database. Credit Price has the following formula, which adds 5% to Price Paid when the payment type is a credit card.
IIF ( [Payment Type].CurrentMember=[Credit Card], [Price Paid] * 1.05, [Price Paid] )
Use the CASE, WHEN, THEN construct to create formulas with multiple tests and else conditions.
The Filter function returns the tuples of the input set that meet the criteria of the specified search condition. For example, to establish a baseline (100) for all products you can add a Baseline member and create a formula for it, as follows:
Count(Filter(Descendants([Personal Electronics],[Products].Levels(0)),[Qtr1] > 100.00))
User-defined attributes (UDAs) are words or phrases that you create for a member. For example, in Sample Basic, top level members of the Market dimension have the UDA Small Market or the UDA Major Market.
The Major Market example used in this topic shows how to create a formula for a member that shows the sum of sales for all major market members. The example assumes that a new member (Major Market Total) has been added to Sample Basic.
IsUda([Market].CurrentMember, "Major Market")
Filter([Market].Members, IsUda([Market].CurrentMember, "Major Market"))
Sum (Filter([Market].Members, IsUda([Market].CurrentMember, "Major Market")))This is the formula that is attached to the Major Market Total member.
This topic discusses creating a transparent partition that connects an aggregate storage database and a block storage database. The partition, called a write-back partition, enables the user to update data on-the-fly (for example, from Spreadsheet Add-in) in the block storage database while data in the aggregate storage database remains unchanged. See Figure 1. Creating a write-back partition potentially decreases calculation time and reduces database size.
To use the information in this topic, you should be familiar with the concepts of partitioned applications. For information about these concepts, see Designing Partitioned Applications
Note: Partitioning is licensed separately from Analytic Services.
Figure 1: Conceptual Diagram Showing a Transparent Partition Used for Analyzing Variance Between Forecast and Actual Data
To create an aggregate storage and block storage write-back partition, use any of the following methods:
Tool |
Topic |
Location |
---|---|---|
You need Database Designer permissions to create a partitioned application.
This topic provides a high-level workflow for creating write-back partitions. The Administration Services Aggregate Storage Partition Wizard guides you through the following steps:
Note: You may want to partition on the time dimension if data for some time periods is stored in the aggregate storage database and data for other time periods is stored in the block storage database. For example, if you have actual data for January through March, which is stored in an aggregate storage database, and you want to budget for the last nine months of the year using write-back members in a block storage database.
Users query and write back to the block storage database. Queries are processed by the block storage database or transparently by the aggregate storage database.
For detailed information on using the Administration Services Aggregate Storage Partition wizard to create write-back partitions, see "Aggregate Storage Partition Wizard" in Essbase Administration Services Online Help.
Consider a situation in which you want to compare (do variance analysis) between forecast data and actual data. The following example is based on the ASOsample Sample database, which is provided with Analytic Services. You can recreate the example by using the Administration Services Aggregate Storage Partition wizard to guide you through the process described in this topic.
To create a write-back partition for ASOsample sample, complete the following steps:
Figure 2: ASOsample Sample Aggregate Storage Database Outline
Note: If you are using the Administration Services Aggregate Storage Partition wizard this step is done automatically. The databases are automatically partitioned on the Years dimension because you selected the Years dimension in step 3. The write-back members are not included in the partitioned area.
You input forecast values into the block storage database write-back members. Because the added members are outside the partitioned area, you can write to them and then calculate data and generate reports based on updated data. The transparent partition provides a seamless view of both databases.
For detailed information on using the Administration Services Aggregate Storage Partition wizard to create write-back partitions, see "Aggregate Storage Partition Wizard" in Essbase Administration Services Online Help.
![]() |