Performance with VIEW in Worksheet

[Willi]

Hello,

working with TM1 9.5.2 and Excel 2010 I have a Cube with 4 Dimensions:

- Year (22 Elements)
- Deliverer (305 Elements)
- Article (1231 Elements)
- Measure (22 Elements)

The first 3 Dimensions has a General Consolidation like "All Elements".

One Measure contains a 1 or 0 for Zerosuppression because the customer don't want to see the sum of every Articles in the Consolidation of the Deliverer. Now I have a Worksheet with this Activeform Reference:

Code: Select all

=TM1RPTVIEW("Cube:3"; 1; TM1RPTTITLE("Year";$D$11); TM1RPTTITLE("Measure";$E$17);TM1RPTFMTRNG;TM1RPTFMTIDCOL)

With the Year pointing to a selected Year and the Measure pointing to the ZeroSupression-Column. Than I have the following 2 Dimensions to open the Active Form:

Code: Select all

=TM1RPTROW($C$9;"Deliverer";"Standard")
=TM1RPTROW($C$9;"Article";"Standard")

And this VIEW for the Measures:

Code: Select all

=VIEW($D$12;$D$11;"!";"!";"!")

$D$11 pointing to the seletcetd Year and $D$12 to the Cubename.

I started to develop the Sheet with the generate Active-Form from a Cubeview and only the Zero-Supression-Column. That works like a charme But as soon as I insert the VIEW and Display one Column over it the Performance grows from a couple of Seconds to several minutes.

Where can I start to look at?

[Wim Gielis]

Hello

So in the rows, you have 2 dimensions stacked: Deliverer (305 Elements) and Article (1231 Elements).
Am I correct in thinking that this is the source of your problem - almost 400,000 combinations times about 20 columns ?
What selections do you have in the rows ?

[Willi]

I guess this is the Problem. But the result after Zerosupression is round abbout 2000.

The starting-Selection is all Elements

[Wim Gielis]

But to know the zero suppressed rows that result, all lines have to be calculated. Times 20 columns.
I guess that is the problem that you need to solve.

How many n-elements do you have per dimension?
What happens if you only work with subsets at n-level?
Do you have rules calculated cells?

And so on.

Needless to say, this is something I would never do in Excel. Terrible performance (understandable) and even if you have 2000 rows times 20 columns, who can work with that ?

[lotsaram]

Why do you need the view formula? In this instance a hardcoded server:cubename reference will work just as well, probably better.

It is quite a small cube 20 x 300 x 1230 x 20 around 1.5 x 10^8 cells (tiny actually). If creating a view with ! on 3 dimensions is taking a long time then I assume there must be quite some heavy rule calculations going on and quite possibly some over feeding as well. To improve the performance first check that cells are not fed unecessarily and also if there is a more efficient way to do some of the calculations.

[Willi]

n-Elements are Number of Elements minus the General Consolidation except the MEasure-Dimension wich has only N-Elements.

I cannot make the first selection smaller because the first view should be all the metioned combination. But in this View I don't Need allt the Measures. There are 10 columns.

But what I'm really wondering about is that the sheet is performing quite well when I have only the 2 TM1RPTROWs and the ZeroSupression-Coolumn. In this Szenario all calculations are done as well. And, to answer lotsaram as well, beside the Zerosupression--rule there is only one other Column Rule-Calculated. And this is only a if some other Column contains some data set it to 1 otherwise to 0. All other Cells are filled by a TI.

I'll give the solution from lotsaram a try and will be back.

Thx

[tomok]

Using a VIEW statement in Perspectives tells TM1 to use a stargate view. What is your VMM setting for that cube? Perhaps you should bump it up to allocate more memory to the stargate view mechanism.

[Willi]

Okay, thx. I'll give that a try, too

[Willi]

Hi,

the solution from lotsaram to work without VIEW just with the Cubename was the best result.

Thx a lot!