Cube Dependency impact on performance

Post Reply
harrytm1
Regular Participant
Posts: 226
Joined: Thu Apr 02, 2009 2:51 pm
OLAP Product: IBM Planning Analytics
Version: Latest version
Excel Version: 2003 to 2019

Cube Dependency impact on performance

Post by harrytm1 »

Hi all,

I read the IBM Best Practice on Parallei Interaction. It mentioned that cube dependency is established during server startup. However, it does not establish dependency feeders that are using ATTRS or ATTRN functions during server load. It mentioned that TI can be used to AddCubeDependency.

Can anyone recall which version is Cube Dependency introduced? I don't recall seeing it in 9.4.1.

What is purpose of cube dependency? I feel that it somehow makes certain calculations slower as rules and feeders that use ATTRS/N will establish CD during first time query or data update. This leads to longeer view generation. Is my understanding correct?

Also, I recently added the ParallelInteraction=T parameter and user feedback so far is that it is slower. the server has 32GB RAM and the TM1 service only utilises 8GB. It is a budgeting solution so users can entering data via cube views, DBS and TI. At the same time, they are also generating reports.

This result is contradicting the purpose of PI. Rather disappointing.


Look forward to your reply.
Planning Analytics latest version, including Cloud
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Cube Dependency impact on performance

Post by lotsaram »

harrytm1 wrote:I read the IBM Best Practice on Parallei Interaction. It mentioned that cube dependency is established during server startup. However, it does not establish dependency feeders that are using ATTRS or ATTRN functions during server load. It mentioned that TI can be used to AddCubeDependency.

Can anyone recall which version is Cube Dependency introduced? I don't recall seeing it in 9.4.1.
The concept of "cube dependencies" was introduced in 9.1 with the cube object based granular locking model. Cube dependencies are determined on server startup by reading feeders. Once the model is running further dependencies are established by view creation, CellGetN or any other query that asks for a rule determined cell value where the calculation engine has to lookup a value outside the current cube in order to evaluate the cell(s) being requested. While a new dependency is being established a server wide lock is held. In a multi-user environment this can have a detrimental performance impact.
harrytm1 wrote:What is purpose of cube dependency? I feel that it somehow makes certain calculations slower as rules and feeders that use ATTRS/N will establish CD during first time query or data update. This leads to longeer view generation. Is my understanding correct?
Due to the potential negative impact from locking of establishing dependencies the ability to "pre-set" dependencies with a TI command was introduced in 9.4.1 (in some later fix pack or hot fix, not sure EXACTLY when and which version). There are also other work arounds to pre-establish dependencies like using ViewConstruct but setting dependencies directly is usually pretty simple and quick. If the dependency is already established BEFORE a user query then there is no additional locking impact. Once persistent feeders came out in 9.5.1 it became more important to pre-set cube dependencies as rule files are no longer eveluared on startup for an initial guestimation of dependencies. The actual action of establishing the dependency itself is very quick and has no performance impact at all. Your interpretation or inference is not correct.
harrytm1 wrote:Also, I recently added the ParallelInteraction=T parameter and user feedback so far is that it is slower. the server has 32GB RAM and the TM1 service only utilises 8GB. It is a budgeting solution so users can entering data via cube views, DBS and TI. At the same time, they are also generating reports.

This result is contradicting the purpose of PI. Rather disappointing.
Which version are you using as Parallel Interaction is only available from 9.5.2. Unless you are using 9.5.2 the config parameter will not have any impact. Parallel interaction will not affect single user query or data entry times. The benefit comes in removing "writer starvation" that affected prior versions where users doing data entry would have to queue behind users querying views (running reports). I have found parallel interaction to be almost immeasurably beneficial, it is hard to quantify the performance benefit in % terms when my 50 - 60 concurrent writers mostly doing sub-second data updates no longer have to wait for 120sec every few minutes due to a handful of users in HQ pulling a massive high level views on forecast submission day.
harrytm1
Regular Participant
Posts: 226
Joined: Thu Apr 02, 2009 2:51 pm
OLAP Product: IBM Planning Analytics
Version: Latest version
Excel Version: 2003 to 2019

