Cubegeek

Essbase Performance Checklist

This is a modified version of an Essbase troubleshooting checklist I made for the good people at Dell many years ago. Even though it was made for version 5 (and maybe even version 4) a lot of it stands to reason today. Besides, I couldn't resist blogging more Essbase.

                                                               

Item.	Notes & Implications
1. Verify which databases are running and   check access, security and startup information.
2. Check operating system environmental   variables as defined on installation and verify path information.	This can   be the cause of a major. Chances are these are set correctly.
3. Check machine and operating systems constraints   impacting Essbase performance.	Commonsense   dillgence.
4. Determine whether lack of disk space or   incompatible file systems are impacting Essbase performance. On NT systems,   consider separating the physical drive on which the NT page file is utilized   to minimize disk contention with the drives which essbase is using for   paging. A combination of NT settings   and ESSBASE.CFG settings can permit this.	Don’t use   SAN or NFS mounted drives unless they have large memory caches.
5. Check to see what applications and   databases are loaded.	Databases   which are not being used still use memory. It could be useful to have a   periodic script shutdown or quiet applications that are not being used. They   do not automatically shutdown.
6. Check database storage, database   statistics, and lock contention.	Lock   contention is all about writeback users and concurrent loads and calcs. Try   to segregate that activity out.
7. Check database status and startup   information, cache sizes, and data compression settings.
8. Examine the configuration of the currency   database.	Currency   conversion can be a major source of performance issues. If it is possible to   be done outside of Essbase, you should try to do so. The exception would be a   specific currency hedging applications for modeling.
9. Check dimension information and   characteristics of data blocks in the database (block density, percentage of   maximum blocks existing, block size, compression ratio).	9. Check dimension information and   characteristics of data blocks in the database (block density, percentage of   maximum blocks existing, block size, compression ratio).
10. Check run time page for lock contention,   insufficient cache size (buffer shortage), "hit" ratio on index   searches, and number of read and write operations.	This can   make a significant dent in performance. It helps a great deal to understand   the virtual memory model of Essbase.
11. Check to see where index files or page data   files are stored, and whether they span multiple disks.
12. Check record of database modifications for   information On last successful data load, calculation, and outline Update   operations.	Actions   to be taken will Include things such as raising the various caches to   possibly Splitting a database into multiple cubes (rare).
13. On exception, check exception error log   report written in $ARBORPATH directory designation.
14. Check Application Event Log File.
15. Check Server Event Log File.	Consider   licensing a 3rd party tool to collect and analyze essbase log files. Or, consider using VB and Essbase to load   log files into an essbase database for analysis.

Beacon's Essbase Scalability

Here's an old press release from Beacon. I think everybody knows who the customer was. They're still around. I was part of the team that originally got this party started. There are funny old stories we like to tell around that. This was something like 1998 so it was a pretty impressive deal at the time. Interestingly enough, I haven't seen many applications this size in a long time. Now that it's all going to be Oracle, I expect to see some BI smackdowns of ROLAP in the future.

An international retail company needed to track weekly sales and inventory down to a product/color/size combination level for each of its 2,000 individual stores.  All together, there are over 100 million store/product/color/size combinations.  In early 2000, based on a prototype and on client references, the company chose Beacon Analytics to build this application using Hyperion Essbase as their database platform.  The client is using Excel with a customized user interface as the front-end reporting tool.

Business managers need to analyze sales and inventory information by store, by product line down to the style/color, and by week.  The application allows the client to better match their product inventory levels to the individual store sales thereby improving inventory turnover.  Two major store chains are being tracked.  Each store chain tracks about 130 measures, 100 Time elements, 200 Season members, as well as a dozen Fiscal and Price information points.  One store chain tracks 1,000 Store Locations and 150,000 Products and the other chain tracks 2,000 Store Locations and 250,000 Products.  One store chain also tracks Currency for foreign exchange.

Data is reloaded weekly and made available to over 100 business managers across the country.

Beacon Analytics, Inc. built the application that contains over 500 GB of data running on a Sun 10000 server running Essbase OLAP 5.02.  The Essbase model built by Beacon spans 15 physical Essbase databases linked together to form two “virtual” cubes.  Each virtual cube represents a single store chain.

Retrieval times for reporting against the 500 GB database are as fast as 3 seconds.

