In aggregate storage outlines and block storage outlines, dimensions are structured to contain one or more hierarchies of related levels and members within the levels. For example, the Time dimension in the ASOsamp.Sample database (see Figure 163, ASOSamp.Sample Database Outline Showing Multiple Hierarchies and Members on the Time Dimension) includes the hierarchies MTD, QTD, and YTD.

Figure 163. ASOSamp.Sample Database Outline Showing Multiple Hierarchies and Members on the Time Dimension

In an aggregate storage database, you can create two types of hierarchies:

The two types of hierarchies have different advantages and restrictions. A dimension may contain both types of hierarchies. To use multiple hierarchies in a dimension (even if they are all stored hierarchies), you must enable multiple hierarchies for that dimension.

When you tag a dimension member as multiple hierarchies enabled, it is automatically tagged label only.

If you do not tag the dimension as multiple hierarchies enabled, Essbase automatically tags the dimension as a stored hierarchy (except the dimension tagged as Accounts, which is automatically tagged as a dynamic hierarchy).

Alternate Hierarchies

An alternate hierarchy may be modeled in either of the following ways:

• As an attribute dimension, which uses attributes to classify members logically within the dimension (for example, a Product dimension can have attributes such as Size and Flavor). See Working with Attributes.

  Note:

  If you use an attribute dimension to create an alternate hierarchy, you can create a crosstab report query of members in the attribute dimension with members in the base dimension. For example, a crosstab report of product sales information could show size attributes (such as small and large) as column headings and products as row headings. If you use shared members to create an alternate hierarchy, you cannot create an equivalent crosstab report query of the shared members with the nonshared members in the primary hierarchy.

• As a hierarchy of shared members. The alternate hierarchy has shared members that refer to nonshared members of previous hierarchies in the outline. The shared members roll up according to a different hierarchy from the nonshared members to which they refer. Shared members on dynamic hierarchies can have formulas. See Understanding Shared Members. Table 129 shows the hierarchies for the ASOsamp.Sample database. The Products dimension is shown in Figure 164, Aggregate Storage Outline Displaying the Alternate Hierarchy High End Merchandise on the Product Dimension of ASOSamp.Sample.

Table 129. Example Hierarchies and Alternate Hierarchies for the Product Dimension of ASOsamp.Sample

Product	Hierarchy	Alternate Hierarchy (containing shared members)
Flat Panel	Products, All Merchandise, Personal Electronics, Home Entertainment, Televisions	Products, High End Merchandise
HDTV	Products, All Merchandise, Personal Electronics, Home Entertainment, Televisions	Products, High End Merchandise

Figure 164. Aggregate Storage Outline Displaying the Alternate Hierarchy High End Merchandise on the Product Dimension of ASOSamp.Sample

The following restrictions apply when creating alternate hierarchies in aggregate storage outlines:

• The nonshared instance of the member must occur in the outline before any shared instances of the member. For example, in Figure 164, Aggregate Storage Outline Displaying the Alternate Hierarchy High End Merchandise on the Product Dimension of ASOSamp.Sample, the member HDTV occurs in the All Merchandise hierarchy before it occurs as a shared member in the alternate hierarchy of High End Merchandise.

• The first hierarchy in a dimension where multiple hierarchies are enabled cannot contain a shared member.

• Stored hierarchy dimensions cannot have shared members. Stored hierarchies within a multiple hierarchies dimension can have shared members.

• To ensure that values are not double-counted, a stored hierarchy cannot contain multiple copies of the same shared member. For example, a stored hierarchy cannot contain a shared member and any of its ancestors. In Figure 164, Aggregate Storage Outline Displaying the Alternate Hierarchy High End Merchandise on the Product Dimension of ASOSamp.Sample, you cannot add the shared member “Televisions” as a child of “High End Merchandise,” because doing so would make “Televisions” a sibling of its children, shared members “Flat Panel” and “HDTV,” causing the values of “Flat Panel” and “HDTV” to be added twice.

• Nonshared instances of a member must be in the same dimension as the shared member (same for block storage outlines).

• A stored hierarchy cannot contain a nonshared instance and a shared instance of the same member.

• A stored hierarchy can contain a shared instance of a dynamic hierarchy member only if the dynamic hierarchy member is a level 0 member without a formula.

Note:

In an aggregate storage database, a shared member automatically shares any attributes that are associated with its nonshared member. This also applies to an implied shared member (for example, a member that has only one child). See Understanding Implied Sharing. You can prevent implied sharing by setting the Never Share property; see Determining How Members Store Data Values. This behavior of shared members and attributes in aggregate storage databases is different from the behavior in block storage databases.

This topic provides information on the differences between aggregate storage and block storage databases with regard to attribute dimensions. To use the information in this topic, you should be familiar with attribute dimension concepts for block storage databases. See Working with Attributes.

The following information applies to attribute dimensions when used on aggregate storage databases:

