Essbase ASO (Aggregate Storage Option)

Exclusive offer: get 80% off this eBook here
Oracle 10g/11g Data and Database Management Utilities

Oracle 10g/11g Data and Database Management Utilities — Save 80%

Master 12 must-use Oracle Database Utilities with this Oracle book and eBook

₨160.00    ₨32.00
by Joseph Sydney Gomez Sarma Anantapantula | July 2009 | Oracle

In this article by Sarma Anantapantula and Joseph Sydney Gomez, we will learn all about ASO which is now also used to store data in the Essbase database. We will learn what ASO exactly is, how it works, and when to use ASO instead of BSO. We will explain the use of the special industry standard multidimensional data query language known as MDX that is employed by Essbase, and is particularly effective with ASO. Finally, we'll discuss the pros and cons of ASO and BSO.

Welcome to the exciting world of Essbase Analytics known as the Aggregate Storage Option (ASO).

Well, now you're ready to take everything one step further. You see, the BSO architecture used by Essbase is the original database architecture as the behind the scenes method of data storage in an Essbase database. The ASO method is entirely different.

What is ASO

ASO is Essbase's alternative to the sometimes cumbersome BSO method of storing data in an Essbase database. In fact, it is BSO that is exactly what makes Essbase a superior OLAP analytical tool but it is also the BSO that can occasionally be a detriment to the level of system performance demanded in today's business world.

In a BSO database, all data is stored, except for dynamically calculated members. All data consolidations and parent-child relationships in the database outline are stored as well. While the block storage method is quite efficient from a data to size ratio perspective, a BSO database can require large amounts of overhead to deliver the retrieval performance demanded by the business customer.

The ASO database efficiently stores not only zero level data, but can also store aggregated hierarchical data with the understandings that stored hierarchies can only have the no-consolidation (~) or the addition (+) operator assigned to them and the no-consolidation (~) operator can only be used underneath Label Only members. Outline member consolidations are performed on the fly using dynamic calculations and only at the time of the request for data. This is the main reason why ASO is a valuable option worth consideration when building an Essbase system for your customer.

Because of the simplified levels of data stored in the ASO database, a more simplified method of storing the physical data on the disk can also be used. It is this simplified storage method which can help result in higher performance for the customer.

Your choice of one database type over the other will always depend on balancing the customer's needs with the server's physical capabilities, along with the volume of data. These factors must be given equal consideration.

Creating an aggregate storage Application|Database

Believe it or not, creating an ASO Essbase application and database is as easy as creating a BSO application and database. All you need to do is follow these simple steps:

  1. Right-click on the server name in your EAS console for the server on which you want to create your ASO application.
  2. Select Create application | Using aggregate storage as shown in the following screenshot:
  3. Essbase ASO (Aggregate Storage Option)

  4. Click on Using aggregate storage and that's it. The rest of the steps are easy to follow and basically the same as for a BSO application.

To create an ASO application and database, you follow virtually the same steps as you do to create a BSO application and database. However, there are some important differences, and here we list a few:

  • A BSO database outline can be converted into an Aggregate Storage database outline, but an Aggregate Storage database outline cannot be converted into a Block Storage database outline.
    Steps to convert a BSO application into an ASO application:
    1. Open the BSO outline that you wish to convert, select the Essbase database and click on the File | Wizards | Aggregate Storage Outline Conversion option. You will see the first screen Select Source Outline. The source of the outline can be in a file system or on the Essbase Server. In this case, we have selected the OTL from the Essbase Server and then click Next as shown in the following screenshot:
    2. In the Next screen, the conversion wizard will verify the conversion and display a message that the conversion has completed successfully. Click Next.
    3. Here, Essbase prompts you to select the destination of the ASO outline. If you have not yet created an ASO application, you can click on the Create Aggregate Storage Application on the bottom-right corner of the screen as shown in the next screenshot:

      Enter the Application and the Database name and click on OK. Your new ASO application is created, now click on Finish. Your BSO application is now converted into an ASO application. You may still need to tweak the ASO application settings and outline members to be the best fit for your needs.

  • In an ASO database, all dimensions are Sparse so there is no need to try to determine the best Dense/Sparse settings as you would do with a BSO database.
  • Although Essbase recommends that you only have one Essbase database in an Essbase application, you can create more than one database per application when you are using the BSO. When you create an ASO application, Essbase will only allow one database per application.

There is quite a bit to know about ASO but have no fear, with all that you know about Essbase and how to design and build an Essbase system, it will seem easy for you.