This application demonstrates the scalability of Essbase, and how it can be used for large volumes of data that industry sectors like Retail typically require.

For more information on how Beacon Analytics can help you implement your OLAP-based Business Intelligence solutions, contact Michael Quataert ([email protected]) at (973) 366-8351 or visit Beacon’s website at www.BeaconAnalytics.com.

Report, Analyze or Audit?

When selecting a reporting tool or optimizing a data mart, I have found that users are looking for three broad categories of output from their queries. There are lots of different ways to break these three categories down, but I think of these as three different types of queries in terms of what the end users actually do.

Report
When I speak of a report, I am thinking that the user wants to see data in a familiar format. Any time a user asks for a particular number in a particular place and he has an idea what that number is going to be, then he is asking for a report. A report in the traditional sense means headers, rows and columns but that's not necessarily the key. A report can almost always be consumed at a glance and key parts of that report are known.

If you sit a piece of paper with numbers on it in front of a manager, it is a standard report. That manager will look at one number and then another and rather quickly come to a decision about whether things are OK or out of shape. This generally happens so fast when a manager is accustomed to a report that we may assume that they are not paying attention. What I have found is that they are actually making several decisions based upon what they see on the page, and in certain cases it may lead them to go to another page. That is almost considered a 'drill down' and it is often mistaken for analysis. But it is not, rather it is a related report or a detailed report. In the end and in either case, the consumer of the report knows where they are going and the answer they are seeking is generally of the sort, Good, Bad or OK.

That is way I consider dashboards to be reports. More specifically, stoplight charts, thermometers, line graphs, trend bars and the like are all reports. They may be interactive, they may be fixed, but in the end what you come up with is a value judgement on a particular part of the business. The difficulty is that it may or may not be a useful part of hte business. It might not tell you nearly enough. But you need reports to be dependable, reliable and formally defined. When you get down to fonts and colors and positions on the page, you're definitely in report-land.

Audit
Audits are detailed reports that match up to something. The end result of an audit is increased confidence in the quality of the data. This is something that managers are not generally interested in, but may come to discover. Audits are all about reconciliation.

The difficulty with audits is that you are generally looking into minutia that doesn't drive the business. It's more like counting fish rather than weighing them. Audits are particularly useful in developing an operational understanding of the business. So you will find that DW requirements that allow for adhoc queries into deep detail are generally fresh to the business and/or have a disconnect between representations of the data in more abstracted reports and the people who are close to that part of the business.

You will often find a disconnect on requirements generation over MOLAP and ROLAP or multidimensional and relational technology over the question of audit. Users who are accustomed to looking at simple greenbar data and making successive filters on queries and storing those are more thinking in the Audit paradigm. The idea of aggregating 10 million rows upon load to small aggregate dimensions may be alien to them. I've also found a reliable indication of the Audit mindset when I have a requirement to show the metadata of the calculated metrics.

Audit detail is always going to be a requirement but it is one that diminishes over time when the business implications of the operational data is clear and the feedback process is institutionalized. In the meantime, get ready to audit individual employee hours, individual journal entries, individual sku counts and the like.

Analysis
Analysis differs from reporting and auditing in that you don't know exactly what your analysis is going to yeild before you begin it. A multidimensional analysis is configured to draw one through a series of dimensional factors which contribute to the size of a particular metric. That is to say it is a continuous disaggregation process which give the user an idea of the causal factors. Ideally, analysis is the reverse of planning. But in any case an analysis is not a result but a process.

Most generally analsyis leads one to exceptions and causalities. So exception reports and alerts can be said to be analytical but only to the extent that they can be indications of multiple contributors. In any case, an Analysis doesn't need involve the user drilling into deep detail if the implications of those details are known. Metrics can be established that reveal deep exceptions at a summarized level.

Analysis requires a known framework. You have to know generically what kinds of exceptions and correlations you are going to expose through your analytical process or else your results may not be actionable. You can end up navigating through huge heaps of data which give you indications then all you can do is say 'so what?'. That kind of analysis is exploratory but not often a good use of IT resources, unless you are indeed mining data.

Data mining is analysis of data that allows you to make connections. These connections should be incorporated first into your planning process if you intend to do something about them.

ASO vs BSO

(from the archives December 2006)

During the course of a long series of performance tests in an exteded Proof of Concept during the Summer and Fall of 2005, we were able to quantify 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.