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.

ASO Test Data or: How I Learned to Stop Worrying and Create Hybrid

I wanted to fill ASOSamp.Sample with some data so I could run a few tests. I wanted to use the completely undocumented "filldb" command within ESSCMDQ in order to do so. Unfortunately, it only works on BSO. So I needed to turn ASOSamp into a BSO database first. There's no way to do that, right? Wrong. A very wise Essbase developer points out in this OTN post that one need only run the Aggregate Store Partition Wizard available within EAS in order to do so.

Let's Recap:

1. Run the Aggregate Store Partition Wizard to make a BSO copy of your ASO cube. It may give you some warnings so I suggest first making a copy of that ASO cube and using the wizard on the copy. If you get warnings, go into the ASO outline and fix the issues. You'll need to remove things like solve order and other ASO specific items.
2. If you only need to turn the BSO cube into a Hybrid cube, you can use Celvin Kattookaran's utility available here that changes all upper level sparse members to dynamic calc members. Stop here if you don't need a test bed of data.
1. Don't forget to set ASODYNAMICAGGINBSO in your essbase.cfg file.
3. Download and unzip ESSCMDQ. It's probably easiest to put this file in the same place where ESSCMD or essmsh already run so you don't need to fiddle with paths, libraries and such.
4. Run ESSCMDQ. Login as you would ESSCMD (no semi-colons!):
1. login hostname user password
5. Select the database you need to fill with data:
1. select appname dbname
6. Run the filldb command. See the link for more information on this command. I would suggest starting out with a small size. I had a very small block size in my cube and it went way past the 1 GB mark even though that's what I set it to. With a normal size block it was close (within 10%) to 1 GB.
1. filldb [size density distance]
7. Export the data from the BSO cube.
8. Import the data into the ASO cube.

Now I can easily create a test bed of data for ASOSamp at whatever density I require. I'll be using this in future posts.