ASO Vs BSO
Summary Difference
The main
difference between ASO and BSO as show you as below:
1) ASO can support large scale of member such as Customer individual analysis, for example if your customer dimension has 1M members, the aggregate calculation time will very faster than BSO.
2) ASO cannot support write-back but BSO can support that feature (Write-Back = Input data directly back to Essbase Cube)
3) ASO can support large scale aggregation but for another purpose such as what-if or allocation should use BSO.
4) For architecture of calculation, ASO use MDX script, BSO use Calc Script.
5) For Cube that have many dimension and more member, ASO can save storage space more than BSO
6) We can use in-line formula on BSO outline but ASO cannot use that
1) ASO can support large scale of member such as Customer individual analysis, for example if your customer dimension has 1M members, the aggregate calculation time will very faster than BSO.
2) ASO cannot support write-back but BSO can support that feature (Write-Back = Input data directly back to Essbase Cube)
3) ASO can support large scale aggregation but for another purpose such as what-if or allocation should use BSO.
4) For architecture of calculation, ASO use MDX script, BSO use Calc Script.
5) For Cube that have many dimension and more member, ASO can save storage space more than BSO
6) We can use in-line formula on BSO outline but ASO cannot use that
7) When
you Add or Delete or changing the outline the entire data will remove.
8) There
is no right back option.
9) In a
standard Dimension + & ~ consolidation property only .In measure -,/,%
other symbols are there..
10) Only
one database for Application.
11) There
are no Calculation scripts and formulas.
12) Two
pass, Variance are not used in ASO.
Basic Difference between ASO/BSO
- Aggregate storage applications differ from block storage applications in both concept and design.
- Storage Kernel: Architecture that supports rapid aggregation, optimized to support high dimensionality and sparse data. It doesn’t have Dense/Sparse concepts.
- Physical storage definition Through the Application Properties window,
- Tablespaces tab in Administration Services.
- Database creation migrate a block storage outline or define after application creation.
- ASO Support only one Database.
Outline Differences between
Aggregate Storage and Block Storage
1)
Multiple hierarchies enabled, dynamic hierarchy, or stored hierarchy designation
2) No
two-pass calculation
3) ASO
does not have concept of Dense/Sparse.
4)
Support for the ~ (no consolidation) operator (underneath label-only members
only) and the + (addition) operator
5) Cannot
have formulas
6)
Restrictions on label only
7) No
Dynamic Time Series members
8)
Dynamic Calc and Store not relevant
9) No
association of attribute dimensions with the dimension tagged as Accounts
Calculation Differences Between
Aggregate Storage and Block Storage
1)
Database calculation Aggregation of the database, which can be predefined by
defining aggregate views
2)
Calculation script is not supported by ASO. Only outline consolidation.
3)
Formulas Allowed with the following restrictions:
4) Must
be valid numeric value expressions written in MDX (cannot contain % operator,
replace with expression: (value1/value2)*100)
5) No
support for Essbase calculation functions
6) On
dynamic hierarchy members, formulas are allowed without further restrictions.
7)
Calculation order Member formula calculation order can be defined by the user
using the solve order member property
8)
Attribute calculations dimension Support for Sum
Data Load Differences Between
Aggregate Storage and Block Storage
1) In ASO
level 0 cells whose values do not depend on formulas in the outline are loaded
whereas in BSO data can be loaded at any level (other than Dynamic Calc).
At the
end of a data load, if an aggregation exists, the values in the aggregation are
recalculated automatically whereas in BSO it has to be explicitly call i.e calc
all. Block storage No automatic update of values. To update data values you
must execute all necessary calculation scripts.
2)
Aggregate storage databases can
contain multiple slices of data. Data slices can be merged.
3)
The loading of multiple data sources
into aggregate storage databases is managed through temporary data load
buffers.
Creating Aggregate Storage Database,
Application and Outline
There are
2 ways to create ASO cube
1)
Convert a block storage outline to an aggregate storage
outline, and create an aggregate storage application to contain the converted
database and outline.
2) Create
an aggregate storage application and database. The aggregate storage outline is
created automatically when you create the database.
Stored Hierarchies
1) Members
of stored hierarchies are aggregated according to the outline structure.
Because aggregate storage databases are optimized for aggregation, the
aggregation of data values for stored hierarchies is very fast. To allow this
fast aggregation, members of stored hierarchies have the following
restrictions:
2)
Stored hierarchies can have the no-consolidation (~) operator (only underneath
Label Only members) or the addition (+) operator.
3)
Stored hierarchies cannot have formulas.
Dynamic Hierarchies
To evaluate a dynamic hierarchy,
Essbase calculates rather than aggregates the members and formulas. The order
in which members and formulas are evaluated is defined by the solve order
property. At the time of retrieval, Essbase calculates the required member
combinations and calculates any required outline member formulas. Because
dynamic hierarchies are calculated, the data retrieval time may be longer than
for data retrieved from stored hierarchies. However, when you design your
database, dynamic hierarchies provide the following advantages
1)
Dynamic hierarchies can contain any consolidation operator.
2)
Dynamic hierarchies can have formulas.
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.) As a hierarchy of shared members. The alternate hierarchy has
shared members that refer to non shared members of previous hierarchies in the
outline. The shared members roll up according to a different hierarchy from the
non shared members to which they refer. Shared members on dynamic hierarchies
can have formulas. The non shared instance of the member must occur in the
outline before any shared instances of the member
Comparison of Aggregate and Block Storage
1.
Introduction
1. Introduction
Essbase provides an aggregate storage kernel as a persistence mechanism for multidimensional databases. Aggregate storage databases enable dramatic improvements in both database aggregation time and dimensional scalability. The aggregate storage kernel is an alternative to the block storage kernel. Aggregate storage databases typically address read-only, “rack and stack” applications that have large dimensionality, such as the following applications:
·
Customer analysis. Data is analyzed
from any dimension, and there are potentially millions of customers.
A sample
application (ASOsamp), a data file, and a rules file are provided to
demonstrate aggregate storage functionality.
Aggregate storage
applications, which differ from block storage applications in concept and
design, have limitations that do not apply to block storage applications. The
following sections describe the differences.2. Inherent Differences
| Storage kernel | Architecture that supports rapid aggregation, optimized to support high dimensionality and sparse data | Multiple blocks defined by dense and sparse dimensions and their members, optimized for financial applications |
| Physical storage definition | Through the Application Properties window, Tablespaces tab in Administration Services | Through the Database Properties window, Storage tab in Administration Services |
| Create database | Migrate a block storage outline or
define after application creation
Do not use the file system to copy a
block storage outline into an aggregate storage application. Use the
migration wizard in Administration Services to migrate the outline.
|
Define after application creation |
| Copy database | Not supported | Supported |
| Databases supported per application | One | Several (one recommended) |
| Application and database names | See Naming
Restrictions for Applications and Databases. Names reserved for
tablespaces, cannot be used as application or database names: |
See Naming Restrictions for Applications and Databases. |
| Application and database information display | Displayed in the Application
Properties window and the Database Properties window in Administration
Services. Information not supported by or relevant to aggregate storage applications is not shown. For a description of aggregate storage-specific information, see the Oracle Essbase Administration Services Online Help for the Application Properties window and Database Properties window. |
Displayed in the Application Properties window and the Database Properties window in Administration Services |
| Configuration settings (essbase.cfg) | For a list of the settings that apply to aggregate storage databases, see the Oracle Essbase Technical Reference. | For a list of the settings that do not apply to block storage databases, see the Oracle Essbase Technical Reference. |
3. Outline Differences
| Dense or sparse dimension designation | Not relevant | Relevant |
| Multiple hierarchies enabled, dynamic hierarchy, or stored hierarchy designation | Relevant | Irrelevant |
| Accounts dimensions and members on dynamic hierarchies | Support with the following
exceptions:
·
No two-pass calculation
(however, for information on specifying the calculation order, see Calculation Order)
|
Full support |
| Members on stored hierarchies | Support with the following
exceptions:
·
Support for the ~ (no
consolidation) operator (underneath label-only members only) and the +
(addition) operator
·
Stored hierarchy dimensions
cannot have shared members. Stored hierarchies within a multiple hierarchies
dimension can have shared members. See Stored Hierarchies.
|
Full support |
| Member storage types | Support with the following
exceptions:
On dynamic hierarchies, ability to
tag any member as label only
On conversion from a block storage
database, attribute dimension members are tagged as Dynamic Calc. On standard
dimension members Dynamic Calc tags are converted and tagged as stored
members, which changes the Members Stored value on the Dimensions tab of the
Database Properties window in Administration Services.
|
Support for all member storage types in all types of dimensions except attribute dimensions |
| Ragged hierarchies and hierarchies with more than 10 levels | Support, with possible performance impact | Support |
| Outline validation | ||
| Outline paging | Support | No support |
| Database restructure | Levels of restructure; see Aggregate Storage Database Restructuring | Levels of restructure; see Optimizing Database Restructuring |
4. Calculation Differences
5. Partitioning Differences
| Partitioning | Support with the following
restrictions: |
Fully supported |
6. Data Load Differences
7. Query Differences
| Report Writer | Supported, except for commands related to sparsity and density of data | Fully supported |
| Spreadsheet Add-in | Supported, with limited ability to change data (write-back) | Fully supported |
| API | Supported | Supported |
| Export | Support with the following
restrictions: |
Supported |
| MDX queries | Supported | Supported |
| Queries on attribute members that are associated with non-level 0 members | Returns values for descendants of the
non-level 0 member. See also Design Considerations for Attribute Queries. |
Returns #MISSING for descendants of the non-level 0 member |
| Queries on attribute members and shared members | A shared member automatically shares the attribute associations of its nonshared member | A shared member does not share the attribute associations of its nonshared member |
| Query logging | Not supported | Supported |
| Query performance | Considerations when querying data
from a dimension that has multiple hierarchies. See Query Design Considerations for Aggregate Storage. |
Hierarchies not relevant |
8. Feature Differences
The performance of Essbase ASO vs BSO.
We had three different models that
were provided to us by our customer to test various types of performance on
cubes. They were small, medium and large. The large cube expanded to about 1GB
with a fully calculated BSO model. On the hardware provided given the data set
it took on about 3 minutes to calculate the necessary cells and then processing
queries against it took about 7 seconds. We essentially reduced calc times to
zero upon conversion to ASO. The only associated ‘calc time’ was that done with
reporting. The resulting queries processed within 15 seconds.
We were able to process the large
database on a laptop in ASO faster than on a four way Intel server in BSO.
These results were similar against the medium and small databases.
ASO allows the database to be
optimized according to the most frequently processed queries against it. This
optimization is much more granular than optimizations against BSO. Whereas an
entire database is optimized under BSO when fully calculated, only select
pieces of an ASO database need be optimized – those pieces most likely to be
queried.
Furthermore, we
showed the Essbase ASO to be a faster performer in all cases than Microsoft’s
Analytical Services under both SQL Server 2000 and the advance version of Yukon.
Our experience with this
customer in a very strictly controlled environment confirmed our expectations
of ASO databases to outperform BSO databases in the following areas:
1.
Load times: Overall
about 20% faster
2.
Calculation times:
Overall about 20X faster
3.
Query times: Over
all about 5% slower
4.
Storage Space:
Overall about 10X smaller.
5.
Memory Utilization:
Overall about the same.
The advantages of the newer versions of Essbase
over prior versions are significant and migration is simple. Conversion of BSO
to ASO without modification can be performed on about 70% of Essbase databases
in a typical customer. Even for those that cannot be directly converted, ASO
partitioning allows customers to take advantage.
No comments:
Post a Comment