Re: Cube Dependency impact on performance

Post by harrytm1 »

thanks, lotsa.

So PI works for you. Recently I came across some discussions about TM1 performance. Someone mentioned that having intercube rules will result in slow read/write performance. the usual budgeting solution setup requires sub-cubes such as Opex, Capex, Manpower, Revenue etc (and each one may have other sub-cubes) pushing data into P&L and Balance Sheet cubes.

So does this mean that, regardless of the introduction of cube dependency, such design will always lead to performance issue? Having all these in one monther of all cube seem a huge ask.
Planning Analytics latest version, including Cloud
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Cube Dependency impact on performance

Post by lotsaram »

Yes PI works. However it only works once cube dependencies are established! (another reason to make sure this is done before letting loads of concurrent writers lose on the model.)
harrytm1 wrote:Recently I came across some discussions about TM1 performance. Someone mentioned that having intercube rules will result in slow read/write performance. the usual budgeting solution setup requires sub-cubes such as Opex, Capex, Manpower, Revenue etc (and each one may have other sub-cubes) pushing data into P&L and Balance Sheet cubes.

So does this mean that, regardless of the introduction of cube dependency, such design will always lead to performance issue? Having all these in one monther of all cube seem a huge ask.
I think you are referring to a comment by Tomok a week or 2 back. Cubes with pure data entry and consolidation will always be faster than cubes with rules purely due to the relative efficiency in the calculation engine between consolidation and rule calculations. But don't take this as advice not to use rules or to try and force different types of data entry all into a single cube, that is not what was intended. It makes sense from both a model design and usability point of view to split say a P&L model into sales, employee cost, direct cost, indirect cost and general assumptions modules all funneling into the P&L cube. Tomok's point was that any kind of design where there is a long chain of dependent rule calculations from cube to cube is not going to perform well once it gets to a certain depth. Hard to say just what that depth is since it would be highly model dependent due to data volume, complexity of calculations and probably most importantly dimension sizes and effect on any overfeeding along the way (both "necessary" overfeeding and any inadvertent overfeeding due to design slips) but suffice to say that any model that has a 10 deep chain of calculations from many different cubes is probably not going to perform that well when a high level view is requested that has to pull everything together.
harrytm1
Regular Participant
Posts: 226
Joined: Thu Apr 02, 2009 2:51 pm
OLAP Product: IBM Planning Analytics
Version: Latest version
Excel Version: 2003 to 2019

Re: Cube Dependency impact on performance

Post by harrytm1 »

Thanks for the reply.

I'm now confused about the parameter in the AddCubeDependency TI function.
AddCubeDependency('CubeA','CubeB');

It says in the version 9.5.2 (NOT FP1) PDF Operation Guide that:
CubeA = The name of the base cube.
CubeB = The name of the dependent cube.
and to quote "Cube B relies on a rule that is dependent on Cube A."

This means the rules are written IN Cube B.

However, in the same version, BUT the HTML Reference Guide launched through Server Explorer, the following is shown:
AddCubeDependency(BaseCube, DependentCube);

BaseCube = The name of a cube that depends on another cube for data. Most commonly, this would be a cube that uses rules to pull data from an external cube.
DependentCube = The name of the cube upon which the BaseCube is dependent.

Example
AddCubeDependency('SalesCube', 'PriceCube');
This example establishes a dependency between the SalesCube and the PriceCube.

They contradict each other in terms of the definition of the 2 parameters. My take is the 2nd one makes more sense i.e. the Web Reference Guide.

Anyone has any idea which is the right one?
Planning Analytics latest version, including Cloud
User avatar
Mike Cowie
Site Admin
Posts: 482
Joined: Sun May 11, 2008 7:07 pm
OLAP Product: IBM TM1/PA, SSAS, and more
Version: Anything thru 11.x
Excel Version: 2003 - Office 365
Location: Alabama, USA
Contact:

