Namaste!
I would like to write few series on the questions my students asked during the course.
Here are few questions and answers I would like to share with you all on Hyperion Essbase related.
More to come latter!
Question1
In Most of the Essbase Outlines always Accounts and Time are set as Dense.
As per my understanding Scenario dimension also Dense in Nature.
But, Why Scenario dimension is set as Sparse mostly in real time? Please advise.
Answer
It also depends upon how you use (retrieve) the data/Calculate the data. Most of the times, we retrieve either Actual data or Budget data and also calculation scripts will run against one scenario at a time.
In this situation if we set scenario as sparse, then retrieval will be easy. System can retrieve only 'actual' related blocks at one time (or blocks related to Budget at a time)
Question2
A little confusion with Data and members.
for example if 1000 is data then it will go and sit in level 0 level right?.
Are level 0 members means data ?
Answer
Level 0 members are not numerical data. Level 0 members are also called metadata, the characteristic (or description) about data.
When we say 1000, there is no meaning rt, it could be sales or discount or rent.
Let us assume it is sales. I will say Sales = 1000. Then we get another question: which month is it? Let's say Jan.
Now we have more description about 1000. Jan, Sales = 1000. In this case Jan is member, sales is member, 1000 is data
now we get another question, which year is it? let's say year 2020. We can call this member as FY20.
Jan, FY20, Sales = 1000
As you need more description about data, you will add more dimensions to Essbase database to describe the data.
Question3
How to build dimensions with formulas that contain "" and ,
IF(@ISMBR("Jan"))
@VARPER ("MetricA"", @PRIOR( "Dec"->"MetricA", 1, @RELATIVE( "Years", 0 )))/100;
ELSE
@VARPER(MetricA,@PRIOR( "MetricA"))/100;
ENDIF
Some of my the metrics I'm building contain such formulas. I'm having trouble uploading these dimensions using the Rules Files because of the commas and spaces in the formulas.
After using the pipeline (|) delimiter and replacing the newlines with spaces as shown below:
IF(@ISMBR("Jan")) @VARPER ("MetricA", @PRIOR( "Dec"->"MetricA", 1, @RELATIVE( "Years", 0 )))/100; ELSE @VARPER("MetricA",@PRIOR( "MetricA"))/100; ENDIF
I still face the problem of EAS removing the quotation marks for me. This appears in the formula editor:
IF(@ISMBR(Jan)) @VARPER (MetricA, @PRIOR( Dec->MetricA, 1, @RELATIVE( Years, 0 )))/100; ELSE @VARPER(MetricA,@PRIOR( MetricA))/100; ENDIF
Please advise what is the best way to build dimensions with such formulas!
Answer
There are two ways of doing this. You may use either of the below method .
1. Please use single apostrophe ' character before and after "
Example '"'MetricA'"'
2. Use \ character before "
Example \"MetricA\"
In general, we would see formulae in account dimension or scenario dimension and most of the times these are one time build, so can be done manually. However we may come across situations where the formulae need to be built using rules files. we have to format the source file formulae as per one of the above formats.
Happy Learning!
I
have published an Advanced course on Hyperion Essbase on Udemy. Here
is the coupon code. Please check it out if you are interested.
https://www.udemy.com/course/hyperion-essbase-advanced-course/?couponCode=ESBADV6
