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

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.