Re: Cube Dependency impact on performance

Post by Mike Cowie »

Hi Harry:

Looking at the Server message log (tm1server.log):

AddCubeDependency( 'CubeA', 'CubeB' ) does this:
TM1.Cube.Dependency Adding cube dependency: cube "CubeB" depends on cube "CubeA"

AddCubeDependency( 'CubeB', 'CubeA' ) does this:
TM1.Cube.Dependency Adding cube dependency: cube "CubeA" depends on cube "CubeB"

So, if you believe the message log (which from some other real examples I can see seems to be trustworthy - talking TM1 9.5.2 no FP), I believe this means that the first cube argument is the cube that the second cube argument depends on in some way. This would mean that this example:
AddCubeDependency('SalesCube', 'PriceCube');

The PriceCube depends on the SalesCube, which doesn't make a lot of sense unless you're calculating prices from sales.

Have you reported the difference/error in documentation? It looks like the first item (PDF) is correct, but the latter (HTML from SE) is not. This is the kind of documentation error that drives people crazy!

UPDATE: I submitted this via "remote email support" on IBM Partnerworld, but by all means if you're an IBM customer feel free to log it as well since it may get more visibility/attention. I never get a warm and fuzzy feeling about "remote email support", since it only ever has a confirmation message and no email receipts like you get when logging an issue for a customer at Passport Advantage. That being said, it's generally easier than getting set up to log calls on behalf of a customer at Passport Advantage...

Regards,
Mike
Mike Cowie
QueBIT Consulting, LLC

Are you lost without Print Reports in Planning Analytics for Excel (PAfE)? Get it back today, for free, with Print Reports for IBM Planning Analytics for Excel!
harrytm1
Regular Participant
Posts: 226
Joined: Thu Apr 02, 2009 2:51 pm
OLAP Product: IBM Planning Analytics
Version: Latest version
Excel Version: 2003 to 2019

Re: Cube Dependency impact on performance

Post by harrytm1 »

Hi Mike,

Exactly... Using the Sales cube and Price cube example, it seems like the first argument is dependent on the second argument i.e. the rule is written in cube in FIRST argument.

Anyway, I just installed 952 FP1 on a server in an effort to solve some serious server-wide locking issue when a huge view is generated. I have enabled Parallel Interaction, using Persistent Feeders (which does not help in this case but just wanna mention it) and also created and ran a TI that AddCubeDependency for any combinations that I can possibly think of, including Control Cubes.

Conclusion: When a user opens a large view in Balance Sheet cube, it will take a long time to generate the view. At the same time, it also hangs the session of other users.

Using TM1Top, I can see that other users are "WaitIXC" while the 1st user's session is running a lot of actions. Part of the reason is because the Retained Earnings from Profit Loss cube is passed to Balance Sheet cube via a rule. At the same time, PL cube is driven by rules such as Capex, Opex, Manpower etc.

Any other ways to avoid locking other users' session? Many thanks!
Planning Analytics latest version, including Cloud
User avatar
Mike Cowie
Site Admin
Posts: 482
Joined: Sun May 11, 2008 7:07 pm
OLAP Product: IBM TM1/PA, SSAS, and more
Version: Anything thru 11.x
Excel Version: 2003 - Office 365
Location: Alabama, USA
Contact:

Re: Cube Dependency impact on performance

Post by Mike Cowie »

Hi Harry:

First, I'd make very sure you've truly added all the cube dependencies needed in advance of opening the view. When opening the view, if you see additional "Adding cube dependency..." messages in the server message log then that would indicate you've missed some that need to be added to your TI process that updates the necessary cube dependencies (bearing in mind that you need to use the PDF guide's interpretation of AddCubeDependency and not the HTML help's intepretation, which appears to be wrong).

If you've definitely ruled dependencies out the only other obvious thing I can think of is does your view contain any dynamic subsets? Those can have similar locking consequences because TM1, I believe, puts in place a metadata lock when recalculating the dynamic subset contents as part of the view retrieval and that is likely to make other users "Wait" until the view calculation is complete. Changing any dynamic subsets used in views to static subsets is the main method to work around this, and many people use dynamic subsets to generate their static subsets (there are other forum topics covering some tips/tricks for doing that, I believe).

