Sanity Check - Retrieval Times

[hbell]

Using 9.1.3.

Am I right in thinking that replacing a load of "on-the-fly" rule calculations with the pre-calculated numbers should result in faster retrieval times? Bizarrely, we are having the opposite experience. It is a large cube (9 dimensions and 4000 lines of rules). We have built an exact 9 dimensional replica - but with most of the rules taken out. We are copying the data across into this new cube - including the calculated values. However, with only a small subset of the data copied across so far it is already noticeably slower to retrieve an identical view (5 seconds vs 1 on the original).

This seems counter-intuitive. At first I thought that pre-populating data might change the sparsity characteristics of the cube. However, I've played around with dimension orders - to no effect so far.

I realise there are many possible complications here. Rather than expecting anyone to be able to tell me what is wrong in this specific instance, I just wanted to check whether my original premise (pre-calc should be quicker than calc) was flawed? Would anyone expect to see this kind of behaviour?

thanks .........hugh

[lotsaram]

Values should consolidate much quicker than rule calculations, that's always been my experience. So yes your result is surprising and counter-intuitive.

You say that you have "taken most of the rules out" in the replica cube, which means not all of the rules are out? My advice would be to take a very careful look at your feeders. The hypothesis being that now that you have much more actual entry level data in the 2nd cube that this data is firing feeders which don't need to be there and causing the slow calculation performance. Get rid of the overfeeding and performance in the 2nd cube should get much better.

Note that to clear feeders you need to unload the cube, saving the rule fie alone won't do the trick.

[hbell]

.. thanks for the suggestion .. but it's not that. I've even deleted the rules completely and the view still takes as long to load

[Marcus Scherer]

I hope you deleted the rule completely and not only the declarations in it? A blank rule sheet will slow things.

[hbell]

... yep ... completely deleted ... but at the very least a blank rulesheet (with skipcheck on it) should be quicker than a sheet with 4000 rules

[hbell]

... new twist. I've created a third version of the cube. Exactly the same dimensions. No rules AT ALL. No data AT ALL. The view still takes longer to open (even with all zeroes) than the original calculating cube. That is now seriously weird.

[Steve Rowe]

Just wondering a few things.

Was the original cube built in a prior release of TM1?
Has a server restart been performed?
The three views are the same? i.e. same elements and the same ordering of dimensions within the view.
When you say view do you mean, cube viewer, view export or excel slice?

Can't think of a reason for this unless the server has not been restarted, it's at least plausible to think that a restart is required to integrate a cube into the RAM properly....

[hbell]

Steve..

1. the cube was built in the current version
2. not sure whether the server has been restarted (the designer is out today). I will check that next
3. as far as I can see everything is identical (dimensions, dimension order, element selections)
4. I was referring to a cube view (Server Explorer). Interestingly I tried slicing out to Excel and the performance is MUCH faster to recalc. In Excel both cubes operate at about the same speed (1 second or so). So the aberrant behaviour seems limited to cube viewer.

hugh

[Herman Moller]

Hugh,

To confirm you are using 9.1 SP3?

Are you opening the cube up via a LAN or WAN?

If you have a small cube 4 dimensions does it open up quicker?

I have seen performance issues with a WAN connection for large number of dimension cubes opened over WAN.

Herman

[hbell]

Steve

... I've restarted the server. No change to the behaviours of any of the cubes. The rule driven one is almost instanteous, the "dead data" ones still take about 10 seconds to retrieve the same view.

Herman

... yes, we are using 9.1.3. I am opening the cube over WAN. If the number of dimensions was an issue, would it not have the same effect for the rule driven cube (9 dimensions also)? However, this calculates really quickly. I am also mystified why the calc time is so much faster in an Excel slice.

Anyhow, I've achieved my objective of sense checking our original intention. It seems everybody agrees it should have worked that way in theory. We will keep digging - and will post the answer (however foolish) if we do find it. Not to discourage anybody who thinks they might have a good idea ...


many thanks ..........hugh

[paulsimon]

Hugh

In general consolidation is about 100 times faster than adding numbers together via rules.

If you fully load the new cube, how does its size on disk and in memory compare to the old cube? I would expect the rule based cube to be smaller on disk but larger in memory. If the new cube is larger in memory that would be suspicious.

Is it possible that the physical order dimensions of the rule based cube was optimised, but in the new data cube the physical order matches the logical order?

Was the export of data excluding calcs? If not is it possible that a lot of unfed ratio calcs got exported and loaded up, so are increasing cube size?

Could the new cube be being fed by another cube - unlikely given that is new unless you have rules with parameterised cube names?

You mentioned a rulesheet does that mean a rule worksheet? Have you checked the rule editor on the cube just has a skipcheck?

Regards


Paul Simon

[Herman Moller]

Hugh,

When opening a largish cube(9 Dimensions) it might take a number of seconds to open up the cube even if it is empty(I am just taking about opening up the cube viewer). This was a error noticed in 9.1 connecting over a WAN, but I think has been worked on 9.1.4.

From the 9.1.4 release notes Fix list:

"Browsing a view in the cube viewer is slower in TM1 9.1 SP3 compared to previous versions of TM1 9.0."

HTH

Herman

[Steve Vincent]

Is it possible that the physical order dimensions of the rule based cube was optimised, but in the new data cube the physical order matches the logical order?

What might help with that is to list the dims in the order they appear in the cube and the number of elements in each. If that order looks "unoptimised" we might be able to help you understand where the issue is.

[hbell]

Steve

... sorry for the delay. I've been out on holiday. Attached is an Excel spreadsheet with screenshots of the dimension orders. They have been optimized according to the "memory usage" indicator that is shown on the reorder dimension dialog.

hugh

[hbell]

OK ... we found the solution to this and it surprises me (though probably known to the more expert among you). Although the views looked the same, in one view a "page" dimension had the element selected from the ALL subset of a 5,000 element dimension. The other (faster) view had the same element selected but from a smaller named subset. This "hidden context" to the view turns out to have an effect on the speed of the view.

Hope that discovery helps someone out there (and doesn't excessively exasperate the gurus).


hugh

[Steve Rowe]

Hi Hugh,

This piece of functionality known as "Stargate", it's a little bit of a mystery as to if it it actually brings a performance benefit. It involves calculating and caching data that has not been asked for but is nearby in terms of the orientation of the view.

UseStargateForRules and DisableWorksheetView are a couple ofconfiguration options in this area, obviously use them with caution, they may help you improve the performance of your system.

The simple thing to do to rule out stargate behaviour as a problem in a worksheet is to range value the=VIEW ( blah) function that returns the cube reference in any sliced reports.

Cheers