Limit a Pivot due to Performance Problems

[chriys1337]

Hi all,

I am building a "Self-Service" Pivot Table,

where the user can select 1 to 35 dimensions to be shown and there is (just) one expression.

Depended on how many dimensions the user has selected, the more bigger the pivot gets.

This pivot has a very bad performance on the Access Point and I wanted to ask for help, something like a condition (under Layout, Condition) to check how many datasets / rows are displayed. The question is, how should the condition look like:

I have tried to use getpossiblecount(Dim A) * getpossiblecount(Dim B) * etc.

and also with count(aggr(1,[Dim A],[Dim B))

I am not really coming further.  Did anybody already touch this performance topic of a self-service pivot, who could please give a advice?

Kind Regards and thank you!

[sunny_talwar]

It might be easy to help if you are able to share a sample?

[chriys1337]

I have uploaded one small application, which is showing the starting position.

Of course this example does not have any performance issue.

But my question is how can I find a limitation / expression, which is counting the data rows.

And if the data rows are exceeding a specific amount, than the table should not be displayed anymore.

[sunny_talwar]

May be try this expression

=Sum(Aggr(If(Sum(Performance) <> 0, 1, 0), $(=Concat('[' & Field_Display_Name & ']', ', '))))

The above will give you the number of rows that are getting displayed in the chart

[chriys1337]

So basically, you used

sum(aggr(1,DimA,DimB).

what is aggr(1,

doing?

Can you explain a little bit please?

[sunny_talwar]

Aggr(1? Where do you see that in my expression? I am confused

[chriys1337]

(If(Sum(Performance) <> 0, 1, 0),

if the performance is not 0, than 1

so 1

so -> sum(aggr(1,DimA....)

[sunny_talwar]

I guess, you can put it that way... but I wouldn't use straight 1 because if you have set analysis in your expression... using just 1 won't be enough... I would use the expression itself and check if it is <> 0.

Anyways, what is the part you don't understand about the expression? I am still not sure I understand what is wrong with this?

=Sum(Aggr(If(Sum(Performance) <> 0, 1, 0), $(=Concat('[' & Field_Display_Name & ']', ', '))))

[chriys1337]

Hi Sunny,

thanks,

my question is:

1. What is sum(aggr(expression,dim1,dim2,dim3) doing? why not sum(aggr(dim1,dim2).

2. I also don't get why are you checking if the performance is not 0? (expression)

Thx a lot.

Chris

[sunny_talwar]

What does this even mean?

sum(aggr(dim1,dim2)

If I know what it means, I might be able to let you know... but I have no idea what it does....

For your second question...., I only want to sum when your expression has a value... that is why checking for 0.

Is it not giving you what you want? I am not sure what the issue is...