MDX: Cousin Function

I recently found a need to reference an alternate hiearchay in an MDX member formula in an ASO cube.

My first attempt was to utilize the Lag function as I had in many other formuals, but found that I kept getting results of zero.  Using the MDX Script Editor in EAS, I found that the Lag function was only valid within the hiearchy that the specified member was found in.  After reviewing the list of available functions, I took a look at the COUSIN function.  I found it was exactly what I needed. 

Here is an example of how it might be utilized:

Let's assume fort his example that you have two hiearchies within your period dimension.  Let's also assume that data is loaded on a weekly basis.  The first hiearchy provides your weeks, months, quarter, and year.  Your alternate hiearchy is a dynamic calc of the quarter to date as of each week.  So your two hiearchies look like this:

For each member you could do a unique formula, specific to each week:

• WK1 = [Wk1]
• WK2 = Sum(MemberRange([WK1],[WK2])
• WK3 = Sum(MemberRange([WK1],[WK3])
• WK4 = Sum(MemberRange([WK1],[WK4])
• WK5 = Sum(MemberRange([WK1],[WK5])

Or instead you could use the the same formula for each WK QTD member of a quarter:

• WK1 thru WK13 = Sum(MemberRange([WK1],Cousin([Period].CurrentMember,[Year])))
• WK14 thru WK26 = Sum(MemberRange([WK14],Cousin([Period].CurrentMember,[Year])))
• WK27 thru WK39 = Sum(MemberRange([WK27],Cousin([Period].CurrentMember,[Year])))
• WK40 thru WK52 = Sum(MemberRange([WK40],Cousin([Period].CurrentMember,[Year])))

It could be taken the next step so that the same formual is use for each WK QTD member:

• Sum(MemberRange([Period].CurrentMember.Parent.Parent.FirstChild.FirstChild,Cousin([Period].CurrentMember,[Year])))

Please note there are probably several different ways that you can set your the first member of your member range to by dynamic.  This is probably a rather crude way, but since this post is about the COUSIN function, I went with the simplest route that would validate.

In summary, the COUSIN function allows the flexibility reference a child member at the same relative position as a member from another ancestor.  This can be used within the same member hiearchy or across another member hiearchy.

ASO Partial Data Clears Using MaxL and MDX

The Essbase 11.1 release brought us the ability to do partial data clears within an ASO database.  Gone are the days of using report scripts or the Excel Add-In to clear data.  There a couple of posts out there on other sites about how to use MaxL and MDX to do a partial data clear, but most of them speak to the simple clear script, performing a restriction using one dimension.  What happens when you need to define your data clear a little more precisely?

Let's start with the basics of data clears in an ASO Essbase cube.  There are two types of data clears you can perform, a logical delete and a physical delete.  A physical delete removes the input cells in the specified region, whereas a logical delete creates a new slice with an exact offset of the input cells you are trying clear.  When you retrieve the data both slices are netted together and you get zeros.  The technical reference notes that a physical delete completes in a time proportional to the size of input data while the logical completes in a time proportional with the size of data being cleared.  For this reason, a physical data clear is suggested when removing large slices of data.  It is also warned that a logical delete will increase the size of your database.  Both of these should be taken into consideration when doing a partial data clear.

Now on to the fun stuff…

The MaxL Script Editor can be used to perform the clear.  The syntax to be used is:

alter database . clear data in region '{MDX set expression}' [physical]

Your appname and database name should be placed in single quotes.  The MDX set expression is the definition of the data you want to clear.  Physical is an optional keyword that should be used when performing a physical clear.  The most difficult part of this syntax is the MDX set expression.

First and foremost, a set must have curly brackets around it.  Second, it has to be one of the prescribed MDX functions that return a set.  Remembering those two things, building the MDX set expressions becomes easier.

Using the ASOSamp application here are a few examples progressively showing you how you can restrict your script more and more precisely:

• If you wanted to remove all Qtr4 data (regardless of year or any other dimension) your command would look like this:

alter database 'ASOsamp'.'Sample' clear data in region '{[Qtr4]}' Physical

·         If you wanted to remove only Qtr4 for current year (regardless of any other dimension) your command would look like this:

alter database 'ASOsamp'.'Sample' clear data in region 'Crossjoin({[Qtr4]},{[Curr Year]})' Physical

·         If you wanted to remove Qtr3 and Qtr4 for current year (regardless of any other dimension) your command could look like any of these:

o   alter database 'ASOsamp'.'Sample' clear data in region 'Crossjoin({[Qtr3]:[Qtr4]},{[Curr Year]})' Physical

o   alter database 'ASOsamp'.'Sample' clear data in region 'Crossjoin({[2nd Half].Children},{[Curr Year]})' Physical

o   alter database 'ASOsamp'.'Sample' clear data in region 'Crossjoin({MemberRange([Qtr3],[Qtr4])},{[Curr Year]})' Physical

·         If you wanted to remove Qtr3 and Qtr4 units for current year (regardless of any other dimension) your command could look like this:

alter database 'ASOsamp'.'Sample' clear data in region 'Crossjoin(Crossjoin({[Qtr4]},{[Curr Year]}),{[Units]})' Physical 

·         If you wanted to remove Qtr3 and Qtr4 units for current year (regardless of any other dimension) your command could look like this:

alter database 'ASOsamp'.'Sample' clear data in region 'Crossjoin(Crossjoin(Crossjoin({[Qtr4]},{[Curr Year]}),{[Units]}),{Descendants([Online])})' Physical

Keeping in mind the basics of MDX scripts is the key to getting your clear script defined down to a more granular level.  It is also important to note that the more complex your MDX is the longer your clear script may take to run.  If the data set being cleared is going to be very small you may want to use a logical delete as the increase to your database size will not be as noticeable.  If you are going to delete a large chunk of data, the physical delete may make more sense as a new large slice may take up a lot of memory and impact performance.

ASO vs. BSO Calculations

There are a lot of reasons why Essbase owners should have the majority (or all) of their cubes as ASO now. ASO loads faster, calcs faster, have a smaller footprint, allow for far more dimensions, and a few other things that others have already written a lot about. Prior to the current version of Essbase there were some concerns and it wasn’t quite as user friendly, but the Fusion edition (11.x) has addressed many of those concerns. Now with incremental loads, partial data clears, and ability to write back to level 0 members there is a lot more flexibility.

The reason why I write this today though is because I found another great reason to have ASO cubes instead of BSO. Some have written that BSO cubes allow for more complex calculations, which may be true in some regards. I have found that MDX in ASO cubes provides greater flexibility and allows writing of complex calculations in far few lines of code in a member formula.

For example, we have one BSO cube remaining that we use for planning purposes. A recent report request required a formula be added to the cube. The formula in and of itself is not complex. What is complex is that there are multiple IF scenarios that have to be embedded to ensure that the right calculation takes place for the right period of time. While writing this formula I received the following error message:

I never knew I was limited to a formula size. It makes obvious sense; I had just never run up against the limit before. I ended up having to remove some of my IF scenarios to get the formula down to the appropriate size. It’s not a huge deal as I guess no one is likely looking at the period over period growth rate from 3 years ago, but it was frustrating none the less.

So this got me thinking, we have this same formula in other cubes, ASO cubes. I was curious how they compared because I know it wasn’t as painful to write the ASO version as what I went through writing the BSO version. This is what I found:

• A fully written BSO formula was 1242 lines and 75,493 bytes
• A fully written ASO formula was 78 lines and 4,445 bytes

(I won’t bother showing you the 2 formulas as who wants to look at that many lines of code if they don’t have to)

And I think it is interesting to point out the BSO formula was written to calculate at the Month, Quarter, and Year level only. The ASO formula is written to calculate the Week, Quarter, Month, and Year levels. So technically, the ASO formula is doing even more than the BSO formula.

The difference is that in MDX there are some wonderful functions that allow you to select your data more succinctly and apply the desired calculation. I will be the first to admit, MDX isn’t always the easiest to read and sometimes building a complex calculation using multiple MDX functions embedded with each other can be frustrating to put together, but in the end I find that almost always my formula is shorter and therefore easier to maintain.