Run Essbase Spreadsheet Reports Quicker with Dodeca’s New EssJet Technology

The Essbase Excel Add-In has been a critical tool in allowing users to easily generate reports from Essbase in the familiar spreadsheet interface of Microsoft Excel. However, as Oracle Essbase and business reporting requirements have evolved, many users have experienced the frustration of the dreaded Excel lock-up errors when processing large volumes of data. We have even seen instances where users of Essbase’s Excel Add-In would use two computers while performing their Essbase reporting processes, so that when one computer encounters the “Microsoft Excel has stopped working” error, they can switch to the other computer to resume their Essbase reporting.

Inherently, the Dodeca Spreadsheet Management System developed by Applied OLAP was developed to address these concerns, and now with the release of Dodeca V8 with EssJet technology we have further improved users’ Essbase reporting experience and capabilities. EssJet technology allows for asynchronous reporting, meaning reports can be broken up and be run in parallel resulting in drastically reduced Essbase query times. EssJet saves Oracle Essbase reporting users significant time and frustration, which translates into an increased ROI on the organization’s Essbase investment.

If you have ever asked, “how do I speed up the Essbase reporting?”, or “is there a better solution than the Essbase Excel Add-In?”, then schedule a Dodeca Spreadsheet Management System today to learn how we can save you time and money by running Essbase reports quicker.

FAQs

How does Dodeca’s EssJet technology improve my Essbase experience?

Dodeca Version 8 featuring EssJet technology reduces the time to generate reports that typically require long query times to pull in data from Essbase. This is achieved by allowing multiple data retrieval areas to be created so reports can be generated asynchronously.

How does Dodeca improve upon the Essbase Excel Add-In?

Dodeca is a separate application from Excel, however it was built using Excel as a foundation so that the user experience and interface is similar. By building Dodeca as a standalone application, we have been able to improve the technical performance beyond that of Excel, so that when Dodeca reads and writes data from Essbase and other data sources, such as SQL, the application does not lock up, like Excel often does when performing similar functions. This means you can generate reports from Essbase AND other data sources like SQL quicker and easier, in the same platform that looks and feels like Excel.

Does Dodeca and its new EssJet technology prevent lock-up issues that frequently occur when running Essbase reports with the Essbase Excel Add-In?

Yes, Dodeca inherently improves technical performance when reading and writing Essbase data due to its architecture. Further improvements have been realized with Dodeca Version 8 through our EssJet technology which allows for parallel execution of queries submitted to Essbase. These improvements have resulted in Essbase users’ ability to run and generate reports with larger volumes of data simultaneously and quickly without experiencing lock-ups during the process.

How many reports can I run at the same time with Dodeca Version 8 featuring EssJet?

The number of reports that can be generated is dependent on an organization’s server processing capacity. We recommend not splitting queries into no more than 8 retrieval areas, however this will vary based on hardware and server configuration.

How does Dodeca reduce Essbase query and report generation time?

In a scenario where a user is generating a report with four grids in an Excel spreadsheet using the Essbase Excel Add-In, each grid typically takes about 15 seconds to execute. Since the Essbase Excel Add-In processes these queries sequentially, this will take about 60 seconds. Since Dodeca and its new EssJet technology can run these queries in parallel, this process will take about 15 seconds, plus 1-2 seconds attributable to overhead for managing those parallel queries. Under this scenario, Dodeca will reduce the time to generate queries by over 70%.

Dodeca with EssJet Technology Full Whitepaper

Executive Summary

For 25 years the predominate way users retrieve and interact with data from Oracle Essbase is with the use of an Excel Add-In. This add-in, which is supplied along with the Essbase engine, has slowly evolved over time. A key aspect of the software is ad-hoc retrieval capability allowing users to format retrievals at their pleasure. With this capability, there are very few governors over how much data can be retrieved. These retrievals can be very large leading to long query times. The nature of Excel is that Excel locks up during these long retrievals. There is a new capability within the Dodeca Spreadsheet Management System called EssJet which allows users to break these retrievals up saving a significant amount of time and freeing up Excel for other uses.

Architectural Overview

The architecture of Essbase is an integral part of the solution that is outlined in this document. In general, Essbase is set up in such a way that each database runs in its own process on the operating system level. When a query is executed by a user, Essbase works to return the data as fast as possible. As long as the database is not disk bound, which is rare if the database is well designed, Essbase will utilize an entire CPU in the execution of that query. Essbase has a highly scalable architecture in that, when a second user executes a query, an entire second CPU will be utilized. Depending on the number of CPUs available, this can scale considerably. In a well-designed system, Essbase can serve up data to hundreds or thousands of users concurrently. It is this parallel design of Essbase that is leveraged by EssJet in Dodeca.

Typically, queries are run against Essbase using the Smart View add-in for Excel. When a query is executed against Essbase in Smart View, Excel is locked and does not respond to user input via the mouse or keyboard until the query is completed. If the Essbase query is short in duration this will not be noticeable to a user. When the queries grow to take five or more seconds, however, it becomes very apparent and can frustrate users. After about 10 seconds, a user will get a message that Excel is not responding. There’s no mechanism within Smart View to send a query to work in the background so a user can continue using Excel. Recent versions of Smart View allow you to open a second instance of Excel but doing so isn’t straight forward for the average user and consumes large amounts of memory.

The Dodeca Spreadsheet Management System also executes queries against Essbase. Dodeca uses the native Excel file format, but operates independently of Excel. It solves the problem outlined above in that each report, also known as a View, in Dodeca runs asynchronously. That is, the building of a view and retrieval or data does not prevent the user from continuing to use the software. A user can start a build, then start a second and third build returning to the initial view at their leisure. Therefore, long running queries can be executed while a user continues to work on other tasks.

