Moving into a new city and settling down is not an easy task.
For those who missed my LinkedIn update, I joined a new firm and moved to a different city 🙂
This post is about a surprise in Essbase (and you maybe knowing it already) and about my interview 🙂
I’ve seen many people asking (dare enough) one-liner questions in OTN and many other forums (most them are a rip-off from so called interview sites) and I replied to one of those questions (long time back) saying that I’ll post about my experience, so here we are.
I don’t believe in those one-liner questions like “What is Dense/Sparse?”.
On a fine day I walked in to Blue Stone’s office (which is now Huron’s office), met Mike Nader on my way and he informed me that John Booth will take your technical round. I can hear my heart beating faster already (not that I’m afraid ;))
After usual introduction John said “Celvin, we have an issue, Let’s see whether you can solve it”.
Celvin: “John, is that part of my interview”
John: “Why not, yes it is”
Celvin: “You sure about it?, What if I mess up?”
John: “No worries, we’ve backups”
Assured by Mr.Infrastructure’s words (about the backup) I started looking at it. My heart still beating faster.
Problem: Values are not showing up correctly for a member which has formula and it is using SUMRANGE.
Ashish (my to be colleague, now my colleague) was explaining to me about the issue and I was trying to figure out whether John is tricking me into this or not. (What can go wrong with SUMRANGE!!!!!!)
Celvin: “John, You are looking at upper level, maybe it is not aggregated and that’s why you are not getting values at top level.”
John: “Ok, let’s look at the lowest level.”
John: “You’ve two minutes to figure out the problem, if not let’s continue with rest of the questions.”
I was on the verge of collapsing, not because of pressure, just because of climbing the stairs (I’m hiding the fact that I used elevator ;))
Luckily I was able to figure out what was happening and that reminded me to write about our Surprise VI. The whole thing reminded me about the movie “Swordfish“. Yes you guessed the scene 🙂
Me (Hugh Jackman) with a laptop trying to crack an issue with SUMRANGE and John (John Travolta) testing whether I can crack it or not. I swear there were no ladies in the room!!!!!
The problem was related to XRANGE in SUMRANGE function.
@XRANGE(Jan,Dec) works, however not @XRANGE(Dec,Jan) – that evaluates to an empty set.
SUMRANGE was written using substitution variables, so I had to change it to get it right. Later after the interview I figured out that there was no need for XRANGE there, anyways let’s continue.
Surprise VI
I changed the substitution variables and forgot to re-start the application. John did the re-start of the application and the issue was fixed.(Oh yes you’ve to do that and that’s our surprise.)
Well it is a documented behavior Setting Substitution Variables.
From the documentation
To ensure that a new substitution variable value is available in formulas, partition definitions, and security filters, stop and restart the application. All other uses of substitution variables are dynamically resolved when used.
Does that mean Smart View can see the updated variable?
Yes it can, well here is the proof.
Create a substitution variable called CurrMnth and set it to “Nov”
Refresh the sheet and you’ll get Nov values.
I’ve a member which refers to the same substitution variable and let’s see (I did a restart of the app)
Now let’s change the substitution variable to “Dec”. Smart View can still pull the correct variable value however the formula cannot.
I’ve seen that happening and used to perform a restart every time I update a sub var. I was lucky that John was looking for an answer to the XRANGE issue 😉
Now that’s makes our VIth surprise. Why can’t formulas, partition definitions, and security filters look at the same place where reporting is querying for the substitution variable?
Hi celvin,.
My requirement is to update substitution variable via batch automation. Challenge here I am facing here is to update weekly variables. Cur variable should be update -5 weeks (5 weeks prior )to current week. For example now it is April 2 nd week so substitution variable cur week should be March first week.we have in period dimension members like jan_week_1,jan_week_2.. etc we are following 4-4-5 distribution.quarter last month will have 5 weeks. Kindly guide me how to achieve this. Any inputs is highly appreciated.
Thanks in advance
I’ve once done this for quarters (take the current month and set Quarters (prior, current and pri prior)). It is going to something similar. Weeks is going to be more complicated. Let me see if I find out what I did at the previous client and post it.
Thanks for quick response celvin 🙂
Thanks for your quick response celvin 🙂
Hi Celvin,
Thank you for good articles.
Even i have similar requirement on substituion variable requirement..Check current variable and update it to next week correctly…for example current week is April_2_week..maxl should update it to April_3_week…our environment is on windows…so not ablw to figure out how to process this and pass correct variable as parameter to maxl update week variable.
Thanks