Keep reading for more valuable information on the ASO for things like, when it is a good time to use ASO, or how do you query ASO databases effectively, or even what are the differences between ASO and BSO. If you understand the differences, you can then understand the benefits.

Oracle 10g/11g Data and Database Management Utilities Master 12 must-use Oracle Database Utilities with this Oracle book and eBook
Published: June 2009
eBook Price: ₨160.00
Book Price: ₨400.00
See more
Select your format and quantity:

Hierarchies

Dimensions can have one or more hierarchies of the members. Included with your installation of Essbase, you get the sample ASO application called ASOsamp to use as a guide when learning about ASO.

In ASO, there are two types of hierarchies:

  • Stored hierarchies
  • Dynamic hierarchies

An outline dimension in an ASO database can have any number of members and these members can only be set as either stored members or dynamic members. They can even have both of the hierarchies. In order to set the dimension to have both types of hierarchies, you need to enable  Multiple Hierarchies Enabled. If no hierarchy is defined, then by default it is tagged as Stored hierarchy.

To enable multiple hierarchies, follow these steps:

  1. Right-click on the dimension or member name you wish to enable the multiple hierarchies on. Click on Edit member properties….

    Essbase ASO (Aggregate Storage Option)

  2. On the Member Properties screen, under the Hierarchy section, select Hierarchies Enabled from the Hierarchy list box: Stored

    Essbase ASO (Aggregate Storage Option)

Stored hierarchies

Using Stored hierarchies, the data is aggregated based on the outline structure. The data aggregation and data retrieval is faster. There are a few restrictions when using Stored hierarchies:

  • Stored hierarchies cannot have member formulas.
  • Stored hierarchies can have the no-consolidation (~) operator for Label-only members. A member that is label only is merely in the outline as a place holder or to be informational and does not store data. A good example of this is our Calendar Periods dimension. While the root member Calendar Periods is useful to have for information, the data would make no sense rolled up to this level.

Dynamic hierarchies

Using Dynamic hierarchies, the data is not aggregated but is calculated at the time of the data retrieval. Since the data is calculated at the time of retrieve, the response time for the output is longer. The account dimension is always tagged as Dynamic hierarchies and you cannot change the account dimension to stored hierarchy. The advantages of the  Dynamic hierarchies are:

  • Dynamic hierarchies can have formulas
  • Dynamic hierarchies can have any consolidation operator

The following screenshot shows an example of how the Dynamic and Stored hierarchies are used in the sample ASO database. In the sample ASO database's case, you can see that the Time dimension has MultipleHierarchies Enabled:

Essbase ASO (Aggregate Storage Option)

Outline paging

This is one major difference between a Block Storage application and an Aggregate Storage application that provides a noticeable boost in performance. Unlike a BSO application, where the database outline must be loaded into memory as a single element, the database outlines in ASO databases are created and stored in what can be considered a page-able format. This means that instead of Essbase loading the entire database outline into memory, the page-able outline can be loaded into memory either one page or section at a time. This can free up resources and can help make data retrieval and data aggregations faster by reducing the amount of memory consumed by large database outlines.

Aggregation

There is no need for complex calculation scripts in an ASO database. Now, you may be wondering how the data gets aggregated without performing any calculated aggregation? How fast will my data get retrieved? In an ASO database, the data gets loaded only into the level 0 (leaf node) cells. When the user attempts to retrieve data at a level higher than the zero level, the data is dynamically aggregated. Also, remember a good portion of this aggregated data is not physically stored in the database. As the database size increases, the dynamic aggregation consumes more time. In order to improve the database performance, you may need to pre-aggregate the data.

MDX query language

So is now a good time to spring a new scripting language on you? Of course it is!

Okay, so it's not really a whole new language, it's just a piece of the MaxL scripting language we haven't gone over with you yet.

You may recall how we've gone over the MaxL scripting language previously. Well, the MaxL scripting language actually has two pieces. These MaxL pieces are known as MaxL DDL for Data Definition Language, which is the piece you are already familiar with and MaxL MDX for Multidimensional Expressions.

Why have we not explained something like MDX in-depth already? The reason is that while the DDL piece of MaxL contains many powerful functions that are written in relatively easy to understand syntax and it can easily replace the Essbase Command scripting language (EssCmd) as your primary tool for automating database maintenance and support processes, the MDX piece of MaxL is more of a data querying language. Yes, MDX is very powerful as is DDL, but its usefulness can be debated since there are several other methods of querying data in Essbase that are just as effective and easier or more convenient to use. The Essbase Reports scripting language with the aid of the Essbase Query Designer, is one example that comes to mind as an effective data querying tool. MDX also supports the XML for Analysis (XMLA) API.

