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.

Ruby Speaks MAXL

Look what I just found, Ruby MAXL. Props to Jon Israelson.

The Ruby/MaxL extension module allows Ruby programs to directly interact with Hyperion Essbase OLAP databases using Hyperion’s MaxL command language. Ruby scripts utilizing this module are portable across both Unix and Win32 platforms. Ruby/MaxL can also be combined with the Ruby/EssApi module (coming soon) to tap into the full Essbase API without having to write any C code.

..And I Helped

Information Age has named Essbase one of the top ten innovations of the past ten years.

Hyperion Essbase was ranked in the top 10 along with Netscape, Blackberry, Google, virtualisation, Voice Over IP (VOIP), Linux, XML, the Pentium processor and ADSL.

Writing in Information Age's 10th anniversary issue, editor Kenny MacIver said: "Hyperion Essbase was the multi-dimensional database technology that put online analytical processing on the business intelligence map. It has spurred the creation of scores of rival OLAP products -- and billions of OLAP cubes."

I don't know about billions, but there's gotta be a bunch of them out there.

A this juncture I've been thinking of two possibilities with regard to the future of BI. Clearly the OLAP paradigm is alive and will continue, but you've got to wonder what Google has up their sleeve. So let us imagine that there's a way to tweak Google appliances to watch intranet traffic in a way that's referenceable. So meta-BI can assist. I'd watch their lexical parsing stuff for new possibilities even though I don't see a practical demand for it.

Secondly, I think we are just about to reach the point at which data mining (first visually, then with sampling) is going to become a bit more mainstream. The reason for this is that Essbase snd MSAS are about to scale past a significant cognitive barrier - which is all the dimensions you can think about. Recall that in 1992, a 1GB datawarehouse was considered pretty big. At least it did for the DB2 mainframe guys. Now that corporate IT understands what it can do, requirements are coming from the grassroots which are more sophisticated. (Finally). We are clearly at the point where best practitioners are not afraid of these requests, and DW shouldn't fail as miserably as it has.

What's going to emerge is the *real* path which is Active Data Warehousing, the stuff that Teradata has been all over. From this, we're going to get some archtypical models for analysis in every industry. I myself am publishing some models within my practice next month. We're getting an infrastructure guy and we're going to have some nice boilerplate stuff to merge up with Hackett's benchmarks. It's going to be fairly awesome as we start belting out models in a variety of customers.

I'm not supposed to talk about the next generation platform from Hyperion, but I'll just say this: The OLAP wars are over. The next 18 months is going to be the end of it. Now it's all about TCO and plug & play. That's what I said, plug & play OLAP. Or as IBM would say OLAP 'On Demand'. The learning curve is about to go flat.

Multi-Path for Hyperion Planning

A rather surprising development in the aftermarket for Hyperion Planning. Here is an idea that we've kicked around a bit and will probably implement on a large scale.

The basic idea that helps me get my mind around advanced complexity in Essbase and Planning apps is that of Multi-Path. The concept is simple. The sum total of data that you may need to make a decision may have varying levels of volatility and accesibility. So diagram their data paths separately and manage their merging into one application using multple cubes and partitions between them.

We're always talking about 'disparate data sources' and ending our paragraphs with 'one version of the truth'. That's the case, but let's look at a baseball analogy with Essbase as the catcher. Most of the time, you're getting the ball at high speed from the pitcher. You have some control over that and your gear has you prepared for that all the time. Call that the main stream of data. But occasionally, the batter will popup behind the plate. That ball is a very important one to catch but it doesn't come very often. What do you do? As a catcher, you stand up, flip up you mask and move to the spot where you can catch it. Different timing, different expectations, different path. Sometimes the batter bunts. Then you've got to run up on the ball and barehand it and throw it to first or catch the advancing runner. That's another path.

Back in the real world, what we've done is integrate an Actuals Path with a Budget Path with a Forecast Path. The budget path goes through multiple iterations before it's stable. That's low volume user originated information. Forecast data uses some actuals data and does an automatic calculation based on variance to plan and then populates another scenario. This is automated from within Essbase. A second path. Actuals are read only and come through the ETL system. In this case we use an ASO cube which is marked read-only and we view it through a partition.

Using a separate cube viewed through a partition allows Planning to manage, in relatively small cubes, the Budget and Forecast data. We avoid long calcs during the overhead work of Planning refreshes.

It takes a little bit of thought and extra work to synchronize the models and manage the partition, but it does increase the scalability of Planning apps and vastly improves performance. Think about it. Just manage each of the data paths according to their requirements and deliver the proper results on time to your hybrid Planning app. Sweet.

ASO Love

They say that necessity is the mother of invention. Necessity has been a mother for the past week and I have found myself face to face with some detail of Essbase Aggregate Storage Option. I have emerged with a clearer understanding and greater appreciation for it.

In the presentation we made to our prospect, we found that there were about three singular criteria that made ASO the preferred choice in the context of their application design. The group we met with were zealously focused on performance, having lived in hangtime hell for several years in another non-Essbase system. Since they required write-back to ASO, something a few of us know how to do with some acumen it changed the normal singular criterion to three.

Normally, there's only one question you need to ask about ASO. Is this a rack & stack application? If the answer is yes, meaning you don't need any complex or procedural calculations, than go with the flow and roll with ASO. What do you get? You get basically everything ROLAP vendors have been crowing about since day one: dimensional scalability. You get it in two ways. In one way you get millions of members in your dimension. In the other way you get to use more dimensions in a single model than are humanly comprehensible. You want 15 dimensions? Go for it. You want 12 million members in a single dim? No problem. What I've heard back from the rumor mill at the Hyperion Sales Kickoff is that with 64 bit, models can get crazy huge.