Regards,
Mike
Mike Cowie
QueBIT Consulting, LLC

Are you lost without Print Reports in Planning Analytics for Excel (PAfE)? Get it back today, for free, with Print Reports for IBM Planning Analytics for Excel!
harrytm1
Regular Participant
Posts: 226
Joined: Thu Apr 02, 2009 2:51 pm
OLAP Product: IBM Planning Analytics
Version: Latest version
Excel Version: 2003 to 2019

Re: Cube Dependency impact on performance

Post by harrytm1 »

You know what? After I had installed 952 FP1, both PDF and HTML Ops and Reference Guides now swapped roles and again contradict one another!!!

So I now have to write two lines for each depency. Either way, it will work... I hope.
Planning Analytics latest version, including Cloud
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Cube Dependency impact on performance

Post by lotsaram »

harrytm1 wrote:You know what? After I had installed 952 FP1, both PDF and HTML Ops and Reference Guides now swapped roles and again contradict one another!!!

So I now have to write two lines for each depency. Either way, it will work... I hope.
Harry why would you need to write the code for each dependency twice? Regardless of whether the documentation is screwed up or not Mike has already given you the correct interpretation, namely AddCubeDependency(<SourceCube>, <DependentCube>)

Mind you I don't think it would have any adverse performance impact to set up a fictitious cube dependency, but why do something when you don't have to? (especially if it's wrong ....)
HyunaHyuna
Posts: 13
Joined: Thu Jun 16, 2016 7:57 pm
OLAP Product: Planning Analytics
Version: 2.0
Excel Version: 2016

Re: Cube Dependency impact on performance

Post by HyunaHyuna »

Hi all, I hope you forgive me for bringing back such an old topic.

We implemented parallel loads a couple of months ago in a 10.2 with FP5, and now we are experiencing locks whenever feeders are changed in rule files (we are optimizing our rules and adding conditional feeders where needed, and these conditions look in other cubes). Upon further investigation, it seems that it's adding cube dependencies, and that is causing the locks.

Last msg in the log:

Code: Select all

TM1.Cube.Dependency   Adding cube dependency: Cube 'A' depends on cube 'B'. 
I looked into it further, and found two solutions: adding manual dependencies using AddCubeDependency, or including AutomaticallyAddCubeDependencies=T in the config file. Naturally, I chose the latter, and it seems that after restarting the server, the cube dependencies are being added. However, I found out that the default value for this is True anyway. We did not have any kind of setup for AutomaticallyAddCubeDependencies in the config file, it was not set to False, and upon checking earlier logs, it is clear that these dependencies had been added in the past. Why did this fail?

According to the documentation https://www.ibm.com/support/knowledgece ... ncies.html here, it the dependencies are supposed to be set up automatically even for rule changes.
...after a rule edit, save, or recompile, the dependencies expressed in that rule, whether from DB(), ATTRS(), or ATTRN() functions, are automatically re-established.
Does anybody have any idea why these dependencies would fail upon rule changes? Here is a sample feeder change:

Code: Select all

[Measure:A] => [Measure:B]
became

Code: Select all

[Measure:A] => DB(IF(DB('othercube',...) =0,'','thiscube'),..., Measure:B)
It is my understanding that after changing this, the cube dependencies should have also been updated, as the documentation says. Why didn't it? And if it's not supposed to do it and my understanding if lacking, what do you do to solve this issue?

I have read the above discussion about adding manual dependencies, is that suggested or is that considered out-dated with the current versions? It seems risky for me - the TI that does this would have to be reviewed every time a rule is changed...
I have added log4j.logger.TM1.Cube.Dependency=DEBUG to my log.properties file, and I see a bunch of debug information about dependencies - are these all the current cube dependencies, or could this list be missing something?
Post Reply