Oracle Essbase 9 Implementation Guide — Save 50%
Develop high-performance multidimensional analytic OLAP solutions with Oracle Essbase 9 with this book and eBook
This article, by Joseph Sydney Gomez and Sarma Anantapantula, gives us a brief introduction about Online Analytical Processing (OLAP) as well as the method to create an application and database.
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?"
eBook Price: $35.99
Book Price: $59.99
Creating your first Essbase application
When viewing information in the EAS, you will notice that it is setup in a similar fashion to Windows Explorer, with a graphical hierarchical tree structure.
To create an Essbase application, click on the File menu in EAS and select New. You then have the choice of selecting either the BSO or the ASO storage options.
For our Esscar Motor Company example, we have selected BSO as our storage option. This is where you also have the option to choose either Unicode or Non-Unicode. We will be using Non-Unicode for our application. Now, give a name to your application, say, ESSCAR.
In a Non-Unicode application, Essbase supports upto 8 characters for the names of all Essbase objects like application names, database names, data load rules file names, and calculation script names.
In Windows-based installations, using spaces in database object names and their associated directory paths should be avoided at all costs. Coding can sometimes be challenging when spaces are used.
Click OK. Hurray! You have now created your first Essbase application. Now that you have created your Essbase application, let us take a quick look into the Essbase application properties.
Essbase Application Properties
As the name suggests, Application Properties will allow you to set the properties for an application. You can update the name of your application, set the start up options, and also set the initial security levels. These properties can only be changed using the EAS by any user who at least has an access level of Application Designer to the application being updated and by automated scripts like Essbase command scripts or Essbase MaxL scripts.
There are two ways to get to the Application Properties:
- Click on the name of the application to select it, then click on the Action menu on the EAS menu bar. Now, click on the Edit Properties for "Esscar".
- Click on the name of the application to select it and right-click over the application name. Here, you will also see Edit Properties for "Esscar" as one of your choices.
This property is used to tell Essbase how to automatically start the application. There are two ways you can start an Essbase application:
- Allow users to start application
- Start application when Essbase Server starts
By default, Allow users to start application is selected and we advise you to leave it as it is. Only select the second option when it is absolutely necessary for the Essbase server to start your application. As you have previously learned, if an inactive application is started it will unnecessarily consume system memory. One of the only times it may be necessary to have an application start (when the Essbase server is started) is if you have an automated batch process which starts immediately following a server restart. This might be performed during routine server maintenance or housekeeping.
There are several different types of security commands. They are listed as follows:
- Allow commands: If checked, this allows users to run commands on the database in the application. This option is selected by default.
- Allow connects: If checked, this allows all users with proper access to connect to the application. When unchecked, only the Application Designer, or higher users, are allowed to connect to the database. This option is selected by default.
- Allow updates: If checked, this allows the users with appropriate access to update the data in the databases. This option is selected by default.
- Enable security: If this option is unchecked, then Essbase ignores all of the security settings and gives Application Designer access to all of the users. This option is selected by default and we recommend you keep it that way.
Minimum access level
This setting sets the minimum access level for all databases within an application, and grants this access level to all valid IDs created on the server. The setting can be overridden at the database level, but it's really a good idea to leave it set to none at the application level. This property will be discussed further, in detail, in the database properties section of this article.
Create your first Essbase database
You have your first Essbase application created and waiting. You have a good high level understanding of the types of Essbase databases that can be created. Let's now create your first database using EAS.
Select the Esscar application and right-click on it to bring up the application menu. From the menu, click on Create Database to bring up the following screen:
On the screen above, make sure you have the correct analytic server selected. Select the correct application (Esscar). Give a name to your database. In this case we will name the database ESSCAR (it's the same name used for the application).
Remember, Oracle Essbase only supports object names upto 8 characters.
Leave the default setting of Normal and do not check Allow Duplicate Member Names. Click OK and you now have a bouncing Essbase database. Congratulations!
Next, click on (expand) the ESSCAR database name shown under the ESSCAR application in EAS to reveal the database object selections that were added when the database was created.
Right-clicking the ESSCAR database reveals several more menu options that are available to you. Click on the Database Properties selection to bring up the Database Properties screen shown as follows:
In the Application Properties screen as discussed previously, you see many database level options or properties that can be set or adjusted to suit your own computing or performance needs. We will take a moment to briefly discuss each available tabbed option on this screen, and the choices contained therein.
On this tab, the database name and description are displayed. Only the description field is editable and can be changed at will. The database name can be changed through another function not found on the properties screen (right-click on the database name in EAS and the Rename Database option will be available).
There is also startup information as shown on the Application Properties screen. In order to have optimal performance, leave the Allow users to start database checked and uncheck the Start database when application starts selection. There is usually no need to have a database start when its parent application starts.
The default calculation settings are best for now. The Aggregate missing values and Create blocks on equations both have database block size implications and should be used with extreme care. We leave the wo-Pass calculation option checked, because it allows you to code a member to use two-pass calculation functionality. You are not forced to use it just because the option is checked on this screen.
It is highly recommended you set Minimum Access Level to None, as all users must then be granted specific access to each database. The other choices are Read, Write, Calculate, and Database Designer.
Data retrieval buffers are settings that help with the performance of the spreadsheet add-in, and data being extracted with a report script object. More on these will be discussed in detail later.
eBook Price: $35.99
Book Price: $59.99
In the Dimensions tab, you are presented with information on your database outline, with regard to the individual dimensions and their designation as either sparse or dense and the number of members contained.
The Statistics tab is a Read-only tab, but is very handy as it displays a wealth of useful information. The following screenshot illustrates this:
Its better to look at the real screen for yourself through the EAS. When it comes to performance tuning, the settings on this screen will be invaluable!
Correctly set caches can make the difference between your business partner wanting to fire you or wanting to adopt you. We will discuss these important settings later on, but for now, just know that the default settings will be adequate for most moderate sized cubes with moderate sized calculation or reporting needs.
One suggestion on caches, unless you are using the direct I/O data load option which we will discuss in detail later, is to never check the Cache memory locking option . The Cache memory locking option locks the maximum amount of memory for each cache, whether the database uses it or not. If your system is running that close to maximum capacity, you better purchase some more memory in a hurry!
Boring! Oh, sorry, this is a necessary option, but it's a setting that makes adjustments which never really help or hinder performance all that noticeably. Committing data after a certain number of transactions and not locking the entire database, when calculating does have merit. We have never really had to play with these settings on large or small databases. You would need to have some sort of extreme situation involving conflicting and concurrent processes to facilitate any adjustment to these settings.
Here is one database property that definitely has merit! These settings affect how and where Essbase stores your database page and index files, the compression used, and the type of I/O used to read/write the data.
For starters, the I/O method can have a noticeable impact on performance when large data loads or transactions are involved. Buffered I/O uses the operating system's file system and is the default setting. When more performance is needed, the direct I/O setting can be used. The direct I/O setting provides asynchronous overlapped I/O that gives the user less waiting time.
The Storage tab also has choices for data compression. The setting for bit-mapped compression is the default setting and is also the most efficient because it only stores cells that contain data. Any null values are not stored. There are several compression methods supported and Essbase can even run with no compression. However, it must be noted that Essbase will expand, to full uncompressed file size, any data page file it needs at the time of the request for data.
If you recall from the previous discussion on the normal and currency cubes and their uses, this tab is where you would create the symbiotic relationship by defining what currency cube the normal cube will be using for its currency conversions.
Within this tab, Essbase will record any changes or modifications made to the database, the data, and the outline in log form. This screen is useful if you need to check when a recent event occurred, possibly for debugging
Thus, we have learnt how to create our first Essbase database and first Essbase Application. We have also covered different properties of various elements.
If you have read this article you may be interested to view :
About the Author :
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 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.