Both Dodeca and Smart View allow a user to have multiple grids on a spreadsheet. Smart View will execute a query for each grid sequentially. If there are four grids on the spreadsheet and each takes approximately 15 seconds to execute, the total query time for that sheet will be about 60 seconds. With the Version 8 release, Dodeca can execute multiple queries in parallel. In the previous example, the four grids would be retrieved simultaneously — provided the Essbase server has the capacity to allow a database to use four CPUs. Once Essbase returns the necessary data, Dodeca builds the spreadsheet which adds a bit of overhead to the process. Therefore, what took 60 seconds in Smart View might only take 16 or 17 seconds in Dodeca. A sizable time savings, which is noticeable by the typical Essbase user.

Designing For Speed

Modifications can be made to large spreadsheets to take advantage of the architectures of a combined Essbase and Dodeca system using EssJet. EssJet technology is comprised of two parts: asynchronous and parallel operations. If a spreadsheet already has multiple retrieve ranges, one can test performance improvements by changing two Dodeca View properties. These are found under “Behavior – Asynchronous Execution”. The first is ParallelExecution which is, by default, set to False. This property needs to be set to True for EssJet to be enabled. The second is ParallelRequestLimit. Ideally this will be set to the number of retrieve ranges you have in your View. If there are four ranges, set this to 4. Additionally, ParallelRequestLimit is a way to govern the power of EssJet.

With ParallelExecution set to True and ParallelRequestLimit set to 4, Dodeca will fire four queries to the Essbase server simultaneously. Assuming the Essbase server has enough processing capacity, Essbase will execute these four queries in parallel. The results will be returned from Essbase to Dodeca as they are available. Dodeca must wait until all the query results are returned from Essbase to finish building the View. This is an important consideration when designing for optimal performance. For example, the sheet might have two retrieve ranges – one that takes 2 seconds and one that takes 20 seconds. If they are executed in parallel the view will take a little more than 20 seconds to build. It would be a good idea to explore how you could break that 20 second retrieval into two or more retrievals. Splitting it into four parts, where each smaller retrieval now takes about 5 seconds each, would mean the view would build in just over 5 seconds. A 17 second savings would be realized making the report run 4.4 times faster.

Figure 1a. Theoretical performance of a sheet with four retrieve ranges where the larger retrieve range is split into four parts

A decision about splitting on columns or rows would need to be made when dividing up an existing retrieve range into multiple retrieve ranges[1]. The design of the existing retrieve could have an impact on the performance of the separated retrieves. If the database being queried is an aggregate storage cube, it’s unlikely that it would matter which way the split happens. If the database being queried is a block storage cube, the split could have an impact. Since the optimal performance of Essbase block storage happens the fewer times blocks are queried, it would stand to reason that splitting the retrieval along a sparse dimension would be the best design choice. If there was an existing retrieve that had months across the columns and Time was a Dense dimension, then the optimal design would normally be to split the range based upon the rows. However, if you had a list of Products across the columns and Product was a Dense dimensions, then the optimal design would usually be to split the range based upon those columns. In either case the best split choice would be to divide the rows or columns as evenly as possible. If you were allocating two processors, you’d split in half. If you had three processors, you’d divide in thirds and so forth.

Figure 2a. Time is a dense dimension across the columns in this example. On the left is the original retrieval range. On the right are two retrieval ranges split based upon rows. Each block would only be retrieved once. Rows 17:20 would be hidden from the user. Note that this example is for illustrative purposes only – practically speaking the retrieve ranges would need to be much larger to have any impact on performance.

Figure 2b. Time is a dense dimension down the rows in this example. On the left is the original retrieval range. On the right are two retrieval ranges split based upon columns. Each block would only be retrieved once. Column D would be hidden from the user.

These type of design choices fall into relatively unchartered waters as the parallel query capability has not previously been available to end users. However, using export and data load design theory we should be able to surmise the best course to plot when splitting up existing queries.

One performance impact that will be difficult to account for without actually testing is that of overhead. There are two main components of overheard. This first is related to the Essbase server. As more queries are sent to the server, the Essbase engine must allocate some resources to managing the multiple queries. While Essbase scales rather well, there will be some performance degradation. Due to the range of existing server and database configurations, it’s difficult to predict how much overhead there will be. The second component of overhead is due to Dodeca. The Dodeca client must wait for all of the data to be available before it can build the view. Once the data is available there will be some, usually small, increase in build time depending on the size and number of retrieves. In general, this shouldn’t be material.

Conclusion

The Dodeca Spreadsheet Management System used as a front-end tool for Essbase can be leveraged to deliver query results significantly faster than any previous front-end tool. Using the correct retrieve range design principles along with a properly tuned Essbase cube will save time and increase productivity for users of the system.

[1] Something to consider when splitting ranges by columns would be the suppression of missing and zero rows. It would be conceivable that one range might suppress a row where the second range would have data. This would yield two retrieve ranges where the row members do not match. Therefore, the most efficient way to handle this situation would be to add a Dodeca Procedure in the AfterBuild Event that hides rows that are all missing, all zero or both. This could be done with the use of a formula such as =IF(AND(MIN(B5:D5)=0,MAX(B5:D5)=0),”True”,”False”). The AfterBuild Event would trigger a Cell By Cell Procedure to check the formula results and hide the necessary rows.

Dodeca Version 8 with EssJet Delivers Faster Essbase Reporting & Prevents Spreadsheet Lock-Ups