Becoming an Oracle Essbase Guru

I give presentations at conferences around the world including over 100 sessions in the last year throughout North America, South America, Europe, and Australia. So it may come as a shock that I don't actually teach that many classes anymore. Usually, it's once or twice a year and they tend to be on advanced topics (unlike my sessions at conferences which tend to be more intermediate and beginner level). It's time for that once or twice a year for me to teach the advanced classes.


I have two classes coming up and both are in November. To register for either, e-mail Danielle White at interRel and I strongly suggest you hurry since seating is very limited. That's not marketing hype. The first one of the classes is literally limited to 12 students. The other one has slightly more capacity since it's more seminar style and less hands-on, but it's limited too.


Becoming an Oracle Essbase BSO Calc Scripts Guru on November 12
This is a virtual class (meaning you can take it from anywhere in the world) and price is $750 for the day. It's designed to teach you all the major points of Essbase Calc Scripts and BSO member formulas in a single day. We'll start off with the basics but we will get to more advanced topics like allocations, goal seeking, multi-pass calcs, and so on. We close the day with an hour of calc script optimization.


The class format is part me teaching and part hands-on labs using the interRel cloud. It will have tons of real world examples (because if it can be done in an Essbase calc script or member formula, I've probably done it) and lots of interactive Q&A as well. As. I mentioned above, because this class will have exercises throughout the day, we are limiting it to 12 students. In other words, drop what you're doing and register now because it'll probably be full by month-end.


I don't know if this is a motivator for anyone, but attendees will all get a copy of the newest Look Smarter Than You Are with Essbase book autographed by the two authors (one of whom is actually a really nice person and looks great in a dress). If you've ever wanted to learn calc scripts or you've wanted to take your knowledge of member formulas to an advanced level, this is the class for you.


Becoming an Essbase Optimization Guru on November 15
This is a class I've been wanting to offer for years. Basically, we'll spend an entire day focusing on designing and optimizing Essbase cubes. It will span both ASO and BSO cubes. It is going to be seminar format so mostly lecturing from me with lots of Q&A by you. Cost is only $375 (that's the good part) but you'll have to attend in real life (IRL, for those who only know text acronyms). The class will be taught at interRel's headquarters at the Rangers Ballpark in Arlington, TX. Lunch will be included for attendees, so it's a pretty darned good deal. And we're only 15 minutes from DFW Airport and only 20 from Dallas Love Field (for those Southwest Airlines junkies).


The seminar will cover every aspect of optimization and to a far greater level than your standard one hour optimization presentation at a conference. Among other things, we'll cover data loading, dimension building, dimension ordering, outline tuning, caches, config settings, ASO specific tips, BSO calc script tips, and 64-bit optimization too. We'll even cover how cube design and optimization changes when you get to the Essbase 11x world.


I will be using various examples of cubes I've optimized in the past, but iI'm also willing to use attendees' cubes for examples too. As such, if you sign up before the end of October and send me your outline, database settings, and calc scripts, I may use them (sanitized so no one knows it's your company) as examples in the class. In other words, you may be getting free Essbase optimization consulting as part of your $375 too which makes it potentially an even better deal. Even if you don't send in your application, there will be lots of time for questions throughout the day.


Oh, and to spice up the day, i will be sprinkling several of my "Hacking Essbase" tips throughout the class. These are the undocumented, unsupported, void your warranty things I've come up with over the last 15 plus years. I can't put them in writing for fear of being sued but we'll be covering the tricks (both helpful and shocking) whenever we have a break in the class.


There is a good thing about scheduling this for November 15th. Many of you will be coming to Dallas for the EPM Connection Point conference on November 16-17. Since this class will be from 10am-5pm on the 15th, you can just come in a day early and learn some advanced Essbase content before the 2-day conference. And if you do register for EPM Connection Point, make sure you use the InterRel promo code of "interrelclient" so you get invited to our super-blowout party on the night of the 16th.


Registration
Hurry and email Danielle at interRel to save your spot because I won't be offering either of these classes again until 2011 (and that's if I ever get a chance to offer them again at all). I can't wait to see many of you at one of the two classes!

