InfoQube IM - Community

Submitted by markfoley on 2009/06/17 23:16

There seem to be some pretty serious limits around number of records that can be handled in pivots. I think it's because of length limitations of the querystring, and the fact the query lists every item individually eg In(123,124,125 etc). Not sure what the actual character limit is, but replicate what I'm talking about with these steps:

a) Create a numeric field and put it on a grid.

b) Enter '1' as the value, cut and paste so you've got 10 or so records.

c) Go Grid->Create Pivot and just drag the field over so it gives you a simple 'sum'. Sum should be 10, or whatever count of records is shown at the top of the tab.

d) Now, copy those rows as XML and paste them a number of times. If you have, say, 30, it's fine and the sum keeps up with the number of rows. Around 60 or so, your sum will top out which means not all rows are being included.

I expect this number of rows would be even less if you were using IQ a lot and your item references were 4 or 5 digits long...

Comments

Aside from those performance issues, I believe this would be a necessity to fix for the next build. If people are going to use IQ to summarise information (and I've heard it mentioned that millions of rows can be used) this odd limitation will have to be fixed at least to allow a few thousand rows.

Can anyone confirm this bug for me? If it doesn't happen with 100 rows, put a few hundred more...

Humm... I repeated your exact steps and went all the way to 500 items and it worked perfectly.

What versions of the OWC are you using (Office 2000, XP, 2003) ?

The about box of the web component says: Office 2003, v12.0.0.6211 from 2006.

Now I look harder, it seems to be a refresh bug. When I went freshly into IQ into the one where I had demonstrated earlier, the graph then reflected the 154 items. I copied all, pasted again and refreshed the graph, it was ok and reflected 308 items. Copied all, pasted, refreshed, and it remained at 308 no matter what I did to refresh (F5, hitting the exclamation mark button, etc).

However either closing the chart tab and reopening it, or restarting IQ, causes it to now reflect the right amount!

At about 23000 it now maxes out which is pretty acceptable for most uses. Would need to be looked at going forward if the intention was to deal with 'millions of rows' as I've seen discussed around the place.

Can you replicate the refresh bug? It can be replicated by making up 1000 records, repeating the cycle of pasting a new lot, and returning to the grid and refreshing. I've done it in different situations a number of times now.

Some history:

A while back, the pivot were using the same source as the grid. In this case, any number of items should work, limited ony by memory and internal OWC limits
An issue was brought that users wanted pivots to reflect displayed items (fully qualified items, context parents, subs). So the source was changed to be a list of items. As the pivot source probably has max length of 64000 chrs, I'm not surprised to see it maxing out. Also, way of doing requires a refresh to the grid (and a grid save if the pivot is already opened)

An option must be added to switch between the two.

I hope this helps

Interesting!

I had a bit of a look into the components, and it looks like they are discontinued and not in Office 2007. I figure as time passes people will only have 2007 installed (much as I dislike it!) and won't be able to use the OWC features.

http://en.wikipedia.org/wiki/Office_Web_Components

What are your thoughts on what you might use next? Are there any 3rd presentation components that might be suitable?

From an interface point of view the type of thing that would be cool is something along the lines of Tableau (an analysis tool) at www.tableausoftware.com. Clean interface and powerful charting, though I couldn't see what components they were using.

Cheers

There are other similar component that could be integrated, but MS has guaranteed availability of OWC for another 10 years and for Office 2007 and up, it is a simple download to install and use. Checkout OFFICE 2007.

Ten years! That's pretty good, and it provides plenty of time for migration later... :)