Developing Essbase Applications: Hybrid Techniques and Practices

Book Review

Developing Essbase Applications: Hybrid Techniques and Practices

I had a colleague many years ago who always talked about writing a book about Essbase. It never materialized of course, because it's really hard to do. First of all there's the most excellent DBA Guide reference which tells you most of what there is to know. Second, the developer community is tiny in comparison to technologies like Java, C, SQL Server, etc. So kudos go to Cameron Lackpour and his co-authors on a writing a second book on the subject of Essbase.

The book is made up of eight diverse chapters on subjects in the Essbase universe. They are able to be separated out so I won't review the book as a whole but rather will give my thoughts on each individual chapter. One major item to note is that the title is a bit misleading. "Hybrid Techniques and Practices" is not really the focus of the book but instead of one of the chapters.

Essbase on Exalytics

Without hardware, software is not possible. Developers tend to take hardware for granted and sometimes don't pay attention to it unless there's a problem -- or unless they're looking for something to blame. This chapter brings the discussion of hardware to the forefront and does a very good job of explaining who needs to buy an Exalytics box and why. John shows how to configure an Exalytics machine so you're taking advantage of all that power. This chapter is required reading for anyone putting together an Exalytics business case or implementing a solution on that platform for the first time.

Hybrid Essbase

In this chapter we get a break down of the latest Essbase hot topic. This should be required reading before even thinking about implementing Hybrid as Tim and Cameron are very upfront about the current short comings of Hybrid. The chapter gives a very good overview of how Hybrid probably works under the covers and for that I'm very thankful. If I had to voice one criticism of this chapter it would be that it was written prematurely. I remember ASO coming along and not touching it for several years. In my opinion, it wasn't ready to be used in most Essbase shops as the initial limitations were overwhelming. People still used it and perhaps that's why it got better. Hopefully this chapter will help to accelerate development at Oracle on this new technology by pointing out the, in my opinion, glaring weaknesses it currently has. It is my hope that this chapter will be updated sooner rather than later in some form or fashion showing how capable Hybrid has become and how the limitations have disappeared.

The Young Person's Guide to Essbase

I'm not sure if the name of this chapter is appropriate as it seems geared more toward someone with little Essbase experience rather than a young person. This is twenty years of Essbase wisdom boiled down to its essence and condensed into a single chapter. This is the chapter you give to an IT Project Manager who is helping to implement Essbase for the first time. I wish that I could have given this to several clients and colleagues over the years. It would have saved many hours of explanation.

Essbase Performance and Load Testing

I know that I need to do performance and load testing but it always seems to be the first thing skimped on during a project. This chapter is useful to understand the conceptual challenges with designing a quality data set for Essbase testing. It would be very useful for a testing analyst with little Essbase experience to read before embarking on the testing phase in an Essbase implementation. I'll be stealing using some of the ideas in this chapter when comparing the performance of different Essbase techniques in the future. A tip of the cap for including some Perl code in the chapter.

Utilizing SQL

I love pulling data from SQL when creating Essbase cubes. It usually allows me to focus on design rather than messing around with file formatting, transferring and such. Not every shop wants to use it -- some only want to deal with flat files. As a result I seem to go from a project that uses it to another that doesn't and I quickly forget everything I knew. I scramble to do searches on all of the basic SQL commands to re-familiarize myself each time. Glenn does an excellent job in giving examples for about 95% of the SQL you will ever need to use during an Essbase project. This is the chapter you'll want to keep handy when starting a new project that uses relational tables. There's no question in my mind that this will save me time in the future.

Copernicus Was Right

As the author points out, "This chapter is intended, primarily, for the Essbase consultants who are starting to work with OBIEE." I have no doubt this chapter will serve as a valuable primer on the subject for those who find themselves in that position. If your company is evaluating whether or not to integrate Essbase and OBIEE or you're just looking at ways to improve the marriage of your summary and transaction level data then this chapter should prove useful.

Managing Spreadsheets through Dodeca

Dodeca is an Essbase front-end from Applied OLAP. If you're a Dodeca user then you'll want to read this chapter. If you're looking for a way to control, audit and manage spreadsheets in your organization, you'll want to check this product out. I have not worked in an organization that uses Dodeca but I've heard many testimonials from customers that love it. I've also corresponded with Applied OLAP owner Tim Tow on Essbase technical forums for the past 15+ years. He does quality work and is extremely responsive to customer needs.

