calcDim vs calcAll

calcDim vs calcAll

Joined: October 21st, 2016, 10:22 am

March 24th, 2017, 12:32 pm #1

Can any one help me to understand the exact difference between CalcDIM and CalcAll

here my concern is if we are calculating a dimension then all dimensions and all combinations should be calculated because we refer data in Essbase member combination off all dimensions(data point) not by a single dimension.

thanks in advance !!
Quote
Like
Share

Cameron Lackpour
Cameron Lackpour

March 24th, 2017, 1:43 pm #2

Sameer,

You really owe it to yourself to read the Database Administrator's Guide (DBAG):
http://docs.oracle.com/cd/E57185_01/EDB ... cadevcs_32

Short answer: CALC DIM calculates but one dimension, CALC ALL does the entire database.

Fwiw, CALC DIM is only required for dense dimensions although it will work for spare ones. If the latter, AGG (dimname) ; is usually faster. If you are calculating a dense dimension, consider making all calculations dynamic.

Regards,

Cameron Lackpour

P.S. Take a look at interrel's series on Essbase books as well. You might check out my books as well. :) The first one has a great chapter on BSO by Dave Farnsworth.
Quote
Share

sdt
sdt

March 24th, 2017, 1:58 pm #3

Can any one help me to understand the exact difference between CalcDIM and CalcAll

here my concern is if we are calculating a dimension then all dimensions and all combinations should be calculated because we refer data in Essbase member combination off all dimensions(data point) not by a single dimension.

thanks in advance !!
If you run a CalcDim on a dimension it will execute any member formulas that are NOT Dynamic Calc too.

The AGG function will NOT execute any non-Dynamic member formulas. Be careful with member formulas on Sparse dims though.

Quote
Share

Joined: October 21st, 2016, 10:22 am

March 24th, 2017, 2:04 pm #4

Sameer,

You really owe it to yourself to read the Database Administrator's Guide (DBAG):
http://docs.oracle.com/cd/E57185_01/EDB ... cadevcs_32

Short answer: CALC DIM calculates but one dimension, CALC ALL does the entire database.

Fwiw, CALC DIM is only required for dense dimensions although it will work for spare ones. If the latter, AGG (dimname) ; is usually faster. If you are calculating a dense dimension, consider making all calculations dynamic.

Regards,

Cameron Lackpour

P.S. Take a look at interrel's series on Essbase books as well. You might check out my books as well. :) The first one has a great chapter on BSO by Dave Farnsworth.
Thanks Dave for your reply.
"CALC DIM calculates but one dimension, CALC ALL does the entire database." this line does't make sense to me because the data we talk about in essbase cube is a combination of each member from all dimensions.

if i say i am calculating one dimension it should calculate all combinations with respective other dimensions members to show up the data.i am confused on that statement how could we say only one dimension is calculated?
Quote
Like
Share

Joined: November 15th, 2007, 1:21 pm

March 24th, 2017, 4:07 pm #5

Yeah, but the different dimensions are calculated/rolled up one by one in that cube. Some dimensions don't need to be calculated for one reason or another.

Calc All might make sense in really small databases, but it's usually not used in larger, more complex databases. There are actually some database designs where calc all would result in *bad* data (it's complex).

Read the DBAG and books like Cameron suggested.
Quote
Like
Share

Mike Henderson
Mike Henderson

March 24th, 2017, 4:25 pm #6

>Some dimensions don't need to be calculated for one reason or another.

Example:

In my experience, nobody ever cares to see the rollups of Years, Scenarios, or Versions. These are typically small, non-aggregating sparse dimensions.

It is also common to set Dense dimensions (typically Period and Account) to roll up dynamically at retrieve time because all of the necessary info is present in the data block your user has pulled ... computers do math quickly but fetch blocks from memory slowly. If the block has all the info, let it compute at retrieve time.

So ... use CALCDIM to roll up only the **aggregating** Sparse dimensions while setting Dense dims to do their stuff dynamically. It seriously improves calc performance with no loss of retrieve speed.

