This Essbase discussion board is provided as a free service and dedicated to all the Essbase professionals out there!
 << Previous Topic | Next Topic >> Return to Index

# calcDim vs calcAll

March 24 2017 at 8:32 AM
No score for this post
Sameer
from IP address 192.8.229.10

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 !!

 Scoring disabled. You must be logged in to score posts. Respond to this message
AuthorReply
Cameron Lackpour

156.39.10.46

# Where to start?

No score for this post
March 24 2017, 9:43 AM
 Sameer, You really owe it to yourself to read the Database Administrator's Guide (DBAG): http://docs.oracle.com/cd/E57185_01/EDBAG/dcadevcs.html#dcadevcs_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.
 Scoring disabled. You must be logged in to score posts. Respond to this message
Anonymous

192.8.229.10

# Re: Where to start?

No score for this post
March 24 2017, 10:04 AM
 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?
 Scoring disabled. You must be logged in to score posts. Respond to this message

Jeff McAhren

165.225.34.126

# Re: Where to start?

No score for this post
March 24 2017, 12:07 PM
 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.
 Scoring disabled. You must be logged in to score posts. Respond to this message
Mike Henderson

73.37.228.249

# Re: Where to start?

No score for this post
March 24 2017, 12:25 PM
 >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.
 Scoring disabled. You must be logged in to score posts. Respond to this message
sameer

157.49.6.194

# Re: Where to start?

No score for this post
March 26 2017, 3:35 AM
 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.
 Scoring disabled. You must be logged in to score posts. Respond to this message

Jeff McAhren

165.225.34.106

# Re: Where to start?

No score for this post
March 27 2017, 10:01 AM
 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.
 Scoring disabled. You must be logged in to score posts. Respond to this message
Pete

1.136.96.66

# Re: Where to start?

No score for this post
March 27 2017, 5:41 PM
 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.
 Scoring disabled. You must be logged in to score posts. Respond to this message
Cameron Lackpour

173.49.81.149

# Here you go...

No score for this post
March 28 2017, 10:37 AM
 http://www.network54.com/Forum/58296/thread/1106602573/Using+Calc+All+with+Except+clause Given the age of that, it looks like way before 11.2.3.anything. I too also cannot literally even. Surely some: -- under the influence of something really strong a PM agreed -- industry analyst identified the lack of this as a box that had not been ticked -- evil and malevolent developer in the Essbase team thought he'd introduce pain because fun is responsible. Regards, Cameron Lackpour
 Scoring disabled. You must be logged in to score posts. Respond to this message
Pete

124.189.106.76

# Actually!

No score for this post
March 27 2017, 5:52 PM
 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?
 Scoring disabled. You must be logged in to score posts. Respond to this message
Cameron Lackpour

173.49.81.149

# Which brings all of this back to RTM

No score for this post
March 28 2017, 10:33 AM
 I get it -- multidimensional is a bit off-putting in the beginning. I know that I had to build my first System W (OMG, on a mainframe) database to well and truly get it. I suspect you are doing the same. I don't know how to describe the full understanding of a multidimensional array but when it clicks, it clicks. The difference between the early 1990s and today is that there are now: -- Presentations (free) -- Webinars (free) -- Books (practically so and pirated everywhere although I'm not suggesting the latter) -- Blogs (free) -- Helpful people on nessage boards (free, see this thread) -- Conferences (to be fair, there were vendor conferences back then but they were exercises in sales) -- Who knows what else (dunno, make it up as it suits you) And documentation. Here's a great bit on understanding how multidimensionality works: http://docs.oracle.com/cd/E57185_01/EDBAG/understanding_multidim_dbs.html#dinconc27419 And another bit on calculation order: http://docs.oracle.com/cd/E57185_01/EDBAG/dcacaord.html This isn't to pick on you, Sameer, it's just that you'll likely get a less disjointed view of things if you RTM. I am assuming that you did not given the nature of your question. If you didn't, go to it. If you did, you might want to think about reading it again. You'll get answers here but by a messageboard's nature there are all (sometimes informative, sometimes entertaining, sometimes both) sorts of responses. Again, this is not a dig at you -- just some food for thought. Regards, Cameron Lackpour
 Scoring disabled. You must be logged in to score posts. Respond to this message
sdt

208.203.94.34

# Also

No score for this post
March 24 2017, 9:58 AM
 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.
 Scoring disabled. You must be logged in to score posts. Respond to this message

 << Previous Topic | Next Topic >> Return to Index

RSS feed for this forum - http://www.network54.com/Forum/58296?xml=rss. Please email hypess (at) gmail.com, if you have any questions/feedback/issues.