Quiz 2 answer

I am amazed no one came up with the answer I was thinking about. Amarnath came up with an interesting idea on using a partition and ASO cube. While it might work, I have not tested it, but in my dealings with ASO cubes, partitions and calculations combined together, I have found less than satisfactory performance. Still it was thinking outside the box and he gets brownie points for that.


My solution (see code below) breaks the rules for using cross dimension operators on the left hand side of the equal sign. The trick here is to bypass all of the intermediate levels and only put data where we need it for our subsequent calculations. If necessary, we could agg the dimension later to get all the values populated. Since the number of level zero combinations is pretty small, cycling through them is very fast. The solution is always writing to the same block, so that block will most likely be kept in memory. Note, this solution will also work if one or two of the dimensions are not at top level. For example; if I had a customer dimension and wanted to get totals by customer, I would just fix on the level zero of customer. If I don’t put the customer dimension in the left side of the equation, it would do the calculation for each customer. If doing this, don’t forget to add the level zero parents in the first clear statement.

So what exactly is the solution?

In text, we clear out where we want to stick the data. This makes the code rerun able.

Then we get to the level zero of the dimensions and cycle through the members for each member we add to our total block. Since this is a dense calculation and it is only on level zero member, this calculation can be very fast.

/* Need to clear out any data that exists at the target intersection.
This really is clearing one block) */

FIX(@levmbrs("Year",0),"Actual","Product","Market")
   "Sales" = #Missing;
ENDFIX

/*Now fix on level zero of the dimensions and add to the total */

FIX(@levmbrs("Year",0),"Actual",@Relative("All Products",0),@Relative("Market",0))

/*Note Sales is in a dense dimension member so we are doing a dense calculation */

/*Since I'm doing a cross dimension operator on the left side of the equal sign it has to be in a block statement */

"Sales" (
    "Sales"->"Market"->"Product" = "Sales"->"Market"->"Product" + "Sales";
)
ENDFIX

At a recent client, using this technique, I was able to cut 30 minutes off of multiple calculations. In total I cut 10 hours off of a calculation process since their calculations did a ton of allocations. It helped that when the calculation was done, the data was copied to an ASO cube for reporting, so I never had to really agg the cube. If I did have to agg the cube, It would have only been one time instead of multiple times. I should point out as side effect of this method was the cube size was brought down from 10 gig to 2 gig and fragmentation was reduced a lot.

Let me know what you have a different solution. If I continue on with this series of quizzes, you will soon know all I know (which is not much). So let me know if you find them interesting.

This and many more tips and tricks can be seen at the Kaleidoscope conference in Washington D.C. at the end of June. It is a worthwhile investment to attend.

Time for another quiz

The last quiz I did was very easy and was answered by a number of people very quickly. In addition, it wasnot technical at all. This quiz is a little harder and makes you think a little.

The problem.

I have expanded sample.basic and now it has 10,000 products with 5 levels in the hierarchy. Alternate hierarchies have been put under a parent called Alternate_Product_Hierarchies and the primary hierarchy is under a member called "All Products". Market too has been expanded and goes to city level so there are now county and city levels in the hierarchy. There are 3141 counties and for fun lets say there are 19,355 cities.

For the actual scenario for each month, I need to get sales at the generation 1 of Product and Market so I can use it for an allocation later. Because of the size of the dimensions, I can't just make the upper levels dynamic. Also, while I could just agg up the dimensions, it is slower that I need it to be. What is the quickest way I can get my sales at the top of the dimensions. This code also has to be re-runable. I'm sure there are multiple ways to do this. I'm interested to see what you come up with.

Have fun.

Too much content at Kaleidoscope

I has looked over the Hyperion sessions at Kaleidoscope and decided which ones I wanted to go to (between my presentations), but now my selections have become muddled. I was reading Mark Rittman's blog and he announced some of the sessions in the BI track that deal with OBIEE. There are a number of great session there that deal with Essbase. Like Oracle Business Intelligence Applications Essbase Integrator and Oracle® Hyperion Smart View for Office, Fusion Edition (This one is from Toufic Wakim). How Mark got him to talk in his track instead of the Hyperion track, I don't know. but I'll bet the information will be good.

You can see the details of these presentations and more at
http://www.rittmanmead.com/2010/03/02/obiee-content-at-odtug-kaleidoscope-2010/


