Performace issues in pivot tables.

[#1]

Hi All,

I have reports in which I have many pivot tables. I have a perfect start schema in my data mdel with 2 facts tables(which are concatenated) and 10 dimension tables who are associated with fact tables with their primary keys.

I have used many inline views in script for fixing rows of pivot tables. Other than that I am using valuelist, num function , rank and aggregation functions, big nested if else loop in multiple expressions. I am facing big performance issues in my report. Kindly suggest major reasons for bad performace as we will try to remove the worst thing we are using now.

Regards,

Kirti

[deepakk]

HI Kirti,

Try the following activties.

1) avoid writing condition at dimesnion level. Used it in expression even if you have to use it in 10 expression.

2) Try to calculate as many as field at script level. Even if you have to combine two table for it.

     eg:  in expression if(Type=A,

                                if(Date1 >vFromdt,Amt), if(Date2 > vFromDt,Amt).

       now in script.

                        If(Type=A, Date1, Date2) as NewDate

      in expression  If(NewDate>vFromDate,Amt)

3) Use Set Analysis. avoid righting if statement (in the above case i just gave an example)

Deepak

[#3]

Hi Deepak,

Thanks for help.

But my problem is that these nested if conditions are different in each pivot table as per report requirements. As per my undersatnding I can't write so many if conditions in script.

Also, Please suggest if using variables for few repeated calculations will improve the performance or not?

Please comment something about value list and inline views too.

Regards,

Kirti

[deepakk]

Hi

Can you attach the snap shot of data model and the expression inside pivot table.

variables doesn't  help much in terms of performance.

Are you opening all the pivot table at one time. Try to minimize the object so that all pivot table doesn't get loaded at once.

Try to use multiple box instead on list box. In this way we can avoid the load on the filter objects(if any)

Deepak

[#5]

I can't attach data model due to security reasons in my project. I am using expressions like below:-

1) 

if(RowNo()>0,num(sum({$<QTR_NBR={1},YEAR_CD={'P'},TREND_CAT_CD={'HI'}>}AMT),'$#,##0'),

sum(if(aggr(rank(sum(CNT/3),4),ID_NAME)<51,aggr(num(sum({$<QTR_NBR={1},YEAR_CD={'P'},TREND_CAT_CD={'HI'}>}AMT),ID_NAME),'$#,##0'))))

2)

if([Measures] ='Membership',NUM(sum({$<QTR_NBR={1},YEAR_CD={'P'}>}CNT/3),'#,##0'),

if([Measures]='Amount',num(sum({$<QTR_NBR={1},YEAR_CD={'P'},TREND_CAT_CD={'HI'},CAT_CD={'AC'},IND ={'*'}-{'Y'}>}AMT),'$#,##0'),

if([Measures]='Admits',NUM(sum({$<QTR_NBR={1},YEAR_CD={'P'},TREND_CAT_CD={'HI'},CAT_CD={'AC'},IND ={'*'}-{'Y'}>}CNT),'#,##0')

where measures is an inline view.

For now, we are loading almost 5-15 pivot tables at the same time. We will suggest client to decrease this number in order to improve performance but we might need to display maximum 10 pivot tables at one point of time.

Please suggest about inline views and valuelist considering performance issues.

Regards,

Kirti

[deepakk]

Hi,

in the first case if all the four are coming from the same table or if you can try to combine in this into one table  you can avoid the ste analysis.

if( QTR_NBR=1 and YEAR_CD= 'P'  and TREND_CAT_CD = 'HI' , Amt) as NewAmt,

In Expresion in becomes Sum(NewAmt).

Any particular reason for using num formating inside expression. You can use the Number format tab.

In the second case too it will be good you can create fields from script like i have mention in the above case.

if not the second case can be written as (without if statement)

sum({$<QTR_NBR={1},YEAR_CD={'P'} ,Measures = {'Membership'}>}CNT/3) +

sum({$<QTR_NBR={1},YEAR_CD={'P'},TREND_CAT_CD={'HI'},CAT_CD={'AC'},IND ={'*'}-{'Y'} , Measures = {'Amount'}>}AMT) + and so on....!!

Calculate ateh fields at script level even if you have to didvide a number by 3.

Inline views are created in script and hence it will not have a performance issue at front end , valuelist is used inside expression hence it will have an impact at front end.

Deepak

[deepakk]

hi

forgot add a point , Use only the required fields.

eg: if you have 20 fields inside a table and in the application if you are using only 4 fields  then comment the rest of the fields.

Deepak

[#8]

Hi Deepak,

Thanks a lot!! Great suggestions!!

Now situation is , first expression what I have given u is only for first expression of pivot table. Subsequent expressions are like:-

if(RowNo()>0,num(sum({$<QTR_NBR={2},YEAR_CD={'P'},TREND_CAT_CD={'HI'}>}AMT),'$#,##0'),

sum(if(aggr(rank(sum(CNT/3),4),ID_NAME)<51,aggr(num(sum({$<QTR_NBR={2},YEAR_CD={'P'},TREND_CAT_CD={'HI'}>}AMT),ID_NAME),'$#,##0'))))

if(RowNo()>0,num(sum({$<QTR_NBR={3},YEAR_CD={'P'},TREND_CAT_CD={'HI'}>}AMT),'$#,##0'),

sum(if(aggr(rank(sum(CNT/3),4),ID_NAME)<51,aggr(num(sum({$<QTR_NBR={3},YEAR_CD={'P'},TREND_CAT_CD={'HI'}>}AMT),ID_NAME),'$#,##0'))))

..... quarter number varies for 3 more quarters and later on year code varies in 4 expressions for current year like below:-

if(RowNo()>0,num(sum({$<QTR_NBR={1},YEAR_CD={'C'},TREND_CAT_CD={'HI'}>}AMT),'$#,##0'),

sum(if(aggr(rank(sum(CNT/3),4),ID_NAME)<51,aggr(num(sum({$<QTR_NBR={1},YEAR_CD={'C'},TREND_CAT_CD={'HI'}>}AMT),ID_NAME),'$#,##0')))).......

after this, following expressions are division or subtraction of first 8 expressions and list goes on. I have total 24 expressions in 1 pivot table.

Also, I have extra inline views in scripts, like 10 inline views with 2 values which can be replaced by only 2 inline views. But doing this leaves me with lots of rework. Please suggest if decreasing these inline view will some help in performance or I can do that later as it wont help much.

Regards,

Kirti

[#9]

Please suggest how to handle above situation in scripts. In other pivot tables filters would change, I will have different trend code, instead of HI, HO will come. If not filters then values will change, instead of amount, we might pick membership , days etc.

Also, we have used num function only in those cases where we need different formats in different rows like integer in first number, float value in next and the money format in other rows.

We are loading only required fields in our file for now. Unused fields are already commented. Please suggest if we are missing something else too.

Thanks and Regards,

Kirti Kakkar