Performance issue with pivot table.

[#1]

We have a pivot table which display customer purchase on each date. something looks like this:

	Customer 1		Customer 2		Customer 3
Country	USA		USA		Asia
Sales code	a		s		ll
10/08/14	f	0	1	0	f	2
11/08/14	0	10	a	0	0	3
12/08/14	0	2	1	0	1	0
13/08/14	f	1	1	30	a	1
14/08/14	0	0	6	2	s	5
15/08/14	0	0	7	3	c	6

We already spend a lot of time on doing this control to make it beautify and functional but our user hate it. one simple reason is the control is super slow.....

We already reduce the calculation in the table, cutting down on numbers of records, simplify the display by show/hide some columns..... but still, it is very slow.... Is there anything we can do to improve on the performance of this control???

[mvanlutterveld]

Hi,

It’s difficult to give an out of the box solution for your performance problems, so I try it with some general tips.

• Avoid using calculated dimension. If you have those in your current pivot, move the calculation to the script
• Keep your expression as lean as possible. Try to prepare the expressions as much as possible in the script
• Show only those records needed by the end user. For example: If the end user only uses the pivot to analyse figures of last month, don’t show more than that of data.
• If you use data stored in several tables take a look at the efficiency of the data model

I hope this will help you and your end users.

[jagan]

Hi,

How many Rows and Columns are there in your Pivot table?  If data is huge then you will get the issues.  What are your expressions and dimensions? 

Regards,

Jagan.

[#4]

normally we display 10 to 60 customers, up to 2 years of dates.....

the difficult part is they wants to see ALL the information in ONE control. So we have the customer settings such as customer country, sales associate.... Sales setting such as allowance, limit... Date setting..... so on and so on..... Besides those, we have to highlight some figures which is over limit.... underline the the Sales date....

All the information, from all tables.... set analysis...  calculated dimensions to Hide columns.... very bad performance.....

[mvanlutterveld]

Hi,

The amount of data the pivot gives, looks to me as far too much.  Not only from size perspective of the pivot (#rows and # columns)  but also from information perspective (data translated into meaningful values). Can’t you add some objects which the end users can use to perform their first analyses and give the pivot a means of drilling down to exceptional regions (values needing the end users attention). For example: Trend charts, Top X, Bottom X, etc…. The end user first analyses for the customer or country of interest and as a second step use the pivot to dive into the areas of interest on data level.

Maybe (it’s just an idea) you can create a few mockup app as an alternative for this huge pivot and present this to a few key users to show the possibilities with this data set.

Cheers,

Michiel