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.