Introduction to OLAP
OLAP is the common term for Online Analytical Processing and is generally known to be a multidimensional, client-server computing environment.
The differences between OLAP analytical solutions and traditional data analysis applications containing backend relational databases are stark. The most obvious being an OLAP analytical application’s ability to provide speedy analysis of broad slices of data. Programs which are complex and expensive to write would be required to perform even a fraction of the functionality provided by a simple Oracle Essbase OLAP application.
Another notable difference is OLAP’s ability to drill-down to the lowest level of granularity with ease. You will even hear phrases like, slice and dice, and multidimensionality, which means having the ability to view the data from virtually any perspective. Finally, the ability to calculate large amounts of data on the fly gives users a superior advantage over traditional applications with relational databases when it comes to “what if” and “cause-and-effect” data analysis and reporting.
Oracle Essbase is widely known as a financial analytical tool. We want to change the mindset just a bit, right here and now. Oracle Essbase absolutely is a superior financial OLAP tool, but it is an equally superior OLAP tool for just about any type of data analysis
Determine data storage options
Get ready to toss out everything you’ve ever learned about storing data in a typical relational database with tables, rows, and columns. Keeping the above example of the Essbase outline in mind, we will now begin covering how the data is stored in Essbase and the various options available to you (the Essbase programmer).
An Essbase cube usually stores less physical data than a typical relational database must store to deliver the same results to the user. Usually, the greatest saving is in the expense of data retrieval times. The results returned from a typical Essbase database require less processing overhead than the similar results being delivered as the result of queries performed against relational database tables.
Essbase stores data in what is commonly referred to as a multidimensional array. Inside the multidimensional array are the data cells. It is these data cells where the data is actually stored.
The smallest vehicle Essbase uses to store data is a cell. A data cell however, cannot stand alone. The smallest usable vehicle to store data, contained in an Essbase database, is the data block (see the following figure). These data blocks are the building blocks of the Essbase cube:
A simplified explanation is that the data blocks are made up of data cells. The number of data cells are, for the most part, in direct relation to the number of dimensions in the Essbase outline (the data attributes explained previously), and the number of possible data combinations or intersections that can be created.
In a traditional relational database, one new element of data may require an entire new row of data in one to manytables. Looking at the previous image, you can see that if you need to add stock information on a vehicle, you will need to insert a new row in the Stock table of your relational database.
In Essbase, that same new piece of data is plugged into the waiting data cell that was created in the data block, when the database outline was structured or restructured.
You can add a new dimension to the database outline or add new members to an existing dimension at any time. By adding dimensions to the database outline you are actually increasing the size of the data block. When a data block is created by Essbase, it contains cells for all of the various dimensions whether you have the data at that point or not. In our example, the data block created by the database would already contain a cell for stock, even if you did not yet have a value to store there. When you have a value for stock, it just gets plugged into its data cell and the size of the database is unaffected.
When you add or remove information from the outline and save the outline, Essbase will automatically restructure the database and modify the data blocks (add/remove data cells) to incorporate the new outline information as necessary.
In Oracle Essbase there are two distinct storage options that can be used when creating a database. These storage options are known as the Block Storage Option (BSO) and the Aggregate Storage Option (ASO). For most transactional Essbase applications, the more suitable of the two options is the BSO. For our example in this article, we will create an application/database using the BSO.
It should be mentioned that the size of the data blocks can have a dramatic effect on the performance of the system. It is always best to try to avoid extremely large and complex database outlines. As we explained previously, the data blocks are structured roughly in relation to the possible combinations of data based on the number of members in the database outline.
More members = larger data blocks.
Less members = smaller data blocks.
Oracle Essbase offers an extremely valuable option to help keep block sizes to a minimum in order to help keep your database running at peak performance. The dynamically calculated database member!
The dynamically calculated member is a measure typically derived from other data elements in the database. It is not physically stored in the database. Instead, it is only created (calculated) at the time you ask for it. There are three great benefits for building your database with dynamically calculated members:
- There is a huge potential to create many new measures without adding new sources of data or writing expensive programs to derive the values.
- While the dynamically calculated member occupies a place in the database outline; it does not affect the block size in the database, therefore, it does not affect performance.
- The resultant measure is always accurate to the other measures in the database and will always tally (the derived number will always equal the result of the stored component numbers). There is never a question of “where did this number come from?”