MDX however, has its place and its place is where it is best suited. The place for MDX is querying ASO Essbase databases.

Oracle Essbase 9 Implementation Guide Develop high-performance multidimensional analytic OLAP solutions with Oracle Essbase 9 with this book and eBook
Published: June 2009
eBook Price: ₨220.00
Book Price: ₨550.00
See more
Select your format and quantity:

MDX functions for ASO

As we said earlier, the MDX data query language is a useful tool in its own right. In most cases, the MDX language has equivalent functions for each member set function found in the Essbase Calculation Script language or the Essbase Report Script language. The following figure shows the complete list of MDX data query functions available:

Essbase ASO (Aggregate Storage Option)

Now, look at the functions listed here. They look like a hybrid between Essbase member selection functions and typical Structured Query Language functions found in any relational database.

Unfortunately, after all of the hard work we've been through getting you to think and act like an Essbase programmer/administrator, we don't want you to slip back into the relational way of thinking. This is only acceptable when querying an ASO database, because it is indeed set up somewhat similar to a relational database in terms of data structure.

Take a close look at the functions, most of them are intuitive as to their function and you will also notice a complete array of functions that are suspiciously similar to the column functions in relational SQL. Again, this is because an ASO database does not store the complete set of data like a BSO database does. ASO only physically stores the data at the lowest levels and is a lot like a relational database where you depend heavily on functions such as SUM, MAX, and ABS to massage the data as it is delivered to you.

MDX function examples

Here, we will show you the same data query function twice. One will be in the format used by the Essbase Calculation Script language and the other example will be in the MDX data query language. The reason for this is to illustrate how vastly different each language is.

The @ANCEST function is a very commonly used function. In an Essbase database, where the data is stored at all levels, you can easily ask for the ancestor member.In an ASO database however, the data in most cases is dynamically calculated, so you have to ask for it in a different way so Essbase can interpret your request properly.

  • Essbase Calculation Script
    Syntax:
    @ANCEST (dimName, genLevNum, [ mbrName])

    Example:

    @ANCEST(Product,2,Sales)
  • MDX Query Language
    Ancestor
    Example:
    (
    Sales,
    Ancestor(
    Product.CurrentMember,
    Product.Generations(2)
    )
    )

The command above assumes you are keying off of the member currently selected. What will be returned is the value of Sales from the ancestor of the member in the product dimension and the Generation level 2.

MDX query syntax

To make querying an ASO database more relational and SQL like, you can actually write your queries like you would in a relational database using SQL.

Looking at the following examples, you can see that the SELECT statement is almost identical to that of a query written in SQL. Because the data is stored similarly, the query language is similar. That way, you won't forget when you're querying an Essbase BSO database or an Essbase ASO database.

Typical MDX query structure:

SELECT [<member_name>,[ <member_name>]]
FROM [<Essbase_database_name>]]
WHERE [<dimension_name.member_name>
[, <dimension_name.member_name>]]

Syntax:

SELECT Sales,Stocks
FROM EssCar
WHERE Calendar Periods.June, Model Year.2010

Typical SQL Query Structure

SELECT column_name>[, <column_name>...]
FROM table_name
WHERE value = value [and value = value]

Syntax:

SELECT Sales, Stocks
FROM MARKET_INFO_TBL
WHERE Month = June and ModelYear = 2010

Well, this should put you in a good place for querying an ASO database. The Essbase Technical Reference included and installed with the EAS on your client PC contains all of the information you need on the actual query functions and member set functions.

Executing an MDX query

Now, here is a question that is begging to be asked. How do we execute an MDX query?

The answer is easy enough. You execute an MDX query in exactly the same fashion as you execute any MaxL statement. Remember, both DDL and MDX are really MaxL scripts. The only difference between MaxL DDL and MaxL MDX is the editor used through the EAS tool.

In the previous screenshot, we showed you the MDX editor that you can access from the EAS tool by clicking on the EDITORS menu selection and then selecting MDX.

When you are using the MDX editor, you have the same nice features as the other editors that are included in the EAS tool. It is through the editor that you can also execute your MDX statements.

The other way to execute your MDX statements is through the command line prompt.

Honestly, if it wasn't for the major outline differences between an ASO database and a BSO database, you could travel through your entire Essbase career and not really need the MDX piece of MaxL. Because the ASO database structure can actually be at least conceptually compared with a relational database more than a multidimensional database, it's a good idea to get friendly with MDX.

Tuples and Sets

