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
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:
- Make reports symmetrical.
- 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
- 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
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
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.
No comments:
Post a Comment