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.

Summing Calculated Members in ASO

One challenge I see on a regular basis on Essbase technical forums is the summing of rate driven calculations. Usually the poster wants to carry out a rate driven calculation at the lowest level of the cube and have it summed up. In BSO and HSO this isn't an issue. You simply create a member formula and calculate it or write a calc script. Let's look at this problem from an ASO perspective.

Assume we are planning a mission to Mars and we are tasked with building a robot to explore the planet. We want to test our various robots on a challenging obstacle course. We can measure the time the robot motors are moving and the rate at which they move but the robot extract file doesn't compute the distance it traveled. We'll handle this in our robot Essbase cube. Let's start with two dimensions: Robots and Measures.

First Try

If you remember back to high school algebra, the equation for distance is Rate * Time. We can put this into our Distance member and Essbase will take care of everything for us. We'll just send some data to the cube and let ASO take care of the rest.

So far so good. Now we'll let ASO handle the calculation for us.

Perfect. Now we have our distances computed. Let's just tally those up for all the robots.

Hold on there a second. 12 + 2.5 + 6 + 48 does not equal 211.5. How could Essbase be wrong? Well, it's not. Essbase is multiplying 4.5 by 47. Okay, we'll just change around our solve orders to fix it. Um, actually that won't do anything. We can change solve orders until we're blue in the face and it won't give us the answer we're looking for (which is 68.5)

Second Try

I'm going to try writing a fancy MDX member formula now. I'll just check to see when the Robot member is at the level zero and perform the computation there. At the other levels, I'll sum the robots up.

1:  IIF(IsLevel([Robots].CurrentMember,0),  
2:    [Rate] * [Time],  
3:    Sum(CrossJoin({[Distance]},{[Robots].CurrentMember.Levels(0).Members}))  
4:  )  

Let's see what I get.

Now I have the correct distance number. I don't like the Rates being added up but at least I have my Distance number.

Third Try

I personally don't ever remember seeing a two dimension cube in a production environment so my guess is that most people tackling this problem are designing databases with several dimensions. Let's add a Dates dimension and see how our solution works.

1:  IIF(IsLevel([Robots].CurrentMember,0) AND IsLevel([Dates].CurrentMember,0),  
2:    [Rate] * [Time],  
3:    Sum(  
4:        CrossJoin({[Dates].CurrentMember.Levels(0).Members},  
5:          CrossJoin({[Distance]},{[Robots].CurrentMember.Levels(0).Members})  
6:        )  
7:     )  
8:  )  

Hmm. Things are getting more complicated. Let's see what Essbase comes back with.

That's not right. I'm not getting the right distance when I add up all of the dates. I'll have to go back and change the formula. At this point I think I'm ready to throw this idea out as it is far too complex. Maintenance would be difficult and if you think about how this works in Essbase, everything is getting calculated dynamically as the user requests it. Potentially a lot of complex calculations will have to take place and performance will almost certainly be poor.

Final Try

I can try using an ASO Procedural Calc or Allocation. This is an example of a simple allocation script that will perform the calculation. Once this calculation is complete, the retrievals will be super fast as the Distance data will be stored.

MAXL> execute allocation process on database Mr.Robot with
   2> pov "CrossJoin([Robots].Levels(0).Members,
   3> [Dates].Levels(0).Members)"
   4> amount "([Rate]) * ([Time])"
   5> target ""
   6> range "{[Distance]}"
   7> spread;

 OK/INFO - 1300006 - Essbase generated [12] cells.
 OK/INFO - 1013374 - The elapsed time of the allocation is [0.01] seconds.
 OK/INFO - 1241188 - ASO Allocation Completed on Database ['Mr'.'Robot'].

And here are the results. The distance data now totals correctly across Robots and Dates.

Another Idea

If you're loading from SQL or another source system -- get it to do the calculation and add a column with the correct value before you load.

Matching on MDX Monday