Kaleidoscope - Wednesday

11:30PM - New Orleans Party and Poker
We got to the party right after it started. On the way in, someone handed me some additional drink tickets. Each person was limited to two drinks (like Collaborate). The original two drink limit kinda surprised me, because there haven't been any limits to the drinks so far that I've noticed. Since they gave me additional drink tickets, I guess the limit is irrelevant, then. The irony, of course, is that I don't drink alcohol so have no need for drink tickets anyway. I ended up giving them to some of the Essbase track attendees who clearly needed more alcohol and to a few interRel people who clearly didn't.

I was skeptical of the party beforehand, but it ended up being a lot of fun. On the way in, there were 5 Tarot card readers telling people's fortunes (or whatever it is Tarot card readers do). I skipped them and went to one of the two temporary tattoo artists. I asked if they had anything resembling a cube, and they showed me a Celtic pattern that looked for all the world like a 4-dimensional hypercube. Sensing fate, I got a dark blue hypercube tattoo on my right forearm. I was thinking it would be a chick magnet the rest of the night, but it turns out that multidimensionality isn't the female draw it used to be. [Note to my wife: I'm kidding. No one has ever been attracted to me but you and I think it maybe wasn't my Essbase knowledge that put you under my spell. I'm thinking it was my manly beard and musky scent.]

The food was plentiful and I ended up getting a semi-spicy penne pasta and then sitting down at a table to eat with some friends. We sat by the dance floor. There was a zydeco band that also did some classic rock. I can now say that I've heard "Stagger Lee" accompanied by a washboard and spoons. One of the highlights is when the lead singer invited people up to the stage to play instruments. Two non-musical types came up on stage and proceeded to play non-musically. Well, it has to count for something that they got up there at least.

There was dance floor filled with, at least as of when I left, no more than 2 people at a time. Natalie and Jay ended up dancing together. At least, I think it was dancing. Natalie has rhythm, but Jay has... whiteness. Again, at least he put himself out there, dancing ability be damned.

After we finished eating, four of us went over to Harrah's for poker and roulette. I stuck to the poker and won around $500. On my last hand of the night, I drew to an inside straight (I know, girls don't make dates with guys who chase straights). I pretended to be remarkably distracted by ESPN's Top 10 Plays of the Day throughout the hand. Pre-flop, the other fellow bet $12. It was my big blind and I only had a Jack/Eight of diamonds, but I was tired of him raising my blind every single time, so I decided to make a stand and see what the flop brought. In this case, it was a 4/7/9 of all different suits.

I needed a 10 after the flop for a straight. I checked, the guy bet $20, and I called. The odds weren't right, but I figured that if a 10 came, he'd never put me on a straight. A 10 arrived on the turn (giving me a straight) and a fellow bet $40 at me. I called looking like I was doing it out of a lack of attention. The dealer actually had to say to me, "hey, we're playing poker here." The river card came, and I knew I had the nuts (the best possible hand). As soon as the card came out, I went all-in and went back to watching ESPN. The guy called and turned over two pair. I showed my straight and took all his money. Everyone seemed very surprised except the player next to the dealer who said "couldn't you people smell a straight? That was the worst acting job ever." Hindsight's 20/20, my friend.

Eduardo lost over $700 at poker, but went over to the roulette table and won $900 betting on red and black. That's not the easiest thing in the world. I left to go back to the hotel and pack. My flight leaves New Orleans for New York at 11AM tomorrow, so I won't be attending tomorrow morning's sessions on Essbase infrastructure.

As such, I guess this makes this my last Kaleidoscope entry although I will do one at some point comparing the various Hyperion conferences now that I've been to all of them. It's been a great ride, folks. Thanks for reading along, and next year, try to make it to to Kaleidoscope yourself. I'll see you there, wherever there is.

