RangeSum performance issue

[johngouws]

Afternoon,

I am having a serious performance issue when using RangeSum in a expression. Even when restricting my data set to just over 40000 rows, the chart takes a really long time to resolve. About 3 minutes!! When the model goes into production it will be loading significantly more!! I have already done things like having to select a single 'Client' before the chart displays and calculates, but it actually makes no difference.

The attached example is a simplified example of what I am doing. The example includes a simple expression and has the 'real world' expression commented out.

The "real world" Amount expressions is: =sum({<Acc_GN_ChartID={'60008'} >} Amount).

The "real world" RangeSum expression is: =Rangesum(above(total Sum({<Acc_GN_ChartID={'60008'} >} Amount),0,rowno(total))).

I would appreciate advice on the expression or pointers when I am going wrong.

Thank you,

John 

[fvelascog72 · Accepted solution]

Hi,

Have you think about calculate in script:

FACT:

LOAD

  Date,

  Description,

  Amount,

  if(RowNo()=1,Amount,Amount+Peek(TEST)) as "TEST";

LOAD * INLINE [

Date, Description, Amount,

2014-11-26, Line 1, 70028.79

2014-11-26, Line 2, 341.28

2014-11-26, Line 3, 349.98

2014-11-26, Line 4, 64507.00

2014-11-26, Line 5, 3743.00

2014-12-05, Line 6, -2316.17

2015-01-14, Line 7, -2316.17

2015-02-14, Line 8, -2316.17

2015-03-14, Line 9, -2316.17

2015-04-14, Line 10, -2316.17

];

exit script;

[swuehl]

John,

when you are removing the set analysis from your expressions, it runs in seconds, just by putting in the set expression slows it down to minutes? Is only the rangesum expression affected or also the pure sum expression?

Your sample doesn't show the data model you are using (at least the field Acc_GN_ChartID is not part of your sample, and I assume it's not just part of the fact table). Could you post some more details about your data model?

Or even better, a sample that demonstrates your issue (i.e. there is a significant impact on performance when using the expression with set analysis put in).

[johngouws]

Hi Swuehl,

You are correct, the example is over simplified. I will see how I can get the pre-prod model posted.

Thank you for your reply,

John

[johngouws]

Hi Federico,

It is something worth trying, I am assuming that I will have to get my source data to be sorted Date, Description? That may be easier said that done.

I will get back to you with a result,

John

[Clever_Anjos]

What you are doing is creating an accumulation column, right?

The native way (Accumulation/Full Accumulation) does not fit your needs?

[johngouws]

That is what I am trying to do,

If you select 'Full Accumulation" on the Amount Expression, it makes no difference. 

[Clever_Anjos]

Would you mind sharing a sample of your app? It should work as designed