According to the Essbase Tech Ref, there is no equivalent MDX function for the @MATCH function. However, there is a function called IsMatch() which can be combined with Filter() to give equivalent results. Maybe the documentation hasn't been updated in a while. Anyway, let's sort it all out here. IsMatch() uses regular expressions which are really powerful. You can read the specification that Essbase uses here.

Let's try to reproduce the three examples given in the Tech Ref for @MATCH. It's not mentioned, but these are obviously being taken from Sample.Basic.

@MATCH(Product, "???-10")

1:  SELECT  
2:  {} on AXIS(0),  
3:  Filter(Product.Members,   
4:    IsMatch(Product.CurrentMember.MEMBER_NAME, "^[0-9]{3}-10$")  
5:   )  
6:  ON AXIS(1)  
7:  FROM Sample.Basic;   

OR

1:  SELECT  
2:  {} on AXIS(0),  
3:  Filter(Product.Members,   
4:    IsMatch(Product.CurrentMember.MEMBER_NAME, "^[A-Z0-9]{3}-10$")  
5:   )  
6:  ON AXIS(1)  
7:  FROM Sample.Basic;   

In this case I'm using a few special characters to ask for 3 characters followed by "-10". The first character is the ^ which forces the regular expression to start at the beginning of a word. In the first example I have [0-9] which asks the engine to match any digit. In the second example I have [A-Z0-9] which will match any alpha character or digit. Next comes {3} which tells the engine to match the preceding statement three times. So the [0-9] or the [A-Z0-9] will need to be matched exactly three times. Next I add "-10" which will need to match that text exactly. Finally, I added the $ which matches the end of the line. In this case either example works because we only have products with numbers in the first three characters. The second example would match a product like "abc-10" as well.

@MATCH(Year, "J*")

1:  SELECT  
2:  {} on AXIS(0),  
3:  Filter(Year.Members,   
4:    IsMatch(Year.CurrentMember.MEMBER_ALIAS, "^J")  
5:   )  
6:  ON AXIS(1)  
7:  FROM Sample.Basic;   

This is a fairly straight forward regular expression. I'm using the ^ to specify that

the string needs only to start with a J. One other difference here is that I'm using

the MEMBER_ALIAS property for Year to do the matching.

@MATCH(Product,"C*") 

1:  SELECT   
2:  {} on AXIS(0),   
3:  Filter(Product.Members,    
4:   IsMatch(Product.CurrentMember.MEMBER_ALIAS, "^C")  
5:  )   
6:  ON AXIS(1)   
7:  FROM Sample.Basic;    

This is basically the same request as the last one. Grab all of the Products whose aliases start with C.

Conclusion

With the ability to use regular expressions, the IsMatch() function is far more powerful than @MATCH. If you come up with some challenges for the regular expression engine, feel free to pass them along.

Corn

Friday is finally here again and I'm ready to enjoy another fall weekend. Here in the Midwest, the corn is beginning to be harvested. Soon huge machines will be going through the fields scooping up the stalks and spitting out the dried kernels that go into so much of the food that we eat. All of this corn around here reminds me of a story James Bender told in his book How to Talk Well.

He told of a farmer who entered his corn in the state fair and won a blue ribbon each year. When a reporter asked him about how he grew it, the reporter discovered something unexpected. The farmer shared his best quality seed with his neighbors. The reporter was surprised and asked how he could afford to share his best seed with the very neighbors he was competing against each year. The farmer responded, "The wind picks up the pollen from the ripening corn and whirls it from field to field. If my neighbors grow inferior corn, cross-pollination will steadily degrade the quality of my corn. If I am to grow good corn, I must help my neighbors grow good corn."

And so it is with technology, in this case, Essbase. If nobody shared their knowledge of Essbase 15 or 20 years ago, the product might have died. But conferences, user groups, and forums encouraged people to share their good Essbase. When our neighbors succeed, we also, in a small way, succeed. The open source movement is the perfect illustration of the power that groups are capable of. This week it was discovered that Microsoft is using Linux to run their cloud services. What once was an April Fool's joke has become reality.