TBLast on an ASO Cube

Today’s post will be short as I have way too much to do before I leave tomorrow morning for Oracle OpenWorld – of which I am very excited about.  I’ll be around at multiple events so I hope to meet you if you are going!

So yesterday you learned that I used to be an in-house administrator.  Today’s “something” is that I am a HUGE football fan.  I love watching the games.  I didn’t get into football until after college when I refused to get cable when I traveled all the time for work.  In the south on the weekends, there was only football on TV.  So, I learned the game and fell in love with some teams.  My blood bleeds ARKANSAS RAZORBACK red on Saturdays and on Sundays you can catch me rooting for the Bengals (hometown is Cincinnati), Falcons (just moved from Atlanta) and Seahawks (my respect for Russell Wilson is enormous…such a fantastic leader and motivator!).

Enough of football (well, not really)…time for today’s post.  Have you ever tried to implement time balances (ie: TBLast) on an ASO cube without the help of a View dimension?  This can be tricky.  You can tag an account as TBLast so long as you do not have more than one hierarchy in your Period dimension.  This doesn’t bode well if you have alternate hierarchies, say, for QTD, YTD, etc.  So how can you implement TBLast on a cube with multiple period hierarchies?  Through the use of UDAs and MDX.  (Note: I will be demonstrating TBLast, but the same logic applies to First and Average.)

The first thing you will want to do is identify the accounts that you want to be TBLast.  The simplest way to do this is to create a UDA named “TBLast”.  The next thing you will need to do is put calculations on various members in the Period dimension.  For example, if we take Quarter1, the MDX would be the following, which checks if a member has TBLast and goes through the calendar in reverse to see if there is a value or if it is #Missing.  Quarter1 will show the last month with data.

CASE
WHEN (ISUDA([Account].CurrentMember, “TBLast”))
THEN (IIF(NOT ISEMPTY([Mar]), [Mar],
IIF(NOT ISEMPTY([Feb]), [Feb],
IIF(NOT ISEMPTY([Jan]), [Jan],
MISSING))))
ELSE [Jan] + [Feb] + [Mar]
END

Pretty simple, huh?  If you are concerned about retrieval time, you should be fine.  I performed tests with this set of code on a LARGE database with no retrieval degradation.  I have listed out other time periods below for you to use and enjoy.

TB_ASO_MDX <- Click here for the Word version of the code