Scribd
Upload
14K views25 pages

Essbase ASO A Quick Reference Guide Part I

Uploaded by

Amit Sharma
Copyright
© Attribution Non-Commercial (BY-NC)
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
14K views25 pages

Essbase ASO A Quick Reference Guide Part I

Uploaded by

Amit Sharma
Copyright
© Attribution Non-Commercial (BY-NC)
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 25

Document: Essbase ASO A Quick Reference Guide

Description:
The document provides an overview on Hyperion Essbase Aggregate Storage Option. The document also outlines the major differences between BSO and ASO.

History:
Version 0.1 01. Description Change Initial Draft Review 1st Author Gaurav Shrivastava Amit Sharma Publish Date 28-May-2011 14th Jun 2011

Business Intelligence Solution Providers | Creating ASO Database

Table of Contents
1. Introduction..3 2. Key Difference between ASO and BSO....3 3. Aggregate Storage Overview..10 a. Key Aggregate Storage Characteristics..11 b. Design Considerations..11 c. Member Formulas..12 4. Aggregate Storage Production Cycle 12 a. Application and Database Trees .13 b. Directory Structures ..13 c. Rules Files for Building Outlines .14 5. Designing Aggregate Storage Outline Hierarchies17 a. Multiple Hierarchies 18 b. Stored Hierarchies 18 c. Dynamic Hierarchies 19 6. Designing Alternate Hierarchies ..19 a. Attribute Dimension Design ...19 b. Shared Members Hierarchy Design...20 7. Converting Block Storage to Aggregate Storage ...21 a. Selecting a Source Outline 21 b. Verifying Outline Corrections..23 c. Selecting a Destination24

Business Intelligence Solution Providers | Creating ASO Database

Introduction: Aggregate storage technique is used when application needs more

dimensions and members in order to support higher degree of analysis without compromising the cube performance. Aggregate storage is mainly used for applications where reporting on business data is considered as primary requirements. Data load in aggregate storage is faster than block storage and the data consolidation at the higher level is done automatically. Aggregate storage required less space in disk and data retrieval is also faster because data is always available in aggregated form. Aggregate storage application is approximate is similar as block storage application but it has so many new features. Aggregate storage database used where application require large dimensionality. Customer analysis - Data is analyzed from any dimension, and there are potentially millions of customers. Procurement analysis - Many products are tracked across many vendors. Logistics analysis - Near real-time updates of product shipments are provided. Below are some benefits of ASO. 1. 2. 3. 4. Faster load and calc times provide Lower hardware costs Lower maintenance costs Higher availability

Key Difference between Aggregate storage and block storage


Aggregate Storage 1 Data load can be possible at level 0 only 2 Write back functionality not supported 3 No need to run consolidation operation 4 Can set data load value 5 Allow to set system resource utilization 6 All calculation done through MDX 7 Complete cube has dynamic calc feature, all formulas and aggregation executes at runtime 8 *.csc file creates for (aggregate storage) 9 Data access is faster 10 Can have more number of dimension 11 12 13 14 No sparse and dense dimension Fast query processing Only level 0 data can be export No currency database Block Storage Data load is possible at any level Write back functionality supported Need to run consolidation operation Cant set data load value Not available No calculation script Only dynamic storage members calculate formulas and aggregation at runtime *.csc file creates for (Block Storage) Comparative slower Performance decrease as number dimension increase Sparse and Dense dimension exist Comparative slower No restriction on data export Currency database exists

of

1. Data load can be possible at level 0 only and write back functionality

In aggregate storage you cant load data at any level. In this example Total Expenses is level 1 member and if you load data in to it, Essbase will give youre an error.

Business Intelligence Solution Providers | Creating ASO Database

Data load at any level is possible in Block Storage Application. Edit data field and click on update button for verification refresh data grid.
Update

This example also shows that you cant write back in aggregate storage but it allow in block storage. 2. No need to run consolidation operation When you load data in to aggregate storage, data will immediately available at all parent level of hierarchy. Load data in below combination of dimension, sales is level 0 member. We will load data in sales and verify that data will be immediately available for Margin level 1 member. Data is not available for below combination.

Data load text file

Business Intelligence Solution Providers | Creating ASO Database

Right Click on data base select load data

Select data file and data load value method then click ok.

Data is loaded successfully.

Without running any calculation script or consolidate operation data is available at level 0.

Business Intelligence Solution Providers | Creating ASO Database

Data at level 1 Data is consolidating automatically for parent level. Data is available for Margin.