Attribute dimensions do not have hierarchy types. You cannot specify an attribute dimension as a dynamic or stored hierarchy. Essbase treats attribute dimensions as stored alternate hierarchies of the base dimension. For example, in the ASOSamp.Sample database, Essbase treats the Store Manager attribute dimension as if the Store Manager dimension were a stored alternate hierarchy of the Stores dimension.

When using query tracking, Essbase considers queries on attribute dimension data and may include attribute dimension members in aggregate view selections. See Selecting Views Based on Usage and Calculating Aggregate Storage Databases.

When selecting and building views based on attribute query data, some queries on attribute data are always dynamically calculated at the time of retrieval, which may affect query performance.

Every query involving attribute dimension members must also include at least one member from the base dimension. If the query involves a single attribute dimension and a sum-of-all dimension member, Essbase aggregates the query data, potentially improving query performance. In other cases, Essbase must calculate the query at the time of retrieval.

The following table describes attribute query types and how Essbase calculates the query:

In the outline displayed in Figure 165, Outline for Attribute Query Example, RealDimension is the sum of all its descendents (it is not tagged as label-only). If a query involves one or more members from a single attribute dimension (for example, AttributeDimension1), crossed with the base dimension member (RealDimension), Essbase can build aggregate cells for the data, potentially improving query performance.

The following queries, however, are always calculated at the time of retrieval:

This topic lists the key design considerations when you create aggregate storage database outlines. For an example of implementing these design considerations, see the ASOSamp.Sample database. Consider the following information when designing an aggregate storage outline:

When querying data from a dimension that has multiple hierarchies, query performance may improve if you query the data in the following way:

Including dynamic hierarchy members and stored hierarchy members in the same query may require a large internal memory cache, which decreases query performance.

By default, the compression dimension in an aggregate storage database is the Accounts dimension. Changing the compression dimension triggers a full restructure of the database. Essbase requires the compression dimension to be a single dynamic hierarchy. If the dimension has a different hierarchy setting, such as multiple hierarchies, it will be set to single dynamic hierarchy automatically. The original hierarchy setting is lost (setting a different dimension as compression does not return the original hierarchy setting). Attribute dimensions cannot be compression dimensions, nor can dimensions with attributes associated to them.

The choice of compression dimension can significantly affect performance. A good candidate for a compression dimension is one that optimizes data compression while maintaining retrieval performance. This topic provides information about choosing an optimal compression dimension.

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, Essbase 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. See Comparison of Aggregate and Block Storage.

Aggregate storage database outlines are pageable. This feature may significantly reduce memory usage for very large database outlines. For aggregate storage databases, Essbase preloads part of the database outline into memory. Then, during data retrieval, Essbase 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 Essbase 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. See Optimizing Outline Paging.

The maximum size of a buildable outline (the number of members) depends on several factors:

Table 131 shows the amount of addressable memory available for Essbase for different operating systems.

Essbase uses about 40 MB of memory on startup. In addition, the various caches require the following memory allocations:

Therefore, the initial memory footprint for Essbase is about 90 MB. In addition, memory must be allocated to process incoming query requests. Typical memory to reserve for this purpose is about 300 MB. The total memory allocated for Essbase 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 outline size depends on whether it is built using a dimension build or from an outline already loaded into Essbase.

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:

See the Oracle Essbase Technical Reference.

When Essbase 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 namespace 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 build a very large outline. After the outline is built and restructured Essbase can be restarted with the namespace settings set back to their default TRUE (on) setting.

When you delete members from an aggregate storage outline, the corresponding records of members in the outline file (.otl file) are marked as deleted but remain in the file. The outline file continues to grow as members are deleted and added. You can minimize the outline file size by compacting the file to remove the records of deleted members. Compacting the outline file causes Essbase to restructure the outline and can take place only when no other users or processes are actively using the database. Compacting the outline does not cause Essbase to clear the data.

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

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 specifies a value.

A numeric value expression is used in queries to build calculated members, which 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} 
    ) 
  )', SOLVE_ORDER=1 

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} 
    ) 
  )' 

The SOLVE_ORDER property specifies the order in which members and formulas are evaluated. See Calculation Order.

A numeric value expression also can 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.

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 Oracle Essbase Technical Reference.

Essbase calculates formulas in aggregate storage outlines only when data is retrieved. Calculation order may affect calculation results. Whenever you use MDX formulas on multiple dimensions in an aggregate storage outline, it is good practice to set the solve order for each member or dimension. See Calculation Order.

When you create member formulas for aggregate storage outlines, observe the following rules:

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. See Setting Field Type Information.

The following sections discuss and give examples of how to write a variety of formulas for members in aggregate storage outlines.

[Units]/[Transactions]

[Price Paid]-[Original Price]

Transactions/(Transactions,Years,Months,
[Transaction Type],[Payment Type],Promotions,Age,
[Income Level],Products,Stores,Geography)

IIF (
     [Payment Type].CurrentMember=[Credit Card],
     [Price Paid] * 1.05, [Price Paid]
)

Count(Filter(Descendants([Personal
Electronics],[Products].Levels(0)),[Qtr1] > 100.00))