A Tuple is a collection of members from different dimensions that represent a slice of data from the database. A tuple can contain one member from each dimension. A tuple should be wrapped in parentheses when written. In theory, each cell in the Essbase cube is defined by a tuple with one member from each dimension. A single member is also considered a tuple.

A tuple is an easy way to describe a slice of data from an Essbase database. A correctly written tuple can contain very few actual words, but can describe a large amount of data.

Here is an example of a tuple:

(Total Market. United States)

The tuple above will return all data at the Total Market level for the United States.

Null Dimensions or Null member references are not valid tuples.

Sets are an ordered collection of tuples. A set can be empty, have one tuple, or it can have more than one tuple. Duplicates of tuples are allowed. Sets are enclosed in curly brackets.

   

Here is an example of a set:

{CALENDER PERIOD.2008} – Defines one Tuple
{CALENDER PERIOD.2008, CALENDER PERIOD.2007} – Defines sets of two
Tuples

Pros and cons of ASO and BSO

There are some differences between the ASO and the BSO. Even though there are differences, we would like the reporting output to be the same and the analyst who is running the report should not even know to which type of database he is connected. Let us take some time to look at a few of the pros and cons between these two types of databases.

Pros and Cons of BSO

The Essbase BSO is the original format of the Essbase database technology. The BSO is also the mainstay for Essbase as it offers robust, full-featured functionality. Here we will describe what we feel are some of the positive features of the BSO as well as some of the not so positive features for you.

Pros

Listed below are just a few high-level features that we feel makes the Essbase BSO a good choice:

  • Several databases stored in one application.
  • No reserved names for application and database names.
  • Account dimension supports all types of calculations and attribute members.
  • Calculation scripts are supported.
  • Uncomplicated write back ability.
  • Formulas are allowed in all dimensions with no restrictions.
  • Outline changes do not automatically clear data values, even if a data source is used to both modify members and load values. Therefore, incremental data loads are supported for all outlines.
  • Currency conversion is supported.

Cons

Listed below are a few high-level features that we feel you may need to be wary of when using the Essbase BSO:

  • For better performance, the outline dimensions must be defined as Dense or Sparse, based on data density, which can sometimes be difficult to get exactly right.
  • Database calculation—calculation script or outline consolidation.
  • Calculation order will need to be defined in the calc scripts and is predetermined in a default outline calculation.
  • Unrestricted write back ability which can be dangerous if care is not exercised.
  • No automatic update to values after data load. Necessary calculation scripts need to be specially executed, including any default calculations.
  • Sometimes requires large amounts of resources.

Pros and Cons of ASO

The ASO is fast becoming the standard for extra large Essbase databases. Where the need for high speed data retrieval for reporting and analysis can eclipse the need for full-featured functionality, ASO fills in nicely. Here we will describe what we feel are some of the positive features of the ASO as well as some of the not so positive features for you.

Pros

Listed below are just a few high-level features that we feel makes the Essbase ASO a good choice:

  • Easy optimization, massive data scalability, reduced disk space, and up to 100 times faster.
  • Database creation is accomplished by either migrating a BSO outline or defined as new after application creation.
  • Outline dimensions will not need to be designated as dense or sparse.
  • Outline is validated every time a database is started.
  • Database calculation or aggregation of the database can be predefined by defining aggregate views.
  • Calculation order is not relevant for database calculation, but is relevant for dynamic calculation formulas.
  • Limited write back ability.
  • At the end of a data load, if aggregation exists, the values in aggregation are recalculated and updated automatically.
  • Aggregate storage database outlines are page-able. This feature significantly reduces memory usage for very large database outlines.

Cons

Listed below are a few high-level features that we feel you may need to be wary of when using the Essbase ASO:

  • Aggregate storage applications have some limitations that do not apply to block storage applications with regard to consolidations, calculations, and overall robust functionality.
  • Can store only one database per application.
  • Names reserved for table spaces cannot be used as application or database names.
  • Accounts dimension does not support time balance members and association of attribute dimensions.
  • On non-account dimensions, there are restrictions on label only members and dynamic time series members. Members tagged as dynamic hierarchies have no restrictions on the consolidation settings. Stored hierarchy members can only be tagged as label only or (+) addition.
  • Non-account dimensions support only consolidation operator (+) addition.
  • Calculation scripts are not supported.
  • Formulas are allowed only on account dimension members and allowed with certain restrictions.
  • Only Level 0 cells whose values do not depend on formulas in the outline are loaded.
  • Data values are cleared each time the outline is structurally changed. Therefore, incremental data loads are only supported for outlines that do not change.
  • Currency conversion is not supported without the use of special MDX queries. This method can have a negative effect on performance.