6:30PM - Ask an Essbase Guru Panel
Tim Tow and I shared the moderating of the "Ask an Essbase Guru" panel. It started about 15 minutes late (my fault due to the Optimizing Calc Scripts presentation running over earlier). The panel was made of up the two Oracle Hyperion ACEs (Glenn and Gary), the speakers from the Essbase Optimization Day, and two of the infrastructure people from Friday to handle any installation questions. It made for a crowded stage.

Steve Liebermensch and Gary Crisci (along with me, even though I was supposed to be co-moderating) ended up answering the majority of the questions. Some of the questions were overly detailed (Does the Set AggMissg setting in a calc script override the database-level setting? Yes.), others were humorous (Is it true that one of Edward's employees beat his score on the Hyperion Certification Exam? Yes, on the Planning exam, Jason Novikoff got a perfect score and I didn't.), and others were purposefully vague (What's your favorite Essbase optimization tip that you didn't hear today? Mine was to use report scripts to speed up large Excel retrieves.)

All-in-all, the panel went well, but by 5PM, my mind was thoroughly blown and I was ready for a break. I thanked everyone for their time (since I won't be here tomorrow) and adjourned for the day. Everyone seems to be getting a great deal of value out of the Hyperion track so far. Cameron Lackpour said some nice words on the Network54 Essbase forum that I think echoed the sentiments of just about everyone here.

After the panel, I thankfully had a 90 minute break before the 6:30 party was due to start. I went back to the room with a few other interRel-ites to meditate on the week (and to eat snacks and have a few drinks, of course). We're headed down to the party now. I'm staying in my interRel bowling shirt (with the Arf on the back) but everyone else in our group changed into less nerdy attire. I am what I am, so I don't see any need to hide it.

4:00PM - Optimizing ASO
Steve Liebermensch, Consulting Product Director from Oracle, gave one of the best ASO Optimization presentations I've ever attended. He really knows his stuff, and I enjoy his sense of humor. He started off by pointing out that ASO optimization is totally different than BSO optimization. He then covered far too many points for me to mention, but here are as many important bullets as I could write down:

• Compression. Compression dimension is not mandatory, but helps performance. The compression dimension is a dynamic dimension. It should be the column headers in a data load file. Ideal compression is achieved if the leaf level member count is evenly divisible by 16.
• Accounts. Accounts is a dynamic dimension that allows for non additive unary operators (minuses in a structure still make a hierarchy dynamic). The only reason to make a dimension Accounts in ASO is for time balancing. Expense flags are accomplished through UDAs and member formulae.
• Time. Can make a good candidate for compression dimension. Should be stored. Use multi-hierarchy if formulae are necessary. Prior to 9.3.1, to-date is best performed in Time dimension. 9.3.1+, use an "analytic dimension" with to-date members like MTD, QTD, and YTD.
• Meta-data vs. data. Don't evaluate data when a metadata check will suffice. For instance, IIF(Is(Scenario.CurrentMember,Actual) is faster than IIF(Scenario.CurrentMember=Actual) because the latter IIF actually compares values.
• MDX Optimization. Don't use the MDX Round() function: rounding is a function of formatting in a reporting tool not the database. Remove CurrentMember if possible, because that's what's already being calculated. Use LastPeriods() instead of Lag() when doing of range of Time periods. Don't use a function where direct referencing can be performed. (Call out specific members instead of functions, for instance.) Only perform calculation when data to support the math exists [i.e., start off with Case When Not IsEmpty()]. In 11.1.1, there is a new NonEmptyMember directive to only calculate when data exists.
• Materialization. You can turn Query Hints (Level Usage for Aggregation) on for specific members (the member information tab in the member properties in EAS). You can also specify a specific level intersection to materialize via EAS. Both types of Query Hints can only be set through EAS. He mentioned that only 1,024 level intersections can be materialized which I've never seen anyone ever come close to.
• Slices. Is the primary feature enabling Excel (Lock&)Send and trickle feeding functionality. Creates "subcubes" alongside the primary slice of the database. Dynamic aggregations are performed across the necessary slices to provide query results.
• Data load. Data should be loaded as additive values (instead of replace even on an empty database). Multiple buffers can be used to parallel load the database. Requires simultaneous MaxL processes to be executed. Ignore Zeros and Missing values whenever possible (buffer setting which was available in 9.0).

Everybody's been writing down their questions for the "Ask an Essbase Guru" panel on little slips of blue paper. Tim and I are going to co-moderate the Guru panel. It should go quickly, because everyone's tired and we're running behind on time.

3:00PM - Optimizing Essbase Retrievals
I didn't know the next speaker, Mike Killeen from Ranzal. Mike submitted an abstract for the conference, and we asked him to give this presentation instead of the one he submitted. Tim Tow's met Mike before and knew he could do it.

He started off with some slides on testing methodology. He had a good slide on recommended standard Essbase.CFG settings that he uses on his projects. They seemed to make sense:
-CALCLIMITFORMULARECURSION True
-SSPROCROWLIMIT
-SSLOGUNKNOWN False
-SILENTOTLQUERY True
-QRYGOVEXECTIME n
-CALCLOCKBLOCK DEFAULT
-OPGCACHESIZE (ASO Only)

There were a few more that I couldn't write down in time. He then talked about optimizing outlines for retrieval including use of dynamic calc members. He mentioned optimizing memory management, caches (data cache, data file cache, index cache, dynamic calculator cache), and some other settings (like retrieval buffers and sort buffers). On the server-side, he also mentioned maintenance routines (defragmentation, log purging, recycling of services to clear memory, and archiving off older data sets into things like history cubes).

He went to optimizing reports themselves. The most important thing, of course, is the layout of the dimensions on the report. He gave two good tips:

1. Make reports symmetrical.
2. Try to order your dimensions like a columnar export: dense dimensions across columns and near the data in your rows, sparse dimensions in your rows.

He pointed out that on Excel retrieves, turning off preserve formulas speeds up retrieves (and just use Excel protection to keep the formulas). Nice tip that I often forget to mention.

For Hyperion Reports (Financial Reporting), he recommends using the MemberProperty() function for filtering along attribute dimension associations, and I completely agree. He made an interesting point that in some reports, it might be better to calculate within Reports instead of the server if a lot of rows are suppressed.

He also covered optimizing report scripts and Web Analysis. Overall, it was a great presentation. Mike went way beyond what I expected from the presentation. The recommendations on optimizing specific front-end tools was a very nice surprise. I definitely feel like I learned some things during his presentation.

2:00PM - Optimizing Calc Scripts
I went 15 minutes beyond the end of my presentation timeslot and into the next timeslot. I guess I'm overly passionate about calc scripts. On a related note, I'm a geek. The audience seemed to enjoy it. In a room of 150+ people, I only saw one person leave at the scheduled time. A lot of people were also taking notes which is always a good sign of concentration and attentiveness.

Doug Bliss, Steve Liebermensch, and Glenn Schwartzberg offered some helpful clarifications to some points. There were some good questions during the presentation from others too. Here are the main points I tried to cover:

• The tips are for BSO only (since ASO doesn't do calc scripts) but does apply to 64-bit BSO
• To optimize calc time, always minimize reading from the hard drive
• Make sure your database is defragmented
• Avoid two-pass members
• No left-handed cross-dims
• CLEARDATA vs. CLEARBLOCK (CLEARBLOCK is better)
• Repeating CLEARDATA commands causes multiple database passes
• CALCDIM vs. CALCALL vs. AGG (use AGG)
• IF vs. FIX (it's not always true to FIX on sparse and IF on dense)
• Avoid unnecessary IFs when, say, a FIX would do.
• You can use @ISxxx functions outside of IF statements
• How to check for missing or zero
• Changing zeros to missings
• @PARENT vs. @PARENTVAL
• @XREF being called in a dynamically calculated member
• Try not to use @ALLOCATE or @MDALLOCATE
• AGGMISSG ON speeds up aggregations
• Pivot calculations to avoid cross-dims
• Use VARs and ARRAYs (especially when pulling info in from an XREF)
• Compile calc scripts into default calc scripts
• CDFs vs. built-in functions (built-in functions are faster)
• DATAEXPORT is fastest way to export
• Use substitution variables to improve maintenance
• CLEARBLOCK before aggregating? Depends but can often slow down calculations
• Use CALCPARALLEL set to max in most cases.
• FIX arguments are reordered
• SET CREATENONMISSINGBLK is the devil's work (second only to left-handed cross dims)
• Use SET FRMLBOTTOMUP ON to speed AGGs (in most cases) but watch out for incorrect results
• Avoid functions that cause Cell Mode calculations

When my slides are posted to the ODTUG website, I'll link to them from here. Someone asked a great question: "Why isn't Tracy McMullen [best co-author ever] an Oracle ACE?" There was the obvious subtext that she does at least as much work answering questions, presenting, and writing as I do. I remarked that no one had nominated her yet, but I'm sure it's just a matter of time until someone does. I asked the room who thinks Tracy should be an Oracle ACE, and the entire room gave a round of thunderous applause.

12:30PM - Lunch
Lunch was a boxed salad. Not much more to say, really. I'm told that there were desserts available on the 3rd floor in the exhibit area, but since I couldn't eat them anyway, I don't have first-hand evidence. Have to go start my presentation now.

11:30AM - Optimizing Caches and CFG Settings
Rick Sawa from Oracle just gave the cache and CFG optimization presentation. There was a lot of theory and not a ton of concrete tips. That said, I actually learned a ton. Rick has an interesting perspective on optimization, because he's normally brought in to tune huge Essbase cubes running under huge Planing implementations with huge concurrent user counts running huge calcs. When he's brought into these huge projects, it's usually because they've failed. As such, Rick really taught everyone about optimizing Essbase for mass concurrency which is a perspective you don't see that often.

He covered the background behind how the memory and storage managers work in Essbase. He didn't exactly say how to tune the caches but rather he said to set them to the minimal amount possible. One of his more interesting points was that the old "load your entire index into RAM" rule is out-of-date. Now, he says to set your index only as large as is necessary.

As a speaker, Rick's very knowledgeable (he wrote one of the appendices for our Look Smarter book) but he does read everything from a prepared script. This makes the presentations not very interactive: it's hard to change based on audience interest if you have pre-planned words. Luckily, this audience seemed in tune with what Rick was trying to get across. Rick has a very interesting whitepaper on the conference CD (and it might by on the ODTUG website some day) that I encourage everyone to see.

10:15AM - Optimizing Data Loads and Dimension Builds
My friend Jeff McAhren from Health Markets gave the optimizing Essbase data loading and dimension building presentation. I think he did a great job of going beyond the standard tips like "sort your data load file correctly." While he did cover the sorting, he gave a ton of other tips including some on ASO loading and others on staging data in your data source.

9:00AM - Optimizing Outlines
I'm the moderator for today's "Optimizing Essbase Day." I said a few brief words and then turned it over to Tracy McMullen, Best Co-Author Ever. I really enjoyed her presentation. She had a fun slide on the differing schools of thought on recommended Essbase block sizes. She first gave the Essbase Database Administrator's Guide recommendation of 8-100Kb, but she pointed out that the DBAG is always very slow to change. She then gave the "Edward Recommendation" of 1-8Kb. She finally pointed out that every one has their own opinions on block size.

She pointed out that some of the rules people follow are not correct such as:
-Hourglass order for dimension ordering
-Accounts first, time second
-Dynamic calculated members slow retrieval

All of those rules are still taught in Essbase classes around the world, and they're all either out-of-date or wrong some of the time (in the case of the last one).

There were some good questions throughout the presentation that I wasn't able to take down quickly enough. I'm impressed at the level of Essbase knowledge at this conference. During one of my normal Essbase sessions, I usually know more than anyone else in the room. At this conference, there are several people that know more about a specific Essbase topic than I do, which is both comforting and frightening.