Smart View Your Way

This chapter scared me a little. Reading it will take you from being an experienced Smart View user to an expert. I'm hesitant to recommend this to an analyst because it might just create a monster. I really hate getting called in to fix complicated spreadsheets with VBA code and I'm afraid urging someone to read this chapter might give them the wrong idea (although to be fair, warnings against such recklessness are included). Nevertheless, there are a lot of valuable techniques and information included. As I am not currently in the role of an analyst, I use Smart View to do some occasional data analysis and number checking. Share it with your users at your own risk.

Conclusion

If you're an Essbase developer, buy the book. Ideas and techniques in it will be referenced in online forums and in conferences for the next few years at least. Also, don't ignore the book if you're skeptical of the Hybrid Storage Option. By the same token, don't expect a book solely focus on Hybrid.

Classic Design -- The Essbase Excel Add-In

What's better than a laid back Friday just before a long holiday weekend? Maybe a philosophical discussion on the nature of design.

High quality designs tend to last a long time. I'm a fan of documentaries and a couple of design related films come to mind: Helvetica and Welcome to Macintosh. These films talk about two things that have incredible designs -- one a font and the other a machine. Both have lasted quite a while and are very popular. Macintosh sprang from design work done at Xerox PARC. They essentially invented GUI but couldn't bring it to market at a low enough cost. Ideas are great but execution is the key to success.

Some designs are beautiful. I'm awestruck whenever I enter a Frank Lloyd Wright designed home. Each one I've seen speaks to me as a manifesto on quality design. Attention was paid to every detail from the view you have of the building as you approach all the way down to the lampshades and electrical sockets.

Some designs are ugly, or at least they seem ugly. Put someone in front of a Unix terminal showing the command line and they might cringe. Put me in front of a Unix terminal and I'm at ease. This operating system is older than I am and is essentially unchanged. Sure it has been improved over time, it handles 64-bit hardware now and you can put a GUI in front of it if you really need to, but at it's heart it is the same. A beautiful, long lasting design, held in high esteem by those willing to take the time to learn the power available. The speed of getting things accomplished is amazing when one doesn't need to ever lift their hands from the keyboard to fidget with a mouse.

This brings me to another lasting design -- the Essbase Excel Add-In. This interface is essentially unchanged since I began my Essbase journey. Yes, it has moved on to be called Smart View, but it uses the same principles.

I recall the first Essbase training class I ever taught. I had a lab with about 30 students in it. Essbase was completely new at this company and I'd be teaching people totally foreign to the product. I still remember the sound when people discovered that they could double left-click to zoom-in and double right-click to zoom-out. The room went nearly silent. All I could hear were hundreds of mouse clicks. There was no talking, just clicking. These people had just been given an incredible tool that would change the way they do their jobs. It would improve their productivity.

Here we are twenty years after it was designed and my users do not even want to try Smart View because it means they would have to part with their beloved, beautiful, simply designed Essbase Excel Add-In.

MDX Monday - Running MDX in Smart View

I've been a bit rough on Oracle for their MDX output -- I think with good reason. Trying to use it in a production batch environment is challenging. But there is some good news. Most of us have a tool at our disposal that gives beautiful MDX output: Smart View!

Simply right click on a connected database in the Connections Panel and select Execute MDX.

Then paste your query into the dialog box and click Execute. There's no need to include the database name specification or the semi-colon.

Here are the results. I got a neatly formatted Excel sheet and Smart View even put the where clause members into my POV! Very nice.

What happens when I use the calculated columns query from this post?

It works! Unfortunately you lose the calculated fields when you refresh the sheet.

It would be great if we could print member properties too. If we could do that, then we could do things like export dimensions in a parent child format directly into Excel. Let's try it. (This is the first query in the post with a PROPERTY_EXPR addition which should print each Market member's parent.)

<sad face> Smart View appears to ignore the properties returned in an MDX query. We don't get an error but we also don't get what we want. Hopefully there will be a future enhancement to Smart View that will add this functionality.