No Execute calculation option is available for aggregate storage application.


ASO application BSO application

Business Intelligence Solution Providers | Creating ASO Database

3. Set system resource utilization

While loading data aggregate storage allows you to set resource utilization. Resource utilization option supports to execute other tasks simultaneously. Some other options those are available for aggregate storage. Dataload in aggregate storage

Dataload in block storage

Calculation script is not supported in aggregate storage applications. You have write calculation script for any calculation.

4. Calculation done through MDX

Business Intelligence Solution Providers | Creating ASO Database

5. Data access is faster Data extraction in aggregate storage is relatively faster than block storage database.

BSO

ASO

6. Aggregate storage dimension supports

Aggregate storage application supports more dimensions in comparison with block storage. The performance of block storage will be decrease as you increase number of dimensions in database. Aggregate storage database performance does not effects by number of dimension.

7. No sparse and dense dimension

In aggregate storage application does not have dense and sparse dimension concepts.

Business Intelligence Solution Providers | Creating ASO Database

8. Restriction on data export Aggregate storage database restrict to export data only for level 0 data block. Block storage allows you to use all data export options.

9. Creating currency database You can create currency data base in block storage database.

Business Intelligence Solution Providers | Creating ASO Database

You cant create currency data base in aggregate storage database. Because database type of currency or normal is not applicable to aggregate storage databases therefore it is not selectable.

Aggregate Storage Overview


Aggregate storage is relatively newer the block storage application. It has additional features as compare to block storage. Aggregate storage database is aggregation-intensive cubes. It supports large numbers of dimensions and members. There is no concept of dense dimension in aggregate storage. It only supports extremely sparse data sets. Aggregate storage reduced calculation times and disk footprint and also reduced complexity in database development.

Key Aggregate Storage Characteristics


1. 2. 3. 4. Data is loaded only at level 0 Member formulas are MDX queries All formulas and aggregations are executed at runtime Aggregation algorithm selects and stores most expensive queries
Business Intelligence Solution Providers | Creating ASO Database 10

5. 6. 7. 8. 9.

Outlines are paged Block storage outlines can be converted to aggregate storage outlines Hierarchy types follow formalized rules Data is stored in table spaces Creating Aggregate storage manually

Design Considerations
Dimensions Ragged hierarchies supported- Ragged hierarchy means it is not necessary that all members of hierarchy contain equal number of child.

Ragged hierarchies

No limit to dimensions- There is no limit on creating dimensions in aggregate storage database outline.

Maximum level combinations The maximum level of combinations between outline dimensions are 2^52, which is very large. Large amount of data can be store in single database. Limitation on Database1. One database per application Restriction for ASO application 2. MaxL commands Eecuted on application level Because there is only one database in each application.
Business Intelligence Solution Providers | Creating ASO Database 11

3. No currency conversion - Restriction for ASO application

Member Formulas

When working with aggregate storage databases, you must write all member formulas in MDX. The Hyperion implementation of MDX is a customized version; it contains a series of commands that are specific to Essbase and is embedded in the MaxL shell. Aggregate storage supports MDX, so write all member formulas in MDX. When converting an outline from block storage to aggregate storage, you may have difficulty converting block storage member formulas to MDX. You have to convert all member formulas in to MDX manually.

Aggregate Storage Production Cycle


The production cycle for aggregate storage databases is similar as block storage database. 1. Create a database outline with database dimensions and hierarchies 2. Load data, using load rules to map to the database dimensions 3. Optional: Aggregate data by using stored or ad hoc aggregations 4. Analyze data in Excel through Smart View or Spreadsheet Add-in Database aggregations decrease query times because many data values at upper-level intersections are calculated and stored, rather than being calculated dynamically on retrieval.
Business Intelligence Solution Providers | Creating ASO Database 12

Instruction for creating aggregate storage database 1. Application and database name should be in eight characters 2. You can create only one aggregate storage database for each application

Application and Database Trees


Block Storage application database tree has more than one database and calculation scripts. Aggregate storage application database tree has only one database and no calculation script exists.

Directory Structures
Directory contains same components in both aggregate and block storage database like outlines (OTL), load rules (RUL), and report scripts (REP). Aggregate storage databases may also contain aggregation script files (CSC). This is sample directory structure for block storage database.

This is sample directory structure for aggregate storage database.

Aggregate database objects


a. b. c. d. Outlines (OTL) Load rules (RUL) Report scripts (REP) Aggregation scripts (CSC)
13