Another item is I believe CALC DIM allows us to specify the order in which dimensions are calculated. CALCALL does them in outline order only.

A last thought ... consider using AGG instead of CALC, especially if your dimension has no member formulas. AGG is often faster since it ignores member formulas and simply rolls up according to the outline consolidation operators.
Quote
Share

Joined: October 21st, 2016, 10:22 am

March 26th, 2017, 7:35 am #7

Thanks you all.

hi Henderson,

thanks for your reply.

Here my question is not whare to start what to use either cac all or calc dim OR year dimension ..etc

my question is logicial not technical.

for example if we have performed calcdim on account dimension and not on departments, in samrtview if we want to see data it should't have any difference in any angle of combinations(either account or departments) because we see data in combination not only in sigle dimension prespective.

here in this case if we pull data at diffent aggrigate levels of departments it shoud have caluculated already while account dimension aggrigation(aggrigation means calculating data to upper level based on current comnination data points). because data in essbase means data point not represented by single dimension.
Quote
Like
Share

Joined: November 15th, 2007, 1:21 pm

March 27th, 2017, 2:01 pm #8

I understand why you're stuck on that point, but you're simplifying Essbase too much, and you're not absorbing what others have been trying to tell you.

Sometimes only one or two dimensions actually aggregate up. Like scenario or fiscal year. Why would you want to calculate them?

There are also reasons why you may not what to roll up another analytical dimensions.

That's why there are different calc commands.

Lots of Essbase applications have complex designs that you are not considering.

In your example, yes, you will need to make sure that you calculate accounts, and roll up departments. Calc All would get it done, but it might be more efficient to calc accounts and agg departments. You will have more control over the order, and you can often times decrease the work that it takes (time). Also, Calc All would touch other dimensions that you might not want calculated because there is special logic in those dimensions that is implemented in another more complex calc, and Calc All would stomp all over it.

Simply put, I think most production Essbase applications are just more complex than the simple scenario you're considering in your head.
Quote
Like
Share

Pete
Pete

March 27th, 2017, 9:41 pm #9

The only 'production' instance we have that runs a calc all is an internal db tracking size and properties of applications - and the calc all is due to sheer laziness.

This is probably also why it's not hybrid and fully dynamic.

To the OP, another option if you're looking at databases where 'calc all' is an option may well be to go straight to an ASO cube. If there is literally no complicated logic held inside the cube you'll likely be better off just in ASO where you don't have to worry about aggregation.

pete

ps: For anyone else who wants a quick laugh, have a look at the new 'CALC ALL EXCEPT' functions (11.1.2.3? .500?). To quote every high school girl ever, I literally cannot even.
pps: I have a vague jet-lagged memory of somebody presenting at kscope 16 on the very unused 'calc all' and 'intelligent calculation' as a very real option - was anyone else in that session or did I literally make it up in my mind.
Quote
Share

Pete
Pete

March 27th, 2017, 9:52 pm #10

Thanks you all.

hi Henderson,

thanks for your reply.

Here my question is not whare to start what to use either cac all or calc dim OR year dimension ..etc

my question is logicial not technical.

for example if we have performed calcdim on account dimension and not on departments, in samrtview if we want to see data it should't have any difference in any angle of combinations(either account or departments) because we see data in combination not only in sigle dimension prespective.

here in this case if we pull data at diffent aggrigate levels of departments it shoud have caluculated already while account dimension aggrigation(aggrigation means calculating data to upper level based on current comnination data points). because data in essbase means data point not represented by single dimension.
I've re-read your post.

Okay, I think you might be getting caught on the different between:

FIX(@LEVMBRS("Company",0))
Calc Dim (Account);
ENDFIX
Agg Company;


And just
Calc Dim (Account);

Logically you're wondering why you need to rollup the company dimension at all, because mathemtically you'll get the correct answer if you rollup the account dimension without fixing on the company (the upper levels will be recalculated).

Is that kind of what you're asking?
Quote
Share


Confirmation of reply: