Some Essbase and Planning stuff

This is a continuous post which will be updated with questions as and when they come to my mind.

Q: Can I use Dynamic calc members in calc script
- In a calculation script, you cannot calculate a dynamically calculated member or make a dynamically calculated member the target of a formula calculation

- Dynamic calc members CAN be used in scripts as source. But, using dynamically calculated members in a formula on a database outline or in a calculation script can significantly affect calculation performance. Performance is affected because Essbase interrupts the regular calculation to perform the dynamic calculation.

Q: Various places where createblockoneq can be done
Create blocks on equation can be enabled at
EAS
MAXL
ESSCMD
Calculation script

Q: What is the idea behind FIXING on dense member and having a sparse member equation on sparse member block for block creation ?
First point to be noted: Essbase numbers all indexes, whether or not a block exists for it or not. at the time of restructure. That is also a way to get the potential number of blocks figure.
Second point : A FIX is basically an OR of sets ex. FIX (A, B, C) == FIX(A OR B OR C)
A Fix statement on  ONLY sparse members, searches for indexes and brings all the members for which blocks are existing in memory, that is, blocks where page file entries are present. Note that you haven't asked for any dense member explicitly.
When a sparse equation is written in calculation, only those targets for which blocks exist will be calculated. If any source is absent, it will give incorrect results.