Business Intelligence Solution Providers | Creating ASO Database

Rules Files for Building Outlines


Creating rule file and building outline is same in aggregate storage as block storage.

Go to file and create new rule file.

Go to file and open relative source file either text file or SQL file.

Set Dimension Build Properties for source file then click ok.

Set Dimension build settings

Business Intelligence Solution Providers | Creating ASO Database

14

Validate

Save rule file and load data.

Select data load mode as Build only then data source and rule file click ok.

New outline dimension is loaded successfully.

Business Intelligence Solution Providers | Creating ASO Database

15

Verify in existing outline.

Designing Aggregate Storage Outline Hierarchies


You can design outline manually by using toolbar. You can create new dimensions add siblings, add child and set properties through toolbar.

Adding Child in dimension member


Business Intelligence Solution Providers | Creating ASO Database 16

There are three types of hierarchies in aggregate storage. 1. Multiple hierarchy 2. Stored hierarchy 3. Dynamic hierarchy

Aggregation hierarchies are structures usually comprising two or more levels of detail that must aggregate from the bottom up to provide a top-level total.

Multiple Hierarchy
When you tag a dimension as Multiple hierarchies enabled the dimension member is automatically tagged as Label Only. To use multiple hierarchies in a dimension, you must enable multiple hierarchies for that dimension.

Stored Hierarchy
Business Intelligence Solution Providers | Creating ASO Database 17

Stored hierarchy has only addition as consolidation operator. You can use the stored hierarchy type where aggregation is the only mathematical requirement. If you have some shared member in hierarchy then use multiple hierarchy.

Advantages: 1. Potential to store aggregated data 2. Enhanced query performance Considerations: 1. Limited use of unary operators 2. Limited use of Label Only 3. Support for only one instance 4. Dynamic Hierarchy

Dynamic hierarchy
The Dynamic hierarchy allows you to do complex calculations and member formulas. Dynamic hierarchies are calculated, the data retrieval time may be longer than for data retrieved from stored hierarchies.

Business Intelligence Solution Providers | Creating ASO Database

18

Advantages: 1. Any consolidation operator 2. Member formulas 3. No Label Only restrictions 4. Unlimited shared members Considerations: 1. Members calculated during retrieval (never preaggregated) 2. Potentially reduced query performance

Designing Alternate Hierarchies


Attribute dimension hierarchy Attribute dimension hierarchy is an alternate hierarchy used for classify additional information of dimension. Advantages: 1. Attribute dimension can be assign for any base dimension 2. Are treated like stored alternate hierarchies

Considerations: 1. Can perform only addition calculations 2. Are calculated dynamically during retrieval

Business Intelligence Solution Providers | Creating ASO Database

19

Shared members hierarchy Shared member hierarchy is also an alternate hierarchy all shared member refers to stored members of outline. In aggregate storage application only multiple hierarchies can have shared members. Jan is a shared member

But Feb is not a shared member, So Essbase will through the below error massages.

Make Feb as shared member and then save it.

Converting Block Storage to Aggregate Storage


There is simple way to converting block storage application to aggregate storage application through conversion wizard. There are many difference between block storage and aggregate storage, so when you convert block storage application to aggregate storage application, wizard will reject not applicable options.
Business Intelligence Solution Providers | Creating ASO Database 20

Conversion steps for Block Storage to Aggregate Storage


1. Select a source outline 2. Verify and correct block storage-only features (either manually or automatically) 3. Select a destination for the converted outline Step #1 Select Source Block storage Outline

Step #2 Verify and correct block storage-only features This wizard will give you the list of features which are only supported by block storage application. Warning comes in conversion of block storage to aggregate storage, because some properties does not support in aggregate storage. This warning information says that
Business Intelligence Solution Providers | Creating ASO Database 21

shown features are not supported in aggregate storage like dynamic time series, shared member and member formula.

Modification information from BSO to ASO Conversion wizard will automatically modify some member properties and delete invalid members.

Step #3 Select Target Aggregate Storage Application You can select target application and database outline then replace the existing outline from the new one. You also can create new aggregate storage application and convert block storage to aggregate storage.

Business Intelligence Solution Providers | Creating ASO Database

22

Select Outline

Select and replace the existing outline

Business Intelligence Solution Providers | Creating ASO Database

23

Click on finish..

Converted Block Storage Application Block storage application successfully converted into aggregate storage application.
Business Intelligence Solution Providers | Creating ASO Database 24

