What does CAFE refresh do ?

[hittony]

Hello,

I'm working on performance trouble shooting as our sheets' refresh takes too much time (30s~40s) to retrieve data. That's why I'd like to understand what are the real actions behind this little CAFE button "refresh".

I presume that from the click, the local machine send a request to the remote server, and then the server reads the cube rules and calculates all the necessary cells in the server's RAM, according to the dimensions presents in the local Excel sheet. When finished, the results will be send back to local machine.

So my questions are:
1. There should be a rules compiler, but can it do auto-optimization, just like that some DB can auto-optimize SQL ? Ex. some logical expression with several "and/or", nested if then else...
2. Our rules haven't skipcheck-feeders, but can feeders bring some improvements in this case ?
3. Is it possible that dimensions layout influence the performance ? Ex: 4/8 dimensions are drop-down lists while the rest 4 are in the cross-tab, this layout may have a worse perf then 5/8 dimensions as a drop-down list ?

Thanks

[lotsaram]

You need to compare the refresh time of a sheet with a CAFE crosstab versus the time taken to refresh a view in server explorer locally on the server hosting the tm1 instance.

Generally CAFE is very fast and efficient. If sheets are taking 30s+ to refresh then I kinda suspect that your performance bottleneck is the model design and the cubes themselves.

[David Usherwood]

Agree with @lotsaram (though I do feel CAFE isn't quite ready for prime time yet). In particular, if you have a decent size system without skipchecks and feeders, it will be ludicrously slow. That's what needs fixing.

[tomok]

1. There should be a rules compiler, but can it do auto-optimization, just like that some DB can auto-optimize SQL ? Ex. some logical expression with several "and/or", nested if then else...

Of course there is a rules compiler there is no auto-optimization in it, at least as far as I know. The way in which you order and structure rules can be critical to their performance. Knowledge is gold.

2. Our rules haven't skipcheck-feeders, but can feeders bring some improvements in this case ?

A cube with rules and without skipcheck and feeders is likely not going to perform too well unless it is small. This is because the sparse consolidation algorithm, which knows to skip empty leaves when consolidating is turned off when you have rules. Skipcheck turns it back on.

3. Is it possible that dimensions layout influence the performance ? Ex: 4/8 dimensions are drop-down lists while the rest 4 are in the cross-tab, this layout may have a worse perf then 5/8 dimensions as a drop-down list ?

Of course. The larger the view (meaning the more dimensions you place in rows and columns), the slower it is going to refresh.

[hittony]

Agree with @lotsaram (though I do feel CAFE isn't quite ready for prime time yet). In particular, if you have a decent size system without skipchecks and feeders, it will be ludicrously slow. That's what needs fixing.

The cube has 8 dimensions, among which the two biggest have 67 and 78 elements, we have 10 years in our year dimension as well, while the system has 13 dimensions. I want to be sure that it's the system size here you are talking about, not the cube size. If so, that means an unused dimension should be removed from a system, otherwise it will ruin all the cubes or views performances. Is that right ?

Of course. The larger the view (meaning the more dimensions you place in rows and columns), the slower it is going to refresh.

Let's take an example, suppose we have 2 layers L1 & L2. In stead of showing both in columns, I put them out of my Excel cross tab and the user can choose one of the two in a drop down list. When the L1 is chosen and calculated, L2 is then ignored ? Or L2 is calculated all the same on the server, but the result isn't sent to the local ?

[David Usherwood]

David Usherwood wrote:
Agree with @lotsaram (though I do feel CAFE isn't quite ready for prime time yet). In particular, if you have a decent size system without skipchecks and feeders, it will be ludicrously slow. That's what needs fixing.

The cube has 8 dimensions, among which the two biggest have 67 and 78 elements, we have 10 years in our year dimension as well, while the system has 13 dimensions. I want to be sure that it's the system size here you are talking about, not the cube size. If so, that means an unused dimension should be removed from a system, otherwise it will ruin all the cubes or views performances. Is that right ?

No!
If you have Rules which have no skipchecks or feeders, then the engine will look at every possible cell in the cube, not just the cells with content. Please follow the Assistance Guidelines and post a selection of the Actual Rules in your cube(s).
I'm going to say that, from your postings, you seem to be at a rather early stage of your TM1 journey - but that's fine, that's what the forum is for.