When a dense member is fixed along with sparse members, essbase has to bring all the combinations of sparse in the fix along with the dense members in it. Since dense member is not brought individually, the whole block is brought into memory for calculation.
Sparse equation forces the calculation to take place on all possible combinations in the fix, whether or not block exists. The 'set create block on equation' forces essbase to create a block when Essbase encounters a scenario where it knows it is doing a calculation but has no placeholder for it. Hence calculation is performed and block is created.
Why only a non-constant value is required when writing equations ?  Reason being CREATEBLOCKONEQ is designed to create blocks only when assigning non constant values.
You can limit the block creation by writing conditional blocks.
e.g
SET CREATEBLOCKONEQ ON
FIX(Sparse and a dense member)
Budget
(
    IF(Actual->Sales != #MISSING) /*Budget block created only if actual data exists*/
         Budget = Actual;
    ENDIF
)
This technique is slower than DATACOPY and must be used properly.

Q: What is a calculation pass:

A pass is when blocks are sent to memory for calculation. Essbase might sometimes need to calculate value of a member more than once. That means it has to bring the same blocks in memory again to calculate. Though it tries its best to do all calculation in one pass only, certain conditions might require it to do it twice, e.g. Percentage or rate or per unit price calculation at parent members.  

When two dimensions are tagged as account and time, and when both are dense,  Essbase calculates everything in one pass. (Provided Two pass calc is set to off and no member is set to two pass calc)

When either one of them is sparse, Essbase does calculates in two passes.

Q: What is calculation method:

Calculation mode can either be top down or bottom up. Default is bottom up. That means essbase goes on calculating members in order in which they are arranged in the outline. However, if there are complex formulas with business functions or cross dimensional operators in the script which requires parent members to be aggregated or some other member combination, essbase switches to top-down mode automatically. This can be explicitly changed using CALCMODE BLOCK/TOPDOWN option in calc scripts or in essbase.cfg file.

“Calculation Passes” on page 404"

Essbase evaluates a calc script to find complex formulas (Cross Dim operator or Top members e.g YearTotal->AllStores), in that case Essbase switches to

CALCMODE Top Down. By default it is Bottom Up.

Q: What are the two calculation modes in terms of threads ? 

Regardless of how a calculation is triggered, Essbase can execute the calculation in one of two

modes: 

- Serial calculation is the default. With serial calculation, each calculation pass is scheduled to

run on a single processor. If invoked from a calculation script, the calculations are executed

sequentially in the order in which they appear in the calculation script.

- Parallel calculation breaks each calculation pass into sub-tasks. The sub-tasks that can run

independently of one another are scheduled to run simultaneously on up to 64 or 128

threads. Block storage databases running on 32-bit platforms support up to 64 threads. Block

storage databases running on 64-bit platforms and aggregate storage databases (whether

running on 32-bit or 64-bit platforms) support up to 128 threads. Each thread may be on a

different processor.

Essbase reads the script to evaluate if parallel calc is possible, if not, it will execute serially even if CALCPARALLEL is set

To change from the default serial calculation to parallel calculation, change, at most, two

configuration settings and restart the server, or add an instruction to the calculation script.

See “Enabling Parallel Calculation” on page 880.

- When you calculate a database, Essbase automatically displays the calculation order of the

dimensions for each pass through the database and tells you how many times Essbase has cycled

through the database during the calculation. Essbase displays this information in the ESSCMD

window and in the application log.

For each data block, Essbase decides whether to do a dense or a sparse calculation. The type of

calculation it chooses depends on the type of values within the data block. When you run a

default calculation (CALC ALL) on a database, each block is processed in order, according to its

block number.

Q: If Member A is

                A             (Formula A= P+Q*R)

                                P +

                                Q +

                                R +

And we apply CALCDIM (A); then what is the result ?

I think Formula is applied

Q: Difference between Dynamic and Dynamic calc and store ? When is Dynamic calc and store needed ?

Dynamic calc and store values are stored once calculated dynamically, subsequent retrievals need not require calculations.

Essbase marks blocks for recalculation when

1. Calc scripts are run

2. Database restructured

3. CLEARBLOCK DYNAMIC command executed

Upon next retrieval, the Dynamic calc and store block is recalculated and stored again.

Essbase DOES NOT mark blocks for recalc when data is updated, say when Dataload or lock and send happens on dependant cells. The retrieval in this case will still show the old value, until another batch calc is executed and datablock is 'retrieved'

Hence it is recommended to use CLEARBLOCK DYNAMIC command after a dataload.

Q: What is the benefit of this ? In Implicit retrievals

Ex.

Market (Dynamic)

                East +(Dynamic Calc and Store)

                                X

                                Y

                                Z...

                               

                West +(Dynamic Calc and Store)

                                P

                                Q

                                R...

                               

When 'Market' value is retrieved, East and West values are CALCULATED but not STORED. So, East and West values are calculated and stored only when

CLEARBLOCK DYNAMIC command is executed and East and West are explicitly retrieved

As a recommendation. Do not tag dense members as Dynamic calc and store.

Both type of members cannot be used as targets (LHS) in a calc script

During a calc all or calc dim, calculation of Dynamic and Dynamic calc and store members is bypassed

If you add a Dynamic member in outline, restructure is not required and will not have any effect.

You can tag any member as Dynamic Calc or Dynamic Calc and Store, except the following members:
Level 0 members that do not have a formula
Label-only members
Shared members

Using Dynamic Calc and Store may slow initial retrieval; however, subsequent retrievals are faster than for Dynamic Calc members. Use Dynamic Calc and Store instead of Dynamic Calc for the following members:
An upper-level sparse dimension member with children on a remote database.
Essbase must retrieve the value from the remote database, which increases retrieval time. See Dynamically Calculating Data in Partitions.
A sparse dimension member with a complex formula.
A complex formula requires Essbase to perform an expensive calculation. Any formula that contains a financial function or a cross-dimensional member is a complex formula.
If users frequently retrieve an upper-level member of a sparse dimension, speedy retrieval is important.

Q: Design Recommendations for outline
Hourglass model
Use Dynamic calc for dense members, at parent level or level 0 with simple formulae
Use Dynamic calc and store for sparse members at parent level and members with complex member formula.
Use Label only wherever possible


Q: What are the different types of variables used in Essbase, Planning.

Substitution variables 
Only a text supported with no association with dimension. The variable gets replaced by the value (text) it holds at run time. This variable is also seen in Essbase EAS

User variables
User specific variables have to be associated with Dimension, hence must hold only the dimension member names as values. They cannot hold any text. These can be used in Webforms for user specific dimension member selection.

Variables as Run time prompts 
Enclosed in square brackets
Two types in this as well: Replacement (RTP) and Execution
RTPs are also associated with dimensions.
Variables can be at global  level, Application level or database level.

Q: What is the precedence or RTPs if two variables with same name at two levels ?

Q: What is the difference between ASO and BSO, elaborate on it.


Q: What is security precedence in Planning. 
Precedence issues can occur when
- user is part of two groups having different access to the same object.
- user is given direct access to an object or having an inherited access through group members.
In such cases the thumb rule is : None precedes read and write, and write precedes read. Individual access overrides inherited access


Q: How is ODI variable declared and used as run time prompt
Create a variable and drag it in package, then select the 'Declare variable ' option in variable and generate a scenario. This will prompt the variable to be used, select the option. When scenario is executed, it will prompt for a value. This can be done through command prompt as well.

Q: UDA and attribute difference:

UDA is a text attribute to identify a peculiarity associated with the member, this is limited only to text format and cannot be rolled-up or used for cross-dimensional reporting. It also cannot be arranged in a hierarchical manner.
E.g.: A text uda of Currency can be attached to an entity. Retrievals can be done using the UDA
Attribute: Same definition: identifying distinct characteristics of the member with additional functionality; Used for cross dimensional reporting, arranged in hierarchy, can be of text, date, or boolean.
E.g.: An entity dimension can be associated with attribute 'Tier': Tier 1, Tier 2, Tier3
Data can be rolled up according to Tiers.
Can the same functionality achieved using Alternate rollups:
Yes, but maintaining hierarchy and having cross dimensional reporting cannot be achieved.
Technical: Attributes can be associated only to sparse base dimensions. And the members SUM, AVG etc are dynamically calculated

Give a functional example of UDA and Attribute 
UDA as the name suggests is a text attribute to classify a member based on a peculiarity. Attributes are also the same but with additional functionalities, Attributes can be classified based on Boolean values, date, text and number.
UDA can be used when we dont want to roll up data based on the classifications eg. Currency assigned to country dimension. We will not roll up data based on currency.
Same country dimension can have an attribute based on population, or a product dimension having elaborate classifications which can be rolled up to parent.

How does tagging account and time dimension help 
Once a dimension is tagged as account or time, these dimensions are rolled up first irrespective of their position in the hour glass model and their sparsity or density.
Then, account and time can have more properties that can be used for additional functional purpose.
First: Time balance property: TB First, TB Last, TB Flow , Average, Fill etc.
Skip (optional on selection of TB property)
Second : Variance reporting

Time Dimension : Dynamic time series for period to date values like Y-T-D, H-T-D

What happens when Aggregate missing ON is set 
Say a hierarchy is like this,
Parent
     A
     B
     C
If A, B and C have values #MISSING,
When we set AGG missing to off, the parent is aggregated only when there is at least one child having a non missing value. This does not help in dense aggregations but a huge improvement when aggregations work on sparse.


Are there any restrictions on using specific member names ?
Yes, Some times, keywords can be accidentally used as member names which can cause problems while application refresh or LCM export or Import.
As an example, using 'Count' or 'Date' as member names can create problem if we decide to use attribute dimensions with the same name later on.

Can we use Dynamic time series on custom period dimensions ?
Yes, make sure 'Year' dimension is named as 'Years' for it to work properly