The unsupported features replaced by supported features. 1) Year dimension is converted from dynamic to storage 2) Measures dimension hierarchy converted as dynamic 3) Product dimension storage hierarchy converted as Multiple Hierarchy 4) All member formulas are rejected

Business Intelligence Solution Providers | Creating ASO Database

25

Common questions

Powered by AI

Aggregate Storage Option (ASO) in Essbase provides several advantages over Block Storage Option (BSO), including faster data load and calculation times, lower hardware and maintenance costs, improved data retrieval speed due to automatic aggregation of data at higher levels, and support for larger numbers of dimensions without performance degradation. Additionally, ASO does not require a consolidation operation to be run, as data is immediately available at all parent levels upon loading .

Using MDX for calculations in Essbase Aggregate Storage Option (ASO) applications enhances flexibility and performance as it allows member formulas to be defined dynamically, making calculations at runtime more efficient. This eliminates the need for pre-computed data storage, reducing disk space requirements and allowing for more complex and extensive analytical scenarios. However, the necessity of writing all member formulas in MDX can introduce a learning curve and potential conversion issues for those accustomed to block storage's calculation scripts .

Data retrieval speed is generally faster in Aggregate Storage Option (ASO) than Block Storage Option (BSO) because ASO pre-aggregates data and stores it at various levels, allowing immediate access without requiring on-the-fly calculations. This design reduces the need for extensive retrieval computation, optimizing the querying process. In contrast, BSO calculates aggregations at runtime, potentially slowing down data retrieval, especially as the complexity and number of dimensions increase .

When designing an Aggregate Storage Option (ASO) database in Essbase, considerations include ensuring large dimensionality capabilities since ASO supports extensive dimensions without performance hits. The design should accommodate ragged hierarchies and high-level combinations of dimensions. There should be awareness of the limitation of one database per application and the use of MDX for calculations. Additionally, ASO benefits from improved query performance through aggregation script files (CSC) that store pre-calculated data efficiently .

Aggregate Storage Option (ASO) has limitations regarding data export and storage compared to Block Storage Option (BSO). In ASO, data export is restricted to level 0 data blocks, reducing flexibility in data sharing and analysis. Furthermore, ASO does not support creating currency databases, limiting its applicability in financial reporting scenarios requiring currency conversion. These limitations can significantly impact applications where such features are crucial for operational needs .

Converting a Block Storage Option (BSO) application to an Aggregate Storage Option (ASO) application in Essbase involves several key steps, including selecting the source BSO outline, verifying and correcting incompatible features, and selecting a destination ASO application. The conversion process replaces unsupported features with ASO-compatible ones and involves adapting dimensions, hierarchies, and member formulas to MDX formats. This transition allows leveraging ASO's advantages, such as real-time aggregation and increased dimensionality, but may require extensive manual adjustments and understanding of MDX to maintain system functionality and performance .

In Aggregate Storage Option, hierarchies are managed with more flexibility compared to Block Storage Option. ASO supports multiple, stored, and dynamic hierarchies, enabling complex calculations and member formulas using MDX. Stored hierarchies have a consolidation operator focus, while dynamic hierarchies allow for complex calculations during data retrieval rather than at load time. ASO does not have sparse and dense dimension concepts and can manage a vast number of dimensions efficiently .

Aggregation scripts (CSC) in Aggregate Storage Option (ASO) databases play a critical role in performance optimization by pre-computing and storing complex aggregations. This approach minimizes on-the-fly calculation requirements during data retrieval, thus reducing query time and enhancing processing speed, particularly useful in ASO's large dimensional configurations. The scripts ensure that frequently queried data is readily accessible, balancing immediate performance demands with storage resource management .

Dynamic hierarchies in Aggregate Storage Option (ASO) databases enhance analytical capabilities by allowing complex calculations and member formulas to be performed at retrieval time rather than precomputing them during data load. This dynamic approach supports more flexible and intricate analytical scenarios, as it adapts to the latest data input and system requirements without predefined aggregation, although it can affect query performance due to computation happening during data retrieval .

In ASO hierarchies, shared members reference stored members of an outline, allowing for memory-efficient reuse of dimensions across various functional or reporting lines. Utilizing shared members can facilitate cohesive and integrated data views across multiple dimensions, enhancing reporting capabilities. However, shared members can only be part of multiple hierarchies, and poor implementation may lead to design complexity due to the need to clearly define relationships and aggregate promptly across vast data arrangements .

You might also like