As you can see, there are some substantial differences and some very good reasons to use one type of database over another. To give you our idea of the ideal application of ASO and BSO, read below:

  • ASO Database: The ASO database is ideal for dynamically built Essbase cubes that are usually Read Only and used for reporting, presentation, and analysis. This type of database would also tend to have a rather large outline where at least one dimension has a significant amount of members. A parts dimension or product dimension comes to mind.
    Behind this ASO database would be a large BSO parent Essbase database, from which the dynamic ASO databases are built on the fly.
  • BSO Database: The BSO database is ideal for virtually any size cube, but where performance is not necessarily the number one priority. Accuracy and completeness of data would be the main consideration. The BSO database is ideal as the large parent database where users from many different departments can trigger jobs which will dynamically build ASO reporting cubes on an as needed basis. The typical BSO database is ideally suited for financial analysis applications.

Of course, these are just one possibility or scenario. The beauty of Essbase is that you can do most anything with it. Heck, you could easily have a large Oracle relational database as the backend data source for your ASO cubes.

The possibilities are endless!

Summary

We have covered a lot about the Essbase Analytics which includes the Block Storage Option (BSO), the Aggregate Storage Option (ASO) and MDX query language. We have also discussed the pros and cons of the Block Storage Option (BSO), the Aggregate Storage Option (ASO).

We hope you have found the information in this article as fun, exciting, and useful as we have. Now, it should be no wonder to you why Oracle Essbase is the number one data analytics tool in the world! It's almost as though Essbase is a product that is never finished, because just when you think it can't get any better, it gets even better!

You should now have a good understanding of the two very different but complimentary Essbase database architectures. The BSO is the old standby foundation in the world of Essbase, and the ASO is the streamlined speedster.

The differences in many cases between ASO and BSO are minor, but their affect can be dramatic. The BSO utilizes the standard data block method of storing data, while the ASO uses the aggregate method. The BSO is built for rugged and robust computing and analysis. The ASO is built for high speed, high volume data analysis and reporting.

We're sure you see by now that whether you use ASO, BSO, or both together, you can be confident you have made the right choice.

If you have read this article you may be interested to view :

About the Author :


Joseph Sydney Gomez

Joe Gomez has been an Essbase developer, designer, and administrator for almost 10 years. Originally educated as a Graphic Designer in the field of Computer Graphics Technology, Joe took a job as a mainframe Y2K bug fixer and the rest is history.

Joe currently works as an Essbase technical specialist and is his company's OLAP Center of Excellence lead. Not a complete computer geek, Joe also enjoys basketball, fishing, bicycle riding, and photography. Volunteer work at a senior citizen apartment, and occasional free-lance design jobs fill out the picture. Oh yes, Joe has a special interest in collecting antique glass telegraph insulators.

Sarma Anantapantula

Sarma Anantapantula currently works as an Essbase Consultant in the OLAP Center of Excellence at the Ford Motor Company. He has over 11 years of experience in the Software industry as a developer, designer, and administrator and has worked in various technologies involving client-server architecture, and Data Warehousing projects (tools like HOLOS and Essbase). Sarma also has expertise in web interface development (both Microsoft and J2EE).

Sarma is a board member of the Hyperion User Group (http://www.hug-mi.org), and has presented on how Essbase is implemented at the Ford Motor Company. He has also published on "Executing DTS Packages from ASP" in ASP Today.

Sarma is well known for his magic fixes; he has a fix for any kind of issue in any technology. In his free time, Sarma likes to spend time answering new technology questions in user forums. If he is not in front of computer he will be playing ping pong or chess with family and friends. He also likes listening to The Beatles, and reading English novels. Sarma is known for being ever smiling and friendly, and can be reached at Sarmaa@gmail.com.

Books From Packt

Oracle Coherence 3.5
Oracle Coherence 3.5

Oracle Warehouse Builder 11g: Getting Started
Oracle Warehouse Builder 11g: Getting Started

Oracle VM Manager 2.1.2
Oracle VM Manager 2.1.2

Oracle 10g/11g Data and Database Management Utilities
Oracle 10g/11g Data and Database Management Utilities

Mastering Oracle Scheduler in Oracle 11g Databases
Mastering Oracle Scheduler in Oracle 11g Databases

Oracle SOA Suite Developer's Guide
Oracle SOA Suite Developer's Guide

Processing XML documents with Oracle JDeveloper 11g
Processing XML documents with Oracle JDeveloper 11g

Oracle SQL Developer 2.1
Oracle SQL Developer 2.1

Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software