Quiz 3

In a cavern, in a canyon,
Excavating for a mine,
Dwelt a miner, forty-niner,
And his daughter Clementine.

Now Clementine in addition to being beautiful was a bit particular. She
was an Essbase Calc script wizard. As I said she was particular, this proves it.

She hated the word IF because she know if statements are slow and Clementine was a fast woman. She figured if she was going to Fix something it should be fixed the first time and not again.

She was asked; well it was highly requested or she would be swimming with the fish (and from the chorus, we all know how that turned out)
Ruby lips above the water,
Blowing bubbles soft and fine,

to create a calc script in the Sample.Basic database (BSO) that would take Sales and and add them into a one of two members.
Major Market Sales
Small Market Sales
(Members she had to create in the Accounts Dimension)
The calculation should be done on level zero of all dimensions and be based on the Market UDAs. Only one fix statement and no if statements.

Can you help Clementine?

I'm so lonely, lost without her,
Wish I'd had a fishing line,
Which I might have cast about her,
Might have saved my Clem

Note, Clementine does not refer to any real person, the song just popped into my head, so I thought it would be a fun to include it in the quiz and perhaps throw you off a bit.

OK, now for some other news, Edward Roske created yet another linked in group, but this one is just for us Essbase types. Not Hyperion, Not Oracle, Not planning, but just Essbase. If you want to be like one of the cool kids, come link it at http://www.linkedin.com/groups?gid=2905269

Kaleidoscope detailed agenda is now available

They just published the detailed agenda for Kaleidoscope at Kaleidoscope Agenda . What I want to know is why all of the presentations that I want to go to are always scheduled when I am presenting. The Best Practices for Performance, Scalability, and Reliability with Oracle BI Enterprise Edition from Mike Duran on Tuesday conflicts with my "MDX Basics, practical coding examples". IT will make it hard for people to choose which to go to.

Then on Thursday Edward Roske's Integrating Essbase and OBIEE: Implementing in the Real World conflicts with my Essbase configuration settings presentation. I would go to Edward's presentation. Finally my Load Rules Basics to advanced conflicts with Matt Milella's Convert Legacy Add-in Solutions in VBA to Smart View VBA or .Net. I really wanted ot go to this one. If everyone in my session goes to Matt's session, I can skip mine and go as well. I'm sure that won't happen, so I'll be presenting. I think mine is a good presentation I just finished putting it together and it has a little bit for everyone. It truly is Basics to Advanced.

As I said before, there is too much great content at the conference and you will not be able to attend everything you want to, but for most presetations the slides will be available. For my presentations, at some point interRel consulting will allow me to present them during their tuesday and Thursday webcasts.

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.