Ever wondered why people in Essbase kingdom emphasize that the outline be arranged in 'Hourglass' manner?
There is no exact answer for the same, most of them emerging from debates and discussions with experts and some others in interviews. The is no document which pinpoints the correct answer for the same.
If Block calculation order in essbase dbag is anything to go by, I think we might just be close to the right answer, albeit only half of it.
So what does the Dbag say ?
It says that essbase numbers blocks sequentially. Sequentially, like 0,1,2,3.....
The numbering starts as soon as essbase encounters first sparse member in the outline starting from the top of the outline. The numbering is done irrespective of whether the block is created or not. That is how we get the total number of potential blocks in any cube.
Take an example of the standard Sample.Basic application. The hierarchy is arranged as
Account (Dense)
Period (Dense)
Product (Sparse)
Market (Sparse)
For the sake of convenience, lets say Product has three members and Market has 10 members
Product
P1 P2 P3
Market
M1 M2 M3 M4 ................. M10
So the block numbering starts like:
0 : P1 -> M1
1 : P2 -> M1
2 : P3 -> M1
3 : P1-> M2
4 : P2 -> M2
5 : P3 -> M2
6 : P1 -> M3
7 : P2 ->M3
8 : P3 -> M3
......
And so on.
Now, whenever a calculation takes place, blocks are brought into memory by always starting the count from zero. Be it calc all, calc dim or fix, essbase will always bring blocks in a sequence decided by the index. How parallel calculation takes place is something that we need to research further. A sparse restructure will always renumber the indexes if need be.
Now consider a case when data exists for M1 and M3, but not for M2. This is how my index file would look in Hourglass as well as opposite of hourglass
Looking at the table above, you can figure out that blocks of M1 and M3 were brought into memory much sooner in 'Hourglass' model than the other one.
Consider a scenario where we would put a Fix statement on Product and Market, Say P3 and M3, the result is found earlier in hourglass mode than its opposite.
All in all, we can find some answers as to why it is beneficial to arrange sparse dimensions in hourglass model.
How the same helps for Dense dimension ?
to be continued ....
There is no exact answer for the same, most of them emerging from debates and discussions with experts and some others in interviews. The is no document which pinpoints the correct answer for the same.
If Block calculation order in essbase dbag is anything to go by, I think we might just be close to the right answer, albeit only half of it.
So what does the Dbag say ?
It says that essbase numbers blocks sequentially. Sequentially, like 0,1,2,3.....
The numbering starts as soon as essbase encounters first sparse member in the outline starting from the top of the outline. The numbering is done irrespective of whether the block is created or not. That is how we get the total number of potential blocks in any cube.
Take an example of the standard Sample.Basic application. The hierarchy is arranged as
Account (Dense)
Period (Dense)
Product (Sparse)
Market (Sparse)
For the sake of convenience, lets say Product has three members and Market has 10 members
Product
P1 P2 P3
Market
M1 M2 M3 M4 ................. M10
So the block numbering starts like:
0 : P1 -> M1
1 : P2 -> M1
2 : P3 -> M1
3 : P1-> M2
4 : P2 -> M2
5 : P3 -> M2
6 : P1 -> M3
7 : P2 ->M3
8 : P3 -> M3
......
And so on.
Now, whenever a calculation takes place, blocks are brought into memory by always starting the count from zero. Be it calc all, calc dim or fix, essbase will always bring blocks in a sequence decided by the index. How parallel calculation takes place is something that we need to research further. A sparse restructure will always renumber the indexes if need be.
Now consider a case when data exists for M1 and M3, but not for M2. This is how my index file would look in Hourglass as well as opposite of hourglass
Consider a scenario where we would put a Fix statement on Product and Market, Say P3 and M3, the result is found earlier in hourglass mode than its opposite.
All in all, we can find some answers as to why it is beneficial to arrange sparse dimensions in hourglass model.
How the same helps for Dense dimension ?
to be continued ....