Since we have an encapsulated app, we were able to design, fairly simply, a writeback capability which is similar to what we percieve is MSAS' current ability. I won't give away the details but basically we can do it with the additional restrictions that we could have about a 10 second gap between interactions against the written-back cube, and that writes be single-threaded.

That's it. ASO writeback is doable in that context.

Quite frankly, I'm pleased enough at the fact that we can use most of the Essbase API for running ASO models. Having developed several apps over the past few years with MSAS, I can tell you that developing dimensions for those models was a real pain in the butt as compared to using Essbase App Manager. And although I have a gripe (that I believe that Eric S. remembers) that I sorely miss the ability to cut and paste hierarchies from Excel directly into the outline, EAS fits the bill just right. EAS + the Olap Underground's Outline Extractor is a great productivity combo that makes developing cubes easy and fun, no matter how complex the defintions become.

I must confess, however, that I have yet to find any front-end I find as nicely ubiquitous as MSSQL's Query Analyzer and Enterprise Manager. Since I can whip up an ODS on the spot (and I usually have to) with MSSQL, I have all the relational support I need. However it would be nice to have equivalent tools for MySQL. I'm going to make a point to do some searching this weekend. (suggestions are welcome).

So now ASO is a closer friend, I'm going to make more use of it. It still seems like magic.

Beating the Monolith

A story I like to tell was about my visit several years ago to State Farm in LA. The guy we met with was a real hot shot. He had a very good handle on his data, and had presided over the creation of about 37 dimensions of analytic data using FOCUS. He was ready for OLAP.

The problem was that he wanted to put everything in one cube. It took about 2 hours of intense discussion until we made a breakthrough. It went something like this: We wrote all of the dimensions he had on the board and talked about their meanings and hierarchies. Like most insurance companies, State Farm had about 12 dimensions of customer demographics, and were looking to add more. I figured out that our guy was thinking of his dimensions in clusters and his measures too. This was my first clue. So I asked him why he needed 12 demographic dimensions in all his models. "Because it's OLAP!", he said. Wasn't that the point?

As he started talking about the dimensions of Housing Type we came across an interesting attribute. It was 'Distance to a Fire Hydrant'. What? It turns out that when it comes writing lines of fire insurance, this is important to know. Breakthrough. I asked if it makes a difference whether the homeowner is male of female. Nope. Smoker or non-smoker? Yep. But the most important dimension, as far as the fire analysts were concerned, was type of roofing. It doesn't matter who you are, if your roof is made of straw, your fire insurance premium is going huff and puff and blow you away. So we ended up chopping down the number of dimensions of interest down to a reasonable size, and therefore had a cube design that wouldn't break the bank.

I've long argued against the human cognitive ability above 9 dimensions, but I think that subject matter experts (if no one else) can manage that many variables in their heads. The point of augmentation is to get the computer to help us manage more information than we normally would do, so I'm not so tough on that score any longer. Especially since I now know tools that can handle that many with ease.

Yet we designers still have to fight the tendancy for people to expect a be-all end-all cube. The way projects are managed contribute to this. Here are some helpful tips.

First off, get used to the idea of having purpose-built cubes. If you think one cube is going to serve everyone, you're going to be disappointed. You want a system that enables you to deliver everyone their own garden in the cube farm.

Secondly, get used to putting up or shutting up when it comes to hardware. It just may be the curse of Essbase, but running it on old beat up or shared hardware is not acceptable. A new colleague was raving this morning about Teradata performance. He said, all you have to do is snap in a new node and anything you need, you get. A Teradata node is 1 million dollars worth of hardware. You don't even talk to Teradata until you're ready for that level. In many respects that is what Essbase demands - first class hardware. Not a million dollars worth mind you, but any time I see Essbase on a server smaller than the ones in my house...

Thirdly, people have to stop assuming that Essbase can be maintained on a part-time basis. I've always said that Essbase is the Porsche of its class, and yes it's true that anyone with a driver's license is capable of driving a Porsche... off a cliff. Hyperion has done a good job in upgrading the look and feel of EAS such that it is appropriately intimidating to newbies, and it expresses the complexity inherent. That complexity needs to be managed by people dedicated to the database technology.

These three hurdles point, inevitably to the creation, development, production and retirement of databases. Not just putting everything into 'Hyperion'.

What requires a bit more understanding is designing for Analysis, which is a discipline most database architects have not studied. Eventually I am going to write the book. Cubegeek readers will be the first to know.

Thus endeth the lesson.

Essbase V5 Tuning Top Ten

(From the Archives, March 1998)

The following are simple methods that will increase performance under Version 5. None of these involve partitioning which can add much greater performance but require extensive administration.

1. Dynamic Calc
2. Block Density
3. Cache Settings
4. Intelligent Calc
5. Paging & Memory Limits
6. Dense / Sparse
7. Calc Script Efficiency
8. Server Contention
9. RAID 5
10. Load & Calc Method

Essbase Capacity Planning

This entry is a pointer to a couple capacity planning documents. The first one I created was back in 1997, which I updated last in 1999.

The 1999 Capacity Planning Document is embedded in the extended entry. (Format sucks)

The updated version will be linked here.