As for my mini-quiz question, A number of people quickly got it (Google is many people's friend), The first conference was called Arbor Dimensions and it was help in 1995 at the Fairmont Hotel in San Francisco(Getting ready to be a part of the future Open World I guess). They quickly outgrew that location and the following conference was at the Westin in Santa Clarita. and soon following at the convention center.

I will have a more technicl question for the next quiz. Thanks to those who responded

A mini- Quiz for you old timers

OK, I'm stealing from Mark Rittman's Blog and doing a little quiz or puzzle from time to time. This one is to see if any of the old time developers have any memory left. We all know that the best conference to go to for Essbase content is Kaleidoscope (Yes this is a plug for the conference). Prior to it, was the Sales pitch intensive Solutions conferences. But what was the first Essbase conference called and in what year did it start? For Bonus points where was it held. And where was the next year's conference held. Yes it was so successful they did it again.

Kaleidoscope is coming too quickly

It's already feburary 16th and Kaleidoscope is less than 4 months away (June 26 -Jul 1, 2010) and I have too many presentations to prepare. I have 4 sessions that I have to create presentations for and one or two more where I show up and make everyone else look more handsome(anyone next to me always looks better. Is that why pretty girls always have an ugly friend to hang with? I know I'll get comments on this. It is meant in fun don't slam me for it).

Anyway my first session (and first session of the conference) is "MDX basics a practical approach". It goes over some of the basics about coding in MDX and then I'll show working examples of coding them and how they work. Then I have a joint presentation sometime with Gary Crisci and Cameron Lackpout on the Best of OTN posts. We share some of the more interesting threads from ONT (and perhaps Network 54). My final two presentations are the last two presentations on Thursday. First and last, I get to open and close the conference. The sessions are "Load rules basics to Advanced" and "Configure it out" which deals with the multitude of configuration settings in the Essbase.cfg file.

I've gotten a good start on all of the presentations, but if there is something special you feel would be something you want to know about or have dealt with and think I should share, please let me know.

As you all know the kaleidoscope conference is the Best conference for Hyperion Technical presentations. It IS your conference, so share your ideas. I know I am speaking to the choir here as most people who read my blog are technical and know the value of the conference, but for those of you how stumble onto my site, I will reapeat what you hear all over. This conference is better than any single training session you can go to. It has more technical content and more knowledgable speakers than ANY other conference I have been to. Even after doing Essbase for about 15 years, I pick up good information in every session I attend. (Even my own, where someone from the audience will tell me something I didn't know) Yes I am talking about you Cameron, and Gary :)

If you read my blog or are on OTN or Network 54, look me up at the conference and say hi. It is nice to put faces with the handles I see on the web.

Book Review - Oracle Essbase & Oracle OLAP The guide to Oracle’s Multidimensional Solution

First my typical disclaimer.
1. I am an Essbase developer and know little about Oracle Olap
2. I work for interRel Consulting which has a competing book out on Essbase (Well not really as you will read)
3. I am a pessimist by nature.
4. I have irritable bowel syndrome (Not really, but it was a fun thing to say)

OK now with those thoughts in mind, on to the review.

Mid December I got a package in the mail. Oh Boy, another Chanukah gift and In a way it was. I opened the package to find my copy of the book “Oracle Essbase & Oracle OLAP The guide to Oracle’s Multidimensional Solution” that was promised to me for review. This book was written by Michael Schrader, Dan Vlamis, Mike Nadar, Dave Collins and a host of others. I will refer to them collectively in the review as the Authors. Being the holiday season, I sat down and quickly put the book aside to look at later. I have finally gotten around to reading it (I spend too much time on planes and this gave me a lot of reading time). I can say, this is a good, worthwhile book.

I am brutally honest in my reviews and hated the last book I read. This one is very good. I will say it is not the detailed walk you through everything you need to know book, but it a good overview. The discussions of similarities and differences between Oracle OLAP and Essbase are good. The book discusses Oracle Olap and Essbase compares the two and contrasts them pretty well. I don’t agree with everything the authors say, but we are entitled to differing opinions. I even learned a little about Oracle OLAP so they must have done something right.

First I will talk about the good, and there was a lot of good about this book.

The book is well thought out and put together. It flows in a logical and concise manner. The discussions are well thought out. Even if you know nothing about Oracle OLAP and Essbase, you would walk away with a general understanding of the products.

I liked the first couple of chapters which discussed OLAP in general, the strengths of each product, The product suites and more.

I really like the section on reporting. It walks through considerations when determining a reporting solution and is a must read for any reporting development project even if Essbase and/or Oracle OLAP are not part of the solution. It covers the basics of what one should consider when designing a reporting solution then delves into product specific solutions to meet those requirements. I thought interesting (and brave) for the authors to include non-Oracle solutions in the discussion. For example, For Essbase reporting and analysis, they discussed Dodeca from Applied Olap which I believe is one of the finest reporting tools available!

While I think the Authors cheated (See my rants below), I did like the approach for Essbase of showing how to build a cube using Essbase studio. This tool is the direction Oracle is heading for all administrative aspects of Essbase. While the chapter concentrates on Studio, The authors do tell you there are some things you still have to do in EAS and walk through some of them. I’m sure for a long while it will still be necessary to know EAS, knowing how to do things in Studio will help you a lot, especially if you plan to use OBIEE as a data source for Essbase cubes.

Unlike the last book I read, this book had no real misinformation. Remember I’m an Essbase developer, so if they lied to me about Oracle Olap, I would not know. For Essbase, I say no real misinformation because I don’t agree with a few things said. I would call them differences of option instead . The examples were good and easy to follow. The screen shots and illustrations were applicable and added value to the text.

Now for the bad about the book, (remember my disclaimer – point 3 to be exact). I find it easier to find fault and complain about things than I do to relate the good. For the record, I don’t think I can really call the items below bad just personal observations.

I think the authors cheated a little as pretty much two of the main chapters were lifted from existing work (at least a good percentage). The chapters that demo building Oracle OLAP and Essbase cubes are from existing documents. I know a good part of the Essbase demo build is taken from the “Dare to Olap” series and The Oracle OLAP references where the tables and additional documentation can be found. I would guess the Authors originally helped put these modules together so I don’t think there is anything wrong. I also think most of the readers of this book will never have seen these demos before so it is probably not a real bad thing.

The book was very redundant. I would read about something only to have parts repeated in the next paragraph or in the next chanpter. Did I say the book was redundant? well it is.

The third thing I did not like is the way the pigeonholed the products. IT supported implementations should use Oracle OLAP, User supported applications use Essbase. While in general (and historically) this might be true. I have seen a great many projects where The Essbase has been initiated and supported by IT ( I can’t speak for Oracle Olap, but would imagine that they would all be IT supported). Now that Essbase is owned by Oracle, IT has gained a respect for what Essbase can do and is embracing it. I think a better generalization would be “If all your data is in Oracle, use Oracle Olap, if you have multiple sources or need complex calculations use Essbase”. Of course if you want the additional functionality like Planning or write-back, use Essbase as well. Damn, I guess even my generalizations are bad as I can think of a number of exceptions to my statement.

Finally, this is a VERY dry read. Yes it provides all the information you need, but with my A.D.D., I really had to concentrate to get through it.


While I don’t think you could become a developer from just reading the book, I do believe it is a good read for an understanding of both products, their ancillary products and the various use cases for the products. I think after reading the book, you would still need a class on the
product(s) to be even somewhat proficient, but I don’t think the authors’ intent was to create a how to book. In order to do either product justice and cover the topics in detail would require a book that the airlines would charge me overweight fees on. I would suggest, reading this book as an overview and then if you are developing for Essbase read the Look Smarter than you are with Essbase 11 book to give you more details (I would make the same statement even if I did not work for interRel). I don’t have the knowledge to recommend an Oracle OLAP centric book as a follow up. Of course it would be even better to take a course to get the expertise of the instructors.

In conclusion, I would like to thank the authors for spending the time and energy to put together this book for us. I know the amount of energy, blood, sweat, tears, late nights, drunken fits and blood curdling screams that go into writing a book like this. This was a great job. I know I don't have the skill, time or patience to write a book like thig. To all the authors, Thank you from the OLAP community for sharing your expertise with us.

FREE Oracle SoCal Roadtrip Conference Jan 26th

For any of you in the So Ca area, On Tuesday Jan 26th, I will be joining Oracle and other speakers on a one day mini-conference in LA. It will be a great event and you won't want to miss. it.
I'll be talking about Essbase optimization tips. Other sessions include:
Best practices in consolidation
Hot Topics in Financial Reporting
Secrets of Customizing Hyperion Planning
Creating an Oracle EPM center of excelence
Taking control of your hierarchies with DRM.

The keynote will be from Tobin Gilman on the EPM roadmap for 2010 and beyond. It is a free event that you should not miss. The complete agenda is available at Agenda

and to sign up go to Register

For more information you can contact Danielle White at dwhite@interrel.com

Did I mention that it is free and you could win door prizes?

as a teaser, I have my review of the new book "Oracle Essbase & Oracle OLAP, The guide to Oracle's Multidimensional Solution" almost done. I'll be posting it in a few days. Check back, I'm sure you'll enjoy it.